ETL Assurance Dashboards for Informatica

Informatica Power Center 9.1 metadata can be exposed to OBIEE reports and dashboards. This metadata includes ETL execution statistics, which can provide valuable information to BI developers/Administrators and BI users alike.  Execution metadata of the ETL is quite useful not only from a monitoring perspective, but the data can be used and analyzed for a number of purposes like expose performance bottlenecks, debugging information and keep track of changes to source systems. For one of my recent projects I developed a set of monitoring dashboards and this post describes the approach. 

The following steps demonstrate how to create an ETL Audit Log Dashboard to expose Informatica metadata in OBIEE.

The Key metadata tables that are used –

• REP_SESS_LOG: View stores all the Session log information. Total Errors, Successful Rows, Failed Rows etc are few measures reported from this view.
• REP_SESS_TBL_LOG: Stores session log at Table Level.
• REP_SUBJECT: Master table which stores Subject Area information (Folders).
• REP_WFLOW_RUN: This view holds workflow Level execution metadata.

Above tables are being populated into Datawarehouse on regular intervals for OBIEE metadata modelling with necessary date lookups.

CM_DATE_D is the Datawarehouse date dimension.

An additional measure and an attribute created in metadata ETL:

Time Taken(in Secs):   Calculates the time taken for every session execution. Below is the SQL calculation used for all 3 facts.

DateDiff(minute,actual_start ,SESSION_TIMESTAMP)

Run Status Desc: This is an attribute to replace the Description of the workflow/session status

case when RUN_STATUS_CODE=1 then'Succeeded'

when RUN_STATUS_CODE=2 then 'Disabled'

when RUN_STATUS_CODE=3 then 'Failed'

when RUN_STATUS_CODE=4 then 'Stopped'

when RUN_STATUS_CODE=5 then 'Aborted'

when RUN_STATUS_CODE=6 then 'Running' else 'Unknown' end RUN_STATUS_DESC,

 Below is the logical model of the OBIEE metadata, all workflow attributes are created as part of degenerated workflow dimension.

Below is Workflow Status LTS scenarios and measures.

Below is the presentation subject area by folders.

Now we can createETL reports using OBIEE Answers. In the below dashboard, we have covered below scenarios:

• Last ETL completion Time Stamp and Status( Total Errors)
• Current month load status by day.
• Time taken for last 10 loads. (Bar Chart).
• ETL time breakdown by workflows (for most recent load).
• Longest run mappings in Last load

Apart from above detailed drilldown reports showing Error Log, Table Level Log are created for detailed investigation of load failures.