BI4Dynamics F&O SaaS Data Transformation Architecture - Tier 2

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

Data Transformation

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 to Tier 2

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.

Tier 2 to Analysis services

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.

Visualizing and querying

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.

Implementation options

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.

Use case Scenarios

Architecture

Virtual Machine (VM)

VM + Azure Services (Hybrid)
BI4Dynamics Desktop client installed on VM
Data Warehouse SQL Server on VM
Analysis Service SSAS on VM Azure SSAS
System requirements
Data warehouse VM with SQL server VM with SQL server
Analytics SSAS on VM Azure SSAS
Processing SSIS on VM SSIS on VM
Data storage Azure Data Lake Azure Data Lake

Preferred option

Preferred implementation option

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

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.