The Azure Data Lake - Death of the SQL Database in Microsoft Dynamics 365

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 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…