Microsoft Cloud for Healthcare launches this week and HCL-PowerObjects is proud to be one of eight Global Systems Integrators selected by Microsoft to offer this transformational new product!
Performance is a critical topic that we need to consider before and after we deliver applications. In this blog, we are going to discuss a scenario where the data refresh in Power BI Service keeps failing and explain why it is happening. We will also introduce a way to reduce refresh time by about 63% in Power BI Service.
Before we continue, the following is the environment we are working with:
If you are looking for how to connect Power BI to Dynamics CRM 2016 Online, please refer to our blogs:
In this scenario, a data refresh failed due to timeout and the error message in the Power BI Service says that the user's credential was not recognized. Upon investigation, the user security in Dynamics 365 and the licenses in Office 365 are not the issue. There is however, a current limitation on the Azure Active Director (AAD) OAuth token that expires in approximately one hour. That is the reason why the data refresh failed in Power BI Service. It means that data refresh must be complete within 60 minutes. We verified that if the data refresh lasted more than 60 minutes, it failed in Power BI Service.
Now we must focus on why the queries created in the Power BI reports are taking over 85 minutes to complete refresh in the Power BI Desktop. The volume is 17 months of data with about 128,000 records in Case entity and 1.22 million records in custom Event entity in Dynamics 365.
The following list shows the troubleshooting items performed. There is a bulls-eye in this list that resolved the performance issue. Which one will it be?
Editing query is the answer in this scenario! Right after a data source was established in the Power BI Desktop and the entities (tables) were selected to build queries, we start applying changes in queries in the following steps:
However, we changed the steps to this:
Filtering the dataset before removing the extra columns and tables changed the data loading time significantly! The download took from 85 minutes to 44 minutes in Power BI Desktop (48% ¯ ) and the refresh time in Power BI Service took 30 minutes (63% ¯). This minor change made difference in this particular scenario.
Finally, it is also recommended testing out both cases above and choosing the best steps accordingly.
For more helpful tips and tricks, be sure to follow our blog!
Happy PowerBI'ing with Dynamics 365 data!