BI4Dynamics BC SaaS Data Transformation Architecture

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. 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.