Leveraging Metadata in Informatica Workflow-Session/Analysis

We can leverage the metadata collected in the Informatica repository for many interesting analysis, few of the scenarios where I have leveraged the Informatica Metadata are as following.

This SQL Queries can be executed in Oracle database with no changes and requires little modification with other databases.

Failed Sessions

The following query lists the failed sessions. To make it work for the last ‘n’ days, replace SYSDATE-1 with SYSDATE – n

QUERY:

SELECT SUBJECT_AREA AS FOLDER_NAME,

SESSION_NAME,

LAST_ERROR AS ERROR_MESSAGE,

DECODE (RUN_STATUS_CODE,3,’Failed’,4,’Stopped’,5,’Aborted’) AS STATUS,

ACTUAL_START AS START_TIME,

SESSION_TIMESTAMP

FROM REP_SESS_LOG

WHERE RUN_STATUS_CODE != 1

AND TRUNC(ACTUAL_START) BETWEEN TRUNC(SYSDATE -1) AND TRUNC(SYSDATE)

RESULT:

Long running Sessions

The following query lists long running sessions. To make it work for the last ‘n’ days, replace SYSDATE-1 with SYSDATE – n

QUERY:

SELECT SUBJECT_AREA AS FOLDER_NAME,

SESSION_NAME,

SUCCESSFUL_SOURCE_ROWS AS SOURCE_ROWS,

SUCCESSFUL_ROWS AS TARGET_ROWS,

ACTUAL_START AS START_TIME,

SESSION_TIMESTAMP

FROM REP_SESS_LOG

WHERE RUN_STATUS_CODE = 1

AND TRUNC(ACTUAL_START) BETWEEN TRUNC(SYSDATE -1) AND TRUNC(SYSDATE)

AND (SESSION_TIMESTAMP – ACTUAL_START) > (10/(24*60))

ORDER BY SESSION_TIMESTAMP

RESULT:

Invalid Tasks

The following query lists folder names and task name, version number, and last saved for all invalid tasks.

QUERY:

SELECT SUBJECT_AREA AS FOLDER_NAME,

DECODE(IS_REUSABLE,1,’Reusable’,’ ‘) || ‘ ‘ ||TASK_TYPE_NAME AS TASK_TYPE,

TASK_NAME AS OBJECT_NAME,

VERSION_NUMBER,

LAST_SAVED

FROM REP_ALL_TASKS

WHERE IS_VALID=0

AND IS_ENABLED=1

ORDER BY SUBJECT_AREA,TASK_NAME

RESULT:

Thanks for reading, do you have other scenarios where Workflow Metadata has been effective …wish you a very happy new year 2011.

To know more about Informatica Workflow