Maschinell übersetztes Thema Sie können zur englischen Version wechseln.

Die genaueste und aktuellste Version dieser Dokumentation finden Sie in der englischen (ursprünglichen) Version. IBM haftet nicht für Schäden oder Verluste, die durch die Verwendung automatisch (maschinell) übersetzter Inhalte entstehen.

Beispielhafte Berichtsabfragen für IBM Knowledge Catalog

Letzte Aktualisierung: 28. Nov. 2024

Überprüfen Sie die folgenden Anwendungsfälle für die Berichterstellung und die SQL-Beispielabfragen, um die Berichte zu erstellen:

Hinweis:

Sofern nicht anders angegeben, gelten die Abfragen für alle unterstützten Datenbanken.
Alle Abfragen verwenden globalschema und zoneschema für Schemanamen. Sie müssen sie durch den Schemanamen ersetzen, der in Ihrer Umgebung verwendet wird:
- globalschema ist das in der Benutzerschnittstelle ausgewählte Standardschema.
- zoneschema ist das für den Katalog, das Projekt oder die Kategorie spezifische Schema.
globalschema und zoneschema können identisch sein, wenn der Benutzer dies in der Benutzerschnittstelle auswählt.

Katalog-, Projekt-und Assetabfragen

Anzahl der Katalogassets gruppiert nach Typ

SELECT
  ASSET_TYPE,
  COUNT(*) AS TOTAL
FROM
  zoneschema.CONTAINER_ASSETS
GROUP BY
  ASSET_TYPE

Anzahl der seit einem bestimmten Zeitpunkt erstellten Verbindungen abrufen

SELECT
  CONTAINER_TYPE,
  COUNT(*)
FROM
  zoneschema.CONTAINER_ASSETS
WHERE
  ASSET_TYPE = 'connection'
  AND CREATED_ON >= TO_TIMESTAMP('2022-01-01', 'YYYY-MM-DD')
GROUP BY
  CONTAINER_TYPE

Anzahl der Katalogassets abrufen, die nach Tag und Assettyp gruppiert sind

SELECT
  TAG_NAME,
  ASSET_TYPE,
  COUNT(*) TOTAL
FROM
  zoneschema.ASSET_TAGS ASSET_TAGS,
  zoneschema.CONTAINER_ASSETS CONTAINER_ASSETS
WHERE
  ASSET_TAGS.ASSET_ID = CONTAINER_ASSETS.ASSET_ID
GROUP BY
  TAG_NAME,
  ASSET_TYPE

Katalogassets auflisten, die keine Beschreibung haben

SELECT
  *
FROM
  zoneschema.CONTAINER_ASSETS
WHERE
  DESCRIPTION IS NULL
  OR DESCRIPTION = ''
  AND CONTAINER_TYPE = 'catalog'

Verteilung von Geschäftsbegriffen auf Assets in einem Katalog anzeigen

SELECT
  GA.NAME,
  AGGR_TABLE.TOTAL
FROM
  zoneschema.GOVERNANCE_ARTIFACTS GA
  LEFT OUTER JOIN (
    SELECT
      ASSOCIATED_ARTIFACT_ID,
      COUNT(*) AS TOTAL
    FROM
      zoneschema.GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS
    WHERE
      ASSOCIATED_ARTIFACT_TYPE = 'glossary_term'
    GROUP BY
      ASSOCIATED_ARTIFACT_ID
  ) AGGR_TABLE ON GA.ARTIFACT_ID = AGGR_TABLE.ASSOCIATED_ARTIFACT_ID
WHERE
  GA.ARTIFACT_TYPE = 'glossary_term'

Katalogassets ohne zugeordnete Geschäftsbegriffe auflisten

SELECT
  HEADER,
  COUNT(*) AS TOTAL
FROM
  (
    SELECT
      CASE WHEN GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS.ASSOCIATED_ARTIFACT_ID IS NULL THEN 'NO TERMS' ELSE 'HAS ATLEAST ONE TERM' END AS HEADER
    FROM
      zoneschema.CONTAINERS CONTAINERS,
      zoneschema.CONTAINER_ASSETS CONTAINER_ASSETS
      LEFT OUTER JOIN zoneschema.GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS ON CONTAINER_ASSETS.ASSET_ID = GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS.ASSET_ID
      AND GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS.ASSOCIATED_ARTIFACT_TYPE = 'glossary_term'
    WHERE
      CONTAINERS.CONTAINER_ID = CONTAINER_ASSETS.CONTAINER_ID
      AND CONTAINERS.CONTAINER_TYPE = 'catalog'
  ) TEMP_TABLE
GROUP BY
  HEADER

Katalogassets mit einem bestimmten Benutzer auflisten, der als Mitarbeiter zugeordnet ist

SELECT
  CONTAINER_ASSETS.CONTAINER_ID,
  CONTAINER_ASSETS.ASSET_ID,
  CONTAINER_ASSETS.NAME,
  CONTAINER_ASSETS.ASSET_TYPE
FROM
  zoneschema.ASSET_COLLABORATORS ASSET_COLLABORATORS,
  zoneschema.CONTAINER_ASSETS CONTAINER_ASSETS
WHERE
  USER_ID = 'IBMid-50DW59BCGD'
  AND CONTAINER_ASSETS.CONTAINER_TYPE = 'catalog'
  AND CONTAINER_ASSETS.ASSET_ID = ASSET_COLLABORATORS.ASSET_ID
  AND CONTAINER_ASSETS.CONTAINER_ID = ASSET_COLLABORATORS.CONTAINER_ID

Trend der Datenqualitätsscores für Katalogassets anzeigen

Für Db2:

SELECT
  CA.NAME AS ASSET_NAME,
  TEMP.TECH_START AS UPDATE_TIME,
  TEMP.TECH_START,
  TEMP.ASSET_ID,
  TEMP.TECH_END,
  QUALITY_SCORE
FROM
  zoneschema.CONTAINER_ASSETS AS CA,
  (
    SELECT
      CONTAINER_DATA_ASSETS.ASSET_ID,
      CONTAINER_DATA_ASSETS.QUALITY_SCORE,
      CONTAINER_DATA_ASSETS.TECH_START,
      CONTAINER_DATA_ASSETS.TECH_END
    FROM
      zoneschema.CONTAINER_DATA_ASSETS FOR SYSTEM_TIME
    FROM
      '2021-08-19-00.00.00.000000000000' TO '9999-12-30-00.00.00.000000000000'
    WHERE
      CONTAINER_DATA_ASSETS.ASSET_ID = '85a89a9c-8fd3-4306-8931-cd15c41ae1d4'
      AND QUALITY_SCORE IS NOT NULL
  ) AS TEMP
WHERE
  TEMP.ASSET_ID = CA.ASSET_ID

Für PostgreSQL:

select
  CA.NAME as ASSET_NAME,
  TEMP.TECH_START as UPDATE_TIME,
  TEMP.TECH_START,
  TEMP.ASSET_ID,
  TEMP.TECH_END,
  TEMP.QUALITY_SCORE
from
  zoneschema.CONTAINER_ASSETS CA,
  (
    select
      CONTAINER_DATA_ASSETS.ASSET_ID,
      CONTAINER_DATA_ASSETS.QUALITY_SCORE,
      CONTAINER_DATA_ASSETS.TECH_START,
      CONTAINER_DATA_ASSETS.TECH_END
    from
      zoneschema.CONTAINER_DATA_ASSETS
    where
      CONTAINER_DATA_ASSETS.ASSET_ID = '130ab82a-c3a4-4fbc-880b-51bc49517ebe'
      and CONTAINER_DATA_ASSETS.QUALITY_SCORE is not null
    union
    select
      HIST_CONTAINER_DATA_ASSETS.ASSET_ID,
      HIST_CONTAINER_DATA_ASSETS.QUALITY_SCORE,
      HIST_CONTAINER_DATA_ASSETS.TECH_START,
      HIST_CONTAINER_DATA_ASSETS.TECH_END
    from
      zoneschema.HIST_CONTAINER_DATA_ASSETS
    where
      HIST_CONTAINER_DATA_ASSETS.ASSET_ID = '130ab82a-c3a4-4fbc-880b-51bc49517ebe'
      and HIST_CONTAINER_DATA_ASSETS.QUALITY_SCORE is not null
  ) temp
where
  TEMP.ASSET_ID = CA.ASSET_ID

Governance-Artefaktabfragen

Anzahl der nach Artefakttyp gruppierten Governance-Artefakte aggregieren

SELECT
  ARTIFACT_TYPE,
  COUNT(*) TOTAL
FROM
  zoneschema.GOVERNANCE_ARTIFACTS
GROUP BY
  ARTIFACT_TYPE

Governance-Artefakte auflisten, die keine Beschreibung haben

SELECT
  *
FROM
  zoneschema.GOVERNANCE_ARTIFACTS
WHERE
  DESCRIPTION IS NULL

Prozentsatz der Governance-Artefakte mit zugewiesenen Stewards anzeigen

SELECT
  COUNT(*) AS TOTAL_COUNT,
  ARTIFACT_WITH_STEWARD_COUNT
FROM
  zoneschema.GOVERNANCE_ARTIFACTS
  LEFT OUTER JOIN (
    SELECT
      COUNT(*) AS ARTIFACT_WITH_STEWARD_COUNT
    FROM
      zoneschema.GOVERNANCE_ARTIFACTS GOVERNANCE_ARTIFACTS
      LEFT OUTER JOIN zoneschema.GOVERNANCE_ARTIFACT_STEWARDS GOVERNANCE_ARTIFACT_STEWARDS ON GOVERNANCE_ARTIFACTS.ARTIFACT_ID = GOVERNANCE_ARTIFACT_STEWARDS.ARTIFACT_ID
    WHERE
      GOVERNANCE_ARTIFACT_STEWARDS.USER_ID IS NOT NULL
  ) TEMP_TABLE ON 1 = 1
GROUP BY
  ARTIFACT_WITH_STEWARD_COUNT

Zugehörige Begriffe eines bestimmten Geschäftsbegriffs auflisten

SELECT
  TEMPTABLE.NAME AS END1_NAME,
  RELATIONSHIP_TYPE,
  SGA.NAME AS END2_NAME
FROM
  (
    SELECT
      GA.NAME,
      BTA.END2_ARTIFACT_ID,
      BTA.RELATIONSHIP_TYPE
    FROM
      zoneschema.BUSINESS_TERM_ASSOCIATIONS BTA
      LEFT OUTER JOIN zoneschema.GOVERNANCE_ARTIFACTS GA ON GA.ARTIFACT_ID = BTA.END1_ARTIFACT_ID
    ORDER BY
      BTA.END1_ARTIFACT_ID
  ) TEMPTABLE
  LEFT OUTER JOIN zoneschema.GOVERNANCE_ARTIFACTS SGA ON TEMPTABLE.END2_ARTIFACT_ID = SGA.ARTIFACT_ID

Hierarchie für eine Kategorie der höchsten Ebene anzeigen

Für Db2:

WITH RPL (
  END1_CATEGORY_ID, END2_CATEGORY_ID,
  DEPTH
) AS (
  SELECT
    ROOT.END1_CATEGORY_ID,
    ROOT.END2_CATEGORY_ID,
    0
  FROM
    zoneschema.CATEGORY_ASSOCIATIONS ROOT
  WHERE
    ROOT.END1_CATEGORY_ID = 'b11e64a2-5466-4c60-bfc5-121c7a80703d'
  UNION ALL
  SELECT
    CHILD.END1_CATEGORY_ID,
    CHILD.END2_CATEGORY_ID,
    PARENT.DEPTH + 1
  FROM
    RPL PARENT,
    zoneschema.CATEGORY_ASSOCIATIONS CHILD
  WHERE
    PARENT.END2_CATEGORY_ID = CHILD.END1_CATEGORY_ID
    AND DEPTH < 1000
)
SELECT
  TEMP.DEPTH,
  TEMP.NAME AS END1_NAME,
  CATEGORIES.NAME AS END2_NAME,
  TEMP.END1_CATEGORY_ID AS END1,
  TEMP.END2_CATEGORY_ID AS END2
FROM
  (
    SELECT
      END1_CATEGORY_ID,
      NAME,
      END2_CATEGORY_ID,
      DEPTH
    FROM
      RPL
      LEFT OUTER JOIN zoneschema.CATEGORIES CATEGORIES ON RPL.END1_CATEGORY_ID = CATEGORIES.CATEGORY_ID
  ) TEMP
  LEFT OUTER JOIN zoneschema.CATEGORIES CATEGORIES ON CATEGORIES.CATEGORY_ID = TEMP.END2_CATEGORY_ID
ORDER BY
  DEPTH

Für PostgreSQL:

WITH RECURSIVE RPL (
  END1_CATEGORY_ID, END2_CATEGORY_ID,
  DEPTH
) AS (
  SELECT
    ROOT.END1_CATEGORY_ID,
    ROOT.END2_CATEGORY_ID,
    0
  FROM
    zoneschema.CATEGORY_ASSOCIATIONS ROOT
  WHERE
    ROOT.END1_CATEGORY_ID = 'b11e64a2-5466-4c60-bfc5-121c7a80703d'
  UNION ALL
  SELECT
    CHILD.END1_CATEGORY_ID,
    CHILD.END2_CATEGORY_ID,
    PARENT.DEPTH + 1
  FROM
    RPL PARENT,
    zoneschema.CATEGORY_ASSOCIATIONS CHILD
  WHERE
    PARENT.END2_CATEGORY_ID = CHILD.END1_CATEGORY_ID
    AND DEPTH < 1000
)
SELECT
  TEMP.DEPTH,
  TEMP.NAME AS END1_NAME,
  CATEGORIES.NAME AS END2_NAME,
  TEMP.END1_CATEGORY_ID AS END1,
  TEMP.END2_CATEGORY_ID AS END2
FROM
  (
    SELECT
      END1_CATEGORY_ID,
      NAME,
      END2_CATEGORY_ID,
      DEPTH
    FROM
      RPL
      LEFT OUTER JOIN zoneschema.CATEGORIES CATEGORIES ON RPL.END1_CATEGORY_ID = CATEGORIES.CATEGORY_ID
  ) TEMP
  LEFT OUTER JOIN zoneschema.CATEGORIES CATEGORIES ON CATEGORIES.CATEGORY_ID = TEMP.END2_CATEGORY_ID
ORDER BY
  DEPTH

Anzahl der Richtlinien abrufen, die einer bestimmten Governance-Regel zugeordnet sind

SELECT
  ENFORCEMENT_RULES.NAME,
  COUNT
FROM
  (
    SELECT
      ENFORCEMENT_RULES.RULE_ID AS RULE_ID,
      COUNT(
        ARTIFACT_ENFORCEMENT_RULE_ASSOCIATIONS.ARTIFACT_ID
      ) AS COUNT
    FROM
      globalschema.ENFORCEMENT_RULES ENFORCEMENT_RULES
      LEFT OUTER JOIN zoneschema.ARTIFACT_ENFORCEMENT_RULE_ASSOCIATIONS ARTIFACT_ENFORCEMENT_RULE_ASSOCIATIONS ON ENFORCEMENT_RULES.RULE_ID = ARTIFACT_ENFORCEMENT_RULE_ASSOCIATIONS.RULE_ID
    GROUP BY
      ENFORCEMENT_RULES.RULE_ID
  ) TEMPTABLE
  LEFT OUTER JOIN globalschema.ENFORCEMENT_RULES ENFORCEMENT_RULES ON ENFORCEMENT_RULES.RULE_ID = TEMPTABLE.RULE_ID

MDI-Abfragen ( Metadata import )

Anzahl der erkannten Assets anzeigen

SELECT
  MDI.METADATA_IMPORT_NAME MDI_NAME,
  ASSET_DETAIL.NAME DISCOVERED_ASSET
FROM
  zoneschema.METADATA_IMPORTS MDI
  INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDI_ASSETS ON MDI_ASSETS.METADATA_IMPORT_ID = MDI.METADATA_IMPORT_ID
  INNER JOIN zoneschema.CONTAINER_ASSETS ASSET_DETAIL ON ASSET_DETAIL.ASSET_ID = MDI_ASSETS.ASSET_ID
  AND ASSET_DETAIL.CONTAINER_ID = MDI_ASSETS.CONTAINER_ID

Anzahl der Assets anzeigen, die seit der letzten Ausführung geändert wurden (Benutzerinformationen zu MDI anzeigen-Erstellung, Aktualisierung, manuelle erneute Ausführung)

SELECT
    MDI.METADATA_IMPORT_NAME AS MDI_NAME, U2.USER_NAME AS MDI_OWNER,
    MDI_DETAILS.CREATED_ON AS MDI_CREATED_ON,
    U.USER_NAME INVOKED_BY, E.IS_SCHEDULED_RUN, E.START_TIME, E.END_TIME,
    E.COUNT_NEW_ASSETS, E.COUNT_UPDATED_ASSETS, E.COUNT_DELETED_ASSETS
FROM
    METADATA_IMPORTS MDI
    INNER JOIN CONTAINER_ASSETS MDI_DETAILS
       ON MDI_DETAILS.ASSET_ID = MDI.METADATA_IMPORT_ID AND MDI_DETAILS.CONTAINER_ID = MDI.CONTAINER_ID
    LEFT JOIN METADATA_IMPORT_EXECUTIONS E
       ON E.METADATA_IMPORT_ID = MDI.METADATA_IMPORT_ID
    LEFT JOIN USER_PROFILES U ON U.USER_ID = E.INVOKED_BY
    LEFT JOIN USER_PROFILES U2 ON U2.USER_ID = MDI_DETAILS.OWNER
ORDER BY START_TIME DESC

Neue Metadatenimportausführungen zählen

SELECT
  MDI.METADATA_IMPORT_NAME AS MDI_NAME,
  COUNT(E.JOB_RUN_ID) AS NUM_JOBS
FROM
  zoneschema.METADATA_IMPORTS MDI
  LEFT OUTER JOIN zoneschema.METADATA_IMPORT_EXECUTIONS E ON E.METADATA_IMPORT_ID = MDI.METADATA_IMPORT_ID
WHERE
  E.START_TIME >= TO_TIMESTAMP('2022-01-01', 'YYYY-MM-DD')
GROUP BY
  MDI.METADATA_IMPORT_NAME

Abfragen zur Metadatenaufbereitung (MDE)

Anzeigen, welche Metadatenanreicherung in einem Projekt enthalten ist

SELECT
  PROJECTS.NAME PROJECT,
  MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
  U.USER_NAME AS MDE_OWNER,
  MDE_DETAILS.CREATED_ON AS MDE_CREATED_ON
FROM
  zoneschema.METADATA_ENRICHMENTS MDE
  INNER JOIN zoneschema.CONTAINER_ASSETS MDE_DETAILS ON MDE_DETAILS.ASSET_ID = MDE.METADATA_ENRICHMENT_ID
  AND MDE_DETAILS.CONTAINER_ID = MDE.CONTAINER_ID
  INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
  LEFT JOIN globalschema.USER_PROFILES U ON U.USER_ID = MDE_DETAILS.OWNER

Zuordnung zwischen Metadatenaufbereitung und Metadatenimport anzeigen

SELECT
  PROJECTS.NAME PROJECT,
  MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
  MDI.METADATA_IMPORT_NAME AS MDI_NAME
FROM
  zoneschema.METADATA_ENRICHMENTS MDE
  INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
  LEFT JOIN zoneschema.METADATA_IMPORTS MDI ON MDI.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
  AND MDI.CONTAINER_ID = MDE.CONTAINER_ID

Details zu den Assets, die geprüft wurden

SELECT
  PROJECTS.NAME PROJECT,
  MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
  MDE_ASSET_DETAILS.NAME ASSET_NAME,
  MDE_ASSETS.REVIEWED_ON
FROM
  zoneschema.METADATA_ENRICHMENTS MDE
  INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
  INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDE_ASSETS ON MDE_ASSETS.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
  INNER JOIN zoneschema.CONTAINER_ASSETS MDE_ASSET_DETAILS ON MDE_ASSET_DETAILS.ASSET_ID = MDE_ASSETS.ASSET_ID
  AND MDE_ASSET_DETAILS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID

Details zu den geprüften Assets und Spalten

SELECT
  PROJECTS.NAME PROJECT,
  MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
  MDE_ASSET_DETAILS.NAME ASSET_NAME,
  MDE_ASSETS.REVIEWED_ON,
  MDE_ASSET_COLUMNS.NAME COLUMN_NAME,
  MDE_ASSET_COLUMNS.REVIEWED_ON COL_REVIEWED_ON
FROM
  zoneschema.METADATA_ENRICHMENTS MDE
  INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
  INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDE_ASSETS ON MDE_ASSETS.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
  INNER JOIN zoneschema.CONTAINER_ASSETS MDE_ASSET_DETAILS ON MDE_ASSET_DETAILS.ASSET_ID = MDE_ASSETS.ASSET_ID
  AND MDE_ASSET_DETAILS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
  INNER JOIN zoneschema.CONTAINER_DATA_ASSET_COLUMNS MDE_ASSET_COLUMNS ON MDE_ASSET_COLUMNS.ASSET_ID = MDE_ASSETS.ASSET_ID
  AND MDE_ASSET_COLUMNS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
ORDER BY
  PROJECT,
  MDE_NAME,
  ASSET_NAME

Überprüfen Sie die zugewiesenen, vorgeschlagenen oder zurückgewiesenen Begriffe auf Assetebene zusammen mit dem Änderungsautor und dem Zeitpunkt der Änderung.

SELECT
  PROJECTS.NAME PROJECT,
  MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
  MDE_ASSET_DETAILS.NAME ASSET_NAME,
  MDE_ASSETS.REVIEWED_ON,
  TERM_DETAILS.NAME TERM_NAME,
  TERMS.ASSIGNMENT_STATE TERM_STATE,
  TERMS.ASSIGNMENT_DATE,
  U.USER_NAME ASSIGNED_BY,
  TERMS.CONFIDENCE
FROM
  zoneschema.METADATA_ENRICHMENTS MDE
  INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
  INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDE_ASSETS ON MDE_ASSETS.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
  INNER JOIN zoneschema.CONTAINER_ASSETS MDE_ASSET_DETAILS ON MDE_ASSET_DETAILS.ASSET_ID = MDE_ASSETS.ASSET_ID
  AND MDE_ASSET_DETAILS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
  LEFT OUTER JOIN zoneschema.GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS TERMS ON TERMS.ASSET_ID = MDE_ASSETS.ASSET_ID
  AND TERMS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
  AND UPPER(TERMS.ASSOCIATED_ARTIFACT_TYPE) = 'GLOSSARY_TERM'
  LEFT OUTER JOIN zoneschema.GOVERNANCE_ARTIFACTS TERM_DETAILS ON TERM_DETAILS.ARTIFACT_ID = TERMS.ASSOCIATED_ARTIFACT_ID
  LEFT JOIN globalschema.USER_PROFILES U ON U.USER_ID = TERMS.ASSIGNED_BY
ORDER BY
  PROJECT,
  MDE_NAME,
  ASSET_NAME

Überprüfen Sie die zugewiesenen, vorgeschlagenen oder zurückgewiesenen Begriffe auf Spaltenebene zusammen mit dem Änderungsautor und der Ablaufsteuerung.

SELECT
  PROJECTS.NAME PROJECT,
  MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
  MDE_ASSET_DETAILS.NAME ASSET_NAME,
  MDE_ASSETS.REVIEWED_ON,
  MDE_ASSET_COLUMNS.NAME COLUMN_NAME,
  MDE_ASSET_COLUMNS.REVIEWED_ON COL_REVIEWED_OM,
  COL_TERM_DETAILS.NAME COL_TERM_NAME,
  COL_TERMS.ASSIGNMENT_STATE COL_TERM_STATE,
  COL_TERMS.ASSIGNMENT_DATE COL_TERM_ASSIGNMENT_DATE,
  U.USER_NAME COL_TERM_ASSIGNED_BY,
  COL_TERMS.CONFIDENCE COL_TERM_CONFIDENCE
FROM
  zoneschema.METADATA_ENRICHMENTS MDE
  INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
  INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDE_ASSETS ON MDE_ASSETS.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
  INNER JOIN zoneschema.CONTAINER_ASSETS MDE_ASSET_DETAILS ON MDE_ASSET_DETAILS.ASSET_ID = MDE_ASSETS.ASSET_ID
  AND MDE_ASSET_DETAILS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
  INNER JOIN zoneschema.CONTAINER_DATA_ASSET_COLUMNS MDE_ASSET_COLUMNS ON MDE_ASSET_COLUMNS.ASSET_ID = MDE_ASSETS.ASSET_ID
  AND MDE_ASSET_COLUMNS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
  LEFT JOIN zoneschema.DATA_ASSET_COLUMN_ARTIFACT_ASSOCIATIONS COL_TERMS ON COL_TERMS.NAME = MDE_ASSET_COLUMNS.NAME
  AND COL_TERMS.ASSET_ID = MDE_ASSET_COLUMNS.ASSET_ID
  AND COL_TERMS.CONTAINER_ID = MDE_ASSET_COLUMNS.CONTAINER_ID
  AND UPPER(
    COL_TERMS.ASSOCIATED_ARTIFACT_TYPE
  ) = 'GLOSSARY_TERM'
  LEFT JOIN zoneschema.GOVERNANCE_ARTIFACTS COL_TERM_DETAILS ON COL_TERM_DETAILS.ARTIFACT_ID = COL_TERMS.ASSOCIATED_ARTIFACT_ID
  LEFT JOIN globalschema.USER_PROFILES U ON U.USER_ID = COL_TERMS.ASSIGNED_BY
ORDER BY
  PROJECT,
  MDE_NAME,
  ASSET_NAME,
  COLUMN_NAME

Überprüfen Sie die zugeordneten Datenklassen auf Spaltenebene zusammen mit dem Änderungsautor und der Ablaufsteuerung.

SELECT
  PROJECTS.NAME PROJECT,
  MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
  MDE_ASSET_DETAILS.NAME ASSET_NAME,
  MDE_ASSETS.REVIEWED_ON,
  MDE_ASSET_COLUMNS.NAME COLUMN_NAME,
  MDE_ASSET_COLUMNS.REVIEWED_ON COL_REVIEWED_OM,
  COL_DC_DETAILS.NAME COL_DC_NAME,
  COL_DC.ASSIGNMENT_STATE COL_DC_STATE,
  COL_DC.ASSIGNMENT_DATE COL_DC_ASSIGNMENT_DATE,
  U.USER_NAME COL_DC_ASSIGNED_BY,
  COL_DC.CONFIDENCE COL_DC_CONFIDENCE
FROM
  zoneschema.METADATA_ENRICHMENTS MDE
  INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
  INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDE_ASSETS ON MDE_ASSETS.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
  INNER JOIN zoneschema.CONTAINER_ASSETS MDE_ASSET_DETAILS ON MDE_ASSET_DETAILS.ASSET_ID = MDE_ASSETS.ASSET_ID
  AND MDE_ASSET_DETAILS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
  INNER JOIN zoneschema.CONTAINER_DATA_ASSET_COLUMNS MDE_ASSET_COLUMNS ON MDE_ASSET_COLUMNS.ASSET_ID = MDE_ASSETS.ASSET_ID
  AND MDE_ASSET_COLUMNS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
  LEFT JOIN zoneschema.DATA_ASSET_COLUMN_ARTIFACT_ASSOCIATIONS COL_DC ON COL_DC.NAME = MDE_ASSET_COLUMNS.NAME
  AND COL_DC.ASSET_ID = MDE_ASSET_COLUMNS.ASSET_ID
  AND COL_DC.CONTAINER_ID = MDE_ASSET_COLUMNS.CONTAINER_ID
  AND UPPER(
    COL_DC.ASSOCIATED_ARTIFACT_TYPE
  ) = 'DATA_CLASS'
  LEFT JOIN zoneschema.GOVERNANCE_ARTIFACTS COL_DC_DETAILS ON COL_DC_DETAILS.ARTIFACT_ID = COL_DC.ASSOCIATED_ARTIFACT_ID
  LEFT JOIN globalschema.USER_PROFILES U ON U.USER_ID = COL_DC.ASSIGNED_BY
ORDER BY
  PROJECT,
  MDE_NAME,
  ASSET_NAME,
  COLUMN_NAME

Liste der Assets, die aus dem Projekt in den relevanten Katalogen veröffentlicht wurden

SELECT
  PROJECTS.NAME PROJECT,
  MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
  MDE_ASSET_DETAILS.NAME ASSET_NAME,
  MDE_ASSET_DETAILS.ASSET_TYPE,
  CATALOG.NAME PUBLISHED_TO_CATALOG
FROM
  zoneschema.METADATA_ENRICHMENTS MDE
  INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
  INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDE_ASSETS ON MDE_ASSETS.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
  INNER JOIN zoneschema.CONTAINER_ASSETS MDE_ASSET_DETAILS ON MDE_ASSET_DETAILS.ASSET_ID = MDE_ASSETS.ASSET_ID
  AND MDE_ASSET_DETAILS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
  LEFT JOIN zoneschema.CONTAINERS CATALOG ON CATALOG.CONTAINER_ID = MDE_ASSETS.PUBLISHED_TO_CONTAINER_ID

Abrufen von Profilmetriken für ein Asset

SELECT 
  c.name AS container_name, 
  ca.name AS asset_name, 
  cda.quality_score AS table_quality_score, 
  cdac.name AS column_name, 
  cdac.quality_score AS column_quality_score, 
  cdac.native_data_type, 
  cdac.inferred_data_type, 
  cda.number_of_records AS total_records, 
  cda.num_rows_analysed AS analysed_records, 
  cdac.unique_count, 
  cdac.null_count, 
  cdac.empty_count, 
  cdac.distinct_count, 
  cdac.std_deviation, 
  cdac.mean, 
  cdac.is_nullable, 
  cdac.tech_start AS dt 
FROM 
  zoneschema.container_assets ca 
  JOIN zoneschema.containers c ON ca.container_id = c.container_id 
  JOIN zoneschema.container_data_assets cda ON ca.asset_id = cda.asset_id 
  AND ca.container_id = cda.container_id 
  JOIN zoneschema.container_data_asset_columns cdac ON cdac.asset_id = cda.asset_id 
  AND cdac.container_id = cda.container_id 
WHERE 
  cda.asset_id = '41fe47a8-faf4-4eab-a7a7-05567e2c1557'

Abrufen historischer Profilmetriken für ein Asset

SELECT 
  c.name AS container_name, 
  ca.name AS asset_name, 
  cda.quality_score AS table_quality_score, 
  cdac.name AS column_name, 
  cdac.quality_score AS column_quality_score, 
  cdac.native_data_type, 
  cdac.inferred_data_type, 
  cda.number_of_records AS total_records, 
  cda.num_rows_analysed AS analysed_records, 
  cdac.unique_count, 
  cdac.null_count, 
  cdac.empty_count, 
  cdac.distinct_count, 
  cdac.std_deviation, 
  cdac.mean, 
  cdac.is_nullable, 
  cdac.tech_start AS dt 
FROM 
  zoneschema.hist_container_assets ca 
  JOIN zoneschema.containers c ON ca.container_id = c.container_id 
  JOIN zoneschema.hist_container_data_assets cda ON ca.asset_id = cda.asset_id 
  AND ca.container_id = cda.container_id 
  JOIN zoneschema.hist_container_data_asset_columns cdac ON cdac.asset_id = cda.asset_id 
  AND cdac.container_id = cda.container_id 
WHERE 
  cda.asset_id = '41fe47a8-faf4-4eab-a7a7-05567e2c1557'

Abrufen einer neu hinzugefügten oder aktualisierten Tabelle mit Tabellenname und Anzahl der durch einen MDI-Auftragslauf hinzugefügten und aktualisierten Spalten

select 
  container_name, 
  asset_name, 
  mdi_job_run_id, 
  mdi_job_action, 
  count(column_name) as columns 
FROM 
  (
    SELECT 
      c.name as container_name, 
      b.name as asset_name, 
      a.mdi_job_run_id, 
      a.mdi_job_action, 
      d.name as column_name 
    FROM 
      zoneschema.container_data_assets a 
      JOIN zoneschema.container_assets b ON b.asset_id = a.asset_id 
      AND b.container_id = a.container_id 
      JOIN zoneschema.containers c ON c.container_id = b.container_id 
      JOIN zoneschema.container_data_asset_columns d ON d.container_id = a.container_id 
      AND d.asset_id = a.asset_id 
      and d.mdi_job_run_id = a.mdi_job_run_id 
    UNION 
    SELECT 
      c.name as container_name, 
      b.name as asset_name, 
      a.mdi_job_run_id, 
      a.mdi_job_action, 
      hd.name as column_name 
    FROM 
      zoneschema.container_data_assets a 
      JOIN zoneschema.container_assets b ON b.asset_id = a.asset_id 
      AND b.container_id = a.container_id 
      JOIN zoneschema.containers c ON c.container_id = b.container_id 
      JOIN zoneschema.hist_container_data_asset_columns hd ON hd.container_id = a.container_id 
      AND hd.asset_id = a.asset_id 
      and hd.mdi_job_run_id = a.mdi_job_run_id 
    UNION 
    SELECT 
      c.name as container_name, 
      b.name as asset_name, 
      a.mdi_job_run_id, 
      a.mdi_job_action, 
      d.name as column_name 
    FROM 
      zoneschema.hist_container_data_assets a 
      JOIN zoneschema.container_assets b ON b.asset_id = a.asset_id 
      AND b.container_id = a.container_id 
      JOIN zoneschema.containers c ON c.container_id = b.container_id 
      JOIN zoneschema.container_data_asset_columns d ON d.container_id = a.container_id 
      AND d.asset_id = a.asset_id 
      and d.mdi_job_run_id = a.mdi_job_run_id 
    UNION 
    SELECT 
      c.name as container_name, 
      b.name as asset_name, 
      a.mdi_job_run_id, 
      a.mdi_job_action, 
      hd.name as column_name 
    FROM 
      zoneschema.hist_container_data_assets a 
      JOIN zoneschema.container_assets b ON b.asset_id = a.asset_id 
      AND b.container_id = a.container_id 
      JOIN zoneschema.containers c ON c.container_id = b.container_id 
      JOIN zoneschema.hist_container_data_asset_columns hd ON hd.container_id = a.container_id 
      AND hd.asset_id = a.asset_id 
      and hd.mdi_job_run_id = a.mdi_job_run_id
  ) mdi_assset 
where 
  mdi_job_run_id = 'df48943f-61fe-409a-b78b-72cd1b7875d2' 
group by 
  container_name, 
  asset_name, 
  mdi_job_run_id, 
  mdi_job_action

Abfragen des Dashboards Data Privacy

Assets abrufen, die Governance-Artefakten zugeordnet sind, die als personenbezogene Daten klassifiziert sind, zusammen mit Datenschutzregeln

-- Join from data assets in a container to associated governance artifacts (glossary terms and data classes),
-- then from governance artifacts to classifications to determine items that are classified as Personal Data.
-- This query also retrieves data protection rules that are associated with the governance artifacts.
SELECT
  c.NAME container_name,
  c.CONTAINER_TYPE container_type,
  tab.name table_name,
  col.name col_name,
  gov.name gov_artifact_name,
  gov.artifact_type,
  col_artifact_xref.assignment_state,
  classif.name classification,
  er.NAME rule_name,
  er.ACTION_NAME rule_action
FROM
  zoneschema.CONTAINERS c
  INNER JOIN zoneschema.container_assets tab ON c.CONTAINER_ID = tab.CONTAINER_ID
  INNER JOIN zoneschema.container_data_asset_columns col ON col.asset_id = tab.asset_id
  AND col.container_id = tab.container_id
  INNER JOIN zoneschema.data_asset_column_artifact_associations col_artifact_xref ON col_artifact_xref.asset_id = col.asset_id
  AND col_artifact_xref.container_id = col.container_id
  AND col_artifact_xref.name = col.name
  INNER JOIN zoneschema.governance_artifacts gov ON col_artifact_xref.associated_artifact_id = gov.artifact_id
  LEFT OUTER JOIN zoneschema.governance_artifact_associations gov_gov_xref ON (
    gov.artifact_id = gov_gov_xref.end2_artifact_id
    AND GOV_GOV_XREF.END2_ARTIFACT_TYPE = 'glossary_term'
    AND GOV_GOV_XREF.END1_ARTIFACT_TYPE = 'classification'
  )
  OR (
    gov.artifact_id = gov_gov_xref.end1_artifact_id
    AND GOV_GOV_XREF.END1_ARTIFACT_TYPE = 'data_class'
    AND GOV_GOV_XREF.END2_ARTIFACT_TYPE = 'classification'
  )
  LEFT OUTER JOIN zoneschema.governance_artifacts classif ON (
    classif.artifact_id = gov_gov_xref.end1_artifact_id
    AND classif.artifact_type = 'classification'
    AND classif.name LIKE '%Personal%'
  )
  OR (
    classif.artifact_id = gov_gov_xref.end2_artifact_id
    AND classif.artifact_type = 'classification'
    AND classif.name LIKE '%Personal%'
  )
  LEFT OUTER JOIN zoneschema.ARTIFACT_ENFORCEMENT_RULE_ASSOCIATIONS aera ON aera.ARTIFACT_ID = gov.ARTIFACT_ID
  LEFT OUTER JOIN globalschema.ENFORCEMENT_RULES er ON ER.RULE_ID = aera.RULE_ID