Archive for category Power BI

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.

odata-url

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)
get-data

 

Type in the URL and it will only retrieve the data you specified.
odata-feed-url

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.
odata-generator-in-xrmtoolbox

 

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

 

Then click on Load Attributes and Relationships.
odata-generator-selecting-fields-and-filer
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.
odata-generator-all-details
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
pbi-edit-queries

 

Select the query on the left and make sure the applied step “Source” is selected on the right.
review-or-edit-odata-feed-url
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.

Leave a comment

Power BI and mixed Team/User Ownership in Dynamics 365

In Dynamics 365 it is easy to assign cases, opportunities, etc. to a Team. This is a very convenient feature and when working with the built-in charts and dashboards, all the charts nicely puts either the team or user name on the legend, depending on what type the owner happens to be.

However, Power BI doesn’t play so nice right off the bat. The reason for that is that the owner field in Dynamics 365 can point to either the User table or the Team table. That means you will need to let Power BI know which table to look at for each record.

As always, there are many ways this challenge can be resolved, but I would like to highlight a few ways that I’ve been working with.

The most important part of this post is simply to recognize that Power BI behaves in this way, and act accordingly. Otherwise, you may end up with weird looking visualizations, or worse, a wrong understanding of what your data really looks like.

I’ll use the Case entity as an example as it is commonly assigned to both users and teams.

Add Type of Ownership and Owner to table

The most simple approach is to add two extra fields on the Case table. One to indicate if ownership type is “User” or “Team”. The other with the name of the user or the team.

The system name for the Case table is of course “incidents”, so that is what will be used throughout these formulas. The examples assume no changes has been made to the column or table names in Power BI from when they were imported from Dynamics 365.

Create a new column and enter this formula or copy and paste from below the image.

Power BI for Dynamics 365 Dynamics CRM and new column to define Owner Type for team and user ownership in Dynamics 365 Dynamics CRM

OwnerType = IF(incidents[_owningteam_value]="","User","Team")

It the field for the owning team is empty, the value will be set to “User”, if it has a value, it will be “Team”. This defines the ownership type.

Create another new column and enter this formula. It is similar to the formula just used, but it inserts the name from the user table or team table instead.

Owner = 
        IF(incidents[_owningteam_value]="",
        LOOKUPVALUE(systemusers[fullname],systemusers[systemuserid],incidents[_owninguser_value]),
        LOOKUPVALUE(teams[name],teams[teamid],incidents[_owningteam_value]))

When entering this formula in Power BI, it would normally be on one line. I added line breaks so it is easier to read and to copy and paste from.

Now, the two new columns can be used in visualizations so you can easily drill down on the specific type of ownership.

Adding two columns in Power BI to better handle mixed ownership between users and teams in Dynamics 365 Dynamics CRM

Click to enlarge animation adding both columns.

Create an Owner Table

Rather than bringing the data in to the Case table, we can create one “Owner” table with all the users and teams in it, along with their unique ID in one column. This table can then be used for all the entities that have both user and team ownership. This method can also be used if you have more attributes on the ownership that you want to categorize by. For example, the manager of the user or the administrator of the team, can all be brought in to this table.

To create this one table with ownership details, I will need to append the User and Team tables.

Click “Edit Queries” to open the “Query Editor”

Edit Queries in Power BI

Click on Append Queries and choose”Append Queries as New”
Append Queries as New in Power BI

The difference between “Append Queries” and “Append Queries as New”, is that “Append Queries as New” will create an additional table with our data that we can work with separately. “Append Queries” will bring data in to an existing table. I am choosing the “as New” option so I still have the user and team table to work with separately if needed.

In the dialog, select the two tables to append and click ok.

Append Queries as New select tables dialog in Power BI - Dynamics 365

Wait for tables to merge and rename the table from “Append1” to “owner”.

Close and Apply the changes.

Close and Apply Changes in Query Editor in Power BI

Find the new Owner table and add two columns indicating the ownership type and the name of the user or team. Similar to the first method described, except simpler since all the values you need are now in the same table.

One new column with the type to indicate if it is either a user or a team

Type = IF(owner[teamid]="","User","Team")

 

One new column with the name of the user or team.

Name = IF(owner[teamname]="",owner[fullname],owner[teamname])

 

One new column which contains the GUID of the user or team.

_ownerid_value = IF(owner[teamid]="",owner[systemuserid],owner[teamid])

I called this column “_ownerid_value”. Not the easiest to type, but it is the same as the schemaname for the mixed user/team owner field. Giving it this name will make it much easier for the autodetect relationships feature to give us some good results.

At the end you should have these three new columns in your owner table.

New columns added in Power BI with Ownership Type, Name of user or team and the GUID.

Finally, go to Relationships and add the relationship between cases and the new owner table matching _ownerid_value to _ownerid_value. Quickest way to do that is to drag _ownerid_value from incidents to the corresponding field on the owner table.

Power BI relationship editor - drag and drop owner id from one table to the other.

Alternatively, click Manage Relationships and run the autodetect feature.

Power BI Manage Relationships and use Autodetect feature.

 

Go back to reports and use the new owner table in some of your visualizations and verify that it is working.

Use new appended owner table with Dynamics 365 Users and Owners in Power BI visulizations.

One consideration for this approach is that you double the amount of data in Power BI consumed by the user and team table. However, these are generally the smallest tables in a Dynamics 365 environments, so the impact is likely limited. I would be cautious of using a similar approach to handle the Customer field that can point to either an Account or Contact.

Other considerations

With the DAX formulas used in Power BI, there are always options to make it more advanced. In my first example, I imported both the user ID and the Team ID into the cases table and based my logic in the DAX formula off of those. I could have imported only the one ownerid and have had the DAX formula base its logic on the content of the user and team tables, rather than the extra columns within the same table. That will save a few columns when importing data and you’ll have fewer columns to navigate.

The DAX formula that brings in the Team or User name, with the logic based on data from other tables would look like this.

Owner = IF(ISTEXT
    (LOOKUPVALUE(teams[name],teams[teamid],incidents[_ownerid_value])),
    LOOKUPVALUE(teams[name],teams[teamid],incidents[_ownerid_value]),
    LOOKUPVALUE(systemusers[fullname],systemusers[systemuserid],incidents[_ownerid_value]))

 

Thanks for reading, and as always, please follow me on Twitter  and sign up for my email newsletter to get the latest tips and tricks to get the most out of your Dynamics 365 data.

, , ,

Leave a comment