Sunday, 23 October 2016

BizTalk Server - Dynamics CRM Online / On Premise CRM Paging Cookie


Problem Scenario

·          While communicating with CRM Entities using Retrieve Multiple operation using request response pattern there  are some scenarios where you need to process bulk request queries like using pattern matching Contains Data,  Not null, etc.

       These query results bulk data to BizTalk as a response in XML Format with all the details.  

        Scenario 1: Assume you need to process 10,000 records by interacting with CRM.
             OR
        Scenario 2: Single fetch Query, Expression can get you up to 5,000 records.

       Handling these huge file in BizTalk at once could result in performance impact. Now think of scenario when  you have to process multiples request of such huge records. 5000 records XML data can be up to 500 MB plus,  depending on the data you retrieve like column / attributes.

Solution


·         In such scenario using the below pattern will help you to process the limited records with multiple calls without overloading BizTalk with huge data.

·         There are multiple ways with which you can query CRM
  1. Retrieve Operation
  2. Retrieve Multiple Operation

·         Some Points one needs to consider when using Retrieve, Retrieve Multiple Operation for retrieving data from CRM.


         Retrieve Operation


  •           Retrieves Only one record using the parameters like Entity Name, GUID, Columns of the record.
  •           Using this we cannot retrieve multiple records or related data.
  •          This will not support for using where clauses.     
  •           Where there is a scenario to retrieve single record, with known GUID we could use this approach.




                                                               Fig – Retrieve Operation

The below request will return you all the column for the GUID matching record.

 <Retrieve xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services">  
      <entityName>account</entityName>  
      <id>1D30CA6E-E355-E511-80F2-C4346BC5A780</id>  
      <columnSet xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">  
       <b:AllColumns>true</b:AllColumns>  
       <b:Columns xmlns:c="http://schemas.microsoft.com/2003/10/Serialization/Arrays">  
         <c:string>name</c:string>  
       </b:Columns>  
      </columnSet>  
   </Retrieve>  

                                                                  Sample XML Retrieve Request

Retrieve Multiple Operation


  • Retrieve Multiple supports many other options / several overloads like QueryExpressionFetchExpression,QueryByAttribute.
  • We can use this operation to retrieve multiple, related entity records in a single request using where clauses, with many other parameters filtering options.
  • However, it has some limitation like single Retrieve Multiple Request can get you some limited records up to 5000, this is configurable in CRM On premise but not in Online.





                                              Fig – Retrieve Multiple Operation

Sample Retrieve Multiple Request from BizTalk to CRM in two formats FetchExpression, QueryExpression for the same query.

      The below sample basic query will return all the entity records with all the columns / attributes specified, maximum entity records can be 5000 as it’s a limit at CRM.

 <RetrieveMultiple xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:ns2="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:ns0="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:ns3="http://schemas.microsoft.com/xrm/2011/Metadata" xmlns:ns5="http://schemas.microsoft.com/2003/10/Serialization/" xmlns:ns1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" xmlns:ns4="http://schemas.microsoft.com/xrm/2011/Contracts">  
      <query i:type="b:FetchExpression" xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">  
       <b:Query>  
       &lt;fetch version=&quot;1.0&quot; output-format=&quot;xml-platform&quot; mapping=&quot;logical&quot; distinct=&quot;false&quot; &gt;  
      &lt;entity name=&quot;sms_registrationpage&quot;&gt;  
      &lt;attribute name=&quot;sms_registrationpageid&quot; /&gt;  
      &lt;attribute name=&quot;sms_firstname&quot; /&gt;  
      &lt;attribute name=&quot;sms_mobile&quot; /&gt;  
      &lt;attribute name=&quot;sms_lastname&quot; /&gt;  
      &lt;attribute name=&quot;sms_ismarried&quot; /&gt;  
      &lt;attribute name=&quot;sms_gender&quot; /&gt;  
      &lt;attribute name=&quot;sms_emailid&quot; /&gt;  
      &lt;attribute name=&quot;sms_country&quot; /&gt;  
      &lt;attribute name=&quot;sms_age&quot; /&gt;  
      &lt;order attribute=&quot;sms_firstname&quot; descending=&quot;false&quot; /&gt;  
      &lt;/entity&gt;  
      &lt;/fetch&gt;  
       </b:Query>  
      </query>  
    </RetrieveMultiple>  

                             Fig- Retrieve Multiple Using FetchExpression

 <RetrieveMultiple xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:ns2="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:ns0="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:ns3="http://schemas.microsoft.com/xrm/2011/Metadata" xmlns:ns5="http://schemas.microsoft.com/2003/10/Serialization/" xmlns:ns1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" xmlns:ns4="http://schemas.microsoft.com/xrm/2011/Contracts">  
      <query i:type="b:QueryExpression" xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">  
       <b:ColumnSet>  
         <b:AllColumns>false</b:AllColumns>  
         <b:Columns xmlns:e="http://schemas.microsoft.com/2003/10/Serialization/Arrays">  
          <e:string>sms_registrationpageid</e:string>  
          <e:string>sms_firstname</e:string>  
          <e:string>sms_mobile</e:string>  
          <e:string>sms_lastname</e:string>  
          <e:string>sms_ismarried</e:string>  
          <e:string>sms_gender</e:string>  
          <e:string>sms_emailid</e:string>  
          <e:string>sms_country</e:string>  
          <e:string>sms_age</e:string>  
         </b:Columns>  
       </b:ColumnSet>  
       <b:Criteria>  
         <b:Conditions/>  
         <b:FilterOperator>And</b:FilterOperator>  
         <b:Filters/>  
       </b:Criteria>  
       <b:Distinct>false</b:Distinct>  
       <b:EntityName>sms_registrationpage</b:EntityName>  
       <b:LinkEntities/>  
       <b:Orders>  
         <b:OrderExpression>  
          <b:AttributeName>sms_firstname</b:AttributeName>  
          <b:OrderType>Ascending</b:OrderType>  
         </b:OrderExpression>  
       </b:Orders>  
       <b:PageInfo>  
         <b:Count>0</b:Count>  
         <b:PageNumber>1</b:PageNumber>  
         <b:PagingCookie i:nil="true"/>  
         <b:ReturnTotalRecordCount>false</b:ReturnTotalRecordCount>  
       </b:PageInfo>  
       <b:NoLock>false</b:NoLock>  
      </query>  
    </RetrieveMultiple>  

                             Fig- Retrieve Multiple Request Using QueryExpression

        

       CRM Paging Cookie


There can be some scenario where you need to query the CRM records and process the response data records in set of records. You can use the paging cookie feature to make paging in an application faster for large datasets. When you use the paging cookie feature when querying a set of records, the result contains a value for the paging cookie. To improve system performance, you can then pass that value when you retrieve the next set of records. 

  Scenario 1:

Assume that an entity has 10,000 records and BizTalk has to query to get those records and process individual records within a loop. Here we know that CRM will return maximum of 5000 records in single response. In this case we can split it up with two requests, set 1st to get 1st 5000, 2nd set to get next another 5000 records and then process them in BizTalk. The downside here will be that file with 5000 records will be size approx 300 – 500 MB depending on the attributes selections.


Scenario 2:

Assume, an entity has 10,000 records, BizTalk has to process them individually. Here the requirement is to query a set of 1000 records at a time, process them in BizTalk and then go for next set of batch of records and so on. So BizTalk has to query CRM 10 times to get 1000 records every time to process 10,000 records. Going with this approach there will be less load in BizTalk as we restricted the batch size.

The approach discussed in Scenario 2 is more performance oriented and yield better results.



Demo


                                                          Fig – CRM Entity Records total 4 records.


·         Page Cookie using FetchExpression



                                                                              Fig – Mapping to FetchExpression

  
 <xsl:template name="FetchExpression">  
   <xsl:param name="Count" />  
   <xsl:param name="pageIndex"/>  
   <xsl:param name="pageCookie" />  
   <RetrieveMultiple xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services">  
    <query i:type="b:FetchExpression" xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">  
     <b:Query>  
      &lt;fetch version=&quot;1.0&quot; output-format=&quot;xml-platform&quot; mapping=&quot;logical&quot; distinct=&quot;false&quot; count=&quot;<xsl:value-of select="$Count"/>&quot; <xsl:if test="$pageIndex">  
       page="<xsl:value-of select="$pageIndex"/>"  
      </xsl:if> <xsl:if test="$pageCookie">  
       paging-cookie="<xsl:value-of select="$pageCookie"/>"  
      </xsl:if> &gt;  
      &lt;entity name=&quot;sms_registrationpage&quot;&gt;  
      &lt;attribute name=&quot;sms_registrationpageid&quot; /&gt;  
      &lt;attribute name=&quot;sms_firstname&quot; /&gt;  
      &lt;attribute name=&quot;sms_mobile&quot; /&gt;  
      &lt;attribute name=&quot;sms_lastname&quot; /&gt;  
      &lt;attribute name=&quot;sms_ismarried&quot; /&gt;  
      &lt;attribute name=&quot;sms_gender&quot; /&gt;  
      &lt;attribute name=&quot;sms_emailid&quot; /&gt;  
      &lt;attribute name=&quot;sms_country&quot; /&gt;  
      &lt;attribute name=&quot;sms_age&quot; /&gt;  
      &lt;order attribute=&quot;sms_firstname&quot; descending=&quot;false&quot; /&gt;  
      &lt;/entity&gt;  
      &lt;/fetch&gt;  
     </b:Query>  
    </query>  
   </RetrieveMultiple  
 </xsl:template>  


                                               Fig – XSLT Used in Scripting




Fig – End to End Output FetchExpression Request, Response with Count Set to 2





·         Using Page Cookie using QueryExpression



Fig – Mapping to QueryExpression Request, Response with Count Set to 2


 <xsl:template name="QueryExpression">  
   <xsl:param name="Count" />  
   <xsl:param name="pageIndex"/>  
   <xsl:param name="pageCookie" />  
   <RetrieveMultiple xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services">  
    <query i:type="b:QueryExpression" xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">  
     <b:ColumnSet>  
      <b:AllColumns>false</b:AllColumns>  
      <b:Columns xmlns:e="http://schemas.microsoft.com/2003/10/Serialization/Arrays">  
       <e:string>sms_registrationpageid</e:string>  
       <e:string>sms_firstname</e:string>  
       <e:string>sms_mobile</e:string>  
       <e:string>sms_lastname</e:string>  
       <e:string>sms_ismarried</e:string>  
       <e:string>sms_gender</e:string>  
       <e:string>sms_emailid</e:string>  
       <e:string>sms_country</e:string>  
       <e:string>sms_age</e:string>  
      </b:Columns>  
     </b:ColumnSet>  
     <b:Criteria>  
      <b:Conditions/>  
      <b:FilterOperator>And</b:FilterOperator>  
      <b:Filters/>  
     </b:Criteria>  
     <b:Distinct>false</b:Distinct>  
     <b:EntityName>sms_registrationpage</b:EntityName>  
     <b:LinkEntities/>  
     <b:Orders>  
      <b:OrderExpression>  
       <b:AttributeName>sms_firstname</b:AttributeName>  
       <b:OrderType>Ascending</b:OrderType>  
      </b:OrderExpression>  
     </b:Orders>  
     <b:PageInfo>  
      <xsl:choose>  
       <xsl:when test="$Count">  
        <b:Count>  
         <xsl:value-of select="$Count"/>  
        </b:Count>  
       </xsl:when>  
       <xsl:otherwise>  
        <b:Count>0</b:Count>  
       </xsl:otherwise>  
      </xsl:choose>  
      <xsl:choose>  
       <xsl:when test="$pageIndex">  
        <b:PageNumber>  
         <xsl:value-of select="$pageIndex"/>  
        </b:PageNumber>  
       </xsl:when>  
       <xsl:otherwise>  
        <b:PageNumber>0</b:PageNumber>  
       </xsl:otherwise>  
      </xsl:choose>  
      <xsl:choose>  
       <xsl:when test="$pageCookie">  
        <b:PagingCookie>  
         <xsl:value-of select="$pageCookie"/>  
        </b:PagingCookie>  
       </xsl:when>  
       <xsl:otherwise>  
        <b:PagingCookie i:nil="true"/>  
       </xsl:otherwise>  
      </xsl:choose>  
      <b:ReturnTotalRecordCount>false</b:ReturnTotalRecordCount>  
     </b:PageInfo>  
     <b:NoLock>false</b:NoLock>  
    </query>  
   </RetrieveMultiple>  
  </xsl:template>  

                                              Fig – XSLT Used in Scripting



Fig – End to End Output QueryExpression Request, Response with Count Set to 2


            


MSDN Sample Using C#


Sample: Use QueryExpression with a paging cookie: https://msdn.microsoft.com/en-in/library/mt269606.aspx 


Sample: Use FetchXML with a paging cookie: https://msdn.microsoft.com/en-us/library/gg328046.aspx 

Building the Sample


Follow the below-detailed steps to Run the below sample.
  •  It has some pre requisites if you are checking this article for the 1st time, Refer the below see also sections articles for related BizTalk artifacts for more details to run this sample End to End.
  • To download the sample BizTalk Application for using Dynamics CRM Online / On Premise CRM Paging Cookie, Refer here MSDN Code Samples  
  •  Zip File Includes: BizTalk Sample Solution, Application Port Bindings, Sample Retrieve Multiple Request, Response files for FetchExpression, QueryExpression.


 


Conclusion


·         In this article you have learn on how to utilize the CRM Online / On premise Paging, Paging Cookie features.
·         Using this technique , you can minimize the load on BizTalk, when you are working with bulk data processing as Retrieve Response.
·         You could choose which query expression you wish to go with depending on the scenarios, both are similar the way we write expression is difference.

     See Also



      Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki