Pentaho Repository Queries

User Info

SELECT LOGIN, NAME, DESCRIPTION, ENABLED FROM R_USER

Job Info

SELECT NAME, DESCRIPTION, JOB_VERSION, JOB_STATUS, CREATED_USER, CREATED_DATE, MODIFIED_USER, MODIFIED_DATE FROM R_JOB

Transformation Info

SELECT NAME, DESCRIPTION, TRANS_VERSION, TRANS_STATUS, CREATED_USER, CREATED_DATE, MODIFIED_USER, MODIFIED_DATE FROM R_TRANSFORMATION

Step Info

SELECT
S.NAME STEP_NAME,
S.DESCRIPTION,
ST.DESCRIPTION AS STEP_TYPE,
T.NAME AS TRANS_NAME
FROM
R_STEP S,
R_STEP_TYPE ST,
R_TRANSFORMATION T
WHERE
    S.ID_STEP_TYPE = ST.ID_STEP_TYPE

AND S.ID_TRANSFORMATION = T.ID_TRANSFORMATION


Job Entry Info

SELECT
E.NAME JOB_ENTRY_NAME,
E.DESCRIPTION,
JT.DESCRIPTION AS ENTRY_TYPE,
J.NAME AS JOB_NAME
FROM
R_JOBENTRY E,
R_JOBENTRY_TYPE JT,
R_JOB J
WHERE
    E.ID_JOBENTRY_TYPE = JT.ID_JOBENTRY_TYPE
AND E.ID_JOB = J.ID_JOB


DB Connection Info

SELECT
T.DESCRIPTION DATABASE_TYPE,
D.NAME NAME,
D.HOST_NAME,
D.DATABASE_NAME,
D.PORT,
D.USERNAME
FROM
R_DATABASE D,
R_DATABASE_TYPE T
WHERE

    D.ID_DATABASE_TYPE = T.ID_DATABASE_TYPE

Dependencies

Check for all transformations using given table, say PRODUCT_MASTER

SELECT
S.NAME STEP_NAME,
S.DESCRIPTION,
ST.DESCRIPTION AS STEP_TYPE,
T.NAME AS TRANS_NAME,
SA.VALUE_STR
FROM
R_STEP S,
R_STEP_TYPE ST,
R_TRANSFORMATION T,
R_STEP_ATTRIBUTE SA
WHERE
    S.ID_STEP_TYPE = ST.ID_STEP_TYPE
AND S.ID_TRANSFORMATION = T.ID_TRANSFORMATION
AND SA.ID_TRANSFORMATION = T.ID_TRANSFORMATION
AND SA.ID_STEP = S.ID_STEP
AND SA.CODE = 'sql'
and SA.VALUE_STR like '%PRODUCT_MASTER%'