Bill of Materials in Neo4j

Where is da BOM? The above question asks, and the obvious answer is right in the middle of your organization. Nestled between Manufacturing, Design, Sales and Supply Chain. But I have a better answer. Your Bill of Materials should be in Neo4j. Today, I’ll show you why.

Let’s start with a simple example first by creating a BOM in Neo4j.

CREATE (a1:Asset {id:'a1'})
CREATE (p1:Part {id:'p1'})
CREATE (p2:Part {id:'p2'})
CREATE (p3:Part {id:'p3'})
CREATE (p4:Part {id:'p4'})
CREATE (p5:Part {id:'p5'})
CREATE (a1)<-[:BELONGS_TO]-(p1)
CREATE (p1)<-[:BELONGS_TO]-(p2)
CREATE (p1)<-[:BELONGS_TO]-(p4)
CREATE (p2)<-[:BELONGS_TO]-(p3)
CREATE (p4)<-[:BELONGS_TO]-(p5)

After we run that command our graph looks like this:

If we want to know all the parts that are needed to produce Asset 1, we could run this query:

MATCH (n:Asset {id:'a1'})<-[:BELONGS_TO*]-(p)

The query finds the asset with an id of “a1” and then traverses all the incoming BELONGS_TO relationships all the way to the end. That’s what the little “*” at the end of the relationship type in our query means. Knowing that some parts may belong in multiple downlines, we use DISTINCT to only see them once in our result.

So that’s pretty simple so far. Let’s delete all that (for those following along in front of Neo4j run “MATCH (n) DETACH DELETE n”), and create a bigger graph. You can see the whole thing if you follow this link, but for now I’ll just show you want it looks like:

We have 3 Families of products with 7 Assets, and 15 Parts between them. Part 14 is common across them. So let us start asking a few questions:

Take a look at Part 14. What Assets require Part 14 in order to be built?

MATCH path=(p14:Part {id:'p14'})-[:BELONGS_TO*]->(a:Asset)

It is in the paths of Assets 1,3,4,5,6,7. But what is the lowest level this part is used at? Assuming Asset is level 0, then this query returns the longest path, and shows p14 has a depth of 5

MATCH path=(p14:Part {id:'p14'})-[:BELONGS_TO*]->(a:Asset)
WITH path, LENGTH(path) AS depth
RETURN path, depth

If we wanted to set the lowest depth, we can modify the query this way:

MATCH path=(p14:Part {id:'p14'})-[:BELONGS_TO*]->(a:Asset)
WITH p14, path, LENGTH(path) AS depth
SET p14.depth = depth

Can we compare two bill of materials (two Assets) and find out common parts are used in both and components unique to each? Let’s try Asset 3 and Asset 5. Here is the query:

MATCH path_a3 = (a3:Asset {id:'a3'})<-[:BELONGS_TO*]-(p3), 
      path_a5 = (a5:Asset {id:'a5'})<-[:BELONGS_TO*]-(p5)
RETURN filter(x IN a3_parts WHERE NOT(x IN a5_parts)) AS unique_a3, 
       filter(x IN a5_parts WHERE NOT(x IN a3_parts)) AS unique_a5, 
       filter(x IN a3_parts WHERE (x IN a5_parts)) AS common

We start from each asset, traverse all the BELONGS_TO paths and collect the distinct parts we find. Then we use the FILTER function to get the unique parts and the common parts of the two assets. Pretty simply right? Let’s up the difficulty a tiny bit by comparing the BOMs of two families of assets. Again to identify common and unique items.

MATCH path_f1 = (f1:Family {id:'f1'})<-[:BELONGS_TO]-(:Asset)<-[:BELONGS_TO*]-(p1), 
      path_f3 = (f3:Family {id:'f3'})<-[:BELONGS_TO]-(:Asset)<-[:BELONGS_TO*]-(p3)
     COLLECT(DISTINCT p3) AS f3_parts
RETURN filter(x IN f1_parts WHERE NOT(x IN f3_parts)) AS unique_f1, 
       filter(x IN f3_parts WHERE NOT(x IN f1_parts)) AS unique_f3, 
       filter(x IN f1_parts WHERE (x IN f3_parts)) AS common

Those are simplified examples of course, how would you introduce the number of parts required in to our model? Let’s try an example:

Here we are modeling a sample trolley. The nodes and relationships would look like this:

CREATE (a1:Part {id:"120-001", desc:"Trolley, 3 wheeled"})
CREATE (p1:Part {id:"110-001", desc:"Wheel Housing"})
CREATE (p2:Part {id:"100-001", cost: 5.30, desc:"MS Bolt, M10x70, Galv"})
CREATE (a1)<-[:BELONGS_TO {qty:3.0, unit:"EA"}]-(p1)
CREATE (p1)<-[:BELONGS_TO {qty:1.0, unit:"EA"}]-(p2)
CREATE (p1)<-[:BELONGS_TO {qty:2.0, unit:"EA"}]-(p3)

We retain the part id, description and in some cases the cost of the parts in the nodes and the quantity required as well as the measurement unit in the relationships. The full command to generate the graph is on the second file of this link and our graph looks like:

Now, how much does it cost to build the trolley? It should be as simple as:

MATCH (t:Part {id:"120-001"})<-[r:BELONGS_TO]-(p:Part)
RETURN SUM(r.qty * p.cost)

But it isn’t because we haven’t aggregated our individual parts up along the way. We can see the “Wheel Housing” node has no cost property. So how do we fix this? Well, we have two options. One we can dynamically calculate the price, but we will be doing that every time and that seems wasteful. The other option is to pre-calculate those combined costs and save them in the part nodes. There is probably a better way to do this, if you know it, add it in the comments.

One way is to find all the nodes in the path that do not have a cost property and that are the last nodes along the path not to have a cost property. We need this to be true in order to be able to set their cost. The query below does that, we will run it until we get “no changes, no records” returned meaning that all part nodes now have a cost. The first time it is run, it calculates the part_cost for the “Top Piece”, “Side Piece” and “Plywood Platform”. The second time it calculates the part_cost for the “Wheel Housing” which required “Top Piece” and “Side Piece” to be calculated first, and then the third time it calculates the price for the Trolley. The fourth time it does nothing.

MATCH path = (t:Part {id:"120-001"})<-[:BELONGS_TO*0..]-(:Part)
WHERE last(nodes(path)).cost is null
WITH t, last(nodes(path)) AS missing_cost
WITH t, missing_cost, SIZE(COLLECT(missing_cost)) AS need_cost
MATCH path = (missing_cost)<-[:BELONGS_TO*0..]-(p:Part)
WITH t, missing_cost, need_cost, COLLECT(DISTINCT p) AS parts
WHERE SINGLE(x IN parts WHERE COALESCE(x.cost, x.part_cost) IS NULL)
MATCH (missing_cost)<-[r:BELONGS_TO]-(p:Part)
WITH missing_cost, SUM(r.qty * COALESCE(p.cost, p.part_cost)) AS part_cost
WHERE NOT EXISTS(missing_cost.part_cost)
SET missing_cost.part_cost = part_cost

Now we can check our price and…

MATCH (t:Part {id:"120-001"}) RETURN t.part_cost

246.965 as expected.

Pretty cool right? And all except that last query were pretty simple to write. Now a real BOM system will have versions, it will have replacement parts, and all kinds of complications, but I promise you, it’s nothing Neo4j can’t handle.

If you haven’t yet, also take a look at this blog post from my colleague Rik Van Bruggen on Using Neo4j to Manage and Calculate Hierarchies. If you are interested in seeing the bigger picture, this blog post shows you how the German toy manufacturer Schleich uses a graph data model to track all the elements related to toy production.

Tagged , , , , , , , , , ,

5 thoughts on “Bill of Materials in Neo4j

  1. yoannmaingon says:

    Hi Max, I love the Bill Of Material topic.You examples are great and I know some companies are actually pushing all their BOM data to a neo4J db to do such research.
    I myself use to work on having live Bill of materials in neo4j (using it as a live DB and not just an analytics/analysis db) and the limit I see in your presentation is the fact that a BOM link is not that simple. If you take your datamodel as is, how will you handle Part Instances and contextual part replacement. For this you would need to pull a relationship from our BELONGS_TO relationship. Which today is not possible I believe in Neo4J.
    So, the datamodel I have to day is the following:
    and a partBOM can have instances

    Thinking about this we could also store all the part instance data on the hasInstance relationship, do you know if it is possible in neo4j to just create a relationship without end-item? I believe not?

    Thanks for you article !

    • maxdemarzi says:

      I should do a more advanced BOM blog post… What are all the things you would want to see in it?

      • yoannmaingon says:

        Cool ! you could add Part versionning. An interesting case would also be to create the 150% BOM and build an options/variant solver with neo4j.

  2. maxdemarzi says:

    >>Cool ! you could add Part versioning.
    Sure, that I can do.

    >>An interesting case would also be to create the 150% BOM and build an options/variant solver with neo4j.

    Can you expand a bit into what this looks like? I am not in the industry, so I’m not sure what a “150% BOM” means. And what would you like to see in the solver? The options and their price points? Rules based just on price or on time? Reserves of items? Give me some more details and I can work on something.

    • yoannmaingon says:

      150% BOM is a term used to say that in your BOM you would include options and variants. For example for a car you would have 4 types of wheels, 5 different engines,… This is the configuration manager job to make sure the different configuration are possible and so he defines configuration rules. If I have X, than I have to have Y. If I have P I can only select R or S. So in the end, the front end allows users to define products by selecting the different options. Based on the options and variants rules, you are driven to select the right element and the array of configuration is given to the server to filter out the 150% BOM.
      It can be quite complex but a simple example that shows a price calculation every time you filter out the BOM would be nice.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: