Metadata SQL views

A database-level integration layer in the form of SQL views (PostgreSQL) is provided, allowing metadata to be queried and analyzed.

Note

In the following examples, the server https://www.myserver.com is used for illustration purposes (e.g. in the path https://www.myserver.com/web/admin) and must be replaced accordingly in all actual requests. Depending on the configuration, the URL may also have to be extended by the context path (e.g. https://www.myserver.com/dataspot/web/admin).

The physical design of the dataspot data model is strongly influenced by the following requirements:

Persistence of metadata objects in an object-oriented inheritance hierarchy
Relationships between metadata objects at different levels in the object hierarchy
Complete versioning of metadata objects (including public and draft versions)
Content in multiple languages
Support for multiple tenants

The metadata SQL views can be used by third-party tools (e.g. business intelligence software) to query and evaluate metadata from dataspot. However, the metadata SQL views are not suitable for modifying the metadata objects. For this purpose, the metadata REST/API can be used.

Overview

The views can be divided into the following three categories:

Asset views (e.g. attribute)
Link views (e.g. derivedfrom)
Other views (e.g. customproperties, timeseries)

Each of these views is available in three forms:

Latest view (e.g. attribute_view)
Public view (e.g. attribute_public_view)
Version view (e.g. attribute_version_view)

The result set of these views can be affected by the following session variables in PostgreSQL:

Session variable	Description
`dataspot.language`	The ISO code of the language Default: only the results in the default language are returned
`dataspot.tenants`	A list of UUIDs of the tenants by which to filter the results Default: all tenants
`dataspot.version`	The timestamp at which metadata should be queried (only relevant for version view) Default: current timestamp

Note

The views are fully documented. When using an appropriate SQL query tool, both the descriptions of the views and the descriptions of their columns are available.

Metamodel

The SQL queries are based on the metamodel. The metamodel documents all properties and relationships of metadata objects and provides the foundation for all metadata SQL views. The metamodel is available in the database meta at the following URL:

https://www.myserver.com/web/meta

Note

The database meta can be created in the admin console (https://www.myserver.com/web/admin).

Examples

The best way to illustrate the use of the metadata SQL views is by example. Therefore, SQL statements to query and evaluate metadata are given below as examples for typical use cases.

The examples assume that search_path in PostgreSQL was set to the schema dataspot as follows:

SET search_path TO dataspot;

Alternatively, the name of the view can be explicitly qualified with the prefix dataspot..

SQL statements

-- Number of business objects

SELECT COUNT(*)
FROM classifier_view
WHERE _type = 'BusinessObject';

-- Published attributes

SELECT classifier.label, attribute.label
FROM attribute_public_view attribute
  JOIN classifier_public_view classifier
ON classifier.id = attribute.has_domain;

-- Attributes that are components of datasets

SELECT dataset.label, asset.label
FROM asset_view asset
  JOIN composedof_view composedof
  ON composedof.composed_of = asset.id
  JOIN dataset_view dataset
  ON composedof.resource_id = dataset.id
WHERE asset._type = 'BusinessAttribute';

-- Attributes in a specific model

SELECT attribute.label
FROM attribute_view attribute
  JOIN scheme_view scheme
  ON scheme.id = attribute.model_id
WHERE scheme.label = 'Fachdatenmodell';

-- Assets with a specific property

SELECT asset._type, asset.label
FROM asset_view asset
  JOIN customproperties_view customproperties
  ON customproperties.resource_id = asset.id
  AND customproperties.name = 'securityClass'
  AND customproperties.value = '"SK0"';

-- Full-text search for assets

SELECT asset._type, asset.label, asset.title, asset.description
FROM asset_view asset
  JOIN fulltext_view fulltext
  ON fulltext.resource_id = asset.id
  JOIN TO_TSQUERY('Person') query
  ON fulltext.tokens @@ query;

-- Time series for reference values of reference objects

SELECT timeseries.valid_from, timeseries.valid_to, timeseries.code, timeseries.short_text, timeseries.long_text
FROM timeseries_view timeseries
  JOIN enumeration_view enumeration
  ON enumeration.label = 'Land'
  JOIN literal_view literal
  ON literal.literal_of = enumeration.id
  AND literal.id = timeseries.resource_id;

-- Assets (including subordinates) for which a person has a role

WITH RECURSIVE results AS (
  SELECT asset.id, asset._type, asset.model_id, asset.label
  FROM asset_view asset
    JOIN attributedto_view attributedto
    ON attributedto.resource_id = asset.id
    JOIN post_view post
    ON post.id = attributedto.attributed_to
    JOIN role_view role
    ON role.id = post.has_role
    AND role.label = 'Data Steward'
    JOIN holdspost_view holdspost
    ON holdspost.holds_post = post.id
    JOIN person_view person
    ON person.family_name = 'Kainz'
    AND person.given_name = 'Barbara'
    AND holdspost.resource_id = person.id
  UNION SELECT asset.id, asset._type, asset.model_id, asset.label
  FROM asset_view asset
    JOIN results
    ON results.id = asset.parent_id)
SELECT scheme.label, results._type, results.label
FROM results
  JOIN scheme_view scheme
  ON scheme.id = results.model_id;

-- All business objects together with the persons (including their role),
-- who are responsible for the business object (or for a superordinate asset)

WITH recursive parent (id, parent_id, resource_id) AS (
  SELECT id, parent_id, id
  FROM dataspot.asset_view
  UNION
    SELECT parent.id, asset.parent_id, asset.id
  FROM dataspot.asset_view asset
    JOIN parent
    ON parent.parent_id = asset.id)
SELECT asset.id, dataspot.asset_to_natural_key(asset.id) AS asset_label,
       attribution.attributed_as, dataspot.asset_to_natural_key(attribution.attributed_as) AS attributed_as_label,
       attribution.attributed_to, dataspot.asset_to_natural_key(attribution.attributed_to) AS attributed_to_label,
       attribution.agent_id, dataspot.asset_to_natural_key(attribution.agent_id) as person_label
FROM dataspot.asset_view asset
  JOIN parent
  ON parent.id = asset.id
  JOIN dataspot.attributedagent_view attribution
  ON attribution.resource_id = parent.resource_id
  AND EXISTS (SELECT FROM dataspot.person_view person WHERE person.id = attribution.agent_id)
WHERE asset._type = 'BusinessObject'
  ORDER by asset_label, attributed_as_label, attributed_to_label, person_label;

-- Attributes at a specific point in time (i.e. version)

SET dataspot.version = '2021-11-11T11:11:11';

SELECT classifier.label, attribute.label
FROM attribute_version_view attribute
  JOIN classifier_version_view classifier
  ON classifier.id = attribute.has_domain;

-- List of all tenants

SELECT id, tenant_name FROM tenant;

-- Attributes of one or more tenants

SET dataspot.tenants = '{"e7a30eac-5b94-4567-a0c7-f99d0d712884", "a782c41a-4aeb-49ea-905e-27c517621457"}';

SELECT classifier.label, attribute.label
FROM attribute_view attribute
  JOIN classifier_view classifier
  ON classifier.id = attribute.has_domain;

Overview​

Metamodel​

Examples​

SQL statements​

Overview

Metamodel

Examples

SQL statements