Exploding Bill of Materials using Graph Shortest Path

Play Exploding Bill of Materials using Graph Shortest Path
Sign in to queue

Description

In this demo, we will show how you can explode a Bill of Materials using Graph Shortest Path function, introduced with SQL Server 2019 CTP3.1, to find out which BOMs/assemblies a given product/part belongs to. This information can be useful for reporting or product recall scenarios. We will explore the Shortest Path function and try to understand different ways in which it can be used.

More information:

Follow Shreya Verma and Jeroen ter Heerdt on Twitter at https://twitter.com/ShreyaVermaKale and https://twitter.com/jeroenterheerdt.

Tags:

SQL, SQL Azure

Embed

Download

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.

Add Your 2 Cents