Best way to build Power BI reports using Dynamics 365 Finance & Operations (D365 F&O) data

[Ramkishor]

Hi everyone,

I’m exploring the best approach to build Power BI reports using Dynamics 365 Finance and Operations (D365 F&O) data, leveraging Microsoft Fabric.

Here’s the situation I’m dealing with:

• I have around 10 D365 F&O tables involved in a single report.

• In Power BI, we can only have one active relationship between two tables at a time.

• Additionally, since D365 F&O stores data for multiple legal entities, it’s necessary to include the DataAreaId field in all relationships to correctly filter data per company.

• This means every relationship often becomes a composite key (e.g., ItemId + DataAreaId), which significantly increases modeling effort and complexity.

• As a result, if I simply hand over all the raw tables to the Power BI developer, it becomes time-consuming and difficult to maintain, especially as the model grows.

• Moreover, since Power BI developers are often not deeply familiar with the D365 F&O data model, they frequently require support from D365 functional or technical consultants to interpret table relationships, field logic, and business context.

• This cross-team dependency slows down development and increases the overall maintenance overhead.

• As the number of reports and required tables grows, this problem scales exponentially, making manual modeling increasingly time-consuming, error-prone, and difficult to maintain.

1. Are there any recommended data modeling strategies within Fabric for simplifying multi-entity and multi-relation D365 data before exposing it to Power BI developers?

My main goal is to reduce complexity for Power BI developers and move toward a centralized, governed data layer in Fabric that’s optimized for D365 F&O analytics.

Thanks in advance!

Regards,

Kishor.

[srlabhe · Accepted solution]

Hi Ramkishor

I must say very well detailed explanation of the issue you are facing.

What I uunderstand is this is very common issue every project must be facing to convert the OLTP data to reporting data.

I suggest you to analyze the required table and involve a better ETL tool which would help you to trasform data in start schema reporting model.

Make sure to identify the prime dims, measures and have those correctly identified and defined. The fact table should be defined in such way that it covers most of the required measures and keys to join to dimension tables.

Its very good practice to have a Date dm table defined as part of model with all the required flags of dates already definied and loaded. This helps a lot if any static reporting of Last Year, YTD, MTD etc.

Try to have model with data in such way that those can be easily related and defined.

Once this reporting stage is ready it becomes very easy to use same in PBI for developers and design reports.

I hope it helps.

[V-yubandi-msft · Accepted solution]

Hi @Ramkishor ,

You’re on the right path using the SQL Analytics Endpoint to create curated, joined views is a smart way to simplify D365 F&O data for Power BI developers, especially for early or mid sized projects. As your reporting needs expand, consider adopting a layered approach in Fabric, such as the Bronze-Silver-Gold model. 
1. Bronze for raw D365 F&O data (for lineage and auditing).
2. Silver for curated and cleansed views (like what you’re currently doing).
3. Gold for a business ready semantic layer with fact and dimension views in a star schema.

This structure helps ensure consistency across reports, minimizes duplicated logic, and enables Power BI developers to work more independently from the complex D365 schema.

Thank You.

[V-yubandi-msft]

Hi @Ramkishor ,

Could you let us know if your issue has been resolved, or if you need any additional information or clarification? We are happy to help.

Thank you.

[V-yubandi-msft]

Hi @Ramkishor ,

May I know if your issue has been resolved, or if you still need any additional details? Please let us know if you need any further assistance.
 

Thank you.

[Shubham_rai955 · Accepted solution]

The best way to simplify reporting with D365 F&O data in Power BI using Microsoft Fabric is to build a centralized, governed Lakehouse or Data Warehouse in Fabric that handles data modeling, relationships, and filters by company (DataAreaId), so Power BI developers work from curated, business-friendly datasets instead of raw tables.​

Recommended Strategies

• Ingest D365 F&O tables into a Lakehouse using Dataflows Gen2 or pipelines, applying any necessary transformations or staging logic up front.​

• Model all relationships (especially those involving composite keys like DataAreaId + ItemId) at the Lakehouse/Data Warehouse layer, creating views or tables optimized for analytics use.​

• Build semantic models and documentation in Fabric—define entity relationships, make business logic explicit, and expose only essential fields to Power BI.​

• For multi-company reporting, standardize filtering using DataAreaId, and ensure each analytical entity includes this field in modeled relationships.​

• Use solutions like BI4Dynamics for automated warehouse creation and out-of-the-box Power BI datasets if you want a turnkey approach.​

• Share documentation and business logic, ideally embedded in Fabric or via OneLake, so Power BI users can self-serve without being D365 experts.​

This central layer reduces complexity, eliminates cross-team dependency for most reporting, and supports scalable, maintainable analytics for D365 F&O

[srlabhe · Accepted solution]

Hi Ramkishor

I must say very well detailed explanation of the issue you are facing.

What I uunderstand is this is very common issue every project must be facing to convert the OLTP data to reporting data.

I suggest you to analyze the required table and involve a better ETL tool which would help you to trasform data in start schema reporting model.

Make sure to identify the prime dims, measures and have those correctly identified and defined. The fact table should be defined in such way that it covers most of the required measures and keys to join to dimension tables.

Its very good practice to have a Date dm table defined as part of model with all the required flags of dates already definied and loaded. This helps a lot if any static reporting of Last Year, YTD, MTD etc.

Try to have model with data in such way that those can be easily related and defined.

Once this reporting stage is ready it becomes very easy to use same in PBI for developers and design reports.

I hope it helps.

[Ramkishor]

Hello @srlabhe,

Thanks a lot for your detailed input — that’s a great explanation and aligns well with the approach I’ve been following so far.

At the moment, I’m leveraging the SQL Analytics Endpoint within the Fabric Lakehouse to create consolidated views by joining all the necessary D365 F&O tables. These curated views are then shared directly with our Power BI developers for report building.

Whenever I encounter complex joins or relationships that are better handled in Power BI , I collaborate with the Power BI developer to manage that part within the Power BI model.

Do you think this approach remains optimal as the number of reports and underlying tables increases, or would you recommend a more scalable pattern?

Thanks & regards

Kishor

[dj38]

Hi Kishor!

Would you mind sharing how do you find the relevant tables needed from F&O and the joins?

Thank you

[V-yubandi-msft · Accepted solution]

Hi @Ramkishor ,

You’re on the right path using the SQL Analytics Endpoint to create curated, joined views is a smart way to simplify D365 F&O data for Power BI developers, especially for early or mid sized projects. As your reporting needs expand, consider adopting a layered approach in Fabric, such as the Bronze-Silver-Gold model. 
1. Bronze for raw D365 F&O data (for lineage and auditing).
2. Silver for curated and cleansed views (like what you’re currently doing).
3. Gold for a business ready semantic layer with fact and dimension views in a star schema.

This structure helps ensure consistency across reports, minimizes duplicated logic, and enables Power BI developers to work more independently from the complex D365 schema.

Thank You.