Getting Relationship Suggestions

In sources with no declared (foreign) keys, find the columns whose values really join and get them suggested as virtual relationships. You get 7 relationships with their evidence from the three sample business databases, create them in a new graph, and run a query that joins two databases.

Why suggestions?#

In Chapter 6 you knew which column pointed to which (PoLine.po_id = PurchaseOrder.po_id) and made the relationships yourself. A real company's sources have hundreds of tables, foreign keys are often not declared, and every system names things its own way. The same purchase order number is po_id in the ERP and ref_po_no in the WMS.

Relationship suggestions measures values, not just names, to find the columns that join, and shows the evidence for each judgement. It works across datasources too.

How it finds them#

The engine only measures; Studio decides which pairs are relationships. Neither statement writes to the source, and no data is copied.

SHOW COLUMN STATS FROM erp.purchase_order
SHOW VALUE OVERLAP FROM wms.shipment(ref_po_no) IN erp.purchase_order(po_id)

• SHOW COLUMN STATS — for each column, over the first 1000 rows: nulls, distinct values, whether it is unique, the five most common values and a short sample.
• SHOW VALUE OVERLAP — of up to 200 distinct child values, how many (and what share) are in the parent column, and whether the parent values are unique.

The rules for deciding:

• The parent column must be unique, and at least 80% of the child values must be in it.
• Status, flag, quantity and date columns never become children. Even when their values overlap, such columns do not point to other rows.
• Names count as evidence too: same_key (same name, po_id → po_id), table_key (table name + key, item_id → item.item_id), rules read through an abbreviation map (abbrev:…, po = purchase_order, vendor = supplier), and so on.
• A column whose values match but whose name gives no evidence is capped at a confidence of 0.6.

For comparison, NVIDIA Auto Ontology looks for joins only within one connection, skips inference when there is no embedding key, and keeps no evidence. This feature suggests across datasources and shows the evidence for every suggestion.

Try it — an empty graph#

The sample graph sample already has relationships, so get suggestions for a new graph scm that has none. Run this statement in the portal tab (covered in detail in Chapter 7). It runs whatever graph is selected.

CREATE GRAPH scm

Try it — getting suggestions#

1. Open the Data Sources tab. If it was already open, close it and open it again so that scm appears in the graph list.
2. Click Relationship suggestions at the bottom left.
3. Check that the datasources crm, erp and wms are all ticked, and choose scm as the graph.
4. Leave Ask AI too for columns with other names off and click Suggest. The rules alone decide.

In our test it measured 13 tables and checked value overlap 57 times in one to two seconds (1.0–1.8 s), and suggested 7 relationships.

All seven have a unique parent key (unique parent on screen). The last two are references across databases and under other names: WMS shipments point to ERP purchase orders and suppliers. Below the table, it also notes that erp.po_line is a junction table linking items and purchase orders.

Reading the rejected candidates#

Open Rejected candidates: 56 — why they are not relationships to see the pairs that were measured but not suggested, with the reason for each.

In the sample graph crm.sales_order.sku_code is linked to items by FOR_ITEM, but with only 3 distinct values the rules could not be sure. A relationship like that is made by a person, as in Chapter 6. Suggestions do not replace your judgement; they narrow the candidates down and attach the evidence.

Try it — creating the chosen relationships#

1. Tick REF_PO and VENDOR, which are not ticked. If you read the evidence and agree, a confidence below 70% is fine.
2. To rename a relationship, edit the box on the right. Here, leave them as they are.
3. Click Create the 7 selected relationships in graph scm.

If the graph has no virtual node reading a table at either end, that node is made first (a parent keyed on its parent column), then the relationship. The relationship statements that ran are listed below in order.

CREATE VIRTUAL RELATIONSHIP REF_PO IN scm FROM Shipment(ref_po_no) TO PurchaseOrder(po_id)
CREATE VIRTUAL RELATIONSHIP VENDOR IN scm FROM Shipment(vendor_code) TO Supplier(supplier_id)

Click scm under the virtual graphs on the left to see 8 virtual nodes and 7 relationships, grouped by datasource.

Using it right away#

In the portal, choose the graph scm and follow the suggested relationships from a WMS shipment to the ERP purchase order and supplier.

MATCH (sh:Shipment)-[:REF_PO]->(po:PurchaseOrder)-[:SUPPLIER]->(s:Supplier)
WHERE sh.delay_days > 3
RETURN sh.shipment_no, sh.delay_days, po.po_id, s.name

It is the same answer you got in the sample graph sample through the relationships people made (SHIPS, ORDERED_FROM). You got the same graph without writing a single relationship yourself.