July 20, 2020
Importing legacy data is one of the most painful stages of any business system PDM, PLM, ERP, and practically any other system. To import data is a project that usually underestimated the amount of complexity, data cleaning, data import, and validation. OpenBOM is very different from many other BOM management and PLM systems you know.
In this article, I will give you ideas and recommendations on how the combination of the OpenBOM data model and tools can allow you to acquire multiple pieces of information and connect it in the rich set of product data.
OpenBOM flexible data model and multi-tenant data architecture is a foundation of how easy OpenBOM can acquire data. Before moving forward, I recommend you refresh your knowledge about OpenBOM’s basic concepts and OpenBOM data model.
There are three basic ways to import data to OpenBOM:
3- REST API
There are few additional tools currently in the development. I will cover them at the end of the article.
To understand the OpenBOM data model is important to make the import the most efficient. The foundation of OpenBOM data is the Item model. The Item is a basic building block of OpenBOM data. Items is a universal representation of anything in design and manufacturing. You can think about standard items and engineering items. The first item is typically purchased and the second one is designed and manufactured. But there are many other ways to think about items.
Importing Items from Spreadsheets
You need to decide how to organize data about Items. The simplest way is to have a single catalog with all items. The pros are simplicity and ease of management. Technically, OpenBOM will do everything for you. You need to create a catalog and start importing Excels in this catalog. The data will be extended, additional properties will be added, existing Items will be extended. The cons of this approach are that each item will have the same set of properties. It can be sub-optimal and confusing. Why electrical item has mechanical properties? The same about parts and assemblies. So, separating catalogs is a good idea.
Importing items from Excel is the easiest way to. Keep in mind a few limitations of current tools:
- OpenBOM importing data in text format. If you import currency, an Excel file should not have currency symbols. Import plain text (eg. 1.25) will convert the value to currency automatically if the property has currency type.
- You cannot import Reference properties (you will get just URLs)
- You cannot import Labels for Images
These and some other limitations will be improved in our next improvement of import tools.
Notes about public and private properties.
Public properties are used by default when you create data in OpenBOM. There are lot of good reasons to use public properties, but some of our customers might concern about having their property names to be exposed (sometimes property name can be exposed to what customers do). Therefore OpenBOM import gives a priority to private property selection if such property exists. So, if you want to use private properties, you need to create these properties first (add them to private property table) and only afterward use Import command.
Importing Bill of Materials and Notes about Parent-Child Relationship
The basic Bill of Materials model is a single table of items with instance properties and references to Item. Such a model gives a great level of flexibility and ways to recombine BOMs (levels) into a nested structure under multiple conditions. The basic Parent-Child relationship is created each time OpenBOM recognizes Item Part Number and BOM Part Number. Therefore, it doesn’t matter for OpenBOM if you import BOMs top-down or bottom-up, which is a big pain in many other systems. It also gives you great flexibility when you importing data. You can import top-level assembly and later sub-level BOMs. But you can also do the opposite.
There are three ways to import BOM from spreadsheets:
- Import each level independently.
- Import an entire structure (you need to use Level property in the format x.x.x)
- A combination of both
Importing Data using CAD add-ins
To import data from CAD-ins and easy, but you might be facing questions about what is the best approach of importing- to grab a top-level assembly or do it by pieces. While there is no silver bullet, please remember that CAD-in import is technically following the same rules as importing data via spreadsheets. This means Items can be imported independently and BOMs can be imported level-by-level.
The following commands in CAD-ins can be used:
- Save Item to save a specific part to a catalog. You can select a catalog (not yet available in all CAD add-ins)
- Import Catalogs
- Import BOMs with a single click
Check OpenBOM out of the box CAD Add-in pages for more information. Here are some links for specific CAD best practices articles
Importing Data Using REST API
OpenBOM REST API is a way to automate and customize data import. Check out the developer’s page.
OpenBOM Partners and independent consulting can be helping you with OpenBOM development and provide service to clean and bulk import the data to your OpenBOM account.
OpenBOM has a new advanced import tool in our development plans focusing on providing wizard-style data cleaning and adjustment including learning capabilities and smart data mapping. The goal of these tools is to provide additional improvement in the ways data can be imported to OpenBOM.
Importing data is not one time process. Data is complex and lives in many places. To build a complete set of product data and to connect all dots can take time. It is a process. OpenBOM provides a robust out of the box set of tools to import data to OpenBOM. Because of a very flexible data management platform, the need to massage and change the date before the import is minimum. Also, OpenBOM can continuously import and merge data. OpenBOM data import scales from simple Excel import to out of the box CAD add-ins and robust REST API driven custom importing projects.
Check our how OpenBOM helps thousands of users and manufacturing companies to manage data and consider to contact OpenBOM consultants and partners about custom import projects.