Real Time Reporting From Dynamics 365 CRM for Large Data Sets

Real Time Reporting From Dynamics 365 CRM for Large Data SetsFollow
Jacob Munster
Jacob MunsterApr 21, 2022 01:29 PM
Hi All, We are looking for the best practice of connecting Power BI to Dynamics 365 CRM with the goal …
1. Real Time Reporting From Dynamics 365 CRM for Large Data Sets

Jacob Munster
Posted Apr 21, 2022 01:29 PM
Hi All,

We are looking for the best practice of connecting Power BI to Dynamics 365 CRM with the goal of achieving near real time reporting from Dynamics. Currently, we have an Azure SQL Server supported by Microsoft’s Data Export Service, which they announced in November 2021 that they will be deprecating later this year. Our goal is to achieve near real time reporting through Direct Query for large data sets (millions of rows of data).

The options we know to date are:
Continuing the utilize Azure SQL with Data Pipeline built from Synapse or Azure Data Factory to load data from Dynamics via Azure Synapse Link to Dataverse.
The performance for large data sets via Direct Query from Azure SQL has not always met our goals, however.
Querying Dataverse directly – to our understanding this is off the table due to the query limit of 83MB
Querying Azure Synapse SQL directly with data loaded from Dynamics vis Azure Synapse with Link to Dataverse – from our testing the performance here has not been great
Has anybody else looked to achieve similar results? If so – what have you had success with? Thanks!

——————————
Jacob Munster
Senior Associate | Project Management
Jacob.Munster@captrust.com

CAPTRUST | 4208 Six Forks Road, Suite 1700 | Raleigh, NC 27609
919.870.6822 office | 919.278.9822 direct | 919.870.8891 fax | 800.216.0645 toll free
http://www.captrust.com | http://www.captrustcommunityfoundation.org

Our mission is to enrich the lives of our clients, colleagues, and communities through sound financial advice, integrity, and a commitment to service beyond expectation.
——————————

2. RE: Real Time Reporting From Dynamics 365 CRM for Large Data Sets

Silver Contributor
Mike Hammons
Posted Apr 22, 2022 02:10 PM
Hi Jacob,

You have outlined your options correctly. A few considerations:

Using Azure Synapse Link is the best approach for getting near real-time data into Azure Data Lake / Azure SQL. Generally changes in CE are synced within a few seconds. A large B2C customer with about 500k transactions daily and after some initial large table sync issues were resolved, performance and updates have been quite high.

Out of the box, just using the exported tables from CE, the performance will be moderate. It is best to think of those as staging tables. Using either Power BI streaming Dataflows or ADF, transform the staged data into more report friendly formats. e.g. removing columns not required for reporting, updating column types, creating surrogate keys, pre-aggregating data, etc.

Consider using Materialized Views in dedicated SQL pools.

Consider Composite Models to store dimension data in the model and DQ large fact tables.

Consider pushing the cleansed data directly into tabular models instead of standard SQL.

Best Regards,

Mike

Consider

——————————
Mike Hammons
Director, Business Intelligence
AKA Enterprise Solutions
——————————

3. RE: Real Time Reporting From Dynamics 365 CRM for Large Data Sets

Adam Artur Boltryk
Posted Apr 27, 2022 01:06 PM
Hi Jacob,

With my experience – PowerBI is NOT business critical reporting tool.
It’s perfect for daily/weekly/monthly reports, when service lags do not make any charm on your operation.
All looks nice when it works.
But then come service lags, it’s not funny.

And noone will record your P1 call.

Regards,

——————————
Adam Artur Boltryk
Business Analyst
——————————