Introduction
This post describes the data transformation process from Microsoft Dynamics D365 BC SaaS to visualization tools (Power BI, Excel). It includes also the total monthly cost for the project.
Data transformation diagram
Data transformation is done in the following steps
-
- Export Business Central (BC) data to Azure Data Lake
-
- Load data to the Data Warehouse staging area
-
- Process data within Data Warehouse and Analysis Services
- Analyze data with visualization tools
Data transformation process
BC database
BI4Dynamics reads data from a BC replica; a synchronized Production database copy. This is not affecting Production operations.
Exporting BC data
Accessing a BC database (Production or Replica) with external tools is not possible. BI4Dynamics is exporting data to Azure Data Lake using web services created by a BI4Dynamics extension.
BI4Dynamics extension
BI4Dynamics extension is available on the App Store and can be installed for a BC tenant. A first-time setting includes:
-
- BC tenant information (ID, user)
-
- Azure Data Lake Storage (subscription)
There are no interactions with the IT team (customer or partner) after this step.
The extension will automatically:
-
- create queries, one query for each exported BC table,
-
- expose queries as web services,
-
- read BC metadata (tables, columns, primary keys, table relations, option fields) – that BI4Dynamics will use for customizations.
A standard BI4Dynamics extension will export 150 tables that are 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.
Running web services
BI4Dynamics creates a Docker, that triggers Web Services (more about Docker is in the last chapter of this document).
Web Services are using OData protocol. This is a lighter, a little faster protocol than SOAP. BI4Dynamics is controlling and triggering web service execution from outside BC in two ways:
-
- automatically: when tenant resource usage is low – BI4Dynamics pulls data from BC anytime during the day
-
- on demand
There is no change to run Web Services. 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:
-
- Import: update new data from BC
-
- Read: BI4Dynamics data warehouse reads data and updates or copies it into the data warehouse staging area. This is not exported since data is kept in Azure Data Lake.
The import and read processes may not necessarily run one after another. “Update data” can run automatically several times during the day to load the latest data only, then the reading process usually runs once a day as a part of data warehouse processing.
Loading data from Azure Data Lake to the Data Warehouse
Triggering the request
The load data request is executed from BI4Dynamics’ data warehouse when the data are processed. This is usually once a day. Loading data from Azure Data Lake to the SQL server is done using the PolyBase feature of SQL that must be installed (more about PolyBase is in the Appendix).
BC table name is kept during all transformations. BC table name equals the CSV file name in Azure Data Lake and equals the stage table name in the data warehouse.
Data Warehouse transformations
BI4Dynamics will load CSV data to the SQL server (stage), where data warehouse 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 usually would not connect to the data warehouse but to the Analysis Services. There are two implementation options where the analytics layer (Tabular model) will be installed. There is no difference in content. Choosing which option is the right one for a project purely depends upon usage and economics (more about this in the next chapter).
Analytics on VM (option 1)
In Option 1 an Analysis Services database (tabular) is installed on an SQL server hosted on VM. The same VM is hosting the SQL server database engine.
Analytics in Azure Analysis Services (option 2)
In Option 2 an Analysis Services database is installed as Azure Analysis Services.
Visualizing and querying
Any BI client can connect to the Tabular Model and used for querying. BI4Dynamics has various standardized ready-made reports for Excel and Power BI. Both tools can do the job. 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 on analysis process – not the person or job description.
