Linking columns to databases

This example shows you how to create a derived relation type (DRT), "Column is part of / contains Database", using the JSON editor. You can use the JSON editor to create complex, optimized relation paths that can fork and join, unlike the visual relation type builder, which supports only linear paths. The example guides you through defining relation paths using JSON syntax, testing the paths, assigning the derived relation type to an asset type, and viewing the derived relation on an asset page.

Objective

Show all databases that contain a column on the column's asset page.

Overview of phases

This example includes the following phases:

1. Create a derived relation type using the JSON editor.
2. Test the derived relation type.
3. Assign the derived relation type to an asset type.
4. View the derived relation on an asset page.

For clarity, the phase of creating a derived relation type using the JSON editor is split into multiple parts.

Prerequisites

• Your environment uses the latest user interface.
• You have a global role with the Product Rights > System administration global permission.
• The Derived relation support setting in Collibra Console is activated. If activated, the Add derived relation type button is shown on the Relation types page in the Operating Model settings.
• The following table lists the out-of-the-box operating model elements used in this example. If these elements were renamed in your environment, use the public IDs to identify them.

1 Create a DRT using the JSON editor

The following image shows the relation path used in the example.

To connect a column to its parent database, you can define your derived relation type as "Column is part of / contains Database", where:

• Head: Column
• Role: is part of
• Co-role: contains
• Tail: Database

The relation path uses the following relation types:

• "Column is part of Table" (role direction)
• "Table is part of Schema" (co-role direction)
• "Schema belongs to Technology Asset" (co-role direction)

1.1 Start the DRT creation process

1. On the main toolbar, click → Settings.
   The Settings page opens.
2. In the Operating model section, click Relation types.
3. On the Relation types page, click Add derived relation type.
   The derived relation type editor opens.
   
4. On the Details tab, enter the following information:
   • Role: is part of
   • Co-role: contains
   • Description: A relation that indicates to which Database a Column belongs

1.2 Define the head and tail of the relation path

1. Click the JSON tab.
   The JSON tab shows a JSON template to get you started.
   Tip As you continue the next steps, the editor shows syntax errors. These errors are expected and will resolve automatically as you complete your valid relation path.
2. In headNode → line 4, set the headType property to Column.
   Tip 

   • The headType property represents the head asset type of your derived relation type.
   • The JSON editor shows syntax errors once you start editing the template.
3. In headNode → outgoingEdges:
   • In line 8, set the type property to ColumnIsPartOfTable. You can either enter the public ID or use the auto-complete option. If using the auto-complete option, select Column is part of / contains Table (ColumnIsPartOfTable) - Explicit.
   • In line 7, set the direction property to role.
4. In tailNode → line 27, set the tailType property to Database.
   Tip The tailType property represents the tail asset type of your derived relation type.

1.3 Define the intermediate nodes of the relation path

1. On the JSON tab, in intermediateNodes → outgoingEdges → line 19, set the type property to SchemaContainsTable. If using the auto-complete option, select Schema contains / is part of Table (SchemaContainsTable) - Explicit.
2. In intermediateNodes → outgoingEdges → line 20, set the nextNodeName property to IntermediateNode2.
   Tip This is the name of the next node, which you will add in the next steps.
3. Copy lines 14 through 23 in the clipboard, paste them at the beginning of line 24, and move ] to the next line.
   The content on the JSON tab shows 39 lines in total.
4. Add the comma character at the end of line 23.
5. In intermediateNodes → outgoingEdges:
   • In line 25, set the name property to IntermediateNode2.
   • In line 29, set the type property to TechnologyAssetHasSchema. If using the auto-complete option, select Technology Asset has / belongs to Schema (TechnologyAssetHasSchema) - Explicit.
   • In line 30, set the nextNodeName property to TailNodeName.
     The JSON tab no longer shows any error, and the Graph tab in the right sidebar shows the relation path. The Save relation type button is now activated.
6. Click Save relation type.
   A public ID is generated and shown in the Public ID field on the Details tab. Your derived relation type is now operational and can be assigned to asset types.
   

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
{
  "headNode": {
    "name": "HeadNodeName",
    "headType": "Column",
    "outgoingEdges": [
      {
        "direction": "role",
        "type": "ColumnIsPartOfTable",
        "nextNodeName": "IntermediateNode1"
      }
    ]
  },
  "intermediateNodes": [
    {
      "name": "IntermediateNode1",
      "outgoingEdges": [
        {
          "direction": "co-role",
          "type": "SchemaContainsTable",
          "nextNodeName": "IntermediateNode2"
        }
      ]
    },
    {
      "name": "IntermediateNode2",
      "outgoingEdges": [
        {
          "direction": "co-role",
          "type": "TechnologyAssetHasSchema",
          "nextNodeName": "TailNodeName"
        }
      ]
    }
  ],
  "tailNode": {
    "name": "TailNodeName",
    "tailType": "Database"
  }
}

1.4 Improve the readability of the relation path

While your derived relation type is operational, you can improve the readability of the content on the JSON tab. The JSON editor and the graph show nodes such as HeadNodeName and IntermediateNode1, which are not user-friendly. Let’s fix this.

1. On the JSON tab, in headNode → line 3, change the name property from HeadNodeName to Column.
   Tip The head node name is informational and doesn’t affect the logic of the derived relation type.
   The Graph tab is automatically updated to show the new node name.
2. In intermediateNodes → line 15, change the name property from IntermediateNode1 to Table.
   
   Tip This makes the path invalid because the head node still references the old name.
3. In headNode → outgoingEdges → line 9, change the nextNodeName property from IntermediateNode1 to Table.
   
4. In intermediateNodes → line 25, change the name property from IntermediateNode2 to Schema.
   
   Tip This makes the path invalid because an intermediate node still references the old name.
5. In intermediateNodes → line 20, change the nextNodeName property from IntermediateNode2 to Schema.
6. In tailNode → line 36, change the name property from TailNodeName to Database.
   
   Tip This makes the path invalid because an intermediate node still references the old name.
7. In intermediateNodes → line 30, change the nextNodeName property from TailNodeName to Database.
   You now have a much cleaner definition of your relation path.
8. Click Save relation type.
   Your derived relation type is created. You will test its relation path in the next phase.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
{
  "headNode": {
    "name": "Column",
    "headType": "Column",
    "outgoingEdges": [
      {
        "direction": "role",
        "type": "ColumnIsPartOfTable",
        "nextNodeName": "Table"
      }
    ]
  },
  "intermediateNodes": [
    {
      "name": "Table",
      "outgoingEdges": [
        {
          "direction": "co-role",
          "type": "SchemaContainsTable",
          "nextNodeName": "Schema"
        }
      ]
    },
    {
      "name": "Schema",
      "outgoingEdges": [
        {
          "direction": "co-role",
          "type": "TechnologyAssetHasSchema",
          "nextNodeName": "Database"
        }
      ]
    }
  ],
  "tailNode": {
    "name": "Database",
    "tailType": "Database"
  }
}

2 Test the DRT

Before testing your derived relation type, ensure that your environment contains data that is compatible with the derived relation type. If you use the Data Catalog database registration feature, you can test the relation path using your own data. The example assumes that your environment contains the following test data.

1. If you closed the derived relation type editor, open it again:
   1. On the main toolbar, click → Settings.
      The Settings page opens.
   2. In the Operating model section, click Relation types.
   3. On the Relation types page, find the derived relation type "Column is part of / contains Database".
   4. In the Actions column of the derived relation type, click → Edit.
2. On the Relation test tab in the right sidebar, in the Test asset field, select DRT column and click Run test.
   DRT database is returned in the results.
   
3. To test the relation path in the opposite direction, select the Start from tail checkbox.
4. In the Test asset field, select DRT database and click Run test.
   DRT column is returned in the results.
   

3 Assign the DRT to an asset type

Now that your derived relation type is created and tested, you need to add it to an asset type assignment to show the dynamically calculated derived relation on the Column asset pages. This allows users to see which databases contain a specific column on the Column asset page. The procedure for assigning a derived relation type to an asset type is identical to assigning an explicit relation type to an asset type. The example assumes that the Column asset type uses a custom layout.

1. On the main toolbar, click → Settings.
   The Settings page opens.
2. In the Operating model section, click Asset types.
3. On the Asset types page, click the name of the Column asset type.
4. On the Column asset type page, in the left pane, expand the global assignment and click Characteristics.
5. On the Characteristics page, click Edit layout.
6. On the Edit layout page, in the left pane, click Add a Characteristic.
7. In the Add a Characteristic dialog box, find and select your new derived relation type, Column is part of Database.
8. In the is part of Database dialog box, select the Add directly to layout checkbox and click Add.
   The derived relation is part of Database is added to the layout.
   
   Tip You can change the position of the derived relation using .
9. Click Publish and close the asset type page.

The derived relation is now available on the Column asset pages. You can verify this in the next phase.

If you want to show all the columns that are part of a database on the Database asset page, assign the same derived relation type to the Database asset type. When doing so, your new derived relation type is shown as Database contains Column. This is because the path is evaluated from the tail asset type (Database), traversing in the co-role direction instead of the role direction.

4 View the derived relation on an asset page

It is now time to verify that the derived relation appears on the Column asset page.

1. Open the DRT column asset page.
   Tip If you prefer to use your own data, open any Physical Data Dictionary domain that contains a Column asset, and then open a Column asset that is connected to a Database asset. All assets ingested using the Data Catalog database registration feature should be correctly connected.
2. On the Summary tab, verify that the asset page shows the derived relation is part of Database with the asset DRT database.
   
   Tip You can identify a derived relation on an asset page by the diagram icon . Clicking shows the relation paths on which the derived relation is based, and clicking an asset name opens the corresponding asset page.

If you also assigned your derived relation type to the Database asset type, the asset page for DRT database shows the columns that are part of it, following the derived relation in the other direction.