5 Replies Latest reply on May 18, 2010 2:48 AM by Hemant K Chitale

    Incremental Loads and Refresh Date

    653953
      Hi all,

      Thank you for taking the time to review this post.

      Environment

      Oracle BI Applications 7.9.6 (Financial & Project Analytics)
      Oracle E-Business Suite 11.5.10

      Question

      I have a Test BI Apps 7.9.6 in a Test environment that is connected to a static EBS 11.5.10 data source. As part of my testing phase I'd like to do multiple Incremental Loads to get an accurate performance impact and timing study for the final pre-approval before migrating to Production. I can get a refresh of EBS which has a week's worth of transactions after my Initial Full Load. What I'd like to do is change Refresh Dates to "trick" the Incremental Load into only loading one days worth of data at a time, rather than the full week's worth of data in the Incremental load. Is this possible, and if so, how?

      Example timeline:

      Today - Initial Full load using Test EBS as of today

      1 week later - Refresh static Test EBS from Production with a week of transactions

      Post Refresh - Run daily Incremental jobs using static Test EBS
      First Incremental Load - Today's position + 1 day,
      Second " " - Today's position + 2 days,
      Third " " - Today's position + 3 days, etc

      As always all comments and solutions greatly appreciated.

      Kind Regards,
      Gary.
        • 1. Re: Incremental Loads and Refresh Date
          653953
          Bounce!
          • 2. Re: Incremental Loads and Refresh Date
            Hemant K Chitale
            The Incremental Load is always driven by the Last Refresh Date.

            Therefore after the refresh on day T+7, the Last Refresh Date would be T+7.
            Instead of using a schedule, run the Execution Plan manually every day thereafter (or delete the weekly schedule and create a new daily schedue). At every refresh the Last Refresh Date would get updated.

            NOTE : If your source EBS is "static", there really isn't much to refresh.

            Hemant K Chitale
            1 person found this helpful
            • 3. Re: Incremental Loads and Refresh Date
              653953
              Thanks Hemant,

              What you say is absolutely correct, but I'm trying to +"trick"+ the system into doing daily incremental loads.

              Just to further clarify the situation. This is a Test BI instance in that it is connecting to a Test EBS environment where no one is entering data i.e. the data is static. The EBS environment is then refreshed one week later (it's not practical to do this on a daily basis), but I'd like the ability to break this week's worth of data down into 7 daily Incremental Loads - to get accurate performance impact and timing study for the final pre-approval before migrating to Production - rather than the one single Incremental Load with the full week of data. This is not something that I'd be looking to implement into Production.

              Is this possible, and if so, how?

              As always all comments and solutions greatly appreciated.

              Kind Regards,
              Gary.
              • 4. Re: Incremental Loads and Refresh Date
                Hemant K Chitale
                Say on the 01st of the month, you did a Load.

                Then on the 08th of the month, the source EBS system was itself refreshed.

                What you want to do is to run a single day refresh on the 08th for all data from the 01st to the 02nd of the month), and then another single day referesh -- whether on the 08th or on the 09th , you don't care -- for all data from the 03rd to the 04th.

                Unfortunately, the refresh is from last refresh date to current date. You can't define "refresh upto date". Therefore, your first 'incremental' refresh on the 08th would refresh all data from the 02nd to the 08th in one shot. What you could try to do is
                a. After the first load on the 01st, shutdown the BI DWH.
                b. When the EBS test source is refresh on the 08th, reset your SYSTEM CLOCK ON THE BI DWH DATABASE SERVER to the 2nd (or 3rd) of the month.
                c. Now, when you run a refresh, BI will extract all data from the 01st to the 02nd or 03rd (even though EBS is as of the 08th).
                d. Once this is done, shutdown BI DWH.
                e. Reet the SYSTEM CLOCK ON THE BI DWH DATABASE SERVER to the 3rd or 4th of the month.
                f. Run another Incremental Refresh.
                ... and so on ...


                Hemant K Chitale
                http://hemantoracledba.blogspot.com
                • 5. Re: Incremental Loads and Refresh Date
                  Hemant K Chitale
                  I realised that resetting the system clock won't help.

                  Whatever the system date-time is, an Incremental Extract will attempt to extract all rows from the source which have a timestamp exceeding the last refresh date.


                  Hemant K Chitale