The typical Engineering organisation sits on huge amounts of Engineering data, which if used properly can return clear bottom-line opportunities.
Let’s take an example. The Bill of Materials (BOM) is structure containing product engineering and manufacturing data enabling a final product to be costed, assembled and configured from constituent parts. Typically, BOMs reside in complex and restrictive data stores that are designed for strict control over the data.
Herein lies the problem – many parts of the typical organsiation (legitimately) want their own views of the BOM (and other engineering data), creating a significant data management problem; consequentially, this makes data analytics a costly and underwhelming activity.
We are out to fix this; our new product, innovatenow.com, has a innovative engineering module which liberates the BOM from the limits imposed by traditional databases; we are doing this using a new database designed for complex hierarchical data structures.
We are using the Graph Database and believe this can be the next game changer in the engineering industry. It will bring simplicity and elegance to a complicated data management problem.
Why try something new?
The Relational Database Management Systems (RDBMS) is the predominant technology used to implement BOMs. They have served industry well, however they do suffer from limitations – particularly when used to implement complex BOM hierarchies. Just a few of the problems:
- Complex hierarchies such as many multi-parent hierarchies are difficult to implement and support in a RDBMS.
- Complex mapping tables are required to adequately represent the real relationships between data in a RDBMS. These are costly to implement and support, particularly when the number of joins reaches 5 or 6
- Increasingly, the discovery of data around a particular node (or data item) is critical to deep analytics – which require recusrive SQL and complex joins on RDBMS. For example, for any part of the BOM, there should be the ability to look for the most similar alternative. A properly indexed graph approach would do this discovery at much less cost and interactivity than possible in RDBMS
- Regulatory compliance sometimes means applying exotic queries to the BOM. In graph queries such as “show me everyone that has ever used a part containing chemical xyz” is a series of simple traversal
So, take a very simple example. The chart below shows a simple BOM for a product, which shows the relationship between the assemblies, sub-assemblies and parts. In reality, BOMs can be 20+ levels and parts will be grouped in their own hierarchies and classified into classification hierarchies.
The Relational Database approach
The Bill of Materials is contained in a self-referencing hierarchy and can have attributes linked to it via mapping tables and joins
- Efficient retrieval of data if it conforms to the underlying table structure
- Good for simple BOM use-cases
- Mature support for large databases (backup/restore/audit/transaction locks/etc)
- Management of design revisions where minimal changes are made is difficult
- For most BOMs, complex SQL is required to join multiple tables
- Searching / discovering data can be difficult if not properly indexed
The Document-Based approach
Part of the noSQL family of databases, this approach creates a “document” based on nested key-value pairs to persist data. Think of how you would create a BOM with pen and paper – a book representing the BOM and pages representing the assemblies, with lines on the page containing the list of parts. The document can grow by adding new pages/lines, be copied, be modified and given a good index can be queried.
This is how the document-based approach could be implemented
- Flexible support for custom attributes
- Designed for fast retrieval
- Structures can be easily compared
- There is no concept of referential integrity, so where identifiers are used across different documents, the consistency here will cause a problem
- Enabling effective search requires significant investment in indexing
- A well-tested application layer is required, as the document database will insert almost anything – and unlike RDBMS, referential integrity cannot be maintained
The Graph approach
A graph database is made up of Nodes (such as BOM item, part, variant) with Properties (such as name) connected by Edges (relationships).
Here is one approach to implementation
- Creating and maintaining complex hierarchies becomes a simple activity- given Graph’s easy way to manipulate relationships and modify nodes
- Excellent potential for engineering analytics
- Powerful fork/merge operations are possible
- A simple tabular view of data is not possible and requires retrieval and processing first
- Potential performance problems when clustering the data
- Lack of skills in the market to manage Graph databases and potential technology risk
We believe graph is an exciting technology and have chosen to progress with this – we believe the potential to perform deep analysis on the BOM structure will provide deep insigght at a much lower cost to engineering organisations.
See the the summary table, below
Stay tuned for the fascinating engineering analytics that are possible