Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications

|

Dynamics 365 Customer Engagement (CE) Online Refresh Limitation

Post Author: Joe D365 |

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

  • Pro License
  • Design Power BI reports for refresh to complete within 2 hour (increased from 1 hour to 2 hours)

B. Pbix file size is greater than 1GB

  • Premium License
  • Design Power BI reports for refresh to complete within 5 hours (increased from 4 hours to 5 hours)

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

  • Pro License
  • Design Power BI reports for refresh to complete within 1 hour

B. Pbix file size is greater than 1GB

  • Premium License
  • Design Power BI reports for refresh to complete within 1 hour

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 ELT/ETL solution such as Dynamics 365 Online to Data Lake and connect from Power BI to the Data Lake. There are other solutions to be considered based on the project 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!

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

Leave a Reply

Your email address will not be published. Required fields are marked *

PowerObjects Recommends