Power Bi Report for IT Applications and Dependencies

[VPower12]

Hi

I'm trying to build a power BI report for IT applciations and depedencies. I have two tables. Component and dependency tables. Componentid will have a unique entry of component types such applications , database, servers etc.. And Dependency will have parent and child relationship and dependency type. So, whenever user selects an application, it should dispaly all childs and until it finds last child from the dependency table.  Just trying to understnad, has anyone already implemented this kind of reports ? Any help on this would be helpful. 

[v-sgandrathi]

Hi @VPower12,

Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
Please feel free to reach out Microsoft fabric community forum.

Thank you.

[v-sgandrathi · Accepted solution]

Hi @VPower12,

Your decision to move the recursive logic to the database side was a strong optimization for your scale. With inconsistent depth levels across applications, the main challenge now is to design visuals that can adapt dynamically to different hierarchies while remaining efficient and user-friendly.

A good solution is to use two visualization layers: a Matrix-based hierarchical view for governance and exploration, and a Network Graph or Deneb-based interactive view for mapping dependencies.

Begin with a Matrix visual in Power BI, which works well for large datasets. Use your AppDescendants table to represent hierarchy, linking components to their ancestors and calculating Depth with a DAX measure. A disconnected “Depth Control” table lets users choose how many hierarchy levels to expand, ensuring performance even with deeper structures. You can also use the depth calculation for conditional formatting, such as color-coding tiers.

For a more interactive view, consider the Network Navigator or Force-Directed Graph custom visuals to display parent–child relationships as nodes and edges. To keep things clear, limit the depth levels and use colors for different dependency types or categories. Alternatively, a collapsible tree diagram with Deneb (Vega-Lite) can allow users to expand or collapse layers, which is useful for uneven hierarchies.

If your users need to analyze dependencies often, aggregate metrics like total descendants, unique databases, total servers, and max depth per application as summary KPIs above the visuals.

For very large graphs, consider a composite model: store the flattened AppDescendants table in Import mode for speed, and keep the raw Dependencies table in DirectQuery for drill-through. This hybrid approach balances scalability and interactivity.

To summarize, your visualization should include:

1. A Matrix view with depth-based filtering.
2. A Graph or Deneb visualization for hierarchical insight.
3. Optional KPI cards and drillthrough pages for depth-level statistics.

This combination provides both performance and flexibility, making it suitable for handling varying depths and large volumes of relationships.

Thank you.

[VPower12]

@v-sgandrathi  : FIirstly, thank you so much for taking time to read my query and come up with the solution. I have tried to do in Power Query and its a taking more time whenever List or Join operations performed. My data has .6 million rows in Component and 1.2 million rows in Dependency table.   Therefore any other alternate soltuion or ideas would be greatly appreacited 

However, I'm now preparing a AppDescendants table which will have Rootcomponent id , parentid and child ids for each depth level to get full exrtact of every component as a table and then display them in the visuals using filters like applciaitons , database, servers, data centres etc... I will keep you updated.

[VPower12]

@v-sgandrathi  : Are there any other better solutions you think of to display visuals in Power BI by pre-preparing the data or dynamic passing values based on the selction from slicers ?

[v-sgandrathi]

Hi @VPower12,

You’ve made a great point about the performance impact of recursive joins and list operations in Power Query, especially with large datasets like 600K components and 1.2M dependencies. Preparing an AppDescendants table to pre-flatten these relationships is a strong approach, as it reduces processing during report interaction. For better scalability, it’s usually more efficient to do recursive expansion at the data source level (like SQL or Fabric Dataflow Gen2) using recursive CTEs or iterative logic, since this is faster than Power Query’s recursion methods.

If your dependency data updates incrementally, consider using incremental dataflows or semantic model partitioning to refresh data in smaller batches. For dynamic visualization, you can also use DAX-based filtering on the dependency table when the relationship depth is limited, and visuals like Deneb, Network Navigator, or Hierarchy Slicer are effective for interactive views.

If you need to manage very large and complex dependency networks, a graph database solution (such as Azure Cosmos DB with Gremlin API or Neo4j) via DirectQuery can offer real-time traversal. Overall, pre-materializing descendant relationships externally provides the best performance, while DAX or visual-based methods work well for smaller hierarchies.

Thank you.

[v-sgandrathi]

Hi @VPower12,

Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
Please feel free to reach out Microsoft fabric community forum.

[VPower12]

@v-sgandrathi  :: Thank you for following up on this. yes, your solution suggestion makes sense and I have done the main logic at database side [ although it takes 4-5 hours to build the depth level information because of huge data ] and will work on the visualisation part. The main challenge here is that depth level is not consistnat. Some applications have different depth level and some will have large depth level. Hence, looking for some visualisation ideas here. Any help on this would be greatly appreciated.

[v-sgandrathi]

Hi @VPower12,

You can achieve this by creating a dependency graph and adding a transitive closure, which includes all ancestor-to-descendant pairs. This way, selecting an application will return every downstream child to the last leaf. Start by setting up two tables: Components (ComponentID, Name, Type: Application/DB/Server/…) and Dependencies (ParentID, ChildID, DependencyType). In Power Query, build an AncestorDescendant table that expands dependencies recursively until there are no more children, using List.Generate or iterative merges. Each row should include AncestorID, DescendantID, and Depth. Relate AncestorDescendant[DescendantID] to Components[ComponentID] as active, and AncestorDescendant[AncestorID] to Components[ComponentID] as inactive. Use a slicer on Components filtered to Type = "Application," and apply a visual-level filter or measure to display only rows where the component is a descendant of the selected application.
For example, use a measure like:
ShowDescendants = VAR sel = SELECTEDVALUE(Components[ComponentID]) RETURN IF(NOT ISEMPTY(CALCULATETABLE(AncestorDescendant, TREATAS({sel}, AncestorDescendant[AncestorID]))), 1, 0),
then filter to ShowDescendants = 1. For visualization, a Matrix works well, or you can use a Hierarchy Slicer or Network Navigator for an interactive graph. This method handles multiple levels and many-to-many dependencies, ensuring all downstream components appear when an application is selected.

Thank you.