Script for Creating and Generating members for Time Dimension

Time Dimension is an important dimension in most of the Data Warehouses, especially when requirement for keeping hours, minutes, and seconds is vital for the business.

Some blog and books and articles consider time dimension as a dimension that contains date columns, but in this post I only focus on the Time part of it, which means hours, minutes, and seconds, which makes this dimension as a Time Dimension. But if you want to know more about Date dimension, follow my previous post here.

In this post you will see the script to Create the Time Dimension structure, and also the script to fill the dimension with members. 

The Time Dimension that I work on it, has columns as below:

Script for creating the structure of time dimension:

SELECT  to_char(dt, 'yyyy-mm-dd hh24:mi:ss') dt,
        to_char(dt, 'yyyy') hour,
        to_char(dt, 'mm') hour,
        to_char(dt, 'dd') hour,
        to_char(dt, 'hh') hour,
        to_char(dt, 'mi') minutes,
        to_char(dt, 'ss') seconds,
        to_char(dt, 'hh24:mi:ss') time24,
        to_char(dt, 'hh:mi:ss') time12
FROM (
    SELECT SYSDATE + (rownum * (1/24/60/60)) dt     
    FROM DUAL CONNECT BY ROWNUM < 36000
    )

Leave a Reply