DAC Data Model

Problem: I am doing a BI Applications project for a customer where I am planning to spend 1 week onsite and work offsite for the other 3 weeks. I have an EBS instance running back at home, so I can always use that to do initial development. However, since I am only doing a single BI Application (Financial Analytics), I wanted to export the data for only the relevant tables and take it back with me.

Solution: The easiest way to find all the source tables for a set of BI Application tasks is to use the Datawarehouse Administration Console (DAC). However, using the UI would be too slow. So I decided to look at the tables and derive a query. It took me a while, but I figured out the joins within the DAC data model. Kudos to the Oracle Product Dev for keeping such a structured data model.

The query below returns all the tasks in a given exeuction plan, and their source and target tables. You can add on whatever other filters your need. I hope this saves others some time.

SELECT distinct
    ep.NAME EXEC_PLAN_NAME,
    eptask.priority EXEC_DEPTH,
    task.NAME TASK_NAME,
    tbl.NAME TABLE_NAME,
    tbltask.type_cd TABLE_TYPE,
    tbltask.sub_type_cd TABLE_SUB_TYPE,
    tbl.type_cd TABLE_FACT_DIM
  FROM w_etl_defn ep,
       w_etl_defn_step eptask,
       w_etl_step task,
       w_etl_step_tbl tbltask,
       w_etl_table tbl
 WHERE ep.row_wid = eptask.etl_defn_wid
   AND eptask.step_wid = task.row_wid
   AND task.row_wid = tbltask.step_wid
   AND tbltask.table_wid = tbl.row_wid
   AND ep.NAME = Execution Plan Name