10 Replies Latest reply: Jun 16, 2011 3:29 PM by 569196 RSS

    two data sources two subject areas  dependency in DAC How

    JV123
      Hi,

      We are using OBIA 7.9.6.1, OBIEE 10.1.3.4.1, Informatica 8.6.1. basically we have two data sources one is siebel 8.1.1 and other is sales data mart. we are extracting data from both the the sources and loading data to OBAW. I customized Informatica and DAC. basically I developed new mappings for a fact table and source for that mapping is sales DM and that mapping also have lookups to some dimension tables(source is siebel). only two fact tables developed based on sales DM.

      we want to do daily incremental for from siebel and weekly full load from Sales DM. for that I created one execution plan for siebel mappings and it is working fine.

      when I try to create new subject area with tasks related to sales DM(tasks have lookups to dimenions of siebel). it is automatically adding dependent dimensions. since we have daily incremental scheduled I dont want to run tasks related to dimension tables as part of weekly full load.

      I want to build a subject area with only two tasks (sales DM) which is actually have dependency on other tasks. how do I do it?

      Edited by: Jay on Jun 13, 2011 1:35 PM
        • 1. Re: two data sources two subject areas  dependency in DAC How
          569196
          The execution plan you built might have tasks included that you dont want. But, you can always disable them so that they wont run every day.
          But when you want to run the weekly update, enable these tasks. See if this works.
          • 2. Re: two data sources two subject areas  dependency in DAC How
            JV123
            Hi,

            Thanks for your response. I can't disable them because those tasks using by other execution plan.

            Thanks
            Jay.
            • 3. Re: two data sources two subject areas  dependency in DAC How
              569196
              Add a task parameter for that particular task. When you have the daily one running, pass a value that will make the source qualifier return 0 rows which means it does run but does not bring any updates. When you run the weekly update, send a parameter value that will force to run the updates and bring all the results you want.

              Award points if its helpful.
              • 4. Re: two data sources two subject areas  dependency in DAC How
                JV123
                Let you give you clear idea about the issue

                subject area1:
                ----------------------
                Dimensions : D1,D2.....D10
                Facts: F1,F2...F6

                subject area2:
                -----------------------
                Facts: F7 and F8 ( these two fact tables have lookups to some dimension tables in subject area 1)

                Execution plan1(subject area1) run daily incremental load.

                Execution plan2(subject area2) run weekly full load.

                problem is with the subject area2, DAC automatically adding some tasks related to subject area 1. I just want to build execution plan with taks related to two fact tables in subject area 2.


                Thanks
                Jay.
                • 5. Re: two data sources two subject areas  dependency in DAC How
                  Ahsan Shah
                  If you include the dimenisons from SA1 as foreign keys to the Fact tables in SA2...the DAC will require these to run as it will consider these dimensions as pre-requisites for the load. Here are some options:

                  Option 1: Since all you need for SA2 is the lookups (and do not want the tasks for SA1 run) you can just schedule the weekly full of SA2 to run as long as the Dimensions from SA1 have already been loaded. Just make sure NOT to specify the dimensions as Foreign keys in the SA2 fact tables (F7, F8). This way, the DAC will run the weekly full load for the second EP but not use the tasks for the dimensions. Basically all you have to do is make sure EP1 dimensions are loaded before EP2 runs weekly.

                  Option 2: You can include both SAs in one EP and set it run to an initial FULL load and then daily incrementals. However, you can write a small script to update the REFRESH DATES of the 2 facts in SA2 (F7, F8) to be NULL every week. This will force a FULL of those two facts and the other SA1 Facts and Dims will continue to be incremental. This may mean you have to run daily incrementals of SA2 Facts as well..not sure if that is an issue or not.

                  Hope this helps.
                  • 6. Re: two data sources two subject areas  dependency in DAC How
                    user382858
                    Hi,
                    i too have the same query

                    I have 2 execution plan i want 2nd execution plan starts immediately\Automatically as soon as 1st Execution Plan gets finished

                    thanks
                    • 7. Re: two data sources two subject areas  dependency in DAC How
                      569196
                      For option2 as mentioned by obiaps, what script (database pl/sql, sql script or something else) can be used to change the values in DAC?

                      Edited by: user566193 on Jun 15, 2011 2:11 PM

                      Edited by: user566193 on Jun 15, 2011 2:11 PM
                      • 8. Re: two data sources two subject areas  dependency in DAC How
                        Ahsan Shah
                        There is no script documented as far as I know...you just need to query the DAC metadata tables that hold the refresh date information and write a small procedure. It may require digging into the DAC metadata tables. I am unsure offhand which tables but the concept is to just update the table directly. The other option is to invoke a polling script (cron job) on the server once the EP kicks off that uses the DAC command line to check the status of the load..if it detects a failure you can restart the EP via command line. You can then add a post session task or task action in the last Task (update row in run table) that then "turns off" the polling when the task runs to completion. Using a combination of DAC command line, ability to call stored porcedures or shell scripts, and also understanding the DAC metadata tables..you can do a number of things like this.
                        • 9. Re: two data sources two subject areas  dependency in DAC How
                          JV123
                          Hi obiaps,

                          Thanks for your input. what type of script can I use to upadte refresh dates in DAC. In fact I dont know how to do it.

                          Could you please explain little bit more about it.

                          Thanks
                          Jay.
                          • 10. Re: two data sources two subject areas  dependency in DAC How
                            569196
                            W_ETL_REFRESH_DT holds the last etl refresh dates for each of the source/target tables in the DAC metadata. This is where we set the refresh date to null.

                            kris

                            Edited by: user566193 on Jun 16, 2011 3:29 PM