Microsoft Dynamics 365 | Integration, Dataverse...

SQL view for Bill of Materials (BOM) explosion

(1) Share

Report

Posted on by Emmanuelle.G

Hello community,

I'm trying to build an SQL view for a BOM explosion, but I'm running into an issue when using a recursive function:
"The query references an object that is not supported in distributed processing mode."

So I'm looking for an alternative approach. Without the ability to use a recursive CTE, I'm running out of ideas.

The goal is to build a Power BI report to analyze component shortages in production.

Has anyone faced the same issue and found a solution?

Thank you for your help,

Emmanuelle G.

Categories:

Dynamics 365 general

I have the same question (0)

All responses (2)

Answers (0)

Sort by

Suggested answer

11manish 1,125 on at

Like
a
(0)

Report
Copy link

Link copied!
For a Power BI report analyzing production component shortages, the most maintainable approach is to perform the BOM explosion during data preparation rather than at report query time.

If you're using Power BI, consider implementing the recursion in Power Query or a Dataflow.

If you're using Fabric or Synapse, create a pre-exploded BOM table through an ETL process (Data Factory, Dataflow Gen2, Spark, or a stored procedure where supported) and have Power BI consume that table.

If your BOM depth is fixed and relatively small, multiple self-joins are a practical workaround.

This approach improves performance, avoids the limitations of distributed SQL engines, and provides a stable dataset for shortage analysis.

Was this reply helpful? Yes No
André Arnaud de Cal... 306,376 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Emmanuelle,

Can you tell us what Dynamics 365 application this question is related to? There are several different solutions in the Dynamics 365 family, like: Sales, Finance, Supply Chain Management, Project Operations, and Customer Insights. If you can clarify the product, we can answer the question or move it to the correct forum.

Can you also share more details about your current attempt?

Was this reply helpful? Yes No