tag:blogger.com,1999:blog-3820031471524503731.post8787736347809589495..comments2022-03-30T12:21:24.920-07:00Comments on Oracle OLAP: Script for Time Dimension TableBrian Macdonaldhttp://www.blogger.com/profile/18408740222558531436noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-3820031471524503731.post-84354446675030387622012-11-14T08:50:53.019-08:002012-11-14T08:50:53.019-08:00Posted updated (see main body) with half years. Th...Posted updated (see main body) with half years. The SQL is also a bit streamlined. Credits to Bernard for the calculation of end date and time span in the CREATE TABLE (rather than as separate updates).Bud Endresshttps://www.blogger.com/profile/02590149552658898625noreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-57653233820139227902012-11-05T03:09:51.056-08:002012-11-05T03:09:51.056-08:00could you include half yearly grain as wellcould you include half yearly grain as wellAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-22354918835715583302012-11-05T03:09:16.434-08:002012-11-05T03:09:16.434-08:00hi
please could you include half yearly as wellhi<br /><br />please could you include half yearly as wellAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-77026369018443782102012-11-05T03:07:01.823-08:002012-11-05T03:07:01.823-08:00Hi
Could you add a query to include half year gra...Hi<br /><br />Could you add a query to include half year grain as well.<br /><br />Thanks<br /><br />PeterAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-44084562395437530892012-11-05T03:05:34.758-08:002012-11-05T03:05:34.758-08:00Hi Bernard,
This is an excellent query. Was just...Hi Bernard,<br /><br />This is an excellent query. Was just using it but i also need to add the half yearly data in my time dimension could you help me with the same <br /><br />Regards<br /><br />PeterAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-76184264034280988152011-03-10T06:11:01.410-08:002011-03-10T06:11:01.410-08:00You can do it all in a single query. I've left...You can do it all in a single query. I've left the original placeholder column in comments.<br /><br />CREATE TABLE time_calendar_dim AS<br />SELECT CurrDate AS Day_ID,<br />1 AS Day_Time_Span,<br />CurrDate AS Day_End_Date,<br />TO_CHAR(CurrDate,'Day') AS Week_Day_Full,<br />TO_CHAR(CurrDate,'DY') AS Week_Day_Short,<br />TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'D'))) AS Day_Num_of_Week,<br />TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'DD'))) AS Day_Num_of_Month,<br />TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'DDD'))) AS Day_Num_of_Year,<br />UPPER(TO_CHAR(CurrDate,'Mon') || '-' || TO_CHAR(CurrDate,'YYYY')) AS Month_ID,<br />-- 31 AS Month_Time_Span,<br />MAX(TO_NUMBER(TO_CHAR(CurrDate, 'DD'))) OVER (PARTITION BY TO_CHAR(CurrDate,'Mon')) AS Month_Time_Span,<br />--to_date('31-JAN-2010','DD-MON-YYYY') AS Month_End_Date,<br />MAX(CurrDate) OVER (PARTITION BY TO_CHAR(CurrDate,'Mon')) as Month_End_Date,<br />TO_CHAR(CurrDate,'Mon') || ' ' || TO_CHAR(CurrDate,'YYYY') AS Month_Short_Desc,<br />RTRIM(TO_CHAR(CurrDate,'Month')) || ' ' || TO_CHAR(CurrDate,'YYYY') AS Month_Long_Desc,<br />TO_CHAR(CurrDate,'Mon') AS Month_Short,<br />TO_CHAR(CurrDate,'Month') AS Month_Long,<br />TO_NUMBER(TRIM(leading '0'FROM TO_CHAR(CurrDate,'MM'))) AS Month_Num_of_Year,<br />'Q' || UPPER(TO_CHAR(CurrDate,'Q') || '-' || TO_CHAR(CurrDate,'YYYY')) AS Quarter_ID,<br />-- 31 AS Quarter_Time_Span,<br />COUNT(*) OVER (PARTITION BY TO_CHAR(CurrDate,'Q')) AS Quarter_Time_Span,<br />-- to_date('31-JAN-2010','DD-MON-YYYY') AS Quarter_End_Date,<br />MAX(CurrDate) OVER (PARTITION BY TO_CHAR(CurrDate,'Q')) AS Quarter_End_Date,<br />TO_NUMBER(TO_CHAR(CurrDate,'Q')) AS Quarter_Num_of_Year,<br />TO_CHAR(CurrDate,'YYYY') AS Year_ID,<br />--31 AS Year_Time_Span,<br />COUNT(*) OVER (PARTITION BY TO_CHAR(CurrDate,'YYYY')) AS Year_Time_Span,<br />-- to_date('31-JAN-2010','DD-MON-YYYY') AS Year_End_Date<br />MAX(CurrDate) OVER (PARTITION BY TO_CHAR(CurrDate,'YYYY')) Year_End_Date<br />FROM<br />(SELECT level n,<br />-- Calendar starts at the day after this date.<br />TO_DATE('31/12/2010','DD/MM/YYYY') + NUMTODSINTERVAL(level,'day') CurrDate<br />FROM dual<br />-- Change for the number of days to be added to the table.<br />CONNECT BY level <= 365)<br />ORDER BY CurrDate;Bernardnoreply@blogger.com