Advertisements

{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.

image

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!

image

 

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.

image

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.

image

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 string 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.

image

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’.

image

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.

image

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.

image

 

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

image

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.

image

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

image

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

image

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!

Advertisements

About Debajit
I am a Dynamics CRM Most Valuable Professional (MVP) with 10 years of experience in Microsoft .NET Technologies and 7 years of dedicated experience in Microsoft Dynamics CRM. I have worked with companies like Microsoft, SanDisk, PwC, TMF Group and have extensive experience of implementing complex CRM solutions from both offshore and client side. Currently the face of XrmForYou.com with significant experience in delivering corporate training on Dynamics CRM and have already delivered multiple projects to client through XrmForYou.com Author of multiple tools on codeplex including the 'Role Based Views' and 'CRM-Sharepoint Metadata manager & Attachment Extractor' which are available for commercial use under XrmForYou.com For consulting/ training, drop me a note at info@xrmforyou.com or visit our website www.xrmforyou.com

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

  1. Excellent stuff Debajit.

  2. Neel says:

    For On-premise (including IFD), as you are installing the Personal Gateway local to the CRM server and CRM server has access to the SQL server, you can use the standard PowerBI SQL adaptor to connect and read directly from the CRM Database SQL views.

    Furthermore, if you utilise PowerBI Pro, you can install the Enterprise Gateway, which supports Direct Queries, which means no need to schedule data source refresh and as queries occur in real-time.

    • Debajit says:

      Hi Neel,
      Yes you are right..But once we are talking of direct access to SQL server, it does not matter any more whether its CRM or custom web application. It’s just an application and SQL Server.

      More over another disadvantage is leveraging the power bi desktop. You cannot use power bi desktop to build your reports as even if you are in IFD to gain database access,
      1. You must be connected to VPN.
      2. You must have a explicit SQL server login.

      Even we consider the first option, the CRM users normally do not have explicit logins created for SQL server as they get access to CRM Data base by their groups.The only option in that case if launch power bi desktop and build.

      the intention of gateway is to establish a connection with the on-premise data source. Actually scheduling refresh (be it on the fly or schedule) needs access to the on-premise data source and personal/ enterprise gateway uses azure service bus to do the same.

      • patnj76 says:

        Yes, your absolutely correct. I must admit my view was a little narrow minded, as I was looking at it from a report writer perspective (who creates and publishes power bi reports) on behalf of the CRM user as against empowering the CRM user directly to build their own.

      • Debajit says:

        Hi,

        CRM world is a bit constrained. Everyday as a consultant we feel that :). I still miss my .NET days when my hands were free to do anything.

  3. Turgut BEKİL says:

    Hi Great Post!

    My question is : Im working for a bank and due to security measures we do not use cloud techolonogies we do not upload any data to cloud. We have dynamics CRM 2016 on premis. Is it possible to use POWER BI Desktop + Crm on premis to create reports and tile and upload and view them from dynamics crm on premis screens and mobile without uploading and data to cloud or without registering powerBI online cloud service?

    • Debajit says:

      Hi Turgut,

      Sorry for the delayed reply as I was on the road for most of the week.

      Now coming to your question, Power BI has only online version. Power BI desktop is a connection enabler which helps you to develop Power BI reports and upload to Power BI online. The only way I can suggest for you is develop excel reports using PowerQuery add in and distribute those to your users.

      The other option might be your company asking to host their power BI instance in private cloud. You need to check with Microsoft for private cloud offering but it is a costly feature AFAIK.

      Cheers
      Debajit

      • turgut says:

        Thank you very much for ur fast reply 🙂 2 more question 🙂

        *** IF I understand wrong please correct me = In order to user powerBI do we have to keep our source data on cloud too ? Orr CAN we keep our data in our servers but we upload JUST the report tiles (I mean the report itself) to cloud and publish them from there ?

        *** Does PowerQuery have also the same capabilities like powerBI , I mean the colourful dashboards etc. ?

      • Debajit says:

        Hi Turgut,
        Sorry for the delayed reply. Yes you can have your data on-premise and then securely connect your data source with power bi using the power bi desktop.

        Coming to the second question, Power Query is in excel and does have wonderful chart capabilities as well. However one very important limitation in power query charts in excel is you cannot have animated and dynamic graphs that you have in power BI

  4. Turgut BEKİL says:

    Thank you Debajit, you and your blog helped alot. Have a nice day!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: