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

BI4Dynamics is a ready-to-use solution that uses Data Lake and transforms Dynamics data with fully automated Data Warehouse and Analysis Services. That ensures powerful analysis on the document level without sacrificing the speed of data processing and querying.

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, e.g. Power BI and Excel.

  D365 F&O Data Transformation

Export D365 F&O tables to Data Lake

D365FO database is not directly accessible so we need to install BI4Dynamics extension that automatically:

  • Reads FO metadata that BI4Dynamics will use for customizations.
  • Creates queries, one query for each exported FO table.
  • Exposes queries as web services.

A standard BI4Dynamics extension exports 200 tables, any other custom tables are added automatically.

Exporting FO tables to Data Lake

BI4Dynamics creates a Docker, a specialized Virtual Machine, that incrementally exports tables as CSV files to Azure Data Lake. The refresh time can be scheduled. Export to Lake operation is not affecting the FO 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

BI4Dynamics is a ready-to-use solution that uses Data Lake and transforms Dynamics data with fully automated Data Warehouse and Analysis Services. That ensures powerful analysis on the document level without sacrificing the speed of data processing and querying.

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, e.g. Power BI and Excel.

  D365 F&O Data Transformation

Preparations (Source)

Export D365 F&O tables to Data Lake

D365FO database is not directly accessible so we need to install BI4Dynamics extension that automatically:

  • Reads FO metadata that BI4Dynamics will use for customizations.
  • Creates queries, one query for each exported FO table.
  • Exposes queries as web services.

A standard BI4Dynamics extension exports 200 tables, any other custom tables are added automatically.

Exporting FO tables to Data Lake

BI4Dynamics creates a Docker, a specialized Virtual Machine, that incrementally exports tables as CSV files to Azure Data Lake. The refresh time can be scheduled. Export to Lake operation is not affecting the FO 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.