FetchXML versus Query Expression

Microsoft Dynamics CRM 4 provides two main mechanisms for querying data from Microsoft Dynamics CRM, FetchXML and QueryExpression.  In general, it is recommended to use QueryExpression over FetchXML because of its better performance and strongly typed results.  But, FetchXML is handy when you need attributes from multiple entities in a single query.  This blog will discuss each type of query and provide an example of each to return the same result.

QueryExpression is class based and is easy to use, although you can only query one entity type at a time.  Therefore, if you need to get data from a parent entity, such as a contact, and then get data from a child entity, such as a custom product entity, you have to perform two queries.  First you must retrieve the contact to get access to its attributes, then you must retrieve the product using the contact id that links the contact to the product.  With QueryExpression, you can specify the attributes you want returned, or indicate you want all attributes returned, you can specify “and” conditions or ”or” conditions, and you can specify a sort order.  To execute a QueryExpression query:

  • Call the Retrieve or RetrieveMultiple methods on the CrmService web service
  • Receive a BusinessEntityCollection containing the results of the query

With FetchXML you can query multiple entity types at the same time as long as there is a relationship between the types.  You can retrieve attributes from multiple entities in one query in this fashion. To execute a FetchXML query:

  • Create a fetch string specifying the query and pass it to the CrmService web service’s fetch method
  • The fetch string contains XML, which specifies the query criteria

Example of QueryExpression:

      QueryExpression query = new QueryExpression();
      query.EntityName = EntityName.contact.ToString();
      ColumnSet columns = new ColumnSet();
      columns.Attributes =
                         new string[] { “contactid”, “lastname”, “firstname” };

      ConditionExpression whereClause = new ConditionExpression();
      whereClause.AttributeName = “lastname”;
      whereClause.Operator = ConditionOperator.Equal;
      whereClause.Values = new string[] { “Jones” };

      FilterExpression filter = new FilterExpression();
      filter.FilterOperator = LogicalOperator.And;
      filter.Conditions = new ConditionExpression[] { whereClause };

      OrderExpression orderBy = new OrderExpression();
      orderBy.OrderType = OrderType.Descending;
      orderBy.AttributeName = “createdon”;

      query.ColumnSet = columns;
      query.Criteria = filter;
      query.Orders = new OrderExpression[] { orderBy };

BusinessEntityCollection retrieved =

Example of FetchXML:

string fetch = @”
   <fetch mapping=””logical””>
       <entity name=””contact””>
            <attribute name=””contactid””/>
            <attribute name=””lastname””/>
            <attribute name=””firstname””/>
            <order attribute=””createdon””/>
                     <condition attribute=””lastname”” operator=””eq””
           string result = service.Fetch(fetch);
   catch(System.Web.Services.Protocols.SoapException se)
            // handle exception

Fast Track Your Enterprise CRM Selection

CRM systems, in one form or another, have been around for as long as selling has.  And, to tell you the truth, they haven’t changed much over the past decade or so.  Technologies and vendors may come and go, but leads, accounts, contacts, pipelines, and so on are constants.  That’s the way sales works after all.  What this means is that vendors already have their required functions, features and application layout identified.  So they can spend the bulk of their time slickening their technology; figuring out ways of streamlining the sales process for salespeople; and providing relevant information for executives. 

So, if we assume that most vendors have the features, why do so many CRM software selection processes begin with matrices of 1000 matrixor so features, when we all know that a vendor in the running has what we need?  And if something like pie charts vs. bar graphs makes the list as “high”, you’re left with your eye on something other than the ball, the scales are tipped to pet features, and you’re left trying to pry Act! or Excel contact lists (or whatever) out of your sales-peoples’ hands and they say big brother’s cramping their style.  6 months down the road you’re trying to explain why adoption is such a challenge.  Was the wrong software selected?  Many wise analysts then say:

“it’s got nothing to do with the software, it’s…lack of executive support; or poorly defined business processes; or looking back vs. looking forward; or not tying use to compensation”.

Of course, these things have left many a CRM project DOA, but picking the right software does matter.  If you focus on a technical fit for what you’re trying to accomplish as opposed to consensus building with overwhelming weighted matrices, you can get rid of lots of noise and make a good decision.  Here are things to weigh heavily, in order of importance:

Think Vendor Before Software

There are literally thousands of CRM vendors, but in the enterprise space they can be whittled down fast.  (These folks, Gartner,  and others provide lots of insight).  If you’re an SAP or Oracle Applications Shop (that includes Siebel), the playing field is NOT level (see Be Realistic… and Integration… and Data Conversion… below).  Save yourself some time and focus on implementing your CRM module effectively.  Those camps aside, if you go with a small or middle market vendor, or one that’s losing market share, you run risks of:

  • your developers not being able to Google sample code (if you don’t think this is important, ask your developers),
  • poor support,
  • bad/incomplete documentation, or
  • (not least of all) poor perception of quality/or importance of the initiative from your sales force.

Salesforce.com and Microsoft Dynamics CRM are almost always left standing.  It’s hard to justify the exclusion of market leading Salesforce.com (unless you’re in the process of getting rid of it because it’s too expensive), and Microsoft is gaining ground because everyone out there has at least half the Microsoft infrastructure stack in place already (an unfair advantage, but one that shouldn’t really be overlooked).

Selecting an established, top software vendor that tracks with your IT strategy can help your implementation team tremendously and as such reduces your overall risk.

Integration Is Huge

OK, so back to “what you’re trying to accomplish”.  You’ve probably been involved in at least one “360 degrees of the customer” project.  That’s because it’s what everyone needs.  Not least of all sales.  Having the whole picture in one place provides incredible benefits and efficiencies.  So often “integration” is a line item buried in the technical section of a selection matrix that’s covered as an afterthought by techies once the real decision makers have left the room. 

The reality is that the ability to leverage all of the data you have about your customers IS competitive advantage.  And CRM software alone won’t provide that since all the information you need will probably not live in your CRM system.  You won’t regret putting a good amount of early, detailed, thought into how integration could work with each vendor’s solution:

  • What data will not/can not be in CRM
  • do salespeople need it
  •  if so how and where will they see it
  • how much data are we talking about
  • is real-time access required
  • what are performance requirements
  • what changes will be required to my integration architecture to provide this access
  • what programming languages/techniques will we need to know to provide this information

Odds on you’ll spend well over half of your time and money developing and testing integration code when you get into the project even though this will likely represent only a fraction of the functionality.  But those little snippets can make a game-changing difference (e.g. this customer that you’ve called 18 times over the last 5 months and is just about to place a monster order hasn’t paid their bill since last July – they should probably be on another contact list. OR, this customer that has always needed our stuff and was almost broke was just bought by Sun, which was just bought by Oracle and they could probably use about two and a half bajillion of our widgets asap). 

Detailed thinking about how the integration will work with a CRM technology can lead you to the right software and hosting option.

Be Realistic About Your Technology Competency

So maybe you’ve got it down to Salesforce.com and Dynamics CRM.  They’re comparable products, market leaders, they look good, and the sales team could go either way.  But the technologies that can be used to customize and integrate these solutions are quite different.  It’s a good idea to take stock of your technical skills to make sure you understand what development and support will look like.  On the upside, both technologies provide Web service interfaces which provide a good level of interoperability with other systems and a broad range of tools.  From that point forward, however, it’s a good idea to consider your internal development skills. 

Dynamics CRM developers will spend a good amount of time using Microsoft Visual Studio with either C# or VB using a Web service API.  Within the administrative UI, quite a bit of JavaScript is used, iFrames are pervasive, and a good amount of custom code may be implemented with CRM Plug-ins which can be a bit quirky until you get used to them.  The Salesforce (SFDC) model has support for a broad range of programming languages in addition to C# and VB.  Developers have a choice of online or an Eclipse-based tool with which to develop.  SFDC has a relatively mature cloud development model relying on their Apex programming language (similar to C# and Java) which does have an associated learning curve.  Of course, both tools are Web-based and as such require developers to have HTML and JavaScript (JScript) skills. 

So, if you have MS developers and go with MS CRM you’ll have an easier curve and you’ll probably find that your developers are comfortable and productive quicker.  If your developers are a curious bunch that tends to sink their teeth into new things and have a demonstrated ability to produce as they learn, they’ll like SFDC.

In the realm of mature, best of breed CRM packages, it’s impossible to say that one is the best in all situations and for all organizations, but you can pick the one that’s best for you.  Being realistic about your technology competency can drive you in that direction.

Don’t Underestimate Data Conversion

Data conversion effort and approaches seem to consistently get under-sold and under-thought.  If you’ve boiled it down to either MS Dynamics or SFDC, you’ll probably find that the online conversion tools will not work for you in real world scenarios, which will leave you with Scribe on the MS side and the APEX data loader for SFDC, which are comparable products.  Starting early and getting a solid idea of what you’re up against (in terms of complexity and performance) will pay off big, especially if you’re able to do some strawman testing as part of the software selection/evaluation process.  Performance for each of these conversion tools will not come close to approaching performance of loading data with straight SQL– an option that’s only available to the best of my knowledge with the MS on-premise tool.  

So just consider, for example, that if you’ve got 2 million records and early testing on comparable hardware to your production system is indicating .5 seconds per customer record with a tool like Scribe or Apex DL, you’re looking at 12 days for a one time conversion.  Compare that to a similar load using SQL and you’ll see that this can be very significant.  Another thing to remember, you’ll have to load data more than once: you should account for associated entities like opportunities and orders, mistakes, updated/delta data, multiple environments, failures due to inconsistent data, mapping issues, network and hardware failures, lack of disk space, etc.., 

If you give data conversion a good deal of detailed thought early on, you can significantly reduce downstream risks by understanding technology options, planning the project effectively, and synchronizing conversion milestones with other efforts.

Be Creative With The Feature Comparison

Of course, the process of feature comparison is important.  But software can be weak in surprising areas; and limitations can be hard or impossible to uncover when reviewing feature matrices.  A better approach, and one that can help those involved in the selection process visualize strengths and weaknesses, is to create demonstration scenarios that focus on business processes and usage patterns that are unique to your business.  SFDC and MS Dynamics are both very easy to customize and demonstrate, and both offer online versions for demonstration purposes.  Investing a few days early on to identify, configure, and review CRM use cases specific to your business is well worth the effort.

Focusing your efforts on demonstrating software as opposed to creating detailed matrixes can help your users visualize the future state and can underscore software weaknesses that wouldn’t otherwise be obvious.

Narrowing your CRM software search early to top players can save lots of time, keep momentum up, and set you up with technology that you can sink your teeth into.  Whereas going through the motions – that is, letting a weighted matrix make the decision for you – can result in skewed priorities, lack of clear focus, and could result in you and your company struggling with mismatched technology.  Detailed technical thought during the selection process takes time and effort but can ensure that you get on and stay on the right CRM track for your organization.