Build with an Azure free account. Get USD200 credit for 30 days and 12 months of free services.

# Exploding Bill of Materials using Graph Shortest Path

Play Exploding Bill of Materials using Graph Shortest Path

## The Discussion

• Is it possible to get a list of all products effected by shortage of a specific part? To borrow from your example, can one produce a list of all bikes effected by lack of availability of ball bearings?

(It's all ball bearing these days)
• Hi @hornygoatweed,

Here is a query that you could use to get a list of all bikes that use ball bearings somewhere in their assembly.

------------------------------------------------------------------------------------
-- Find all bikes that require a Bearing Ball in the bill of materials
------------------------------------------------------------------------------------

SELECT
P1.ProductID,
P1.Name,
LAST_VALUE(P2.Name) WITHIN GROUP (GRAPH PATH) AS [Assembly],
LAST_VALUE(P2.ProductID) WITHIN GROUP (GRAPH PATH) AS [Final ProductID]
FROM
PRODUCT P1,
PRODUCT FOR PATH P2,
ISPARTOF FOR PATH IPO
WHERE
MATCH(SHORTEST_PATH(P1(-(IPO)->P2)+))
AND P1.ProductID = 2
ORDER BY P1.ProductID

If a ball bearing appears in the assembly of a bike, the production of that bike will be affected by the shortage of this product.

Hope this helps.

Thanks,
shreya
• Thanks, that is great. One more follow-up:

Your shortest path match MATCH(SHORTEST_PATH(P1(-(IPO)->P2)+)) seems to suggest that it go "N" deep. So, no matter how deep the graph was, it would find the dependent product?
• @hornygoatweed, that's correct. This pattern MATCH(SHORTEST_PATH(P1(-(IPO)->P2)+)) will keep looking for dependent product, no matter how deep the hierarchy is. The recursion stops when we have exhausted the entire graph and have not found a dependent product. Note that this will only return you the shortest path between P1 and P2. If there exist multiple paths between P1 and P2, this will return you the one that is shortest.
• @ShreyaVerma,
In my testing of exploding a BOM tree, performance of a recursive-cte-based query was significantly better then using SHORTEST_PATH() function.
Are there best practices for improving performance of SHORTEST_PATH() queries and SQL Serve Graph Db in general?
Thank you.