Example: Reports for interpreting data metric results

Data rules are linked with information governance policies by means of the relationship chain 'Information Governance Rules-Implemented By' (visible in IBM® InfoSphere® Information Governance Catalog). No such linkage exists for data metrics. However, you can link operational data with glossary objects in various ways despite the lack of a dedicated relationship type or chain.

The following model fragment shows how data metrics can be represented in a reporting model. The fragment closely resembles the dimensional model for data rules. IAMETRICEXECUTIONHISTORY is the view that represents facts. This view is surrounded by dimensions that add design-time information, such as the name of the metric, the project it belongs to, and the related rules. The IAPROJECT view is linked to the IAMETRIC view in a snowflake manner. The view might also be added to a separate copy of IAMETRIC, making it a star schema.

You can use the linkage between data metrics and data rules to assign data metrics to information governance policies, information governance rules, terms, and categories. In this case, the RID of the data rule serves as the linkage between the dimensional model for data metrics and the model for data rules. The following query shows the average data metric results per information governance policy, based on the information governance policies that are associated with the data rules from which the metric is derived.

SELECT
   policy_dim.NAME Policy,
   datametric_dim.NAME Metric,
   AVG ( datametric_fact.METRICRESULT ) AvgMetricResult
   
   FROM IAVIEWS.IAMETRICEXECUTIONHISTORY datametric_fact
   INNER JOIN IAVIEWS.IAMETRIC datametric_dim
      ON datametric_dim.RID = datametric_fact.METRICRID
   INNER JOIN IAVIEWS.IAMETRICDEPENDENCY bridge0
      ON bridge0.METRICRID = datametric_dim.RID
   INNER JOIN IAVIEWS.IARULE datarule_dim
      ON datarule_dim.RID = bridge0.DEPENDSONRID
   INNER JOIN IGVIEWS.IGIMPLEMENTEDBYIARULE bridge1
      ON bridge1.IMPLEMENTATIONRULERID = datarule_dim.RID
   INNER JOIN IGVIEWS.IGBUSINESSRULE igrule_dim
      ON igrule_dim.RID = bridge1.BUSINESSRULERID
   INNER JOIN IGVIEWS.IGRULEREFBYCONTAINERPOLICY bridge2
      ON bridge2.RULERID = igrule_dim.RID
   INNER JOIN IGVIEWS.IGPOLICY policy_dim
      ON policy_dim.RID = bridge2.POLICYRID

   GROUP BY
      policy_dim.NAME, datametric_dim.NAME
   ORDER BY
      policy_dim.NAME

The results of this query might look like the data in the following table.

Alternatively, there are more direct ways to associate data metrics with categories. The following examples illustrate two options: using external asset linkage and using custom attributes.

The first option ties data metrics to terms in InfoSphere Information Governance Catalog based on an external asset of type 'Data Metric'. The linkage is done by entering the name of the associated data metric as the value of the external identifier property of an external asset added to the term. The following graphic shows how setting up this linkage might look in InfoSphere Information Governance Catalog.

The following model fragment reflects this linkage:

The following query returns the result of the most recent run for each metric assigned to the category 'Configuration Validation,':

SELECT
   category.NAME Category,
   term.NAME Term,
   metric.NAME Metric,
   metric_fact.METRICRESULT Result
   
   FROM IAVIEWS.IAMETRICEXECUTIONHISTORY metric_fact
   INNER JOIN IAVIEWS.IAMETRIC metric
      ON metric.RID = metric_fact.METRICRID
   INNER JOIN IGVIEWS.IGEXTERNALASSETREFERENCE extasset 
      ON extasset.EXTERNALIDENTIFIER = metric.NAME
   INNER JOIN IGVIEWS.IGBUSINESSTERM term
      ON extasset.BUSINESSCONCEPTRID = term.RID
   INNER JOIN IGVIEWS.IGBUSINESSCATEGORY category
      ON category.RID = term.OFCATEGORY
         
   INNER JOIN (
         SELECT
            METRICRID MAXRID,
            MAX( STARTTIME ) MAXTIME
            FROM IAVIEWS.IAMETRICEXECUTIONHISTORY
            GROUP BY METRICRID
      ) latest_metric_fact
      ON latest_metric_fact.MAXRID = metric_fact.METRICRID
     AND latest_metric_fact.MAXTIME = metric_fact.STARTTIME
      
   WHERE
      extasset.SUBTYPE = 'Data Metric' AND
      category.NAME = 'Configuration Validation'

The result from the query might look like the data in the following table.

The second option ties data metrics to terms in the catalog based on the Associated Metric custom attribute that is assigned at the level of terms. The custom attribute needs to be of the type 'Predefined Values' with a single value that represents the name of the metric. The following graphic shows how setting up this linkage might look in the catalog.

Instead of creating the linkage by entering the name of the associated data metric as the value of the external identifier property of an external asset added to the term, as in the first option, this linkage is done by entering the name of the associated data metric as the value of the 'Associated Metric' custom attribute of the term. The following model fragment reflects this linkage:

SELECT distinct
   category.NAME Category,
   metric.NAME Metric,
   metric_fact.STARTTIME Time,
   metric_fact.METRICRESULT Result
  
   FROM IAVIEWS.IAMETRICEXECUTIONHISTORY metric_fact
   INNER JOIN IAVIEWS.IAMETRIC metric
      ON metric.RID = metric_fact.METRICRID
   INNER JOIN CMVIEWS.COMMONCUSTOMATTRIBUTESTRINGVAL custattrval
      ON CONVERT(NVARCHAR(5), custattrval.VALUE) = CONVERT(NVARCHAR(5), metric.NAME)
   INNER JOIN CMVIEWS.COMMONCUSTOMATTRIBUTE custatt
      ON custatt.RID = custattrval.OFCUSTOMATTRIBUTERID
   INNER JOIN IGVIEWS.IGBUSINESSTERM term
      ON custattrval.OBJECTRID = term.RID
   INNER JOIN IGVIEWS.IGBUSINESSCATEGORY category
      ON category.RID = term.OFCATEGORY
     
   INNER JOIN (
         SELECT
            METRICRID MAXRID,
            MAX( STARTTIME ) MAXTIME
            FROM IAVIEWS.IAMETRICEXECUTIONHISTORY
            GROUP BY METRICRID
      ) latest_metric_fact
      ON latest_metric_fact.MAXRID = metric_fact.METRICRID
    AND latest_metric_fact.MAXTIME = metric_fact.STARTTIME
     
   WHERE
      custatt.NAME = 'Associated Metric'
      category.NAME = 'Configuration Validation'

The results of this query might look like the data in the following table.

The previous two queries mainly serve illustrative purposes. The approach might be valuable because it provides direct linkage between metrics and glossary objects and works similarly with policies. However, a limitation is that the linkage is done based on names rather than IDs, which means the values must be entered manually into the glossary and are visible to glossary users. An RID does not help a glossary user to understand which metrics are related to a term at issue.