Archive for January, 2017

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

Setup the Data Export Service with a Power BI Solution Template

The Data Export Service is a new and exciting service from Microsoft. It is usually referred to in the context of creating SQL based reports on Dynamics 365 data. However, it is lot more than that and can definitely be leveraged for Power BI as well. I will explore that in this blog post and go through a step by step of the easiest method I have found so far to set it up.

What is the Data Export Service?

The Data Export Service is a cloud-based service that replicates your Dynamics 365 data, to an Azure SQL database and keeps it updated. Here are some benefits for why you might want to do that:

  1. Significantly faster querying times in Power BI
  2. Can aggregate multiple sources into one database
  3. More flexibility with the data and can use SQL queries
  4. Less impact on your production environment as analytical queries are in a separate database
  5. With Power BI Pro, can get closer to real-time analytics with Dynamics 365 data using DirectQuery to the Azure SQL database

Another reason I find the Data Export Service interesting, is that it is also used for the new Azure Customer Insights functions, so when that is finally released, you can use the Data Export Service to see if your data there is up to date too.

I will not get detailed on the setup of the Data Export Service. Scott Durow already posted a great video on how to set it up in detail, and if you are planning on using the Data Export Service in a production environment, you should definitely watch it.

However, the setup does require some skills and you’ll need to fire up PowerShell, install the Azure cmdlets to create an Azure Key Vault, and create an admin user in the Microsoft SQL Server Management Studio. If that is not your thing, and you just want to try it out, then Microsoft has made a Power BI Solution Template available that will let you skip most of those steps.

Setup Data Export Service using Solution Template

My starting point is a trial for Dynamics 365.

I’ll need to add two services to it before I start with the solution template. The Data Export Solution and signing up for a free Azure Trial.

1.  Install Data Export Service

Inside Dynamics 365, go to the AppSource and search for “Data Export Service”.

appsource-data-export-service

Click on “Get it now” and go through all the steps of installing the solution. Basically just click “Next” until it is installed.

Once the installation is complete, navigate to the Data Export Service in Dynamics 365.

data-export-service-in-navigation

There you will need to accept some licensing agreements etc.

2. Sign up for Azure

Open up a new tab in the same browser and go to https://azure.microsoft.com/en-us/free/

azure-free-trial

Click on “Start free” and follow the instructions. You will need to verify your identity with both phone and credit card. No worries, you won’t be charged for a trial unless you explicitly change your subscription. And I am of course not liable for any charges 🙂

When Azure is all set up, just leave it open in its tab. If you accidentally close the tab, you can always get it back on https://portal.azure.com

3.       Power BI Solution Template

Open a new tab again and go to https://powerbi.microsoft.com/en-us/solution-templates/dynamics-crm/

This is the Power BI Solution Template that will setup the Data Export Service and provide a Power BI file with some premade reports.

solution-template-first-page

Click on “Install now”

 

solution-template-now-also-supports-on-premise

Select “Dynamics 365 Online” and click “Next”. Note that you could also use an on-premise installation of Dynamics 365.

 

2 Connect to Org Dynamics 365.png

Click “Connect” and authorize a connection to your Azure subscription. You may be prompted to log in again. If so, use your credentials for the trial environment that you are using.

 

2 Dynamics 365 login choose org and resgroup name CRMchartguy.png

Make sure the right Dynamics 365 organization and Azure subscription is selected. If you have a trial and follow these steps there should only be one option for each. Remember to check the box for authorizing the deployment of the template.

If you click “Advanced” you can optionally name your resource group in Azure. Or if you are ok with a random text string for a name, just leave it and click “Next”.

 

3-connect-to-azure-keyvault

Click “Connect” to connect to and authorize the Azure Key Vault. You may be prompted for login credentials again. This step is really cool as it is the one that lets you avoid firing up PowerShell.

 

3 Connected to Azure Key Vault.png

You are now connected. Click “Next”.

 

4-connect-to-your-sql-database

Next step says “Connect to your SQL Database”, but you have not created one yet. Don’t worry. That is what this step does for us.

Enter any name in “Server Name”. It just has to be unique. Create a SQL Username and password and make sure you remember them.

Click “Check Availability” and if everything looks good click “Next”.

“Advanced” options on this screen will let you set a pricing tier for the Azure database. I will return to what that means later in this post. You can leave the default setting as is as it can always be changed.

Note that you can also change the destination to an existing Azure SQL database if you already have one.

 

5-track-your-progress-2

Now the solution template will do all the heavy lifting and

  • Create an Azure Resource Group
  • Create a SQL server and database with the credentials you gave
  • Create an Azure Key Vault
  • Create an admin user in your database for access
  • Create a replication profile and connect it via the Key Vault
  • Start adding data to your SQL database from Dynamics 365

Pretty nice right?

Once it has gone through all the steps it will start downloading a Power BI template for you.

5 All done waiting for Power BI report.png

When it is ready, download the template.

And that’s it for setting up the Data Export Service with a Power BI Solution Template.

4. Verify the Data Export Profile

Go back to Dynamics 365 and navigate to Settings -> Data Export.

Data Export Profile.png

Click directly on the name of the profile to open it. A double click on the line won’t work here.

This is also the view where you would see your Azure Customer Insights (ACI) connection once that feature is ready.

Data Export Profile Detailed.png

Here you can see the sync status of all the entities included. Initially, you may see some errors due to dependencies on a few entities, but they will resolve themselves as the data gets loaded completely.

Note the buttons on the top of the screenshot for “Manage Entities” and “Manage Relationships”. Use those if you want to include entities that were not already included in the solution template.

5. Azure

Open your tab with Azure and refresh it.

Azure after setup.png

You should see three items. Make a note of the database name. 9bv7irkt3nvg in the example above. No need to do anything with them, but that is where your SQL server, SQL database, and Key Vault is.

6. Power BI

Now that we have an Azure SQL copy of our Dynamics 365 data, we can use that in Power BI.

Open the Power BI templated that you downloaded earlier.

The first report actually has the instruction for getting set up, but they haven’t really worked for me, so here are the steps I use.

pbi-go-to-edit-queries

Go to “Edit Queries”

 

PBI Go to Data Source Settings.png

Data Source Settings

 

PBI Change Source.png

Change Source

 

PBI SQL Server Source.png

In the Server field, replace “servername” with the name you gave the server during the setup. Make sure the .database.windows.net is included at the end.

In the Database field, add the name of the database that was generated. It is something weird like 9bv7irkt3nvg. If you forgot, go back to Azure and copy it from there.

When done click “OK”.

 

PBI Edit Permissions.png

Then go to “Edit Permissions”

 

pbi-edit-permission-edit

Click “Edit”

 

pbi-database-login

Make sure the tab is “Database” on the left-hand side. Enter the database login details you created earlier and hit “Save”. Then click “OK” and “Close” until you are back to Query Editor.

 

pbi-refresh-preview

Click on “Refresh Preview” to ensure it is working. You should now see the preview data populate.

 

pbi-close-and-apply

Click on “Close and Apply” and the data will load into Power BI from your Azure SQL Database rather than from Dynamics 365.

Scaling your Azure database

In the beginning of the blog post, I mentioned a number of reasons why you might want to take this approach. One of them was significantly faster querying times.

Well, how much faster? That depends partly on how much money you decide to throw at your SQL database.

In Azure, you can see the pricing tier for your database. It is measured in Database Transaction Units (DTUs). You can read a lot more about DTUs here.

The short story is that the more expensive it is, the more performance you are getting.

Go to your tab on the Azure portal and click on the SQL database. The one with the randomly generated name.

Azure Pricing Tiers.png

Under “Settings”, click on the “Pricing tier (scale DTUs).”

By default, it is setting you up with the version that costs about 75.02 USD a month. Don’t worry, it’s a part of the 200 USD you got for free with the trial.

You can select any of the other tiers and click “Select” and the database will immediately scale to the new settings. It only takes a few minutes but varies depending on the amount of data.

So how much of a difference does it make?

Well, here are some tests refreshing from a Dynamics 365 organization with about 262K records.

time-to-refresh-in-seconds

All times are of course approximate and can vary depending on the amount of fields per record etc. The initial refresh might also take a little longer than the following refreshes.

The standard OData connection directly to Dynamics 365 took about 2700 seconds to refresh. That is 45 minutes!

From the Azure SQL database and at the cheapest rate of 4.99 USD a month at 5 DTU it took about 120 seconds. Or around 2 minutes.

For 15.00 USD a month and 10 DTU it is down to 45 seconds. From then on you don’t win that much for the extra cost, unless you have other reasons to upgrade your database. The 100 DTU version is 150 USD per month.

As you can see, there are some significant differences in the time it takes it takes to query the data. Even at the cheapest version, there’s over a twentyfold increase in speed. In fact, it’s kind of mindblowing. So skip one almond milk latte per month and get yourself an Azure SQL database.

Keep in mind that you can scale the database anytime you like. So if you plan to work in Power BI for a day and test out a lot of queries, you can increase the pricing tier for a couple of hours. Just remember to scale it down afterwards.

Glad you made it to the end. Hope you found something useful. As always please sign up for my newsletter and follow me on Twitter.

5 Comments