Practice Notes: Query for DAC metadata

Time and Again there may a situation where one may need find the DAC tasks which populate particular target table be it a temporary table or staging table or the warehouse table. This information could easily be found from the DAC interface. But if we need to get this data from a TOAD/SQL developer, the following query would do the work.

select step.name as "DAC TASK",

tbl.name as "TARGET TABLE",

step.app_wid as "ETL LAYER",

step.priority

from w_etl_step_tbl step_tbl,

w_etl_step step,

w_etl_table tbl

where tbl.row_wid = step_tbl.table_wid

and step.row_wid = step_tbl.step_wid

and tbl.name = 'W_EMPLOYEE_D'

and step_tbl.type_cd = 'Target'

order by step.priority, step.name

Practice Notes: PER_ALL_ASSINGMENTS_F ASSINGMENT_TYPE

Probably not worth blogging. But don't have mind to just leave it after having searched for quite sometime.

The lookup type for the PER_ALL_ASSINGMENTS_F.ASSINGMENT_TYPE is ALL_ASGN_TYPE

So next time i can just look at my diary.

OBIEE : Beware of Session Variables

Blogging after a long time. It may be interesting to note some interesting outcomes in the usage of server variables. You may refer to the syntax of using different OBIEE variables here. We may note that the syntax for referring a session variables is

VALUEOF(NQ_SESSION.sess_var_name) -------- (1)

To refer a repository variable,

VALUEOF("rep_var_name") --------------- (2)

But what if you refer a session variable using syntax (2)?

I have taken two such session variables CURRENT_FSCL_YEAR and CURRENT_FSCL_PERIOD from the BI Apps RPD.

The incorrect way of using a session variable CURRENT_FSCL_YEAR:

The incorrect way of using a session variable CURRENT_FSCL_PERIOD:

The result is :

As everyone can see, it has fetched the default value of the session variable for the incorrect representations. Internally repository or session, they are server variables hence this behavior. Hence it may not be good idea to mix the syntax of both the variable types.