Data refresh in Power BI service keeps failing and we shall find out ways and means to reduce refresh time. The business analytics service by Microsoft that provides interactive visualizations with self-service business intelligence capabilities seems to fail perhaps due to time-out issue.
Performance is taking a beating lately which is a critical matter that needs to be considered and resolved with tips that could be beneficial.
Presently we are working in the following environment:-
- Backed by professional licenses, BI reports are set with daily refresh in Power BI Service.
- The Data source is a default entity and a custom entity in Dynamics CRM 2016 online (8.1)
- OData connection and oAuth2 authentication method
Please refer to blogs for how to connect Power BI to Dynamics CRM 2016 Online.
- Connect Your CRM Online Data to Power BI for Powerful Analysis
- How to Connect to Dynamics 365 Data from Power BI Desktop
Owing to timeout, a data refresh failed in the present situation and the error message in the Power BI Services states that the user’s credential was not recognized. However, investigations revealed that issue has not related either to the user security in Dynamic 365 or the licenses in Office 365. However, the current limitations on the Azure Active Director (AAD) OAuth token that expires in approximately one hour. Primarily this is the reason why the data refresh failed in Power BI Service, in other words data refresh must be completed within 60 minutes flat.
The queries created in the Power BI reports are taking over 85 minutes to complete refresh in the Power BI Desktop. The data is voluminous with over 128,000 records in Case entity and 1.22 million records in custom Event entity in Dynamics 365. This is where the focus is.
The following list shows the troubleshooting items performed.
- PBIX file size (restrict to 250 MB)
- Data Source Connection with Instance Web API rather than Org. Service
- Data Source Set with OAuth2
- User Credentials and License Type in O365Security 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 & Column Names
- Selecting right unique key (GUID) columns (mention value)
- Calculated Columns & DAX Measures are optimized
- The number of visuals in reports (less than 4-8/page)
In the current scenario, Editing query resolved the performance issue!
Immediately after establishing a data source in the Power BI Desktop and the entities (tables) selected to build queries, changes in queries in the following steps are applied:
- Remove Unnecessary Columns & Tables
- Update the Data Types
- Filter Data (Created on)
There is a significant change in the data loading time by filtering the dataset before removing the extra columns and tables. The minor changes made the following difference in this particular scenario:
- Download took between 85 minutes and 44 minutes in Power BI Desktop 48 %↓)
- Refresh time in Power BI Service took around 30 minutes (63%↓)
In summary, it is recommended to test both cases and appropriately select the best the suits.
