7 Replies Latest reply on Aug 8, 2011 11:35 AM by 766094

    Problems with extending Fiscal Day/W_DAY_D tables


      We are implementing BI Apps 7.9.6 for Oracle EBS - Fins, PO and Projects.
      Configured time dimension's $$END_DATE to 12-31-2030.
      After a full load, all tasks are completed sucessfully.
      But time dimension tables are populated upto 31-12-2010 only.
      So the fact tables will have time dim references upto 2010.

      Found an oracle note to extend W_DAY_D without a full load.
      So created a separate execution plan in DAC and executed.
      This time the dates are populated upto 2030.

      I don't understand why it is not pupulated during a full load, even the parameters are set.

      Is any of you faced this issue before.?
      What is the best way to populate time dimension tables upto 2030 during an initial full laod?

      Many Thanks.
        • 1. Re: Problems with extending Fiscal Day/W_DAY_D tables
          Ahsan Shah
          The note you are referring to is just showing you how to do it without a FULL load. This is for customers who are trying to avoid a FULL load. You can, of course, extend W_DAY_D on a full load..just make sure your parameters for SIL_DayDimension ($$START_DATE, $$END_DATE=) are correct. Make sure yoiu make this change in YOUR container..not the OOTB container. This approach is easier than extending without a full load.

          If this was helpful, please mark the response as correct.
          1 person found this helpful
          • 2. Re: Problems with extending Fiscal Day/W_DAY_D tables
            Hi There,

            Thank you for a quick update.
            I have already set $$START_DATE, $$END_DATE parameters to the desired dates in my custom container.
            These parameters were set for SIL_Day Dimension and the refresh dates for the related target tables were set to NULL.
            But it is still populating with the default dates.
            The parameter file generated by DAC showing $$START_DATE = 01/01/1980, $$END_DATE = 12/31/2010 where as the values set in DAC are $$START_DATE = 01/01/1980, $$END_DATE = 12/31/2030.
            I don't understand where it is picking those values from..?

            Many Thanks.

            Edited by: obiuser on 04-Aug-2011 08:15
            • 3. Re: Problems with extending Fiscal Day/W_DAY_D tables
              Ahsan Shah
              Are you overriding the parameter value anywhere and are the Subject Areas you are using from the CUSTOM Container (not the OOTB container)? The issue seems to be with your configuration..not the actual FULL load. I would recheck the parameter and/or DAC SUbject areas to confirm its picking up the right dates. Also do you have 'overrideMpltVarWithMapVar' set correctly and are DAC/INFA on same server? Once you have this, the W_DAY_D should get populated correctly.
              • 4. Re: Problems with extending Fiscal Day/W_DAY_D tables

                In the DAC, I have created Custom_Oracle R12 container as a Copy of OOTB Oracle R12 Source System Container.
                And I had set these $$START_DATE and $$END_DATE parameters in my Custom_Oracle R12 container.
                I have rechecked the parameters, they are all set as desired.
                'overrideMpltVarWithMapVar' is set to 'Yes' in the INFA admin console and DAC/INFA are on same server.
                I am not sure where it is overriding these values.

                When I created a separate subject area/execution plans for Time dimensions and did a full load for this execution plan,
                Surprisingly these are populated upto 2030. Nothing is changed, same mappings and same parameters. Only the execution plans are different.

                Do we need set $$START_DATE and $$END_DATE in DAC's source system papameters as well..?
                Currently there are only set at SIL_Day dimension task level.
                Once again thank you for your time.
                • 5. Re: Problems with extending Fiscal Day/W_DAY_D tables
                  Ahsan Shah
                  You dont need to set them at the DAC source system level, they only need to be at the Task level. Did you confirm that your Subject Area in your custom container is owned by that container or the original container? Once you change the task level parameters, make sure your Subject Area is also customized and owned by the container (as opposed to referincing the original container). Make sure to re-assemble the subject area and rebuild the execution plan. Let me know if that helps.
                  1 person found this helpful
                  • 6. Re: Problems with extending Fiscal Day/W_DAY_D tables
                    Well..Well..The subject areas in my Custom execution plans are still showing the OOTB source system 'Oracle R12'.
                    As I remember I had assembled those subject areas before doing a full load.
                    Anyway, I am assembling again to make sure they belong to custom source system.
                    Let me give a try...Many Thanks..!
                    • 7. Re: Problems with extending Fiscal Day/W_DAY_D tables
                      Hello There,

                      I have reassembled all the required subject areas individually and rebuild the Custom execution plan.
                      Confirmed all my tasks, subject areas and parameters are pointing to the custom container.
                      After an initial full ETL, all the time demension tables are populated with desired dates and everything looks OK.

                      Thanks so much for your time and guiding me through..Its a good learning..

                      Edited by: obiuser on 08-Aug-2011 04:35