Managing ObjectSet Custom Field in Alation Analytics¶

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

ObjectSet custom field in a catalog page allows adding references to other objects in the catalog. It refers to the objects of below types:

Data source
Schema
Table
Column
User or Group
Article
Business Policy
Folder (Glossary)
Document (Term)

Adding ObjectSet Custom Fields in Alation¶

To add an ObjectSet custom field in Alation, you must configure Customization under the Admin Settings page. For more information, see Manage Custom Fields and Manage Templates.

When creating an ObjectSet custom field, you must configure the Permitted Types (allowed object types) under the on the Target Page for the field, and specify the Backref Name (back-reference name) under the on the Source Page section, which determines how the field appears on the catalog page of the referenced objects.

Getting ObjectSet Custom Fields from Alation Analytics¶

Custom fields metadata are stored in the customfield table in Alation Analytics. You can fetch the ObjectSet custom fields from the table using below query:

SELECT customfield_id, name_singular, allowed_otypes
FROM public.customfield
WHERE field_type='OBJECT_SET'
   AND customfield_id NOT IN (1, 2); -- exclude system fields 1 and 2

Query Result

customfield_id

name_singular

allowed_otypes

8

Steward

{user,groupprofile}

10006

appicable_policies

{business_policy}

10008

db_objects

{data,schema,table,attribute}

10064

related_docs

{article,glossary_v3,glossary_term}

Querying ObjectSet Fields for an Object Type from Alation Analytics¶

Custom fields added to a template are mapped to their respective object types in the otype_customfield_map table. For example, custom fields associated with the Data Source type can be queried in Alation Analytics using the following query.

SELECT field_id, field_name FROM public.otype_customfield_map WHERE otype='data';

To filter only the ObjectSet custom fields for a specific object type, you can join the data with the customfield table. For example, ObjectSet fields associated with the Data Source type can be queried as shown below.

SELECT field_id, field_name
FROM public.otype_customfield_map m
LEFT JOIN public.customfield cf ON cf.customfield_id=m.field_id
WHERE m.otype='data' and cf.field_type='OBJECT_SET';

Setting ObjectSet Custom Field Values in Alation¶

Once you add the ObjectSet field to the required template, it appears on the corresponding catalog page, and you can add values by searching for objects of the permitted types.

Example

In the Applicable Policies ObjectSet custom field, users can search for and select business policies by typing in the search bar. The system displays matching results with the policy title and type (e.g., business_policy) for users to choose from.

To add values to the Applicable Policies ObjectSet custom field:

Navigate to the catalog page and click Details.
In the Applicable Policies field, click the + (Add) button next to the field.
In the pop-up, type in the search bar to find the relevant business policy.

The system displays matching results, showing the policy title and object type (e.g., business_policy).
Select the desired policy from the list.

The selected policy is added to the ObjectSet field.

Fetching ObjectSet Field Values in Alation¶

In Alation Analytics, all custom fields associated with a specific object type are added as additional columns in the corresponding metadata table for that object type. For example, custom fields related to the Data Source object type appear as columns in the rdbms_datasources table.

If steward and applicable_policies are ObjectSet fields linked to the Data Source object type, you can query their values from Alation Analytics using the below query:

SELECT ds_id, steward, applicable_policies FROM rdbms_datasources;

Query Result

The values of ObjectSet columns are stored as arrays of type TEXT[] (text array), where each item follows the format {OBJECT_TYPE}_{OBJECT_ID}.

For example: user_612, business_policy_285.

ds_id

steward

applicable_policies

1

{user_612,user_8}

{business_policy_285}

15

{business_policy_22}

121

{user_655}

1033

{groupprofile_12,groupprofile_11,user_6}

1037

{user_1033}

{business_policy_163}

Querying Referenced Objects in ObjectSet Custom Field from Alation Analytics¶

Each referenced object in an ObjectSet field belongs to one of the object types listed below. Each object type has a corresponding table in Alation Analytics from which its metadata can be queried.

Object Type

Table name in Alation Analytics

user

users

groupprofile (Group)

alation_group

business_policy (Policy)

business_policy

data

rdbms_datasources

schema

rdbms_schemas

table

rdbms_tables

attribute (Column)

rdbms_columns

article

article

glossary_v3 (Folder / Glossary)

glossaries

glossary_term (Document / Term)

terms

The following sample query parses the referenced objects from the steward field and retrieves user and group information for Data Source objects:

WITH rdbms_datasource_stewards AS (
   SELECT
      ds_id,
                substring(object_key from '^(.*)_\d+$') AS object_type,
      substring(object_key from '_(\d+)$')::int AS object_id
   FROM rdbms_tables,
      unnest(steward) AS object_key
   WHERE object_key IS NOT NULL
             AND object_key <> ''
             AND object_key ~ '^.+_\d+$'
)
SELECT d.ds_id, d.object_id, d.object_type
FROM rdbms_datasource_stewards d
LEFT JOIN users u
   ON d.object_type = 'user'
           AND d.object_id = u.user_id
LEFT JOIN alation_group g
   ON d.object_type = 'groupprofile'
           AND d.object_id = g.groupprofile_id
WHERE u.user_id IS NOT NULL
        OR g.groupprofile_id IS NOT NULL
ORDER BY d.ds_id;

Deleting Objects Referenced in ObjectSet Custom Fields¶

If a user referenced in an ObjectSet field is later suspended, the reference is not removed from the field’s stored values, it is retained. This ensures that if the user is reactivated, all existing references in ObjectSet fields will be restored and visible again in the UI.

In the catalog UI, suspended users are either hidden or marked with a [GONE] prefix, depending on the context:

In Articles, suspended users are shown with the prefix [GONE].

In other catalog pages, suspended users are hidden from view.

Excluding Suspended Users in Alation Analytics While Querying ObjectSet Custom Fields¶

Since suspended users are not removed from ObjectSet field values, they must be explicitly excluded when querying Alation Analytics. In the example below, the condition u.suspended = false in the JOIN clause ensures suspended users are filtered out.

The query also excludes hard-deleted objects. This is handled by the condition u.user_id IS NOT NULL OR g.groupprofile_id IS NOT NULL in the WHERE clause.

WITH rdbms_datasource_stewards AS (
   SELECT
      ds_id,
                substring(object_key from '^(.*)_\d+$') AS object_type,
      substring(object_key from '_(\d+)$')::int AS object_id
   FROM rdbms_tables,
      unnest(steward) AS object_key
   WHERE object_key IS NOT NULL
             AND object_key <> ''
             AND object_key ~ '^.+_\d+$'
)
SELECT d.ds_id, d.object_id, d.object_type
FROM rdbms_datasource_stewards d
LEFT JOIN users u
   ON d.object_type = 'user'
           AND d.object_id = u.user_id
           AND u.suspended = false
LEFT JOIN alation_group g
   ON d.object_type = 'groupprofile'
           AND d.object_id = g.groupprofile_id
           AND g.deleted = false
WHERE u.user_id IS NOT NULL
        OR g.groupprofile_id IS NOT NULL
ORDER BY d.ds_id;

Similar to users, if an object referenced in an ObjectSet field is later deleted from the catalog, its reference is retained in the field’s values, even though it is hidden in the UI. This ensures that if the object is reinstated later, all its references in ObjectSet fields will reappear in the catalog pages.

For example, if a schema is excluded from metadata extraction (MDE) and subsequently deleted from the catalog, its reference remains in the ObjectSet field. If the schema is later re-included for extraction, it will automatically reappear on all catalog pages where it was originally referenced.

Sample Queries to Filter Deleted Objects from ObjectSet Custom Field Columns¶

ObjectSet custom fields hold references to various object types within Alation. While deleted objects persist in the database with existing references in ObjectSet fields, the user interface displays only active objects or marks deleted ones with a GONE prefix or suffix.

Alation Analytics extracts and retains all object references, including deleted ones, in ObjectSet field columns. Consequently, any queries involving these columns in Alation Analytics require filtering to exclude deleted object references.

Refer to the following sample queries that filters out both soft-deleted and hard-deleted objects from the result:

Filter Out Suspended Users and Deleted Groups from an Objectset Field¶

Consider a scenario where the steward column is a PeopleSet custom field that stores references to users and group profiles within the rdbms_tables table.

The following SQL query retrieves the table ID along with the assigned steward users and steward groups referenced in this field, excluding suspended users and deleted groups:

WITH rdbms_tables_stewards AS (
   SELECT
     table_id,
             substring(object_key from '^(.*)_\d+$') AS steward_type,
     substring(object_key from '_(\d+)$')::int AS steward_id
   FROM rdbms_tables,
      unnest(steward) AS object_key
 WHERE object_key IS NOT NULL
             AND object_key <> ''
             AND object_key ~ '^.+_\d+$'
)
SELECT t.table_id, t.steward_type, t.steward_id
FROM rdbms_tables_stewards t
LEFT JOIN users u
   ON t.steward_type = 'user'
           AND t.steward_id = u.user_id
           AND u.suspended = false
LEFT JOIN alation_group g
   ON t.steward_type = 'groupprofile'
           AND t.steward_id = g.groupprofile_id
           AND g.deleted = false
WHERE u.user_id IS NOT NULL
     OR g.groupprofile_id IS NOT NULL
ORDER BY t.table_id;

Query Result

table_id

steward_type

steward_id

225

user

688

225

user

678

225

groupprofile

5

225

groupprofile

9

15732

user

662

15732

user

678

15732

user

1017

15732

groupprofile

11

Filter Out Deleted Business Policies from an Objectset Field¶

Consider a scenario where the applicable_policies column is a custom field that stores references to business policies within the rdbms_schemas table.

The following SQL query retrieves the schema ID along with the associated business policies referenced in this field, excluding any policies that have been marked as deleted:

WITH rdbms_schemas_policies AS (
   SELECT
      schema_id,
             substring(object_key from '^(.*)_\d+$') AS object_type,
     substring(object_key from '_(\d+)$')::int AS object_id
   FROM rdbms_schemas,
      unnest(applicable_policies) AS object_key
   WHERE object_key IS NOT NULL
             AND object_key <> ''
             AND object_key ~ '^.+_\d+$'
)
SELECT s.schema_id, p.policy_id
FROM rdbms_schemas_policies s
LEFT JOIN business_policy p
   ON s.object_type = 'business_policy'
           AND s.object_id = p.policy_id
           AND p.deleted = false
WHERE p.policy_id IS NOT NULL
ORDER BY s.schema_id;

Query Result

schema_id

policy_id

127

4

127

5

140

5

Filter Out Deleted RDBMS Objects from an Objectset Field¶

Consider a scenario where the db_objects column is a custom field that stores references to RDBMS objects including data sources, schemas, tables, and columns within the article table.

The following SQL query retrieves the article ID along with the associated RDBMS objects referenced in this field, excluding any entries that have been marked as deleted:

WITH articles_rdbms_objects AS (
   SELECT
     article_id,
             substring(object_key from '^(.*)_\d+$') AS object_type,
     substring(object_key from '_(\d+)$')::int AS object_id
   FROM article,
      unnest(db_objects) AS object_key
   WHERE object_key IS NOT NULL
             AND object_key <> ''
             AND object_key ~ '^.+_\d+$'
)
SELECT a.article_id, a.object_type, a.object_id
FROM articles_rdbms_objects a
LEFT JOIN rdbms_datasources d
   ON a.object_type = 'data'
           AND a.object_id = d.ds_id
           AND d.deleted = false
LEFT JOIN rdbms_schemas s
   ON a.object_type = 'schema'
           AND a.object_id = s.schema_id
           AND s.deleted = false
LEFT JOIN rdbms_tables t
   ON a.object_type = 'table'
           AND a.object_id = t.table_id
           AND t.deleted = false
LEFT JOIN rdbms_columns c
   ON a.object_type = 'attribute'
           AND a.object_id = c.column_id
           AND c.deleted = false
WHERE d.ds_id IS NOT NULL
             OR s.schema_id IS NOT NULL
             OR t.table_id IS NOT NULL
             OR c.column_id IS NOT NULL
ORDER BY a.article_id;

Query Result

article_id

object_type

object_id

1

data

2

1

data

3

1

schema

25

2

schema

28

2

table

103

3

table

142

3

attribute

1235

3

attribute

1437

Filter Out Deleted Articles from an Objectset Field¶

Consider a scenario where the related_articles column is a custom field that stores references to articles within the bi_folder table.

The following SQL query retrieves the BI folder ID along with the associated article objects referenced in this field, excluding any entries that have been marked as deleted:

WITH bi_folder__articles AS (
   SELECT
     bi_folder_id,
             substring(object_key from '^(.*)_\d+$') AS object_type,
     substring(object_key from '_(\d+)$')::int AS object_id
   FROM bi_folder,
      unnest(related_articles) AS object_key
   WHERE object_key IS NOT NULL
             AND object_key <> ''
             AND object_key ~ '^.+_\d+$'
)
SELECT b.bi_folder_id, a.article_id
FROM bi_folder__articles b
LEFT JOIN article a
   ON b.object_type = 'article'
           AND b.object_id = a.article_id
           AND a.deleted = false
WHERE a.article_id IS NOT NULL
ORDER BY b.bi_folder_id;

Query Result

bi_folder_id

article_id

6

1022

7

1013

7

1014

Filter Out Deleted Documents or Folders from an Objectset Fields¶

Consider a scenario where the related_docs column is a custom field that stores references to both documents and folders within the bi_folder table.

The following SQL query retrieves the BI folder ID along with the associated document and folder objects referenced in this field, excluding any entries that have been marked as deleted:

WITH bi_folder__docs AS (
   SELECT
     bi_folder_id,
             substring(object_key from '^(.*)_\d+$') AS object_type,
     substring(object_key from '_(\d+)$')::int AS object_id
   FROM bi_folder,
      unnest(related_docs) AS object_key
   WHERE object_key IS NOT NULL
             AND object_key <> ''
             AND object_key ~ '^.+_\d+$'
)
SELECT b.bi_folder_id, b.object_type, b.object_id
FROM bi_folder__docs b
LEFT JOIN glossaries g
   ON b.object_type = 'glossary_v3'
           AND b.object_id = g.glossary_id
           AND g.deleted = false
LEFT JOIN terms t
   ON b.object_type = 'glossary_term'
           AND b.object_id = t.term_id
           AND t.deleted = false
WHERE g.glossary_id IS NOT NULL
             OR t.term_id IS NOT NULL
ORDER BY b.bi_folder_id;

Query Result

bi_folder_id

object_type

object_id

10

glossary_v3

100

10

glossary_term

1235

58

glossary_v3

101

58

glossary_v3

102

58

glossary_term

7110

Frequently Asked Questions (FAQs)¶

What happens when an ObjectSet field is deleted in Alation?¶

When a custom field is deleted in Alation, all associated values and their history are permanently removed from the system. Therefore, if an ObjectSet field is deleted, all references stored in that field are also deleted from the database.

Will deleted fields appear in Alation Analytics?¶

No. Custom fields with no associated values are automatically dropped from Alation Analytics and will not appear in the reporting schema.

What happens when an ObjectSet field is removed from a template in Alation?¶

When an ObjectSet field is removed from a template, the field and its values are no longer displayed in the catalog pages. However, the field values and their history are still retained in Alation. If the field is added back to the template later, the previously stored values will reappear in the catalog pages.

Will fields removed from a template appear in Alation Analytics?¶

Yes. Since the field values are not deleted from the catalog, any field that still has associated values will continue to appear in Alation Analytics.

Managing ObjectSet Custom Field in Alation Analytics¶

Adding ObjectSet Custom Fields in Alation¶

Getting ObjectSet Custom Fields from Alation Analytics¶

Querying ObjectSet Fields for an Object Type from Alation Analytics¶

Setting ObjectSet Custom Field Values in Alation¶

Fetching ObjectSet Field Values in Alation¶

Querying Referenced Objects in ObjectSet Custom Field from Alation Analytics¶

Deleting Objects Referenced in ObjectSet Custom Fields¶

Excluding Suspended Users in Alation Analytics While Querying ObjectSet Custom Fields¶

Sample Queries to Filter Deleted Objects from ObjectSet Custom Field Columns¶

Filter Out Suspended Users and Deleted Groups from an Objectset Field¶

Filter Out Deleted Business Policies from an Objectset Field¶

Filter Out Deleted RDBMS Objects from an Objectset Field¶

Filter Out Deleted Articles from an Objectset Field¶

Filter Out Deleted Documents or Folders from an Objectset Fields¶

Frequently Asked Questions (FAQs)¶

What happens when an ObjectSet field is deleted in Alation?¶

Will deleted fields appear in Alation Analytics?¶

What happens when an ObjectSet field is removed from a template in Alation?¶

Will fields removed from a template appear in Alation Analytics?¶

Alation User Documentation PDFs