Microsoft CRM 365 is a cloud-based CRM solution, used by numerous customers worldwide. It has got some internal limited reporting capabilities, but if you want to do more with your data, you should consider moving it from Microsoft CRM to a DWH environment.
Microsoft now has a tool called Azure Synapse Link for Dataverse, that will help you export the data to Azure synapse analytics, Microsoft cloud-based DWH ecosystem. This tool replaces the soon to be deprecated Data Export Service (retiring in November 2022).
Here is the announcement - https://powerapps.microsoft.com/en-us/blog/do-more-with-data-from-data-export-service-to-azure-synapse-link-for-dataverse/
Azure Synapse Link for Dataverse is a service that monitors changes on CRM and replicates the data from CRM entities to Synapse data lake files. Microsoft promises us near real-time performance.
The data from CRM is being moved to CSV files in Azure data lake storage. You can choose which entities (contact, accounts, etc.) to sync, including customized entities. Each entity has its own folder, and the files inside are partitioned for better performance (you can choose between year and month for each entity).
After your CRM data is in synapse, you can:
· Connect to it with PowerBI for reporting
· Run SQL queries against your data with serverless SQL pool
· Use Synapse Apache Spark to process and analyze the data
· Ingest your data to other systems with Synapse pipelines
Azure Synapse Link for Dataverse limitations:
· It moves the CRM data to data lake files, and lacks the ability to sync to SQL
database, either inside synapse (dedicated SQL pool) or outside (Azure SQL database). You can, of course, build a pipeline that will move the data the way you want it.
· The data lake files it creates are CSVs, which are limited compared to other data lake
file types. For example, querying Parquet or Avro files is usually much faster than
CSV files.
· The files are partitioned on the createdon field (of the CRM entity), which is usually
not the best solution, and you can't change that.
Still, it's a very good starting point for your CRM DWH journey.
Some helpful links:
How to set it up - https://docs.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-synapse
Query data with serverless pool - https://docs.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-serverless
Frequently asked questions - https://docs.microsoft.com/en-us/power-apps/maker/data-platform/export-data-lake-faq