5 Replies Latest reply: Nov 19, 2012 5:09 PM by nk9 RSS

    DAC Repository database question

    658640
      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
          user743400
          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
            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
              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
                user743400
                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
                  ----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