The metadata that Informatica Power Center 8 retains in its repository can be exposed via OBIEE reports and dashboards. This metadata includes ETL execution statistics, which can provide valuable information to BI developers/Administrators and BI users alike. ETL execution metadata can expose performance bottlenecks, error-details, debugging information, and the last successful ETL load timestamp. The following steps demonstrate how to create an ETL Monitor Dashboard to expose Informatica metadata in OBIEE.
In this example, we will use the following four views from the Informatica schema:
- REP_SESS_LOG
- REP_SESS_TBL_LOG
- REP_SUBJECT
- REP_WFLOW_RUN
Steps to build the Informatica Metadata Reports using OBIEE:
Import the four views listed above into the OBIEE repository (RPD) and create the physical joins as shown below on SUBJECT_ID. Build the corresponding business model layer and presentation layer.
Now, you will be able to create ETL reports using OBIEE Answers. In this example, we have build reports to display the following information:
- Last ETL completion Time Stamp
- ETL Execution statistics
- Number of Errors in the latest ETL Execution
- Latest ETL Execution log (which will drill down to the individual interface log and also error detail
- Error log (Table level)
The dashboard screenshot below shows our implementation of these reports.
Last ETL completion Time Stamp: Data Current as of [MAX (REP_WFLOW_RUN.END_TIME)]: This report shows the latest ETL execution completion time stamp.
ETL Execution Statistics: This report shows processing time for the last n ETL executions. The graph is plotted by the date when ETL ran and the duration of each ETL run (in Minutes).
Number of Errors in the latest ETL Execution: This report gives the number of errors encountered in the last n ETL executions.
Last ETL Execution Log: This report shows the ETL execution log of the last ETL run. We have designed this report to include several drill-down options as shown below.
Error Log report:
We can also use Informatica Metadata tables for Impact Analysis & Security Analysis using the following views / tables:
Impact Analysis:
- REP_TASK_ATTR
- REP_SUBJECT
- REP_SESSION_INSTANCES
- REP_ALL_TASKS
- REP_WORKFLOWS
- OPB_MAPPING
- OPB_WIDGET_INST
Security Analysis:
- REP_VERSION_PROPS
- REP_USERS
- REP_SUBJECT
