The SQL Below generates a date dimension table where the DATE_WID is a chronological key. This is essential for getting the time functions to work in OBIEE.
SELECTn AS Date_ID,TO_NUMBER(TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'YYYY')||TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'MM') ||TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'DD')) as DayNum,TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day') AS Full_Date,TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'DD') AS Days,TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'DAY') AS Day_Name,'WEEK-'||TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'WW') AS Week_Num_Of_Month,TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'YYYY')||TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'MM') AS Month_Num,TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Mon') AS Month_Short,TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Month') AS Month_Long,TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'YYYY')||TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Q') AS Quarter_Num,TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'YYYY')||'-Q' || TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Q') AS Quarter_Short,TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'YYYY')||' Quarter ' || TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Q') AS Quarter_Long,CASE WHEN TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Q') in ('1','2') then 'Jan-Jun' else 'Jul-Dec' end as HALF_YEARLY,TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'YYYY') AS Year,FROM (select level nfrom dualconnect by level <= 1096