Prefilter data for Power BI with OData URLs

When you connect Power BI to Dynamics 365, it pulls all the records from the entities that you have selected. That is generally a good thing, but there are situations where that is very inconvenient.

Maybe you are dealing with large datasets, or you need to filter what records are available for security purposes.

If you have just a few 100k records, Power BI can take over 40 minutes to pull all the records. That is frustrating if you are just going to filter them anyway and only use a small subset of the data.

Or if you are creating a Power BI dashboard intended for sharing, and you want to make sure the dataset does not have records the users are not supposed to have.

You can get around that by amending the OData URL you use in Power BI. Credit goes to my colleague and Dynamics 365 Wonder Woman Michelle Eldridge who told me about this trick recently.

Prefilter records in OData

Here is an example of an OData connection URL for Accounts with parameters added.

The $select specifies the fields it will include. The $filter defines the filter that is applied. In this case, only records in the state of California.

Use the URL in Power BI

Click on Get Data and select the OData Feed.
(or New Source if you are already in the Query Editor)

 

Type in the URL and it will only retrieve the data you specified.

There are some details on MSDN on the filters that you can apply in OData URLs.

That part is simple enough, but how did I create that URL?

Use the OData Generator Plugin

If you are not excited about the prospect of creating those URLs manually, then there is an awesome plugin for the XRMToolbox that will do them for you.

It is called the OData Generator Plugin and has been made available by Nizar JLASSI.

You can download the OData Generator Plugin on CodePlex here.

At the time of writing the plugin is not available in the XRMToolBox Plugin Store. You will have to download the .dll file from the link and drop it in the plugins folder where you have your XRMToolBox.

Then open the plugin in the XRMToolBox.

 

Click on Load Entities and select the entity you want to work with. In this case, I am choosing the account entity.

 

Then click on Load Attributes and Relationships.

In the “Select” column, check all the fields you want to include. In the “Filter” column, check the fields you want to apply a filter to. In the box for the filtering attributes, I added the state field and the equal to “CA” operator and value.

It does take a few seconds for the fields to show up on the right side from when you click on them.

When done, click on Generate OData.

This will generate the OData URL with all the right parameters. Copy the URL and paste it into the OData Feed in Power BI.

Simple as that.

Tip: If you need to create a URL for a different entity, or need to go back and make changes, then I have had more success with starting over by closing the OData Generator and opening it again, rather than trying to use the clear buttons.

The OData Generator also have some additional features for expanding data from the relationships on the entity.

In Power BI, if you need to make adjustments to the records and the fields the OData URL is retrieving, you can modify the connection URL you created.

Open the Query Editor

 

Select the query on the left and make sure the applied step “Source” is selected on the right.

In the formula bar, the OData URL is then displayed. From here, you can either add or remove fields from the URL or change the filter. I would recommend only making minor modifications manually unless you are very familiar with the syntax. You can always go back to the XRMToolBox and the OData Generator Plugin and have it create a new URL to replace the one you have here.

You can use the OData URLs in conjunction with the standard OData connection to Dynamics 365 in Power BI. While I have not decided on a preferred method yet, it seems to be the easiest to use the standard OData connection to select most of the entities you need. Then add the entities that specifically require filtering using the approach detailed in this blog post.

Note: If you are using the Data Export Service to replicate your Dynamics 365 data to an Azure database, then you have other means of prefiltering data before it is pulled into Power BI, but that will have to be a different blog post.

That is it for this time. As always please sign up for my newsletter and follow me on Twitter. Follow @CRMChartGuy