August 4, 2022
In my article yesterday, I reviewed basic import data strategies and how you can bring your legacy data to OpenBOM. I was focusing on a basic OpenBOM data modeling setup and importing catalogs and BOMs as two fundamental data objects in OpenBOM. Please check the article – OpenBOM Import Data Strategies before continuing with this one.
Today, I would like to talk about more advanced techniques of data import to OpenBOM and I’d like to focus on three important topics:
- Multi-Sourcing and Catalog Fusion
- Multi-Level and Multi-Disciplinary Product Structures
- Simultaneous Imports from Excels and CAD systems
OpenBOM provides a very powerful mechanism for data fusion and data organization as the outcome of simple and flexible data modeling architecture.
OpenBOM Data Architecture
For more advanced information about data management architecture, please check the OpenBOM training library and the following article OpenBOM Flexible Data Model. Here is the most important diagram about data modeling in OpenBOM and how data can be expanded and extended in OpenBOM.
The most important practical data management principles are related to item databases and bill of materials, how they managed data, and how these principles can be applied to data imports from multiple data sources.
Items are organized in catalogs, however, each item is independent and identifiable by a unique part number. Part Number uniquely identifies and are super important, so you should avoid using the same part number for different items.
OpenBOM automatically arranges parent-child hierarchies based on the Item Part Numbers. This means it is enough to create 2 BOMs and use Part Numbers of sub-level Items in the top-level BOM for OpenBOM to create parent-child data structures.
Each catalog might have a different set of properties and, as a result, a product structure can include different item properties in the same level or different levels. It can even be more extended by adding instance-level properties in the BOMs.
Here is how these principles can be applied to the practice of data imports.
Data Fusion in Catalogs
You can import data in the same catalog and for the same item multiple times. OpenBOM catalog import logic will attempt to identify a record with the specific part number and update (or append) attributes to already existing items with a specific Part Number. You can use this technique to bring multiple groups of attributes exported from different existing legacy systems and data sources. Items with new Part Numbers with no match in the catalog will result in the addition of a new item to the catalog.
Therefore your technique should be to identify all data sources and do data wrangling by bringing all data in multiple Excels spreadsheets. For example, basic item data (Part Number, Description, etc.), engineering data (dimensions, etc), purchasing data (eg. Cost, Suppliers), and Files (eg. specs and links). Once you have these spreadsheets you can import them into one or multiple catalogs and all data will be merged.
REMEMBER: you can import in the same catalog multiple times. New data will be added, existing data will be updated, and No data will be deleted.
Multi-level and Multi-disciplinary BOM Structures
The second most important data import technique is related to BOM imports. The logic of BOM import is fundamentally different from catalogs. First, in the OpenBOM data model, each level in the multi-level BOM is represented as a separate BOM. OpenBOM uses a graph database to manage relationships between them, but each BOM level is independent and can include different attributes (both for the item and instance level). Therefore you can import the data independently by levels or by a single import of Excel with the “level” attribute in a special format – read our import from spreadsheet documentation.
The technique of multi-level BOM imports can include imports of specific BOM levels, structures, and sub-structures. You can do it in multiple imports and OpenBOM will intelligently connect levels and sub-levels based on the part numbers.
REMEMBER: Avoid creating circular references because they will prevent you from the ability to explore and drilling down in sub-level in a multi-level BOM user interface. If you by mistake got a circular reference alert, use the “composed of” command instead of “+” expanding sub-levels to find a part number that caused circular reference and fix the data problem. For more information check the following article – Circular Reference Found – What Does It Mean?
Simultaneous Import From CAD and Excel
The principles I described above can be applied to import data from CAD systems and external Excel and legacy databases. The main idea here is that OpenBOM will merge data in the catalog about each item based on the Part Number (only) and, as a result, the data you imported from CAD about the item will be merged with the data that was imported to the catalog from Excel before.
A similar strategy can be applied when you need to import data from multiple CAD assemblies and sub-assemblies designed in different CAD systems (eg. multiple MCADs and EDA/PCB design). OpenBOM will automatically connect sub-levels by Part Numbers and you will be able to import a multi-disciplinary product structure created in multiple CAD systems.
OpenBOM provides powerful advanced capabilities to import data from multiple sources and fuse this information together in a consistent semantically connected data model. Keep in mind that you need to apply some data model planning before embarking on this data import journey – to plan properties, data types, catalogs, and multiple structures in the way they will be organized. You can always contact OpenBOM support. Also, I recommend OpenBOM premium onboarding training as a great format to discuss your data management implementation and import strategies.
REGISTER FOR FREE to check how you can import your data to OpenBOM.