Skip to main content

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:

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

Each of these views is available in three forms:

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

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

Session variableDescription
dataspot.languageThe ISO code of the language
Default: only the results in the default language are returned
dataspot.tenantsA list of UUIDs of the tenants by which to filter the results
Default: all tenants
dataspot.versionThe 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 and Best Practices​

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;
-- Query for responsibilities for systems

select sv.label, sv.title, av.family_name, av.given_name, av.email from dataspot.attributedagent_view aav
join dataspot.system_view sv on aav.resource_id = sv.id
join dataspot.agent_view av on aav.agent_id = av.id where av._type='Person'
-- Query for responsibilities for a specific asset
-- Returns the responsible persons who are responsible for an asset in the respective role. This includes not only directly assigned responsibilities, but also hierarchically inherited responsibilities. Posts are resolved and the respective person is displayed.

select r.label as Rolle, concat(p.given_name, ' ', p.family_name) as Person
from attributedasset_view a, person_view p, role_view r
where a.id = 'f0f9a6b6-241f-3419-88b4-f57fec5a41aa' -- UUID of the affected asset
and a.attributed_to = p.id
and a.attributed_as = r.id
-- Query for all business objects in a specific collection

with recursive collections (collection_id) as
(select id from dataspot.collection_view
where id = 'a0877ee3-096f-36ec-96c0-f6bd65e901a0' -- UUID of the collection to be evaluated
union
select id from dataspot.collection_view
join collections
on in_collection = collections.collection_id)
select * from dataspot.classifier_view
join collections
on in_collection = collections.collection_id
-- Query for all DQIs of a specific model (identifiable by UUID), including the persons responsible in a role (posts are ignored or resolved)

select a.label as DQI, c.label as Rolle, concat (d.given_name, ' ', d.family_name) as Person
from asset_view a, attributedasset_view b, agent_view c, agent_view d
where a._type = 'Indicator'
and a.model_id = 'd8e01570-18b1-3c03-9ab7-840e33dc6d2e' -- UUID of the model to be evaluated
and a.id = b.id
and b.attributed_as = c.id
and b.attributed_to = d.id
and d._type = 'Person'
order by a.label
-- Query for all assets that currently use a tag

select htv.name, htv.resource_id, av.label, av._type from dataspot.hastag_view htv
join dataspot.asset_view av on htv.resource_id = av.id
-- Query for all persons responsible for a business object (including inherited responsibilities and resolved posts/groups)

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;
-- Query for all vacant posts (the post is currently not held by any person)

select id as UUID, label as Postname from post_view p
where not exists (select 1 from holdspost_view h where h.holds_post = p.id)
-- Query for grouped statistics of the latest changes starting from a specific comparison date

select modified_by as changed_by, _type as Asset_Type, count(*) from asset_view
where lower(version_range)::timestamp > to_timestamp('2024-08-06', 'YYYY-MM-DD') -- Comparison date
group by modified_by, _type;
-- Query for all changes starting from a specific comparison date

select * from asset_view
where lower(version_range)::timestamp > to_timestamp('2024-08-06', 'YYYY-MM-DD') -- Comparison date
and modified_by = 'lwagner' -- User identifier / User ID
-- Query for all assets with an empty description

select id, _type, description, label
from asset_view
where description ISNULL
-- Historische Abfrage eines Assets inklusive Label und Title
with v as (
select distinct resource_id, lower(version_range) as version
from dataspot.asset_get_versions('b440668b-f772-3053-a5f4-5950844f1af5'))
select v.version, a.label, a.title
from v
join lateral dataspot.asset_version_at(v.resource_id, v.version) as a on true
order by v.version desc
-- Historische Abfrage von custom properties
with v as (
select distinct resource_id, lower(version_range) as version
from dataspot.asset_get_versions('b440668b-f772-3053-a5f4-5950844f1af5'))
select v.version, c.name, c.value
from v
join lateral dataspot.customproperties_version_at(v.resource_id, v.version) as c on true
order by v.version desc
-- Historische Abfrage von Links
with v as (
select distinct resource_id, lower(version_range) as version
from dataspot.asset_get_versions('b440668b-f772-3053-a5f4-5950844f1af5'))
select v.version, l._predicate, l.target_id
from v
join lateral dataspot.link_version_at(v.resource_id, v.version) as l on true
order by v.version desc
-- Abfrage aller veröffentlichten Geschäftsobjekte inklusive der historischen Versionen
with ids as (
select id as asset_id
from classifier_view
where _type = 'BusinessObject'
and status = 'PUBLISHED'
),
v as (
select distinct ids.asset_id, gv.resource_id, lower(gv.version_range) as version
from ids
cross join lateral dataspot.asset_get_versions(ids.asset_id) as gv
)
select v.asset_id, v.version, a.label, a.title
from v
join lateral dataspot.asset_version_at(v.resource_id, v.version) as a on true
order by v.asset_id, v.version desc