Read more
About Azure Analytics, Data & AI
The Azure Data Lake - Death of the SQL Database in Microsoft Dynamics 365
06 Mar, 2021
The Data and Analytics Roadmap
If you’ve been working in the Business Intelligence and Analytics space, within the realm of Dynamics AX and Dynamics 365 for the last few years, you may have felt it’s taken the backseat, and in some cases a step backwards, with no notable changes having been made to the Out-of-the-Box solutions for several years now, aside from moving to Azure. Within the last 12 months, however, things have started to gain traction with the introduction of Azure Data Lake. I’ll talk more about some of the new features below.
The Data and Analytics Roadmap
If you’ve been working in the Business Intelligence and Analytics space, within the realm of Dynamics AX and Dynamics 365 for the last few years, you may have felt it’s taken the backseat, and in some cases a step backwards, with no notable changes having been made to the Out-of-the-Box solutions for several years now, aside from moving to Azure. Within the last 12 months, however, things have started to gain traction with the introduction of Azure Data Lake. I’ll talk more about some of the new features below.
What is an Azure Data Lake?
Data Lakes are nothing new, but we have certainly seen a flux in popularity over the last few years and they are commonplace in a lot of Big Data and Streaming Analytics Architectures. This is down to the low cost, scalability and accessibility. As you’re not paying for any compute, there’s not many options that come in quite as cheap. Azure Data Lake includes all the capabilities required to make it easy for developers, data scientists and analysts to store data of any size and shape and at any speed, to enable all types of processing and analytics across platforms and languages.
What is the point of an Azure Data Lake?
Data Lakes are system and file type agnostic. Because of this they do not care what format the data is in, where data has come from, nor where it is going to.
If you are a Finance Consultant, you will be able to access all financial data available in the system on a near-real time basis and if you’re receiving supplementary data from an external system, that can be stored here too and you can mash it up. You could also generate snapshots within the Data Lake to be able to generate your monthly P&Ls and Balance Sheets.
If you are BI Developer and you’re compiling complex reports using disparate sources, this can all be centralised into one location; then transformed, modelled and visualised using Self Service tools such as Power BI.
If you’re a Data Scientist, all your data will be kept in its rawest format and can be used in training Machine Learning (ML) and Artificial Intelligence (AI) Models as well as having access to some of the modelled data to enrich it further.
Dynamics 365 Entity Store in Azure Data Lake
Last year we saw something pop up on the roadmap that made Business Intelligence and Data folks ears stick up “Entity store data is available in Azure Data Lake” For those that are unaware, Entity Store in Dynamics 365 F&O is what Analysis Services (SSAS) was for Dynamics AX 2012 and provides you with a kind of Multi-Dimensional Cube that can be used for Business Intelligence. In Dynamics 365 F&O, the Entity Store is used exclusively for the Out-of-the-Box Embedded Power BI Reports.
What can you do with the Entity Store Data, now it’s in the Azure Data Lake?
In short… not a lot. The data exported is already modelled, and naturally is very siloed. Power BI doesn’t recognise it as a cube, and we must therefore define the relationships all over again. So, if you’re looking to create or extend a Data Warehouse, you’ll still need to go down the Bring Your Own Database (BYOD) route and use Azure SQL DB to stage all your data in. This can be quite an expensive solution if you don’t have a spare Azure SQL DB or are a small organisation, since you need to spin up a new SQL Instance and pay for compute, storage and all, to hold a few staging tables for your Data Warehouse Solution.
But we still had hope, the ball’s rolling now, and I’ve still got a lot data in my Finance and Operations and Dynamics 365 CDS Databases that I want in my Data Warehouse; there must be a better way to get at it…
Azure SQL Database
And then it started, the Death of the SQL Database in Dynamics 365
In October 2019, we saw Exporting CDS data to Azure Data Lake is Generally Available. Excellent! We can now start moving away from DES and get all Customer Engagement data into our Data Lake.
In April 2020, not one, but two features really caught my eye. Finance and Operations entities in a customer’s data lake and Tables in a customer’s Data Lake Storage Gen2 data lake and is currently scheduled for general availability (GA) in August 2020. With this update we will be able to export all our Data Entities that were once used in our BYOD solution and the source tables to the Azure Data Lake. This will not only make the transition from BYOD easier, but it’ll keep our Data Scientists happy that they can access the data in its rawest form.
These features come with the final blow:
“This feature enables customers to reduce their reliance on Bring your own database (BYOD) and to eventually retire the use of BYOD.”
Although there haven’t been any dates confirmed, we can see it’s clear that we will be moving away from Azure SQL DBs to stage our data when working with Dynamics and would be wise to prepare early.
The final blow:
“This feature enables customers to reduce their reliance on Bring your own database (BYOD) and to eventually retire the use of BYOD.”
Although there haven’t been any dates confirmed, we can see it’s clear that we will be moving away from Azure SQL DBs to stage our data when working with Dynamics and would be wise to prepare early.
To learn more about how we can help you on your Data and Analytics Journey with Dynamics 365, CDS and Power BI please contact us.