This discussion is archived
5 Replies Latest reply: Nov 19, 2012 3:09 PM by nk9 RSS

DAC Repository database question

658640 Newbie
Currently Being Moderated
I am trying to write a query that will provide me a list of Subject Areas and their corresponding Tasks. The same list you would see in the "Tasks" tab in the DAC Client Design view for a specific Subject Area. This is in our test environment with the 7.9.5 OBI Apps.

I am not very familiar with the DAC database tables but through the help files, information I've found online, and a little bit of hacking I believe the tables I need are:

W_ETL_SA
W_ETL_STEP
W_ETL_SA_STEP

When executing the following query I do not get the full set of results.

SELECT
W_ETL_SA.name as SA_Name
,W_ETL_STEP.name as STEP_Name
FROM
W_ETL_SA
,W_ETL_STEP
,W_ETL_SA_STEP
WHERE
W_ETL_SA_STEP.SA_WID = W_ETL_SA.ROW_WID
AND W_ETL_SA_STEP.STEP_WID = W_ETL_STEP.ROW_WID

The W_ETL_SA_STEP table has 69 distinct SA_WID values but only 5 of them correspond to records in the W_ETL_SA table.

I've been able to run a similar query for Execution Plans and their corresponding Subject areas with the W_ETL_DEFN_SA, and a query for Execution Plans and their corresponding Tasks with W_ETL_DEFN_STEP.

Any ideas why this query does not work? Have I misunderstood the purpose of the W_ETL_SA_STEP table?


Thanks is advance

Tom
  • 1. Re: DAC Repository database question
    657970 Explorer
    Currently Being Moderated
    DACs repository structure is complicated, even though you have identified the major tables. The query is not simple as the data is partitioned based on containers, and the schema definitions are not documented anywhere.

    Why not use DAC's own flat views?

    Right click on the subject areas, choose flat views->Tasks
    The applet opens in a query mode.

    Either you can define some query criteria or simply hit the go button.

    The results can then be exported to a file by right clicking on any list and choosing output to file.

    Let me know if this helped.
  • 2. Re: DAC Repository database question
    658640 Newbie
    Currently Being Moderated
    Thanks for the suggestion. I'm still pretty new to OBIEE and wasn't aware of that feature.

    I am trying to develop a larger, more complex query, that was failing. I determined the issues was related to where I was trying to associate Subject Areas and Tasks.

    I discovered the W_ETL_DIFF_ENTY and W_ETL_DIFFMERGE tables yesterday. Using the data I found there I was able to write another query that appears to be working. It isn't using the W_ETL_SA_STEP table at all (still not sure its purpose). This is what I came up with.

    SELECT
         W_ETL_DEFN.NAME AS EXPLAN_NAME --Execution Plan
         ,W_ETL_SA.NAME AS SA_NAME --Subject Area
         ,W_ETL_STEP.NAME AS STEP_NAME --Task
    FROM
         W_ETL_DEFN_STEP
         ,W_ETL_DEFN
         ,W_ETL_STEP
         ,W_ETL_DEFN_SA
         ,W_ETL_SA
    WHERE
         W_ETL_DEFN_STEP.ETL_DEFN_WID = W_ETL_DEFN.ROW_WID
         AND W_ETL_DEFN_STEP.STEP_WID = W_ETL_STEP.ROW_WID
    /*****************************************************************/
    /* you can included this condition to limit the resutls to a specific execution plan */
    /* and/or subject area
    --     AND W_ETL_DEFN.NAME LIKE ...
    --ND W_ETL_SA.NAME LIKE 'Financials - %'  

    /*****************************************************************/
         AND W_ETL_DEFN_SA.ETL_DEFN_WID = W_ETL_DEFN.ROW_WID
         AND W_ETL_DEFN_SA.SA_WID = W_ETL_SA.ROW_WID
  • 3. Re: DAC Repository database question
    658640 Newbie
    Currently Being Moderated
    sorry, hit the "Post" button by accident.

    here is the query


    SELECT
         W_ETL_DEFN.NAME AS EXPLAN_NAME
         ,W_ETL_SA.NAME AS SA_NAME
         ,W_ETL_STEP.NAME AS STEP_NAME
    FROM
         W_ETL_DEFN_STEP
         ,W_ETL_DEFN
         ,W_ETL_STEP
         ,W_ETL_DEFN_SA
         ,W_ETL_SA
    WHERE
         W_ETL_DEFN_STEP.ETL_DEFN_WID = W_ETL_DEFN.ROW_WID
         AND W_ETL_DEFN_STEP.STEP_WID = W_ETL_STEP.ROW_WID
    /***************************************************************/
    --     AND W_ETL_DEFN.NAME LIKE
    --     AND W_ETL_SA.NAME LIKE
    /***************************************************************/
         AND W_ETL_DEFN_SA.ETL_DEFN_WID = W_ETL_DEFN.ROW_WID
         AND W_ETL_DEFN_SA.SA_WID = W_ETL_SA.ROW_WID
  • 4. Re: DAC Repository database question
    657970 Explorer
    Currently Being Moderated
    Ah, thats what you are trying to do. It wont work here is why!

    SA are just a collection of tasks.
    EP is a collection of SA(s) with subsequent ordering between the tasks.
    The list of tasks collected for an EP is a deduped list of tasks that belong to the SA(s) that are part of that EP. In other words if a task belongs to multiple Subject Areas, it will appear only once. And so the pedigree information as to which SA(s) enabled the task for the EP is kind of lost.

    You will be able to write a query about EPs/SAs
    You will be able to write a query about EPs/Tasks
    But you will not be able to write a query about EPs/SAs/Tasks - You will end up with a cartesian product.

    Diff Merge and Diff entity tables do show some relationships between the objects in DAC repository. But that alone is not enuf.

    Let me know what is that you are trying to accomplish... Will let u know how it can be done.
  • 5. Re: DAC Repository database question
    nk9 Newbie
    Currently Being Moderated
    ----Execution-----subject area----tasks--------------------

    SELECT
    EP.NAME , SA.NAME , STEP.NAME

    FROM
    W_ETL_DEFN EP,
    W_ETL_DEFN_SA EPSA,
    W_ETL_SA SA
    ,W_ETL_SA_STEP SASTEP
    ,W_ETL_STEP STEP
    ,W_ETL_DEFN_STEP EPSTEP

    WHERE
    EPSA.ETL_DEFN_WID = EP.ROW_WID
    AND EPSA.SA_WID = SA.ROW_WID
    AND SASTEP.STEP_WID = STEP.ROW_WID
    AND SASTEP.SA_WID = SA.ROW_WID
    AND EPSTEP.ETL_DEFN_WID = EP.ROW_WID
    AND EPSTEP.STEP_WID = STEP.ROW_WID
    AND EP.NAME = 'exec plan '
    AND SA.NAME = 'SUbject are a name '

    ;
    -------------------------------------

    ---execution plan and subject area
    SELECT
    EP.NAME , SA.NAME
    FROM
    W_ETL_DEFN EP,
    W_ETL_DEFN_SA EPSA,
    W_ETL_SA SA
    WHERE
    EPSA.ETL_DEFN_WID = EP.ROW_WID
    AND EPSA.SA_WID = SA.ROW_WID
    AND EP.NAME = 'type EP name '
    ;
    --------------------------------
    ----subject ara ----tasks
    SELECT
    SA.NAME , STEP.NAME
    FROM
    W_ETL_SA SA
    ,W_ETL_SA_STEP SASTEP
    ,W_ETL_STEP STEP
    WHERE
    SASTEP.STEP_WID = STEP.ROW_WID
    AND SASTEP.SA_WID = SA.ROW_WID
    AND SA.NAME = 'Type subject area'


    ;
    -----------exec plan --tasks

    SELECT
    EP.NAME,STEP.NAME
    FROM
    W_ETL_STEP STEP
    ,W_ETL_DEFN EP
    ,W_ETL_DEFN_STEP EPSTEP
    WHERE EPSTEP.ETL_DEFN_WID = EP.ROW_WID
    AND EPSTEP.STEP_WID = STEP.ROW_WID
    AND EP.NAME = 'type EP name here '


    But the question i have is
    iam finding few missing tasks in subj-area ---task
    and i foigured because of inactive marked on them in DAC

    but how does DAC mertadata fuigure that
    and i want some one to add more to this

    -----regards,
    Nagendra

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points