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
.svg)
.svg)