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

    Time Dimension Population with ODI

      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’?

      Thanks in advance
        • 1. Re: Time Dimension Population with ODI
          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 )
          'YEAR' as TIME_LEVEL,
          NULL as YEAR_MONTH,
          NULL as YEAR_QTR,
          to_char(DT,'YYYY') as YEAR
          from row_generator
          to_char(DT,'YYYY')||' / '||to_char(DT,'Q'),
          to_char(DT,'YYYY') from row_generator
          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

          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
            user258674 - oracle
            Thanks Alastair,
            Perfect Solution. 10 points for you.

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