of the
Turbocharge your PowerBI Connectivity to CRM – Amaze your friends and strangers!
Working in a hardened bunker deep on the mysterious island of “Advanta”, the Microsoft CRM team’s top scientists have unleashed a powerful new API for connecting to CRM. It’s known only by the secret codename “Instance Web API” and today your semi-humble tipster is here to help you unleash it in Power BI!
Based on the OData v4 specification, this API allows, among other things, PowerBI to read data from CRM 5-10x faster than before! (That said, it’s still not as fast as direct SQL access, but that’s a tip for another day.)
How do you as a CRM Online user take advantage of this within PowerBI?
First, you must promise to use it for good and not for evil (unless absolutely necessary).
Second, you must have CRM 2016 Online, preferably the spring update.
Third, Within CRM Settings, under Customizations / Developer Resource find the Instance Web API Service Root URL for your organization.
Fourth, having memorized – or at least copied – the service root URL from your organization, in Power BI, choose to “Get Data” from “Microsoft Dynamics CRM Online”
Now unleash that “Instance Web API Service Root URL” as the URL for connecting to CRM.
Once you connect, the Navigator will display the entities from your environment and you can select as needed.
That’s all there is too it!
Note: There are still best practices to keep in mind for optimum performance – Typically limit your download for a given report dataset to 5 entities (stay focused on a specific purpose for that dashboard) – Filter your datasets to typically 50K records or fewer. – Only include the minimum attributes that you absolutely need. Build related tables for lookup items and link based on the “_attributename_value” field rather than ‘expanding’ the attributes within the record (especially for repeated lookups like ‘systemuser’ or ‘territory’) –
We’ll explore these and more in future ‘CRM Tip(s) of the Day’.
Hi Scott, is this applicable for On Premise too?
Hi Eric –
Yes – this is for CRM 2016 on-prem as well. – That said, if you have onprem, you have access to the CRM Filtered views and those are faster by far.
If I were working with onprem, I’d likely just create a new SQL view that consumes the filtered views and presents CRM data in a format ready for PowerBI consumption. –
I would be :
– aliasing the views and columns with ‘user-friendly’ names (similar to what they see on-screen in the app.)
– removing all unneeded columns (“hey FAX number, the 80’s called and want you back!”)
– flattening it by joining in important, related tables for needed dimensions
– removing the GUIDS and Optionset Integers, etc.
Hope that helps.
Scott.
Thanks Scott for the tip. I will give it a try very soon. PowerBI could give an extra charge to system.
Can I Change the URL in an existing Power BI query? I dont’t want create a hole new Dashboard 🙂
i have the same question! Thank you
How to select only required columns using new api URL
I am using PBI Desktop to connect to CRM Online. I have updated my PBI solution to use the new URL and have discovered that you need to basically re-write existing queries. The field names are different in a lot of cases and the drill downs to different levels (such as “name” from the related account) don’t always work (spectacular error messages though!).
I noted the comment about using lookups vs drilling down (thank you very much for this!) and will try to implement this instead.
[…] Web API” available in CRM 2016 or later. (for more information, see this CRM Tip of the Day: Turbocharge your CRM to PowerBI connection […]