Ontology in Practice series 4/4 · (1) The Meaning of Data That Should Survive a System Change · (2) Four Tasks Behind a Sewing Data Dictionary · (3) Finding Meaning in a Database Without Keys · (4) Letting AI Answer on Top of a Meaning Layer
KEY POINTS
AI that turns natural language questions into SQL guesses meaning from table and column names alone, so it easily goes wrong on columns that share a name but mean different things. SIJE first builds one table per domain with the needed columns joined in advance, a semantic view organized by business concept, and a data dictionary that records the definition and constraints of each item, and designs the AI to answer only on top of these. When the ERP changes, meaning is reconnected by master data and stock movement data before any tables are matched.
What the fashion brand in part 3 asked SIJE for was less the data cleanup itself than the step after it. The goal was an environment where people who do not work with data could ask a question in natural language and get, beyond simple lookups, sales analysis they can trust and lists of target customers.
“Which store saw the biggest drop in jacket sales last season?”
Getting AI to answer a question like this straight away is harder than it looks. The short answer is that if AI is left to guess at the table structure of a database, the answers come out wrong, and the results can be trusted only when it answers on top of the meaning layer built in part 3. This article covers that structure, and how the integration problem that arose when the customer replaced its ERP is reconnected through the same meaning layer.
What goes wrong when AI reads tables directly
The technology that turns a natural language question into a database query (SQL) is called Text-to-SQL. Given a question, the AI looks at table and column names, decides which tables to join and how, and writes the query.
The problem is that many columns cannot be understood from their names. As in part 3, when two columns both count returns and are updated by different rules, the AI has no way of knowing which one to use. Nor is it written anywhere in the tables that the confirmed price for some channels is an estimate. The question appears to be handled smoothly, but the number can be wrong, and the person who asked has little chance of noticing.
It is like giving a new hire nothing but database access and asking for a sales report. Without knowing the company’s terms and calculation rules, even careful work produces numbers on a different basis.
Three devices for answering on top of a meaning layer
The approach SIJE proposed to the customer is not to hand tables to the AI directly, but to put a layer organized by business concept in between.
| Device | What it does | When applied |
|---|---|---|
| Semantic view and data dictionary | Builds views that group stock movement, sales and customer tables by business concept, and records the definition, notation and constraints1 of each item so the AI does not connect the wrong columns | Short term |
| Question and query examples | Stores frequently asked questions paired with their standard queries, and offers them as reference examples when a similar question comes in | Medium to long term |
| Knowledge graph | Expresses the relations among customers, stock movements and sales as nodes and relations2 to answer questions that require following several steps | Medium to long term |
All three devices narrow the range the AI has to guess. When definitions are written down, the AI builds its query on the written definition instead of inferring meaning from a name.
One table per domain
Below the semantic view sits one table per domain (OBT, One Big Table). It is a table that joins the sales, store, product and customer columns needed for sales analysis in advance and puts them in one row.
A shopping basket is a good comparison. Instead of going back and forth between the fridge, the cupboard and the store to gather ingredients every time you cook, you put the ingredients for the dishes you make often into one basket in advance. Because the joins are already done, the AI has fewer chances to pick the wrong join path, and the calculation rules brought out in part 3 (estimated confirmed price, the basis for identifying returns) are calculated in advance as derived columns when this table is built.
| Design item | Details |
|---|---|
| What a row is | A unique key is set for each table, and when a key value is updated, all its child columns are joined and brought in again as one row |
| Derived columns | Existing report formulas are carried over so that values such as confirmed price and return type are calculated in advance |
| Query scope | The agreements from part 3 (closed stores included, confirmed and provisional data separated) are reflected as columns and conditions |
| Verification material | Sample rows built from real data are delivered together with examples of natural language questions this table can answer |
The way the data is stored was chosen for the same purpose. The tables of the existing database were converted into a file format that compresses data by column (Parquet) and loaded into cloud storage, and an analytics data platform was connected to read from it. Since the purpose is preservation and analysis, no database server with a constant running cost was kept.
When the ERP changed
Separately from this work, the customer was in the middle of replacing its existing ERP with a new product. The customer passed the core table specifications SIJE had organized to the new ERP vendor, and the vendor replied that its field structure differed from that of the existing database, so the specification could not be used for integration. The new ERP did not provide its database schema either.
The situation described at the start of part 1 had actually happened. Starting from the table structure at this point would mean guessing at the new ERP’s tables one by one. SIJE reversed the order and reconnected the meaning first.
| Step | What is done |
|---|---|
| 1 | Receive all the columns shown on the new ERP’s screens, by domain such as sales and product |
| 2 | Compare the received column names with the definitions in the existing meaning layer first (based on names and descriptions, without actual values) |
| 3 | Redefine the integration items by master data (product, supplier) and stock movement data (logistics: receipt, shipment, transfer, other account, inventory / store: store receipt, return, R/T, other account, sale, inventory) |
| 4 | Separately check properties that are easily lost in migration, such as margin, discount rate, coupons and loyalty points |
| 5 | Fix the integration scope using the flow from order to inventory as the reference view |
Steps 1 to 4 can proceed without knowing the table structure of the new system. Business concepts such as product, supplier, receipt, sale, return and inventory, and their definitions, are already organized in the meaning layer, so all that is needed is to find which of them each column of the new ERP corresponds to. The one table per domain, the semantic view and the AI query structure built on the meaning layer stay as they are. Only this connection changes.
Wrapping up the series
Over four parts, this series has covered how SIJE runs data systems with an ontology. Part 1 covered why the meaning of data has to be defined separately from the system, and the publicly available ontology technology. Part 2 covered how a dictionary of 47,359 standard processes was built for sewing process data. Parts 3 and 4 covered how meaning was restored from a fashion brand’s sales data and an AI query structure was built on top of it.
The process data of a sewing factory and the sales data of a brand are different in content, but they were handled in the same order. Concepts were defined first, hidden rules were brought out and kept as documents, and calculation and AI queries were made to run on those definitions. The definition and rule documents left behind this way become the reference for redefining integration items the next time a system is replaced.
👉 Ontology in Practice (1) : The Meaning of Data That Should Survive a System Change
👉 Ontology in Practice (2) : Four Tasks Behind a Sewing Data Dictionary
👉 Ontology in Practice (3) : Finding Meaning in a Database Without Keys
Sewing Production Glossary
Text-to-SQL
Technology that turns a natural language question into a database query (SQL).
Semantic view
A query structure that groups several tables by business concept and attaches meaning to each item.
One Big Table (OBT)
A table that joins the columns needed for analysis in advance and puts them in one row.
Derived column
A column that is not in the original data and is created by a calculation rule.
Master data
Reference information for transactions, such as products and suppliers.
Stock movement data
Transaction records that increase or decrease inventory, such as receipts, shipments, sales and returns.
References
- W3C, OWL 2 Web Ontology Language Primer (Second Edition), 2012. Class hierarchies and constraints. ↩
- W3C, RDF 1.1 Concepts and Abstract Syntax, 2014. Definition of the triple, the basic unit of a knowledge graph. ↩
