Querying Work Repository to identify SCD behaviour

The access to the Slowly Changing Dimension Behaviour is a bit uncomfortable in ODI 11g because it requires to go to each attribute (column) of the data store and then to the “Description” tab.

There is another option going through the background and querying the repository tables to get the behaviour of hundreds of columns in a second.

 

Query:

SELECT
  sm.mod_name AS MODEL_NAME,
  st.table_name    AS TABLE_NAME,
  sc.col_name      AS COLUMN_NAME,

  CASE
    WHEN sc.SCD_COL_TYPE IS NULL
    THEN 'Undefined'
    WHEN sc.SCD_COL_TYPE = 'OC'
    THEN 'Overwrite on Change'
    WHEN sc.SCD_COL_TYPE = 'SK'
    THEN 'Surrogate Key'
    WHEN sc.SCD_COL_TYPE = 'IR'
    THEN 'Add Row on Change'
    WHEN sc.SCD_COL_TYPE = 'NK'
    THEN 'Natural Key'
    WHEN sc.SCD_COL_TYPE = 'ST'
    THEN 'Starting Timestamp'
    WHEN sc.SCD_COL_TYPE = 'ET'
    THEN 'Ending Timestamp'
    WHEN sc.SCD_COL_TYPE = 'CR'
    THEN 'Current Record Flag'
    ELSE 'UNKNOWN'
  END             AS COLUMN_TYPE,

  sc.scd_col_type AS COLUMN_TYPE_CODE

FROM [ODI_WORK_REPOSITORY].snp_model sm,
 [ODI_WORK_REPOSITORY].snp_table st,
 [ODI_WORK_REPOSITORY].snp_col sc

WHERE st.i_mod   =sm.i_mod
AND st.i_table   =sc.i_table
AND sm.mod_name  ='[MODEL_NAME]'       -- Please specify model name
AND st.table_name='[TABLE_NAME]' -- Please specify table name

ORDER BY sc.POS;

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s