CRM Online issues with oData, and solution with FetchXML

[achinm45]

Problem Statement:

There is a problem which has been reported by few businesses when they try to create reports on CRM Online data using Power BI’s OData Connector. It is reported when an entity is refreshed. This error can occur wither in Power BI Desktop or Power BI online service. Error reported is as:

“Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host”

Introduction:

We have faced similar error while working with customer on their CRM Online system. We are BI and DW consultants delivering insights to our customers from their data. On reporting of an error from deployed Power BI reports, we analysed our design and data hosted by CRM Online. However, we could not find any issues.

Then we started our conversation with Microsoft engineers and came to know about change in API for CRM Online.

Technical Details:

Technical Details of the Report having issues were as below:

1. Application : Power BI July Release
2. Connector : OData
3. Source System : Microsoft CRM Online
4. Operations : Fetch Data of entities and related entities

Problem Analysis:

In our conversation with Microsoft engineers, they indicated change in behaviour of new API released from Microsoft for CRM online 2016. The API has issues when OData connector tries to expand multiple records for related entity. We were trying to expand ‘Subject’ entity from ‘Incidents’ entity in order to fetch Subject Title of an incident.

There is a blog which describes the issue:

http://joegilldotcom.blogspot.in/2016/03/web-api-querying-with-expand.html

In nutshell, we can achieve expansion operation for related entity for single record. However its not possible for multiple records.

Alternate Approach / Solution:

Since CRM supports web services as data source, we thought of using ‘Web Source’ as connector to CRM Online. Web Source will query web service in order to fetch data.

Now the question arises: “How can I query Web service from Power BI to fetch data from CRM Online”

Answer lies with fetchXML. It is a proprietary query language used in Dynamics CRM. It can be used in JavaScript, SSRS, and web services.

Can we use FetchXML directly in Power BI? Answer is NO. We have to hit web service from Power BI. This web service understands URI. URI will encode fetchXML query and thus will fetch data from CRM Online.

So our approach will have steps as:

Create fetchXML query to get relevant data > Encode it in form of URI > Write final URI in “Web Source” connector

• Create fetchXML query Here you can query for entities and related entities by searching relevant fields under drop down box. When you press Run or execute, CRM will report results. There is another button on ribbon “Download FetchXML”.

Using CRM for generating FetchXML

• Press that and you will get xml definition of your query. For example if in our case, we wanted to fetch incident and its subject title, our fetchXML looked like as below:   
• <fetch mapping="logical" output-format="xml-platform" version="1.0" distinct="false">   <attribute name="title" />   <attribute name="createdon" />   <attribute name="caseorigincode" />   <link-entity name="subject" to="subjectid" from="subjectid" alias="aa">       <condition attribute="title" operator="not-null" />   </link-entity></fetch>

1. </entity>
2.      </filter>
3.      <filter type="and">
4.    <order descending="false" attribute="title" />
5.    <attribute name="incidentid" />
6.    <attribute name="ticketnumber" />
7. <entity name="incident">
8.  
9.  
10. This can be done by logging to CRM Online and going for “Advanced Find”

This fetchXML only picks incidents with Subject Title having data. Thus blank Titles are filtered. That may not be good when we want to give insights into CRM Data.

So we can update the above query. How? We can remove filter element and introduce attribute element. So our final query will look like as below:

<fetch mapping="logical" output-format="xml-platform" version="1.0" distinct="false">

<entity name="incident">

   <attribute name="title" />

   <attribute name="ticketnumber" />

   <attribute name="createdon" />

   <attribute name="incidentid" />

   <attribute name="caseorigincode" />

   <link-entity name="subject" to="subjectid" from="subjectid" alias="aa">

     <attribute name="title" />

   </link-entity>

</entity>

</fetch>

We have fetchXML query. However we can not use it directly in Power BI. We now have to encode this Fetch XML Query to URI so that it can be used in Web Source.

1. Encode it in form of URIPaste the fetchXML query and get the encoded string in below format:%3Cfetch+mapping%3D%22logical%22+output-format%3D%22………. %3E It will be in form of: https://<your org>.api.crm8.dynamics.com/api/data/v8.0. Since we are going to query incidents we have to write our final query as  

• https://<your org>.api.crm8.dynamics.com/api/data/v8.0/incidents>fetchXml=%3Cfetch+mapping%3D%22logical%22+output-format%3D%22……….%3E
• Thus our final URI query will be in format as below:
• <API address >/incidents?fetchXml=<URI we got>
•  
• For encoding URI, we now need API address for our CRM Online. This API address will be located under Settings > Customization > Developer Resources (I have not written full query here, since it will take lots of space. So I have mentioned start and end part). There are online tools available at here
• Write final URI in “Web Source” connector This is Json output and you can convert this into table structure.
• Please open Power Query (using Edit Queries) and use “Web Source” as Data connector. Paste the generated URI under URL. Sign in using your credentials (if you have relevant permissions) and then you will get resultant screen as below

 WebSource to Table Structure

Final Words

After you have written all queries and converted data to table structure, you can relate it in Power BI Modelling window. Then publish the reports to Power BI Online and refresh them. This solution worked for us and I hope it will also work for all concerned parties.

In case you need any more information, please feel free to message me.

Achin

Inflexion Analytics