DEV Community

Cover image for Supply Chain Optimization using postgreSQL and Apache-Age - Part 2
Adeel Ahmed
Adeel Ahmed

Posted on

2

Supply Chain Optimization using postgreSQL and Apache-Age - Part 2

For Part 1 follow this link.

Load data into the database:
To load data into the supply chain graph, you can use the Cypher 'CREATE' and 'MERGE' statements. Here's an example of how to load sample data into the supply chain graph:

1 - Create Suppliers:

SELECT * FROM cypher('
  CREATE (s:Supplier {id: 1, name: "Supplier A", location: "USA"}),
         (s2:Supplier {id: 2, name: "Supplier B", location: "Germany"})
');
Enter fullscreen mode Exit fullscreen mode

2 - Create Manufacturers:

SELECT * FROM cypher('
  CREATE (m:Manufacturer {id: 1, name: "Manufacturer A", location: "USA"}),
         (m2:Manufacturer {id: 2, name: "Manufacturer B", location: "Germany"})
');
Enter fullscreen mode Exit fullscreen mode

3 - Create Distributors:

SELECT * FROM cypher('
  CREATE (d:Distributor {id: 1, name: "Distributor A", location: "USA"}),
         (d2:Distributor {id: 2, name: "Distributor B", location: "Germany"})
');
Enter fullscreen mode Exit fullscreen mode

4 - Create Retailers:

SELECT * FROM cypher('
  CREATE (r:Retailer {id: 1, name: "Retailer A", location: "USA"}),
         (r2:Retailer {id: 2, name: "Retailer B", location: "Germany"})
');
Enter fullscreen mode Exit fullscreen mode

5 - Create Supplies relationships:

SELECT * FROM cypher('
  MATCH (s:Supplier {id: 1}), (m:Manufacturer {id: 1})
  CREATE (s)-[:Supplies {cost: 100, lead_time: 7}]->(m)
');

SELECT * FROM cypher('
  MATCH (s:Supplier {id: 2}), (m:Manufacturer {id: 2})
  CREATE (s)-[:Supplies {cost: 120, lead_time: 10}]->(m)
');
Enter fullscreen mode Exit fullscreen mode

6 - Create Produces relationships:

SELECT * FROM cypher('
  MATCH (m:Manufacturer {id: 1}), (d:Distributor {id: 1})
  CREATE (m)-[:Produces {cost: 200, lead_time: 5}]->(d)
');

SELECT * FROM cypher('
  MATCH (m:Manufacturer {id: 2}), (d:Distributor {id: 2})
  CREATE (m)-[:Produces {cost: 250, lead_time: 6}]->(d)
');
Enter fullscreen mode Exit fullscreen mode

7 - Create Distributes relationships:

SELECT * FROM cypher('
  MATCH (d:Distributor {id: 1}), (r:Retailer {id: 1})
  CREATE (d)-[:Distributes {cost: 150, lead_time: 4}]->(r)
');

SELECT * FROM cypher('
  MATCH (d:Distributor {id: 2}), (r:Retailer {id: 2})
  CREATE (d)-[:Distributes {cost: 180, lead_time: 3}]->(r)
');
Enter fullscreen mode Exit fullscreen mode

This example creates a simple supply chain graph with two suppliers, two manufacturers, two distributors, and two retailers. The relationships between these entities are defined by the cost and lead time for each step of the process. You can customize the data to match your actual supply chain and use Cypher queries to analyze and optimize the network.

Query the data for insights:
Here are some example Cypher queries to gain insights from the supply chain graph data:

1 - Find all suppliers for a specific manufacturer:

SELECT * FROM cypher('
  MATCH (s:Supplier)-[:Supplies]->(m:Manufacturer)
  WHERE m.id = 1
  RETURN s.name, s.location, m.name
');
Enter fullscreen mode Exit fullscreen mode

2 - Find all retailers that a specific distributor serves:

SELECT * FROM cypher('
  MATCH (d:Distributor)-[:Distributes]->(r:Retailer)
  WHERE d.id = 1
  RETURN d.name, d.location, r.name, r.location
');
Enter fullscreen mode Exit fullscreen mode

3 - Calculate the average lead time from manufacturers to distributors:

SELECT * FROM cypher('
  MATCH (m:Manufacturer)-[p:Produces]->(d:Distributor)
  RETURN avg(p.lead_time) as average_lead_time
');
Enter fullscreen mode Exit fullscreen mode

4 - Find the shortest path between a supplier and a retailer based on cost:

SELECT * FROM cypher('
  MATCH p=(s:Supplier)-[:Supplies|:Produces|:Distributes*]->(r:Retailer)
  WHERE s.id = 1 AND r.id = 1
  RETURN p, reduce(totalCost = 0, rel in relationships(p) | totalCost + rel.cost) AS totalCost
  ORDER BY totalCost
  LIMIT 1
');
Enter fullscreen mode Exit fullscreen mode

5 - Find suppliers with the lowest lead time for a specific manufacturer:

SELECT * FROM cypher('
  MATCH (s:Supplier)-[sup:Supplies]->(m:Manufacturer)
  WHERE m.id = 1
  RETURN s.name, sup.lead_time
  ORDER BY sup.lead_time
  LIMIT 5
');
Enter fullscreen mode Exit fullscreen mode

6 - Find the most efficient distribution route based on a combination of cost and lead time:

SELECT * FROM cypher('
  MATCH p=(s:Supplier)-[:Supplies|:Produces|:Distributes*]->(r:Retailer)
  WHERE s.id = 1 AND r.id = 1
  RETURN p, reduce(totalScore = 0, rel in relationships(p) | totalScore + rel.cost + rel.lead_time) AS totalScore
  ORDER BY totalScore
  LIMIT 1
');
Enter fullscreen mode Exit fullscreen mode

These queries demonstrate different ways to gain insights from the supply chain graph data. You can customize and expand these queries to answer more specific questions about your supply chain and identify opportunities for optimization.

Visualize the results:

You can use graph visualization tools like Apache AGE Viewer to visualize the results of your analysis. These tools can help you understand the structure of your supply chain network and identify areas for improvement.

Related Articles

Supply Chain Optimization using postgreSQL and Apache-Age - Part 1

Contribute to Apache AGE

Apache AGE website: https://age.apache.org/

Apache AGE Github: https://github.com/apache/age

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay