2 Replies Latest reply: Feb 8, 2012 2:00 PM by user258674 - oracle RSS

    Time Dimension Population with ODI 11.1.1.5

    815939
      Hi All,
      I am planning to build a time/Date dimension to use with the HR Fact tables (sourced from Oracle HRMS).

      Customer would like to supply start and end dates (01-JAN-05 TO 31-DEC-11) and i should be able to generate and load the time data between these dates.

      Down the line, if the customer to append the Time Dimension for future data, he should be able to specify the new start and end dates (01-JAN-12 To 31-DEC-15), i should be able to incrementally load the new time data, instead of truncating and regenerating all the time data.

      Any ideas on implementing this requirements using ‘Oracle Data Integrator 11.1.1.5’?

      Thanks in advance
      Regards,
      Venkat
        • 1. Re: Time Dimension Population with ODI 11.1.1.5
          PeakIndicators_Alastair
          This is what I did recently,

          Download the 'SQL_as_source' knowledge module from the Oracle ODI Blog here : http://blogs.oracle.com/warehousebuilder/entry/odi_11g_simple_flexible_powerful


          Used a temporary interface with the following simple query :

          with row_generator as (
          SELECT to_date('01011980','ddmmyyyy') + LEVEL - 1 DT
          FROM sys.dual
          CONNECT BY LEVEL <=
          /* End Date in here -> */ to_number(to_date('31122030','ddmmyyyy') -
          /* Start Date in here -> */ to_date('01011980','ddmmyyyy')) +1 )
          SELECT DISTINCT
          'YEAR' as TIME_LEVEL,
          NULL as YEAR_MONTH,
          NULL as YEAR_QTR,
          to_char(DT,'YYYY') as YEAR
          from row_generator
          UNION
          SELECT DISTINCT
          'QUARTER',
          NULL,
          to_char(DT,'YYYY')||' / '||to_char(DT,'Q'),
          to_char(DT,'YYYY') from row_generator
          UNION
          SELECT DISTINCT
          'MONTH',
          to_char(DT,'YYYY')||' / '||to_char(DT,'MM'),
          to_char(DT,'YYYY')||' / '||to_char(DT,'Q'),
          to_char(DT,'YYYY')from row_generator
          order by 1,2,3,4


          As you can see in the above code, it would be possible to pass in ODI variables as the start and end dates , you could either allow the user to type these in at run time or read them from a control table.

          Use the temporary interface as the source data for your Time dimension interface (you might want to add more Time Attributes - Mine was very simple) , then use an IKM Incremental update approach (Im using IKM Oracle Incremental update (Merge)).

          Hope this helps
          Rgrds
          Alastair

          p.s - my time dimension is loading multiple levels (Years, Quarters, Months) into the same table - the SQL contains a row for each day though so easily adapted to report days.

          Edited by: PeakIndicators_Alastair on Jan 24, 2012 12:05 PM
          • 2. Re: Time Dimension Population with ODI 11.1.1.5
            user258674 - oracle
            Thanks Alastair,
            Perfect Solution. 10 points for you.
            Regards,
            Venkat

            Edited by: user10505064 on Feb 8, 2012 11:59 AM