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