Sonoma Partners Microsoft CRM and Salesforce Blog

Accelerate Your Dynamics 365 to Power BI Analytics Path

Today's blog post was written by Keith Mescha, Principal Architect at Sonoma Partners.

If you have tried to connect to your Dynamics 365 org via the OData API feed through Power BI, you probably struggled to make sense of how to build something useful from that experience. I’m sure you have read all the blogs out there suggesting the solution of syncing the data down to a SQL database and building your reports from there. It is often a decent solution but requires additional setup, configuration, licensing, and gets beyond the abilities of many Dynamics Admins. We do support that approach and have delivered several solutions using this method including for our own reporting needs. However, we also felt there is a need for a better, quicker option for customers.

To that end, we sat down and designed an accelerator for Dynamics to make this a reality.

Why is this a better option?

  1. You can use existing Dynamics system or personal views to auto create Power BI Queries. We don’t limit you to this as you can also augment those with other fields, but they serve as good starting point. Not sure what you need in your report, use advanced find get close and save that as a view and then build that out more later as you go.

  2. Options sets and lookup values are automatically mapped for you in the query. When hitting the OData API by default, you get the integer values and GUIDs of options sets and related records. This requires a lot of additional work to relink related tables and values which is tedious work that is hard to maintain over time. With this tool, we do that mapping for you based on the Dynamics configuration.

  3. All fields are renamed to the labels as you have defined them in your Dynamics configuration. This cuts down on a bunch of work typically required when pulling in data other ways as schema names are what usually comes over. No more typos or copy paste errors, and the reports you build will have the same field names as users see in Dynamics on forms and views.

  4. Also is using the well-documented FetchXML queries options in the WebAPI you are limited to the 5,000 record limit to you queries. Well we have written some sweet M code to overcome that limit paging. Best of all you can deploy this to the PowerBI Service and schedule updates of your data so you are not limited to just PowerBI Desktop.

*Currently this solution will work in PowerBI Desktop for Online and on Prem IFD enabled Dynamics orgs. However, if you deploy your pbix file to the Online PowerBI Service you will only be able to schedule data refreshes if you have a Dynamics 365 Online Org.

Overall the main goal here is to let you work in Power BI building cool charts and analysis to solve your challenging business problems and less time plumbing and wrangling your data into Power BI from Dynamics.

Here is a quick walkthrough of what this solution provides.

Once you install our managed solution into your org you can access the tool via the solutions area of Dynamics.

Keith 1

Upon launch, you can pick any entity and any view saved against that entity. In this example, we are using the out of the box Active Accounts view. After selecting that view I can chose additional attributes or even select the “All Attributes” option and pull all fields. I will caution that the more you pull, the slower and harder your data will be to work with in Power BI so the best practice is to be as selective as you can in building out a query.

Keith 2

One you have all the attributes you need you can click the Generate button.

Keith 3

At this point we present to you a screen where you can copy queries to the Power BI desktop application. The first item is the URL for the API of the org you are in. Be diligent with the version at the end of the query. Dynamics 365 is going to be V8.2. This serves as the anchor for other queries and should be created first in Power BI as other queries will rely on this.

Keith 4

Copy this URL into a Blank query in Power BI desktop and rename that query “CRMServiceUrl”. Casing is important here so be sure to make sure it’s put in exactly as seen here.

Keith 5

On the Accelerator page hit the Next button to move onto the next query which is the main data query for the Account view we selected. As you can see here there is quite a bit going on. What this step is doing is generating a FetchXML query using an OData Query against the WebAPI. This is returned to us as a JSON document which we use the M language of Power BI to parse into a table and then rename all the fields to the Labels as you have defined in your org configuration. Finally returning that into usable data set, essentially doing all the heavy lifting of pulling your data out of Dynamics for you so you can focus on building reports.

Keith 6

Paste that query into Power BI as another blank query and rename it “Accounts.”  Notice that this query is referencing the URL we created in our first data set.

Keith 7

This point you should have a valid data set with fields that match your labels on the entity as setup in Dynamics. You will need to follow the auth prompts and login to your org to refresh the data.

Keith 8

You can build whatever reports you need at this stage in Power BI. If you want to pull other related data from Dynamics, you can do that and model the relationships in Power BI as needed.

Keith 9

Here I have created a simple chart counting the number of accounts by City. I then deploy that solution to My Workspace on Powerbi.com. At this point I can go into my deployed data set and schedule a refresh for my data.

Keith 10

From here I can enter credentials for my org. Note that the data refresh will use that account when making the calls to the API so if be sure to use an account that has the rights to read all the data.

Keith 11

Choose Oauth2 as the Authentication method, which from we have found is only currently available with Online orgs. We have tried on IFD enabled orgs and do not get the Oauth2 prompt to allow us to configure the refresh. You will be prompted to enter your credentials on a few other pages not show here. After this step is complete your data will start refreshing on the schedule you set.

Keith 12

You can check the Refresh history from the dataset area in PowerBI.com.

Keith 13

From here you can keep on building out additional queries by using our solution then copying those queries into your Power BI desktop file. After you make changes deploy them to the Power BI Service for use in dashboards.

We have made this Dynamics Managed Solution available on our website for you to try out.

*Please make sure that you have downloaded and are always using the latest version of Power BI Desktop when using this solution.

If you'd like to view this in solution in action, you can watch our demo video here.

We are happy to assist in your BI projects and have a team of data professionals ready to assist you as you extend your Dynamics system to other O365 offerings like Power BI.

Topics: Analytics Microsoft Dynamics 365