As we’ve seen in the previous articles on How to work with Power BI in Dynamics 365 for Sales and Service as well as How to work with Power BI in Dynamics 365 for Sales and Service–Part 2, we can leverage the existing content packs to simplify our data presentation for the Sales and Service modules in Dynamics 365. And that works fine if your requirements conform to what’s already built in those content packs. But most of the time that’s not necessarily the case.
Let’s have a look at how you can work with Dynamics 365 Customer Engagement data in Power BI from scratch. We’re going to be doing this from Power BI desktop. If you don’t have it installed on your machine, grab it from the Microsoft Store or from here.
Once installed, launch it. It starts with a wizard, and you can directly start from Get data.
Alternatively, if you closed the wizard, on the Home ribbon go to Get Data.
This brings up the Get Data source selector.
If you go to Online Services and find Dynamics 365 (online), that will allow you to select an already created content pack.
We’ll look at creating content packs in a future article, for now all we want is a direct connection to our trial environment. So instead, select Other and find OData.
When done, click Connect. The wizard then prompts you for the OData feed URL. You get this URL from your Dynamics 365 environment by going to Settings > Customizations > Developer Resources > Instance Web API > Service Root URL
Do note that, at the time of this writing, using the v9.0 URL results in the following error, so instead use v8.2
If not already signed-in, you will be prompted to sign-in. Use an account with access to the Dynamics 365 data you intend to leverage in your report.
Next, the Navigator allows you to select the entities you want to leverage in your dashboard. We can choose here for example the accounts and salesorders.
Once you chose your entities, you can click on Select Related Tables to add to your data set tables with matching key columns in a 1:M relationship, but if you take that approach, make sure and validate what else is being pulled in. You could bring in too much data you don’t really need, like for example entities data related to Field and Project Service if these solutions are installed in your environment. You might not want that data for what you are trying to achieve. Alternatively, knowing the data model in CRM you can create the relationship in the Power BI model and bring in only the minimum amount of data you require.
When done, click on Load and let it run. You can see the progress in the next pop-up.
Once the load is done, you can look at the data model by bringing up the relationship view.
And now that you have your data available, you can proceed to working with it towards getting it ready for your new dashboard.
Enjoy!
thatcrm.com 