Ejemplos de consultas de informes para IBM Knowledge Catalog

Volver a la versión inglesa de la documentación

Última actualización: 28 nov 2024

Ejemplos de consultas de informes para IBM Knowledge Catalog

Consulte los casos de uso siguientes para generar informes y las consultas SQL de ejemplo para crear los informes.

Nota:

Las consultas son aplicables para todas las bases de datos soportadas a menos que se especifique lo contrario.
Todas las consultas utilizan globalschema y zoneschema para los nombres de esquema. Debe sustituirlos por el nombre de esquema utilizado en el entorno:
- globalschema es el esquema predeterminado elegido en la interfaz de usuario.
- zoneschema es el esquema específico del catálogo, proyecto o categoría.
globalschema y zoneschema pueden ser los mismos si el usuario lo elige en la interfaz de usuario.

Consultas de catálogo, proyecto y activos

Número de activos de catálogo agrupados por tipo

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

Obtener el número de conexiones creadas desde la hora específica

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

Obtener el número de activos de catálogo agrupados por etiqueta y tipo de activo

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

Listar los activos de catálogo que no tienen una descripción

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

Mostrar la distribución de términos empresariales entre activos en un catálogo

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'

Listar los activos de catálogo sin términos empresariales asignados

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

Listar los activos de catálogo con un usuario determinado asignado como colaborador

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

Mostrar la tendencia de puntuaciones de calidad de datos de activos de catálogo

Para 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

Para 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

Consultas de artefactos de gobernabilidad

Agregar el número de artefactos de gobernabilidad agrupados por tipo de artefacto

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

Listar artefactos de gobernabilidad que no tienen una descripción

SELECT
  *
FROM
  zoneschema.GOVERNANCE_ARTIFACTS
WHERE
  DESCRIPTION IS NULL

Mostrar el porcentaje de artefactos de gobernabilidad con representantes asignados

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

Listar los términos relacionados de un determinado término de negocio

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

Mostrar la jerarquía de una categoría de nivel superior

Para 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

Para 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

Obtener el número de políticas asignadas a una regla de gobernabilidad determinada

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)

Mostrar el número de activos descubiertos

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

Mostrar el número de activos que se han cambiado desde la última ejecución (Mostrar información de usuario relacionada con MDI-creación, actualización, reejecución manual)

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

Contar las nuevas ejecuciones de importación de metadatos

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

Consultas de enriquecimiento de metadatos (MDE)

Mostrar qué enriquecimiento de metadatos contiene un proyecto

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

Mostrar la asociación entre el enriquecimiento de metadatos y la importación de metadatos

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

Detallar los activos que se han revisado

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

Detallar los activos y columnas revisados

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

Comprobar los términos asignados, sugeridos o rechazados a nivel de activo junto con el autor del cambio y la temporización

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

Comprobar los términos asignados, sugeridos o rechazados a nivel de columna junto con el autor del cambio y la temporización

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

Comprobar las clases de datos asignadas a nivel de columna junto con el autor del cambio y la temporización

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

Lista de activos publicados desde el proyecto a los catálogos relevantes

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

Recuperar la métrica del perfil de un activo

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'

Recuperar la métrica histórica del perfil de un activo

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'

Obtener la tabla recién añadida o actualizada con el nombre de la tabla y el recuento de columnas añadidas y actualizadas por la ejecución de un trabajo 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

Consultas del panel de control Data Privacy

Recuperar activos asociados con artefactos de gobernabilidad que se clasifican como Datos personales, junto con Reglas de protección de datos

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

Tema principal: Configuración de informes para IBM Knowledge Catalog