BI4Dynamics BC SaaS Data Transformation Architecture

BI4Dynamics is ready to give you total insight into your business the next day after installation. The unique data transformation with extra data layers ensures no compromising between service costs and BI capabilities.

Data transformation from D365BC to Power BI & Excel

The unique data transformation with extra data layers in Data Lake, Data Warehouse, and Analysis Services ensure powerful analysis on the document level without sacrificing speed of data processing and querying.

Data transformation is done in the following steps:

  1. Export BC data to Azure Data Lake
  2. Load data to Data Warehouse
  3. Process data Data Warehouse and Analysis Services
  4. Analyze data with visualization tools

BC database

BI4Dynamics is reading data from BC replica, a synchronized Production database copy. This is not affecting Production operations.

Exporting BC data

Accessing the BC database (Production or Replica) with external tools is not possible. BI4Dynamics is exporting data to Azure Data Lake using web services, created by BI4Dynamics extension.

BI4Dynamics extension

BI4Dynamics extension, available from App Store is installed to BC tenant. First time setting includes:

  • BC tenant information (ID, user)
  • Azure Data Lake Storage (subscription)

The extension will automatically:

  • create queries, one query for each exported BC table,
  • expose queries as web services,
  • read BC metadata that BI4Dynamics will use for customizations.

A standard BI4Dynamics extension will export 150 tables that be used to create the following business areas: Sales, Purchase, Receivables,
Payables, Finance, Inventory, Fixed Assets, Jobs and Resources, Production, Service, Bank Accounts, Item Information, and Service.
Because the process is automated there is no interaction with the IT team needed after this step.

Running web services

BI4Dynamics creates a docker, that triggers web services. Web services are using the OData protocol. This is a lighter, a little faster protocol than SOAP.  The results are BC tables, exported as CSV files to Azure Data Lake.

Azure Data Lake Storage

BC data are saved in Azure Data Lake storage as CSV files. There are two processes:

    1. import: update new data from BC
    2. read: BI4Dynamics data warehouse reads data and updates or copies it into the data warehouse staging area.
      This is not export as data are kept in Azure Data Lake.

Import and read process may not necessarily run one after another. Update data can run automatically several times during the day, to load the latest data only, and read process usually runs once a day as a part of data warehouse processing.

Azure Data Lake to Analysis Services

Loading data from Azure Data Lake to the data warehouse
Load data request is executed from BI4Dynamics data warehouse when DW data are processed. This is usually once a day. Loading data from Azure Data Lake to SQL server is done using PolyBase feature of SQL that must be installed. More about PolyBase is in Appendix.

BC table name is kept during all transformations. BC table name equals CSV file name in Azure Data Lake and equals stage table name in data warehouse.

Data Warehouse transformations

BI4Dynamics will load CSV data to SQL server (stage), where DW transformation will be started.

Data warehouse objects (stored procedures, views, dimensions, facts) are created and processed in one data transformation process.

(details are available on BI4Dynamics web)

Analytics

Users query data in Analysis Services. Data are in Memory, so querying is very fast.

Power BI and Excel

Any BI client that can connect to the Tabular model can be used for querying. BI4Dynamics has standardized ready-made reports for Excel and Power BI. Both tools can do the job a usage is not exclusive. BI4Dynamics recommends using both tools in the same BI project:

  • a free desktop version of Power BI is used for best visualizations and dashboarding,
  • an Excel 2013 or higher version is best for ad-hoc reporting and analytics.

Which tool to use depends on the reporting or analysis process – not the person or job description.

Data Transformation

Data transformation from D365BC to Power BI & Excel

The unique data transformation with extra data layers in Data Lake, Data Warehouse, and Analysis Services ensure powerful analysis on the document level without sacrificing speed of data processing and querying.

Data transformation is done in the following steps:

  1. Export BC data to Azure Data Lake
  2. Load data to Data Warehouse
  3. Process data Data Warehouse and Analysis Services
  4. Analyze data with visualization tools
BC to Azure Data Lake

BC database

BI4Dynamics is reading data from BC replica, a synchronized Production database copy. This is not affecting Production operations.

Exporting BC data

Accessing the BC database (Production or Replica) with external tools is not possible. BI4Dynamics is exporting data to Azure Data Lake using web services, created by BI4Dynamics extension.

BI4Dynamics extension

BI4Dynamics extension, available from App Store is installed to BC tenant. First time setting includes:

  • BC tenant information (ID, user)
  • Azure Data Lake Storage (subscription)

The extension will automatically:

  • create queries, one query for each exported BC table,
  • expose queries as web services,
  • read BC metadata that BI4Dynamics will use for customizations.

A standard BI4Dynamics extension will export 150 tables that be used to create the following business areas: Sales, Purchase, Receivables,
Payables, Finance, Inventory, Fixed Assets, Jobs and Resources, Production, Service, Bank Accounts, Item Information, and Service.
Because the process is automated there is no interaction with the IT team needed after this step.

Running web services

BI4Dynamics creates a docker, that triggers web services. Web services are using the OData protocol. This is a lighter, a little faster protocol than SOAP.  The results are BC tables, exported as CSV files to Azure Data Lake.

Azure Data Lake to Analysis services

Azure Data Lake Storage

BC data are saved in Azure Data Lake storage as CSV files. There are two processes:

    1. import: update new data from BC
    2. read: BI4Dynamics data warehouse reads data and updates or copies it into the data warehouse staging area.
      This is not export as data are kept in Azure Data Lake.

Import and read process may not necessarily run one after another. Update data can run automatically several times during the day, to load the latest data only, and read process usually runs once a day as a part of data warehouse processing.

Azure Data Lake to Analysis Services

Loading data from Azure Data Lake to the data warehouse
Load data request is executed from BI4Dynamics data warehouse when DW data are processed. This is usually once a day. Loading data from Azure Data Lake to SQL server is done using PolyBase feature of SQL that must be installed. More about PolyBase is in Appendix.

BC table name is kept during all transformations. BC table name equals CSV file name in Azure Data Lake and equals stage table name in data warehouse.

Data Warehouse transformations

BI4Dynamics will load CSV data to SQL server (stage), where DW transformation will be started.

Data warehouse objects (stored procedures, views, dimensions, facts) are created and processed in one data transformation process.

(details are available on BI4Dynamics web)

Analytics

Users query data in Analysis Services. Data are in Memory, so querying is very fast.

Visualizing and querying

Power BI and Excel

Any BI client that can connect to the Tabular model can be used for querying. BI4Dynamics has standardized ready-made reports for Excel and Power BI. Both tools can do the job a usage is not exclusive. BI4Dynamics recommends using both tools in the same BI project:

  • a free desktop version of Power BI is used for best visualizations and dashboarding,
  • an Excel 2013 or higher version is best for ad-hoc reporting and analytics.

Which tool to use depends on the reporting or analysis process – not the person or job description.

Implementation options

Virtual machine (VM)

VM hosts SQL server that is used for DW and analytics. VM can be pause only when data are not being processed or queried.

VM + Azure services (Hybrid)

VM hosts SQL server used for DW. Analytics is installed on Azure Analysis Services. VM can be paused after processing (most of the time), Azure Analytics can be paused when users are not querying.

Azure Serverless

DW and Analytics are used as Azure services. DW as Azure SQL server, Analytics as Azure Analysis Services. Azure SQL can be scaled down (not paused) while not used. Azure Analytics can be paused when no querying.

Architecture

Virtual Machine

 (VM)

VM + Azure Services

(Hybrid)

Azure Serverless
BI4Dynamics Desktop client installed on VM Web service
Available Yes No (in development)
Data Warehouse SQL Server on VM Azure SQL
Analysis Service SSAS on VM Azure SSAS Azure SSAS

System requirements

Data warehouse VM with SQL server VM with SQL server Azure SQL
Analytics SSAS on VM Azure SSAS Azure SSAS
Processing SSIS on VM SSIS on VM Azure Data Factory
Data storage Azure Data Lake Azure Data Lake Azure Data Lake

Preferred implementation option

All options are delivering the same results and scalability using slightly different azure resources.

The strong influencer may be customers’ preference for a specific technology.

Virtual Machine: best for reusing existing resources (hardware and software) in a private cloud.

Hybrid: best for economically cautious customers as it maximizes the benefits and minimizes the costs.

Azure Services: best for simplicity of usage (web browser).

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. 

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.

close

LET’S KEEP IN TOUCH!

We’d love to keep you updated with our latest news 😎

We don’t spam! Read our privacy policy for more info.