powerobjects: Power BI Data Refresh Performance

[Blog bot]

Источник: https://www.powerobjects.com/2018/01...h-performance/
==============


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)

However, we changed the steps to this:

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

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!



Источник: https://www.powerobjects.com/2018/01...h-performance/