With the introduction of Analytics Marketplace in v7.1.0, discovery and search capabilities across multiple data catalog platforms have been significantly expanded.
You can search for database table assets without leaving the Metric Insights UI and without recreating them as objects in Metric Insights. Global Search will return the assets with the list of the available metadata and links to proceed to the asset in the data catalog tool.
The process consists of the following steps:
- Create a Dataset to retrieve all Table Assets metadata into Metric Insights.
- Create a Derived Dataset from the Tables Dataset to generate JSON objects for metadata fields available in the data catalog tool and include any additional custom fields specific to your environment.
NOTE: The Columns field is already formatted in JSON. - Add the Dataset to Search and map the Dataset Columns.
- Add Dataset Columns for Search — include the fields from Step 2 so they are indexed and displayed in the search results for each object, as shown in the final screen.
PREREQUISITES:
- Established connectivity with Collibra.
1. Create a Dataset from the Tables Object
For more details on how to create Datasets, refer to Understanding Datasets.
For more details on how to create a Dataset from Collibra, refer to Create Dataset from Collibra.
- Create a Dataset from the Tables object under All Data Assets.
- Note the Dataset ID to be used in Step 2.
2. Create a Derived Dataset from the Tables Dataset
- Data Source: select the Data Source used as Data Storage when creating the Dataset from the Collibra Tables object in Step 1.
- Query: the query is intended to:
- Create content_type to match MI Content Types (found under Admin > Plugins > Content Types)
- Create the "type" column to use as the Object Type Column
- Transform standard Collibra fields, such as Status, Created By, Last Modified By, etc., into JSON objects.
- Transform user-added fields into JSON objects.
- This step will ensure that metadata is searchable and appears in search properly formatted.
- The query must include the ID of the Dataset [
datasetID] created from the Collibra Tables object in "FROM dataset_datasetID".
- Make sure the Dataset Column Length for "columns" is set high enough. This may be necessary if your table contains many columns or very detailed descriptions.
SELECT display_name
, IFNULL(description, description_from_source_system) as "description"
, asset_url
, domain
, if(connection_type = "Power BI", "PowerBI", connection_type) as content_type
, if(type like "%Table", "Table", type) as type
, tags__field_
, columns
, JSON_OBJECT('Status', status ) AS "Status"
, JSON_OBJECT('Created By',created_by
, 'Created On',created_on
) AS "Created"
, JSON_OBJECT('Last Modified By',last_modified_by
, 'Last Modified On', last_modified_on
) AS "Last Modified"
, JSON_OBJECT('Owner', owner
, 'Data Custodian', data_custodian
, 'Community Manager', community_manager
) AS Owners
FROM dataset_XXX
Acceptable JSON Formats:
{"Pii Data": ""}
{"Data Steward": "Clyde, Curt", "Business Owner": "Clint", "Technical Owner": "Kyle"}
[{"label":"Catalog","value":"Gifts"}]
[{"name":"Product Type","description":"High level grouping of our products."}]
NOTE: [v7.2.2] Only roles assigned directly to an asset are collected. Roles inherited from its domain or community are not. A role that no table has directly gets no column, e.g., community_manager. Remove such columns from the query.
4. Add Dataset to Search
Access Admin > System > Search Setup
- Under Datasets to include in Search, [+Add Dataset].
- Dataset & View: choose the Dataset created in Step Create a Derived Dataset from the Tables Dataset.
- [Save]
5. Map Dataset Columns
Use the following mapping example with Allow click through to external catalog enabled.
| Field | Dataset Column |
|---|---|
| Title Column | display_name |
| Description Column | description |
| External Catalog Type | Collibra |
| External Catalog Asset URL | asset_url |
| Content Type Column | content_type |
| Object Type Column | type |
| Tags Column | tags__field_ |
For more details on configuring search across Datasets and Access, refer to Configure Search Across Specific Dataset (v7.1.0+).
6. Add Dataset Columns for Search
- Under Dataset Columns for Search, [+Add Dataset Columns].
- Dataset Column: choose a custom field column from the Dataset. This will be one of the JSON formatted columns.
- Column data is formatted in JSON: enable. Must be enabled for all added columns, if multiple.
- Icon: optionally, select an icon.
- [Save] and add as many additional Columns for Search as needed.
- In our example, we added: columns, Last Modified, Owners, Status, and Created.
- The order can be updated in the UI. This also sets the order in which these fields are shown in the search results.
- [Back to Search Setup] and [Update All Search Indexes] at the upper right.