Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications

| | |

Power BI Data Refresh Performance

Post Author: Joe D365 |

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:

  • BI reports are set with daily refresh in Power BI Service with professional licenses
  • Data source from an out-of-the-box entity and a custom entity in Dynamics CRM 2016 online (8.1)
  • OData connection and oAuth2 authentication method

If you are looking for how to connect Power BI to Dynamics CRM 2016 Online, please refer to our blogs:

  1. Connect Your CRM Online Data to PowerBI for Powerful Analysis
  2. How to Connect to Dynamics 365 Data from PowerBI Desktop

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?

  • PBIX file size (should be less than 250 MB)
  • Data Source Connection with Instance Web API rather than Org. Service
  • Data Source Set with OAuth2
  • User Credentials and License Type in O365
  • Security Roles in CRM
  • Editing Query (Transform Tables and Columns)
  • Relationships (Join, cross filter directions, fact-dimension tables as star-schema, the same relationships as CRM)
  • Data Types and column names
  • Selecting right unique key (GUID) columns (_value)
  • Calculated columns and DAX Measures are optimized
  • The number of visuals in reports (less than 4-8 per page)

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:

  1. Remove Unnecessary Columns and Tables
  2. Update the Data Types
  3. Filter Data (Created on)

Power BI Data Refresh

However, we changed the steps to this:

  1. Filter Data (Created on)
  2. Remove Unnecessary Columns and Tables
  3. Updated Data Types

Power BI Data Refresh

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!

By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

One comment on “Power BI Data Refresh Performance”

  1. You may even bring it to under 19 minutes using data from views instead of native dynamics connector which doesn't allow prefiltering.

PowerObjects Recommends