BI4Dynamics D365 Finance and Operations Data Lake Architecture

BI4Dynamics state-of-the-art architecture powered by Microsoft technology ensures no compromising between service costs and BI capabilities.

Data transformation from D365F&O to Power BI & Excel

The unique data transformation with Data Lake and Synapse ensures first-in-class business intelligence from D365F&O. BI4Dynamics automated process builds Data Warehouse, and Analysis Services and ensures powerful analysis.

Data transformation is done automatically in the following steps:

  1. Preparation: Export F&O tables to the Data lake and insert them into Data Warehouse.
  2. Transformation: Data Warehouse Automation with best-in-class content.
  3. Consumption: Analyze data with visualization tools, such as Power BI and Excel.

  D365 F&O Data Transformation

Export D365 F&O tables to Data Lake

D365 F&O database is not directly accessible so we need to use Microsoft’s Export to Data Lake extension. The extension enables the export of 200 tables suitable for analytics as CSV files in Data Lake Storage.

The refresh time is not scheduled and it cannot be affected by BI4Dynamics, since it is set up by Microsoft. Export to Lake operation is not affecting the production process.

Azure Data Lake to BI4Dynamics Data Warehouse

CSV files are inserted as tables into SQL Data Warehouse with Azure Synapse.

Data Transformation is the process where BI4Dynamics takes the raw copy of tables that landed in the BI4Dynamics staging area and transforms it using SQL objects (stored procedures, views, dimensions, facts).

The SQL engine is super scalable and can quickly process terabytes of data. SQL engine can support any model. SQL code generated in this data warehouse automation transformation process is fully automated.

There is no need for specific SQL knowledge. All customizations are wizard-driven and require only BC knowledge.

Data Warehouse data are pushed to the Tabular database for a better user experience. Users will connect with ExcelPower BI, or any other tool, where also permissions are set.

The tabular database can be implemented on:

  • On-Premises using existing hardware.
  • Azure Analysis Services, hosted by Microsoft, paid per service, not per user.
  • Power BI Premium Capacity – paid per user, not per server.

The business intelligence content is the same in all cases. We will help you choose the right environment by taking into consideration database size, the number of users, geographic usage, self-service needs of advanced users, needs for sending reports to users’ emails, and other factors.

Arhitecture

Data transformation from D365F&O to Power BI & Excel

The unique data transformation with Data Lake and Synapse ensures first-in-class business intelligence from D365F&O. BI4Dynamics automated process builds Data Warehouse, and Analysis Services and ensures powerful analysis.

Data transformation is done automatically in the following steps:

  1. Preparation: Export F&O tables to the Data lake and insert them into Data Warehouse.
  2. Transformation: Data Warehouse Automation with best-in-class content.
  3. Consumption: Analyze data with visualization tools, such as Power BI and Excel.

  D365 F&O Data Transformation

Preparations (Source)

Export D365 F&O tables to Data Lake

D365 F&O database is not directly accessible so we need to use Microsoft’s Export to Data Lake extension. The extension enables the export of 200 tables suitable for analytics as CSV files in Data Lake Storage.

The refresh time is not scheduled and it cannot be affected by BI4Dynamics, since it is set up by Microsoft. Export to Lake operation is not affecting the production process.

Azure Data Lake to BI4Dynamics Data Warehouse

CSV files are inserted as tables into SQL Data Warehouse with Azure Synapse.

Transformation (DW)

Data Transformation is the process where BI4Dynamics takes the raw copy of tables that landed in the BI4Dynamics staging area and transforms it using SQL objects (stored procedures, views, dimensions, facts).

The SQL engine is super scalable and can quickly process terabytes of data. SQL engine can support any model. SQL code generated in this data warehouse automation transformation process is fully automated.

There is no need for specific SQL knowledge. All customizations are wizard-driven and require only BC knowledge.

Consumption (Business Layer)

Data Warehouse data are pushed to the Tabular database for a better user experience. Users will connect with ExcelPower BI, or any other tool, where also permissions are set.

The tabular database can be implemented on:

  • On-Premises using existing hardware.
  • Azure Analysis Services, hosted by Microsoft, paid per service, not per user.
  • Power BI Premium Capacity – paid per user, not per server.

The business intelligence content is the same in all cases. We will help you choose the right environment by taking into consideration database size, the number of users, geographic usage, self-service needs of advanced users, needs for sending reports to users’ emails, and other factors.

Implementation options

Virtual machine (VM)

VM hosts SQL server that is used for Data Warehouse. Virtual Machine can be paused when data are not being processed in BI4Dynamics Data Warehouse. Most of the day since data warehouse is built within a couple of minutes to a couple of hours.

On-Premises

BI4Dynamics Data Warehouse can be processed on an On-Premises machine with SQL server no matter where the source database is located. This option is good for companies with existing infrastructure.

On-Premises

BI4Dynamics Analytical database can be processed on On-Premises machine with SQL server no matter where the source database is located. This option is good for companies with existing infrastructure. But has limitations with web access.

Azure Analysis Services

Azure Analysis Services are paid per service – per hour. The service costs increase with the database size and hours needed for analysis. Best choice for databases under 20 GB. It offers web access to the database.

Power BI Premium

Analytics runs on Power BI Premium per user which is charged per user, not per server. It is suitable for database model up to 100GB. Best choice for up to 20 users. Offers mobile and web access and all other Power BI features.

Request Demo

Sign up for a demo today, and not only will you receive a full unrestricted BI4Dynamics license, with all modules activated and our unique Customization Wizard for a full Data Warehouse Automation experience, but we will also do an on-line demo, install the solution across your data, connect Power BI and Excel dashboards and give you 1 half-day workshop at NO CHARGE.
Experience a full onboarding experience of BI4Dynamics free for 30 days. 
Interested in:

Superior out of the box BI developed, especially for Microsoft Dynamics.

Unparalleled flexibility that allows your team to be in control of the BI project.

Experiences from 1.000 projects in many industries and company sizes.