In this blog, we would like to share a lesson learned from a project when we used a Dynamics 365 Customer Engagement (CE) online data source connector, called Instance Web API, for Power BI reports.
We published a blog post in June 2019, “Power BI Scheduled Refresh Impacts Power BI Report Development?” In it we discussed how we approached when starting a Power BI report project and learned about data refresh impacts in the beginning of the project.
We asked three starter questions to learn more in depth about the data sources, the data model, and embed options to deliver lower cost and higher performance solutions to our customers. Moreover, we highlighted data refresh limitations in the blog above and summarized:
Conclusion is that pbix file size tells us license options (cost) and there is a way we need to design data models with data sources and minimize the file size to optimize reports for data refresh performances.
Here is our lessons learned on Dynamics 365 Online Web API connector for Power BI reports. There are two points:
- Pbix file size is less than 1GB
- Pro License
- Design Power BI reports for refresh to complete within 2 hour (increased from 1 hour to 2 hours in May 2020)
- Pbix file size is greater than 1GB
- Dedicated Premium License
- Design Power BI reports for refresh to complete within 5 hours (increased from 4 hours to 5 hours in May 2020)
The second point is that regardless the sizes of the pbix file limitation and the different license type (Pro or Premium), the data refresh is limited to 1 hour window if Dynamics 365 Online Customer CE Instance Web API is used to connect from Power BI. For example, we had a 400 MB Pbix file with a few million records in Lead and Activities entities and the daily refresh was successful after go-live, which means that the refresh completed within an hour. A few months later, the Activities and the Lead records increased at a high rate. We started receiving a time-out data refresh error. We checked the Pbix file size and it was less than 800 MB, which was still less than 1 GB limitation with Pro license.
The Microsoft documentation says the following…
Refresh fails when updating data from sources that use AAD OAuth
The Azure Active Directory (AAD) OAuth token, used by many different data sources, expires in approximately one hour. You can run into situations where loading data takes longer than the token expiration (more than one hour). Although Power BI service waits for up to two hours when loading data, the data loading process (data refresh) can fail due to the AAD token expiration.
Data sources that use AAD OAuth include Microsoft Dynamics CRM Online, SharePoint Online (SPO), and others. If you’re connecting to such data sources, and get a credentials failure when loading data takes more than an hour, this may be the reason.
The following list is a quick reference to license options and data refresh performance based on Pbix file limitations when using Dynamics 365 Online Web API connector:
- Pbix file size is less than 1GB
- Pro License
- Design Power BI reports for refresh to complete within 1 hour
- Pbix file size is greater than 1GB
- Dedicated Premium License
- Design Power BI reports for refresh to complete within 1 hours
Our recommendations:
- Measure and estimate the maximum Pbix file size for the reporting periods in the beginning of the project requirement phase and test the data refresh in Power BI Service (Not in Power BI Desktop) and make sure the refresh completes successfully. It is better to include data model and visuals when testing if possible.
- If the refresh fails, consider not using Dynamics 365 CE Web API and design data source with Dataverse, Common Data Service (Legacy) or other ELT/ETL solution such as Dynamics 365 Online to Data Lake or many other solutions to be considered based on the report requirements.
- As a temporary solution, apply incremental refresh policy to one or more of the datasets and reduce the refresh time. Please do a proof of concept beforehand in non-production environment. The incremental refresh may or may not work depending on the data model you have.
Happy Power BI’ing!
