328.77 million terabytes of data are generated every day - this is the estimate for the first quarter of 2023 (1). These are massive amounts of data on a global scale. On a smaller scale, such as an enterprise, it's difficult to estimate because it depends on the organisation in question, but one thing is certain - collecting, processing and analysing this data is the key to business success today. Why is the Data Lake service so important for data analysis and reporting in Microsoft Dynamics 365 Finance and Operations? How can you tame the data by integrating Microsoft Power BI analytics with Data Lake? We have drawn up some tips.
First the export, then the analysis
The conclusions reached from data analysis, the strategies built on their foundation and the decisions based on them enable an organisation to grow, win customers and stay ahead of the competition. This is why it is so important to use a repository where data can be easily collected and from which information can be easily extracted whenever it is needed.
Over the course of several years implementing Microsoft Dynamics 365 F&O and Power BI, we have faced a number of data export and reporting issues. E.g. by not having direct access to the transactional database, data had to be extracted in a properly aggregated way.
Integrating Dynamics 365 F&O with Microsoft Power BI has, up until recently, been possible by using an Entity store service, an OData connection or BYOD technology.
These three solutions had both advantages and disadvantages. Distinctive among the latter were:
- Low performance of the technology used
- Low refresh rate and data processing
- High infrastructure maintenance costs
In the first half of 2022, Microsoft officially released the Export to Data Lake feature on production environments, allowing data to be moved from the Dynamics 365 FO environment in the form of tables and entities to the Data Lake service on the Microsoft Azure cloud platform. The new feature eliminated the drawbacks of previous solutions and allowed for near real-time reporting. Easy access to data, analysis and ad hoc reports generated from up-to-date data allow for accurate and immediate business decisions.
Data Lake - what is this?
A data lake is a centralised repository where large volumes of data can be collected and stored in their original form. The open and scalable data lake architecture can accommodate all types of data, structured, semi-structured and unstructured, from any source. In the context of D365FO, Data Lake allows you to store copies of records from the system database, tables and entities as CSV files. And it has several useful features that have displaced the shortcomings of Data Lake's predecessors.
1. Automatic updating
What characterises this solution and is also its greatest advantage is its ability to automatically update stored data practically in real time, ensuring it is compatible and consistent with the system source. No more manual setup and management of exports - changes to data in Data Lake are visible minutes after they are entered into the D365 system.
2. Lower implementation and maintenance costs
Choosing Data Lake as the data repository for the D365FO system entails lower implementation and maintenance costs for the infrastructure. Compared to, for example, BYOD technology, the cost of storing and processing the same volume of data is lower. Data Lake is an Azure service, which means that Microsoft is responsible for maintaining the infrastructure and the user actually pays for the volume of data stored within the service.
3. Exporting without overloading the system
An important advantage of Data Lake is the smooth process of exporting data to the repository. Exporting data does not affect system performance, which was the case when exporting via BYOD. The agility and speed of data transfer to Data Lake make this repository a functional source of data for Power BI reports.
Reporting in Power BI with Data Lake data
We have applied Data Lake in several ANEGIS implementation projects. In one of these, we constructed and implemented an architecture involving exporting data to Data Lake from the D365 system and reporting in Power BI.
Over 200 reports were created as part of the project, including various types of control, financial, consolidation and management reports. Trial Balance, Balance Sheet, Employee Headcount, Actual vs Budget or Subledger Reconciliation are just a few. The biggest challenge, however, was the consolidation reports, which required the integration of data from several IT systems present in the company.
Export to Data Lake connected the entire enterprise system environment to the designated repository stores, and we used the Azure Synapse Analytics service to support data processing and analysis.
Azure Synapse Analytics - extra integration, storage and analysis
Azure Synapse Analytics was an important part of the architecture of the above-mentioned project. It is an unconstrained, unified analytics platform that combines integration, enterprise data warehousing and big data analytics. With the SQL views created with the help of Azure Synapse Analytics, the raw data was transformed into a form that enabled it to be analysed in Power BI. In Azure Synapse Analytics, it is possible to create data pipelines, which in this case were used to periodically generate so-called data snapshots for auditing purposes.
Data Lake - is it worth it
The new data export model is flexible, efficient and cost-optimised. With Export to Data Lake, we eliminate all the previous reporting problems that resulted from the limitations of Entity store, OData, BYOD.
- With regard to BYOD - we no longer need to monitor exports, which reduces the costs generated by the process.
- Compared to Entity store - Export to Data Lake removes the problem of frequency of data export and customisation of aggregated entities.
- Exporting data to Data Lake, on the other hand, does not have the performance problems that the OData service had.
Data Lake as a central repository for Dynamics 365, integrated with Power BI, allows analysis and reporting of always up-to-date data in almost real time, which for a modern organisation operating in a dynamically changing and evolving business environment is a unique advantage.
Source:
- What Is Big Data? How To Make Important Data Driven Decisions, https://www.g2.com/articles/big-data
