BI4Dynamics BC state-of-the-art architecture powered by Microsoft technology

BI4Dynamics Data Transformation ensures no compromise between service costs and BI capabilities.

From D365 Business Central to Power BI

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 BC tables to the Data lake and insert them into the 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 BC Data Transformation

Exposing BC tables as web services

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

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

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

Exporting BC 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 BC production process.

Azure Data Lake to BI4Dynamics Data Warehouse

CSV files are loaded to DW using the standard SQL feature PolyBase.

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 Excel, Power 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

From D365 Business Central to Power BI

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 BC tables to the Data lake and insert them into the 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 BC Data Transformation

Preparations (Source)

Exposing BC tables as web services

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

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

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

Exporting BC 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 BC production process.

Azure Data Lake to BI4Dynamics Data Warehouse

CSV files are loaded to DW using the standard SQL feature PolyBase.

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 Excel, Power 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.

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.