Exemples de requêtes de reporting pour IBM Knowledge Catalog

Retourner à la version anglaise de la documentation

Dernière mise à jour : 28 nov. 2024

Exemples de requêtes de reporting pour IBM Knowledge Catalog

Vérifiez les cas d'utilisation suivants pour la génération de rapports et les exemples de requêtes SQL pour créer les rapports.

Remarque :

Les requêtes sont applicables à toutes les bases de données prises en charge, sauf indication contraire.
Toutes les requêtes utilisent globalschema et zoneschema pour les noms de schéma. Vous devez les remplacer par le nom de schéma utilisé dans votre environnement:
- globalschema est le schéma par défaut choisi dans l'interface utilisateur.
- zoneschema est le schéma spécifique au catalogue, au projet ou à la catégorie.
globalschema et zoneschema peuvent être identiques si l'utilisateur le choisit dans l'interface utilisateur.

Requêtes de catalogue, de projet et d'actifs

Nombre d'actifs de catalogue regroupés par type

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

Obtenir le nombre de connexions créées depuis une heure spécifique

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

Obtenir le nombre d'actifs de catalogue regroupés par étiquette et par type d'actif

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

Répertorier les actifs de catalogue qui n'ont pas de description

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

Afficher la répartition des termes métier entre les actifs d'un catalogue

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'

Répertorier les actifs de catalogue auxquels aucun terme métier n'est affecté

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

Répertorier les actifs de catalogue avec un utilisateur particulier affecté en tant que collaborateur

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

Afficher la tendance des scores de qualité des données des actifs de catalogue

Pour 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

Pour 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

Requêtes d'artefacts de gouvernance

Agréger le nombre d'artefacts de gouvernance regroupés par type d'artefact

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

Répertorier les artefacts de gouvernance qui n'ont pas de description

SELECT
  *
FROM
  zoneschema.GOVERNANCE_ARTIFACTS
WHERE
  DESCRIPTION IS NULL

Afficher le pourcentage d'artefacts de gouvernance avec des intendants affectés

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

Répertorier les termes associés d'un terme métier particulier

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

Afficher la hiérarchie d'une catégorie de niveau supérieur

Pour 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

Pour 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

Obtenir le nombre de stratégies affectées à une règle de gouvernance particulière

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

Metadata import (MDI)

Afficher le nombre d'actifs reconnus

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

Afficher le nombre d'actifs qui ont été modifiés depuis la dernière exécution (afficher les informations utilisateur relatives à la création de MDI, à la mise à jour, à la réexécution manuelle)

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

Compter les nouvelles exécutions d'importation de métadonnées

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

Requêtes d'enrichissement de métadonnées (MDE)

Afficher l'enrichissement de métadonnées qu'un projet contient

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

Afficher l'association entre l'enrichissement de métadonnées et l'importation de métadonnées

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

Détailler les actifs qui ont été révisés

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

Détailler les actifs et les colonnes révisés

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

Vérifiez les termes affectés, suggérés ou rejetés au niveau de l'actif, ainsi que l'auteur du changement et le calendrier

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

Vérifiez les termes affectés, suggérés ou rejetés au niveau de la colonne avec l'auteur du changement et le calendrier

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

Vérifiez les classes de données affectées au niveau de la colonne, ainsi que l'auteur du changement et le délai

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 des actifs publiés à partir du projet dans les catalogues appropriés

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

Récupérer la métrique d'un profil pour un actif

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'

Récupération de l'historique des mesures du profil d'un bien

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'

Obtenir une table nouvellement ajoutée ou mise à jour avec le nom de la table et le nombre de colonnes ajoutées et mises à jour par l'exécution d'un travail MDI

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

Requêtes du tableau de bord Data Privacy

Extraire les actifs associés aux artefacts de gouvernance qui sont classifiés en tant que données personnelles, avec les règles de protection des données

-- 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

Sujet parent : Configuration de la création de rapports pour IBM Knowledge Catalog