BI4Dynamics F&O SaaS Data Transformation Architecture - Tier 2

Data transformation from D365F&O to Power BI & Excel

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

Data transformation is done in the following steps:

1. Export F&O Production database to Tier 2 Environment.
2. Load data to Data Warehouse and process it in Data Warehouse and Analysis Services.
3. Analyze data with visualization tools, such as Power BI and Excel.

Production environment

Production environment Azure SQL is not accessible to any external tool.

Exporting FO data

There are the following ways to get data from FO:

1. BYOD (bring your own database): export Entities from FO to Azure SQL (requires creation of entities that are periodically pushed to Azure SQL). This option is going to be discontinued by Microsoft.
2. BYOL (bring your own lake): export Tables or Entities from FO to Azure Data Lake; it is available for Entity export and in Private Preview for Tables. This is going to be the ultimate way of getting data from FO as it is working on system level with very small latency.
3. Copy Production to Tier 2 environment: if Tier 2 is available (not being used for testing) then this is the fastest way to get data from FO. Tier 2 data are in Azure SQL database that must be provisioned before copying data.

For now, we will be doing POCs from Tier 2 environment.

Getting Metadata and Financials dimensions

BI4Dynamics requests access to FO services that provide:

• FO database Metadata (AOT): used for DW customization
• Structure of Financial dimensions: used for mapping dimensions to transactional tables across DW

Getting metadata from another environment may take from 15 minutes to more than an hour. It is needed only once as metadata are saved to BI instance locally and retrieved from FO again only if metadata, that will be used in modeling, have been changed.

Accessing Tier 2 environment in self-service deployment

Microsoft is providing self-service deployments in LCS and no longer RDC to Tier 2 environment (Self-service deployment). Environments are managed by Microsoft. Access to the database is provided in just-in-time manner on request (Enable just in time database access). According to our information access is granted for 8 hours, several times.

Loading data from Tier 2 to the data warehouse

Tier 2 Azure SQL database and Data Warehouse database are connected with linked server connection. Data are loaded on request or periodically, depending on how often Tier 2 is updated. Request for load always comes from Data Warehouse (DW).

Data Warehouse transformations

BI4Dynamics will load SQL tables from Tier 2 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. Users will not access the data warehouse. Data warehouse is hosted on a virtual machine in Azure.

Analytics

DW tables (dimensional and transactional) are copied to:

• Analysis services on the same Virtual Machine (VM) as DW: VM runs when processing and quiring
• Azure Analysis Database: VM runs when processing and Analytics run when querying.

This database is exposed to users for querying therefore roles and permissions are set here.

Power BI and Excel

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:

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