February 5, 2020 | By admin

Currently, the options for getting data out of Dynamics 365 for analytics or business intelligence are somewhat limited.

Existing options rely on expensive third-party tools or the built-in data export service, which is connected to a potentially costly Azure SQL database.

With these options, you are naturally encouraged to carefully select what data you choose to export, in order to minimise storage costs. You may then move this export of data again, this time to a data warehouse, where you can perform transformations to make it usable and consumable for self-service BI, such as Power BI. This process does not easily empower deeper analytics and the use of machine learning using tools such as Databricks.

The alternative option is to use a custom data pipeline to pull data from the Common Data Service (CDS) into an Azure Data Lake: a cost-effective, scalable storage platform designed for analytics performance. This requires an orchestration tool, like Azure Data Factory, which calls for data engineering skills to set up and maintain but only attracts a fraction of the cost, in comparison to an Azure SQL database.

With Dynamics 365 sitting on top of Microsoft’s CDS, data is stored and organised in a secure and standardised format, the Common Data Model (CDM). The CDS goes beyond Dynamics 365 and underpins the entire Power Platform providing a predictable model of data across many systems and tools.

screengrab for export to data lake blog

The solution – Export to Data Lake

A feature just hitting general availability, appears to  be the answer to the above issues. The new Export to Data Lake service simplifies your access to CDS data, mirroring Microsoft’s vision for the modern data platform by bringing the data out to an Azure Data Lake, as a central store for raw data. This is the key to getting the data where you need it for data warehousing, machine learning, reporting and any other integration purposes.

Azure Data Lake adopts an inductive (bottom-up) approach to information management where all data is collected in its raw state so that patterns and conclusions can be derived from the raw data. This type of analysis allows for more advanced analytics, such as doing predictive or prescriptive analytics, as data of all types can be arbitrarily stored in the data lake before any formal definition of requirements or schema. This approach is often called ELT (Extract-Load-Transform)

The Export to Data Lake service is a pipeline to export CDS data to Azure Data Lake Gen 2, continuously, after an initial load and in regular snapshots. This works for both standard and custom entities and replicates all operations (create, update and delete).

CDS to Azure data lake image

Export to Data Lake is enabled through the Power Platform admin portal and requires no configuration beyond a connection to an existing data lake, making it very simple to set up.

Some caveats exist that need to be in place beforehand:

  • The CDS and Azure Data Lake must be in the same region.
  • The user setting up the service must have owner rights on the Data Lake resource, to allow delegation of permissions.
  • Any entities you want to use with the service must have change tracking enabled.

Once the service is set up, you can view the status of each entity within the Power Platform portal. Your CDS data is kept in sync with the Data Lake while regular snapshots are also taken to provide timely intelligence to changes in your data.

By using Export to Data Lake, you don’t need to compromise on the data you chose to extract for analytics. What is more, by embracing the cost-effective Azure Data Lake platform, you open up the ability to explore more advanced analytics and integrations with minimal effort.

Discover more about how Telefónica Tech can help you to enhance the potential of your data and derive deep insights across your organisation.