Data Flow:                                                        

1. From D365 FO to CSV files – Microsoft service:

• Utilizing Synapse Link, managed by Microsoft at no additional costs.
• Customers manually select D365 FO tables in Dataverse.
• Microsoft Service generates CSV files into an Azure Storage Container.

2. From CSV files to Data Warehouse – BI4Dynamics solution:

• Synapse Serverless reads these CSV files.
• Inserts them into the BI4Dynamics Data Warehouse staging area.

A new solution from BI4Dynamics is available; the solution used in Export to DataLake will not work.

Key Features:

• Data Format: Synapse Serverless employs architecture like Export to DataLake, with different CSV formats and file structures.
• Cost: The most economical option: Synapse Serverless is priced at 5$ per terabyte (TB) of data processed.
• Complexity: This option requires the transformation of CSV files, making it from a customer perspective more complex. However, with a BI4Dynamics license, customers get a fully automated solution.
• Latency: It boasts the best latency among available options, it takes about 5-10 minutes after On-demand execution from receiving the CSV files to landing data in the data warehouse for a DFO database of 200 GB.

Verdict: This setup offers efficient data integration with cost-effectiveness, although it may require some complexity in development. However, it provides the advantage of optimal latency, which is crucial for real-time analytics and reporting.

Data Flow:

1. From D365 FO to CSV files – Microsoft service:

• The same as it was described in option #1.

2. CSV conversion to Delta/Parquet – Microsoft service:

• Synapse Spark Pool engine is utilized to transform these CSV files into Delta/Parquet format.
• Files are linked in OneLake to act like SQL endpoint, available for many processing solutions (Power BI, data warehouse)

3. From Delta/Parquet to Data Warehouse – BI4Dynamics solution:

• OneLake SQL endpoint acts as an SQL database and is used as the source for the BI4Dynamics Data Warehouse staging area.

Key Features:

• Data Format: Delta/Parquet format, providing many advantages for further processing.
• Cost: Utilizing Synapse Spark Pool incurs an additional cost (priced per hour). Refer to the Azure pricing page for details.
• Complexity: Synapse Spark Pool transforms CSV files to Delta/Parquet files which gives a better starting point for building the BI model.
• Latency: have higher latency compared to #1 due to the additional step of Spark conversion. Conversion from CSV to Delta/Parquet is scheduled in intervals, minimum is 15 minutes.

Verdict: This approach provides processing flexibility and the data format is great for further transformations; This comes with additional costs and potentially higher latency due to the Spark conversion process.

Data Flow:

1. From D365 FO to OneLake – Microsoft service:

• Using Link to MS Fabric, all tables are automatically selected.
• CSV conversion to Delta/Parquet - same as described in option #2, but managed by Microsoft.
• All tables are automatically transformed and stored in One Lake.

2. From OneLake to Data Warehouse – BI4Dynamics solution:

• OneLake SQL endpoint acts as an SQL database and is used as the source for the BI4Dynamics Data Warehouse staging area.

Key Features:

• Data Format: Utilizes Delta/Parquet format, providing flexibility for further transformation.
• Cost: The most expensive option. Costing 40$ per month per GB of data in Dataverse. For example, a 250 GB DFO database, typically using 150 GB in Dataverse will cost 6.000$ / month.
• Complexity: simple installation and no management from the customer's end, as it's handled by Microsoft.
• Latency: Higher latency compared to the first and second approaches due to the conversion process to One Lake with 60-minute intervals.
  

Verdict: This approach is technically the best; It is closest to SQL replica (all tables in Fabric as SQL endpoint). Simple implementation comes with a much higher cost (can be prohibitively high) and potentially higher latency due to the conversion process. If the price and interval go down, this is the best option.

After completing the ETL process, the modeling phase begins.

All options for extracting data from D365 F&O, lead to accessing transactional data that isn’t optimized for analytics. At this stage, the choice lies between initiating modeling efforts or selecting from existing models or business intelligence solutions available on a market.

BI4Dynamics Data Warehouse Automation

Data Warehouse Automation is a BI modeling process that automatically creates a Data warehouse without writing a line of code. Using BI4Dynamics templates for D365 F&O, 90% of BI project completion is available on day 1. BI4Dynamics brings a paradigm shift in business intelligence for Microsoft Dynamics.

The model, built on best practices, includes immediately available:

• 50 transactional tables,
• 150 dimensional tables and
• over 3000 immediately available KPIs.

In-house Power BI modeling

Project implementation cycle

Opting for Power BI alone for modeling triggers a project implementation cycle primarily responsive to business demands. This approach may result in unpredictable outcomes and expenses, often costing more than out-of-the-box solutions such as BI4Dynamics, which offer anticipated results from day one.

Resource Intense project

Although Power BI’s functionality improves and becomes more user-friendly, achieving high productivity requires additional skills, experience, and expertise. Incorporating multiple databases, companies, or even different versions of Dynamics introduces an additional layer of complexity to the modeling.

Data validation

BI4Dynamics automates data validation processes and flags any discrepancies in the data. With in-house modeling, data validation must be done with each major project update to ensure matching the data between BI and ERP systems. It’s challenging to create a matching model that covers all specifics as non-item sales, charges, multiple discount types, cost posting in sales, etc. If data doesn’t match, people might lose trust in the system and stop using it.