Fast vs. Furious: PowerBI + Dynamics CRM Avoid this common mistake and watch your data fly!
There’s a common mistake in connecting PowerBI to CRM – and it can result in the difference between PowerBI being fast – vs. you being furious (at how slow it is.)
If you hear someone say that the PowerBI-CRM connection is unusably slow, this common mistake may be the root of the problem. Until I realized what was happening, I assumed that connecting PowerBI to CRM was effectively useless for datasets of anything more than a few thousand records.
After making this change to the style of my PowerBI query, a dataset that originally took more than an hour to complete – the identical dataset could be fully refreshed in less than 90 seconds!
Note: This performance breakthrough requires that you are using the new “Instance Web API” available in CRM 2016 or later. (for more information, see this CRM Tip of the Day: Turbocharge your CRM to PowerBI connection )
When selecting lookup fields for inclusion in a dataset (e.g. parentcompanyid, ownerid, etc.), the OData feed offers you the attribute name you’re familiar with, (“AttributeNameID”) and another version that looks a bit odd, “_AttributeNameID_value”.
Here’s an example retrieving from the account entity and wanting the name of the country stored in a lookup field. – (Note that the ‘hsl_countryid’ is referenced two times, once as “hsl_countryid” and once as “_hsl_countryid_value”.)
If you choose the one that looks more familiar – in this example: “hsl_CountryId” – you’ll find in the query worksheet that you have a column filled with ‘Table’ and it allows you to ‘expand’ to capture any field from the related table.
If you choose the one that is formatted a bit oddly, in this example: “_hsl_countryid_value” the query worksheet will just contain a GUID and no related text, meaning that you’ll need to join to another table to find the name of the country.
Both options work, it’s just that the option when you ‘expand’ the field causes a HUUUUGE hit against performance.
Apparently for every row, the query does a separate lookup to the related entity to retrieve value in the related entity name field (if you’ve ‘expanded’ the lookup field.) This results in a massive amount of time and data thrashed back and forth. – By downloading the related table once and then joining the two within PowerBI you get an amazing performance improvement.
Build related tables for lookup items and link based on the “_AttributeNameID_value” field rather than ‘expanding’ the attributes within the record (especially for repeated lookups like ‘owner’ or ‘territory’) –
Once you close and apply the query – manage the relationships and join the tables.
Open the table editor –
Add a “new column” in the account table – and use a LOOKUPVALUE function to grab the country name value (“hsl_name”) off the related table.
Performance Impact: “Huuuuuge”
As a test, I set up a relatively straightforward PowerBI query pulling the account’s ID and Name and a look-up to a custom ‘country’ entity.
Following the pattern of using one query to retrieve the needed fields from the country entity, and using the ‘_value’ field from the base table to joining it to the related table within PowerBI, it took about 90 seconds to refresh the data from the source. (68K records in the base table and 240 country records)
As a comparison, on the same dataset, I rebuilt the query and ‘expanded’ the lookup field to include the country name – the query ran for more than an hour and pulled more than a gigabyte of data across the wire before I had to leave the office. (I don’t know how long it ultimately took to complete – and frankly I don’t care.)
For any primary table in your report – (account, contact, opportunity, case, etc.) return that basic table with only the needed ‘_AttributeNameID_value’ fields- (do not select and ‘expand’ the lookup fields formatted as ‘AttributeNameID’). In separate queries return the related (lookup) tables. Join these related tables to the base table within PowerBI’s relationship manager. Finally add columns using the LOOKUPVALUE function to build the row you want.