You may be eligible for a free six-month trial of Dynamics 365 Marketing! Read this blog to learn how it works.
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:
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…
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:
Happy Power BI’ing!