In this webinar, our experts showcase a variety of demo use cases of how different components of the...
At PowerObjects, our consultants are 100% dedicated to Dynamics 365 and Microsoft Business Applications. In this blog, we would like to highlight a lesson learned from a project when we used Dynamics 365 Customer Engagement (CE) online data source connector, called Instance Web API, for Power BI reports.
We published a blogpost in June 2019, “Power BI Scheduled Refresh Impacts Power BI Report Development?” It was a study blog in which 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 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 that there is a way we need to design data models with the data sources and minimize the file size to optimize report for data refresh performances.
Here are our lessons learned on Dynamics 365 Online Web API connector for Power BI reports. There are two points. The first point is that pbix file size limitation was updated for data refresh in May 2020. Great and thanks for Microsoft Power BI Team!
A. Pbix file size is less than 1GB
B. Pbix file size is greater than 1GB
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 a 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 millions 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. 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…
https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-troubleshooting-refresh-scenarios
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:
A. Pbix file size is less than 1GB
B. Pbix file size is greater than 1GB
Happy Power BI’ing!