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:
BI4Dynamics is reading data from BC replica, a synchronized Production database copy. This is not affecting Production operations.
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, available from App Store is installed to BC tenant. First time setting includes:
The extension will automatically:
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.
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.
BC data are saved in Azure Data Lake storage as CSV files. There are two processes:
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.
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.
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)
Users query data in Analysis Services. Data are in Memory, so querying is very fast.
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:
Which tool to use depends on the reporting or analysis process – not the person or job description.
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:
BI4Dynamics is reading data from BC replica, a synchronized Production database copy. This is not affecting Production operations.
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, available from App Store is installed to BC tenant. First time setting includes:
The extension will automatically:
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.
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.
BC data are saved in Azure Data Lake storage as CSV files. There are two processes:
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.
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.
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)
Users query data in Analysis Services. Data are in Memory, so querying is very fast.
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:
Which tool to use depends on the reporting or analysis process – not the person or job description.
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 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.
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 |
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).