New Era of Business Intelligence
for Microsoft Dynamics Finance and Operations
Get Your BI Project Done in Just Hours (Data Warehouse Based)
Data Warehouse Automation
Proven Business Intelligence across all areas of your D365 in a matter of hours
Drag and Drop
Pre-made Reports
Fully customizable 150+ Ready-to-use Power BI reports with 2000+ KPIs
Redefine Efficiency,
Slash Modeling Costs
No-Code Wizard Driven customization.
Add any field or source with a click of a mouse.
BI4Dynamics Cloud Architecture
Solution, not a project
Integrate Dynamics 365 Finance and Operations (F&O) Cloud with Microsoft Dynamics AX 2012 R3 or any other data source seamlessly in one Data Warehouse.
BI4Dynamics’ automated process empowers your organization with complete insight into all business areas within hours. Take advantage of the most flexible data warehouse on the market and analytical model with more than 2000 BI metrics.
How to get data out for D365 Finance and Operations?
Architecture
ETL (extract, transform, load) from D365FO
D365 Finance and Operations data is not available to external tools due to security and performance reasons. Microsoft is providing the following ETL process options (extract, transform, load) to get data out of D365:
- Synapse link – Synapse Serverless
- Synapse link – Synapse Spark Pool
- Link to Microsoft Fabric
All options for extracting data from D365 F&O lead to accessing transactional data that isn’t optimized for analytics.
1: Synapse link - CSV files - Synapse Serverless
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.
2: Synapse link – Synapse Spark Pool - Delta / Parquet
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.
3: Link to MS Fabric – Delta / Parquet in One Lake
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 Warehouse Automation vs Power BI Modeling
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.
You can have it all – content and performance
Fast development
Data Warehouse Automation tool simplifies development to an unseen level. Automatically generates tens of thousands of rows of SQL code that builds a corporate data warehouse, and project documentation with a click of a mouse.
Easy customizations
Open for manual customizations
Empowerment of consultants
Microsoft Dynamics consultants can deliver changes with brief training. The key to success is understanding Microsoft Dynamics data structures. Complete BI Project can be either customized or build in five wizard-driven steps.
Drag-and-Drop Power BI Dashboards
In just a few hours, over 150 Power BI Reports are connected to your data, enriched through the BI4Dynamics transformation & modeling.
