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

Start free today

Exploding Bill of Materials using Graph Shortest Path

Play Exploding Bill of Materials using Graph Shortest Path

The Discussion

  • User profile image
    hornygoatwe​ed
    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)
  • User profile image
    ShreyaVerma
    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
  • User profile image
    hornygoatwe​ed
    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?
  • User profile image
    ShreyaVerma
    @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.
  • User profile image
    alex

    @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.

Add Your 2 Cents