DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Spring Data Neo4j: How to Update an Entity
  • Leveraging Neo4j for Effective Identity Access Management
  • The Beginner's Guide To Understanding Graph Databases
  • Externalize Microservice Configuration With Spring Cloud Config

Trending

  • Stateless vs Stateful Stream Processing With Kafka Streams and Apache Flink
  • Doris: Unifying SQL Dialects for a Seamless Data Query Ecosystem
  • Simplify Authorization in Ruby on Rails With the Power of Pundit Gem
  • Segmentation Violation and How Rust Helps Overcome It
  1. DZone
  2. Data Engineering
  3. Databases
  4. Bill of Materials in Neo4j

Bill of Materials in Neo4j

Your Bill of Materials is probably right in the middle of your organization, nestled between all different teams. Ouch. Here's a better way to handle that.

By 
Max De Marzi user avatar
Max De Marzi
·
Nov. 22, 17 · Tutorial
Likes (1)
Comment
Save
Tweet
Share
8.4K Views

Join the DZone community and get the full member experience.

Join For Free

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)
RETURN DISTINCT 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's 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)
RETURN DISTINCT a

It is in the paths of Assets 1, 3, 4, 5, 6, and 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
ORDER BY depth DESC
LIMIT 1

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
ORDER BY depth DESC
LIMIT 1
SET p14.depth = depth

Can we compare two bills of material (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)
WITH COLLECT(DISTINCT p3) AS a3_parts, COLLECT(DISTINCT p5) AS a5_parts
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 simple, 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)
WITH COLLECT(DISTINCT p1) AS f1_parts, 
     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 for 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 it will have 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.

Neo4j Database

Published at DZone with permission of Max De Marzi, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Spring Data Neo4j: How to Update an Entity
  • Leveraging Neo4j for Effective Identity Access Management
  • The Beginner's Guide To Understanding Graph Databases
  • Externalize Microservice Configuration With Spring Cloud Config

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!