Faster Reporting with Dynamics

Anyone who uses Dynamics 365 Online will likely be using Power BI as their core business intelligence tool. Power BI can connect to the entities that sit in Dynamics by utilising the Dynamics ODATA feed. This feed allows users to start pulling data from all system entities (including custom entities) and perform business intelligence. However, it gets to a point where you’re pulling so much data that performance starts to degrade or you want to do more with the data you’re pulling. ODATA can only go so far before it becomes unmanageable and with the latest version of Dynamics 365 Online v9.0, could change. This blog will hopefully give you a better understanding of your options when moving to v9.0 or moving away from an ODATA feed.

For both cases the Data Export Service is an AppSource plugin you can install on your Dynamics tenant to start synchronising your data to a SQL database. This is a Microsoft plugin that has been created to give developers better control over their data. Once the plugin has been installed, you’ll need to create Azure resources for the database and key vault. You will also need to create a profile against the service in Dynamics. The profile allows you select specific entities that will be synchronised over to your new database in Azure. The initial sync will create all the relevant tables and populate with the stored data at that point in time. Following this a delta sync will occur updating any records. This data sync will also include the following:

  • Changes are updated every few minutes
  • Metadata changes will be synchronised
  • Built in recovery
  • Many more.

Now there’s really no point me recommending this service unless I provide you with some benefits. Below are some of the benefits you’ll be getting once you move to the Data Export Service:

  • Reduced constraints on the production Dynamics 365 Online instance.
  • Advance analytics with Power BI
  • Machine Learning Models
  • Many more.

There are however some drawbacks to using this service that you should be aware of. Below is a list of the drawbacks I’ve found:

  • Additional cost of having Azure resource.
  • Additional cost to manage you Azure resources.

More details of this service can be found in the following Microsoft developer blog poster in April 2017:

I did mention previously that the ODATA API could change. There are currently multiple versions of the Dynamics 365 api with the latest being v9.0. From the Power BI forums, it’s apparent that some users have been experiencing some issues with this API. Additionally, anyone using previous versions will have issues updating as each api presents the data slightly differently. Resulting in either minor query changes or a complete Power BI report update. The above solution is one way to get around this as there is no longer a need to user the ODATA api.

About the author