In my last article Power BI Hack to Improve Power BI Speed with Microsoft Dynamics 365 (formerly Dynamics CRM), I talked about the performance issues that we commonly see when our clients are using Power BI with Dynamics. For this blog, we are going to dig a little deeper and cover one of three best practices that will address speed of importing data.
There are a few simple techniques which will allow us to build out our data set with no unnecessary database queries.
First, when you click Get Data and select Dynamics 365 to begin building your dataset, be sure you are using the 8.1 API. When you bring in data from CRM, the API URL should look like this:
Construct it by using your organization root URL and appending "/api/data/v8.1". This gives us access to features we will need which won't be present if you use the old API URL (which looked like this: https://orgname.crm.dynamics.com/XRMServices/2011/OrganizationData.svc).
For my example, I am just going to bring in account and opportunity data, so I will enter the API URL as above, enter my credentials, and then select those two tables from CRM to start. Later in the process I'll need to bring I additional tables referenced by these two, such as Territory and User, in order to resolve those lookups.
If you are new to the current API version, you'll notice many tables now have friendlier names, "opportunities" instead of "opportunityset".
The second key point - and the reason we need to be sure to use the 8.1 API - is a change in how lookups are handled. Previously, if you wanted the owner's name field you'd bring in ownerid and then you'd have to expand that to get to either a name or a GUID. Now you'll see there are two attributes listed for each lookup:
There is "ownerid" and there is "_ownerid_value".
For an illustration of the differences, I'll bring in both for the Territory lookup - "territoryid" and "_territoryid_value" as the territory table is simpler to work with than the user table.
The results look like this:
The territoryid column is a related record, which we would need to expand by clicking the expand column button in the column header to see the contents. I will do that and select the "name" attribute:
And the result is a nice, friendly territory name:
However, this is the practice we want to avoid. This results in a per-row query to the database. Instead of one query for a thousand line table, we now have 1,001 queries. If we were to continue expanding each lookup - one for user, one for customer account, and so on - the slowdown would become apparent and depending on the amount of data potentially unusable due to timeout errors or simply unacceptable wait times to refresh data.
If you've been following along, delete the expansion step and remove that column from your dataset.
For all lookups, we want to pull in the new "_value" fields only. We will also need to bring in the tables for those lookups so for territories I will Get Data from CRM again, pull in the Territory table, and select just the needed columns, in this case name and the GUID:
No join is necessary, so just bring in the needed data and leave it for now.
We'll also need to set up some custom tables to resolve our optionsets, so for each I'll just use the Enter Data function and add a simple table, for example the below to resolve Opportunity statuses:
Here again, we just want to leave this as is for now.
Previously, I would have merged the opportunity query with the new table on the status column by clicking Merge Queries, selecting my statecode column and custom status table:
And then again I would need to expand the column:
This would turn the column into a nice readable one, but again at the expense of a per row query, so we'll avoid this (again, if following along, delete those steps).
In the past, this would just have been the beginning of building the dataset queries, but using this method, we're essentially done.
As a final step, I like to rename all the columns which are in their final form to something friendly, and leave those which still need processing as the schema names. This lets me see at a glance in my report designer where I still have work to do:
If we create a simple table in the report, we see the current state of our data. Text, Date, Currency and other simple fields are complete, and lookups and optionsets still need work.
If I try to bring in a field from the account table, say to look at my opportunities by customer tier or industry, I can't do it at all, but we're closer to the goal than it seems.
In the next blog in this Power BI series, we will cover relationships and in the last article I'll address some formulas that together will give us access to everything we need without having to do a single join or expansion step.
Looking for more Power BI best practices? Check out the 3 Best Practices to Improve Power BI Speed with Microsoft Dynamics 365 (formerly Dynamics CRM) white paper.