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”.

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.

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/

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.

Click on “Install now”

 

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

 

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.

 

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”.

 

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.

 

You are now connected. Click “Next”.

 

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.

 

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.

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.

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.

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.

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.

Go to “Edit Queries”

 

Data Source Settings

 

Change Source

 

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”.

 

Then go to “Edit Permissions”

 

Click “Edit”

 

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.

 

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

 

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.

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.

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. Follow @CRMChartGuy