{In Depth} Power BI and Dynamics CRM on-premise–When and how it works?

Microsoft has been focusing recently in Cloud First Strategy for Microsoft Dynamics CRM. Every new cool feature you name, you see it in online first. However as with everything, when you focus heavily on one side, some aspects always gets neglected. And big on-premise customers are at the receiving end recently for this. Believe me as a consultant, I am faced every now and then with the perennial question from my on-premise customers – “When is that feature coming in on-premise?” And many a times I don’t have an answer as well. That the same product has some features which is not present their version of CRM is something very difficult to convince a customer.

Same is the Power BI integration with Dynamics CRM. Just talking of Power BI, it’s a awesome product. And it feels really great when something like this works with Dynamics CRM. And with dynamics CRM Online, this integration just works WOW.

However the same cannot be said about Dynamics CRM on-premise. In this topic, I will cover in depth the integration of Dynamics CRM on-premise with Power BI. Sit tight because we are going to go deep here.

First of all, we need to understand the Power BI connecting string for Dynamics CRM. Microsoft Dynamics CRM comes as one of the listed data sources for Power BI. So let us choose that.

Once we try to connect to that, we need to enter the OrganizationData.svc url. You would be asked to enter your credentials. And voila, you are done!

Wonderful! Just a few clicks and you are all set up with the data. You can start building wonderful reports. However we are more interested in CRM + Power BI connectivity. So let’s explore the connection string that is used by Power BI to connect to Dynamics CRM Online. The connection string looks something like below.

If you explore the connection string, the first thing to note here is the Data Source property. Microsoft has really come up with an inventive name – $EmbeddedMashup(<some random guid>)

I am more interested in the Extended Properties section. Looks like Microsoft has hidden the query string in that. Seems interesting right! Let’s explore it together.

From the string format it seems to me it is base64 encoded string. Oh, very simple it is. Let us quickly covert the base64 string to readable text. Below is the sample code to do that. I have written this code in the Console Application. And the ExtendedProperties value, I have stored in the App.Config file.

var str = ConfigurationManager.AppSettings[“ConnectionString”];
var rawstring = System.Text.Encoding.UTF8.GetString(Convert.FromBase64String(str));

But alas! The rawstring gives the below output. Certainly not what we expected!

PK\0\0\b\0�|iHA�k��\0\0\0�\0\0\0\0\0Config/Package.xml �\0(�\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0���\n�@�_E���jb��&Rt�u�%]C���:�H�BAݺ�\f����v�tn��Aw&!\f<�(#�R�*!�=�I�\ny.*��f��A’���S:ML>t}E��1z̶��U[��\f�0R�/U�����{���C\b��\a�c�͢����<�?1n�Ǝ�ʸ��b�~~�’PK\0\0\b\0�|iH�髤\0\0\0�\0\0\0\0\0[Content_Types].xml �\0(�\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0m�K�0\fD�y���@\b5e܀\vD�����E�l,8W mw��g�y��zW�d\a�1��)�%\br��z�*���{8���(�uQA���tdu,| ��ƏVs>��6w�n�r��;&ǒ�PWgj�4���,��\aqZss��ĸ����?y�����$m�v!q^PK\0\0\b\0�|iH��r{�\0\0\0�\0\0\0\0\0Formulas/Section1.m �\0(�\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0m��\n�@��@��8�\b��F��R��!ȺY��N�6�|ws���if�����F���8�#߂�ZL��v�\vb�,�Rlą8�Ġ�viH�;`H�Du\”[�_e��iv�2E��i}7���O�WH>[��<����uh�=��l�|�’��%�F�<����@9+Tc�;7ğgY�ʰ��#e����PK-\0\0\0\b\0�|iHA�k��\0\0\0�\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0Config/Package.xmlPK-\0\0\0\b\0�|iH�髤\0\0\0�\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0�\0\0\0[Content_Types].xmlPK-\0\0\0\b\0�|iH��r{�\0\0\0�\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0�\0\0Formulas/Section1.mPK\0\0\0\0\0\0�\0\0\0�\0\0\0\0

Don’t be upset. Even in this weird string we have some ray of hope. Just check for the highlighted names – Package.xml, Section1, [Content_types].xml. Does they look familiar. Well from a simple guess all these seems file names. What else other that an archived file like (.zip) can store multiple files within it. So let’s convert it into a zip file. Below is the code for the same.

var str = ConfigurationManager.AppSettings[“ConnectionString”];

    System.IO.File.WriteAllBytes(@”C:\Debajit\Personal\My Projects\Power BI\connection.zip”, Convert.FromBase64String(str));

Now when I open the connection.zip, below is the screenshot.

And our guess was correct! Not let’s quickly see the hidden connection string. It is actually stored in the Formulas folder in the file – Section1.m. When I open the file in Visual Studio, I can see the connection string below.

section Section1;

shared #”AccountSet (2)” = let
    Source = OData.Feed(“https://xrmtr15.crm5.dynamics.com/xrmservices/2011/organizationdata.svc”),
    AccountSet_table = Source{[Name=”AccountSet”,Signature=”table”]}[Data]
in
    AccountSet_table;

As you can clearly see, Microsoft uses an internal method OData.Feed to connect to the Dynamics CRM Online feed. So surely we cannot do any trick to this connection str
ing to make it work for on-premise. It’s an inbuilt stuff after all.

So how can we make it work for on-premise environments. Microsoft says you can use the OData Feed. However let’s explore where it works and where it might not work for you. In both the examples, I would make use of Power BI Desktop

Scenario 1: On-Premise (non-IFD)

No brainer here. Instead of Dynamics CRM Online Data source, select OData Feed here. Most of the cases you need to be in your network to connect to Dynamics CRM on-premise OData Url.

Then enter OData URL for your organization. It might ask you for credentials. So enter the same. Once you are connected you will need to select the tables for your report. Here I have selected RoleSet to create an absolutely waste PIE Chart. The PIE Chart shows the count of Role by business unit. I saved it by the name ‘crmdata’.

I save the file as crmdata.pbix. Then I go ahead to URL – https://app.powerbi.com and in Get Data –> Files-> Local File -> Upload the recently saved Power BI File. As you can see, now my Power BI workspace shows the report and the data set as well.

So now what. You have uploaded your on-premise report but how Power BI will refresh the report when the underlying data source changes in your on-premise CRM. For that you need to schedule refresh of the dataset – crmdata here. But how is that going to happen? After all how does Power BI, hosted in Cloud can connect to your data source? Well Microsoft have your back here. For this you would need to install the Power BI Personal Gateway.

You can download the Power Bi Personal Gateway from the following link – https://www.microsoft.com/en-us/download/details.aspx?id=47753

A detailed understanding of how power bi personal gateway works is explained in the following link – https://powerbi.microsoft.com/en-us/documentation/powerbi-personal-gateway/

Just to give you a brief understanding

• You install Power BI Personal Gateway on your laptop/ desktop/ common server machine. Power BI installs as a service (not always though, please refer to the documentation) and keeps running as long the machine in which it is installed is up and running.
• Power BI secretly connects with the Power BI personal gateway at the scheduled refresh times (I will come to that on how to configure the same)
• The connection happens through Azure Service Bus Namespace. For this some outbound ports needs to be opened for connection to happen. Please refer to the above documentation for the same.

Once you have installed and configured the Power BI – Personal Gateway, open Power BI and go to Settings –> Data Sets-> Select the data set that you want to configure.

If you expand Gateway connections, you would find that Power BI will detect if the Gateway is online.

Now expand Data Source Credentials. You might see an error icon in this. Don’t worry, you are only interested for OData Connection. So set up the OData connection here. Once successful your screen should show like the below.

The final step is very easy. The refresh schedule. I think the below screenshot is good enough here. No need for words.

All set and done, as long as you Power BI – Personal Gateway is running, your report would keep refreshing.

Scenario 2 – On-Premise with IFD

Not so good news here. Specially if your IFD is configured with a proxy server. And that is usually the case in most standard implementations. In these case once you enter the OData URL, you would receive an error like this in the Power BI

This is because when you are configuring the IFD with ADFS Proxy server, you would have a home realm URL. Remember at the beginning of the blog, where we explored the connection string, we found Microsoft users OData.Feed API to connect. Unfortunately OData.Feed API will not validate the Home Realm Url in this case.

All you can do in this case is use stand alone excel files and use the Power Query add-on in Excel to refresh the data. Check for this wonderful article on the same topic. Please note that even after applying the trick, the excel files would refresh, but if you try to upload the same in Power BI, it won’t work because Power BI will not take the connection string embedded in the Workbook which has been uploaded.

http://blog.cobalt.net/blog/excel-odata-feed-from-dynamics-crm-on-premise-or-ifd

Hope this helps next time somebody wants power bi integration with CRM on-premise!