Time Dimension Table Design for Data Warehouse Project withh Sample Data

When we are working with Business Intelligence Projects, It is mandatory to have Time dimension to create lot of measures like Year Ago, Month Ago, Week Ago, Date Ago or Year To Date, MTD, YTD , WTD and much more.

Designing or Creating a Time dimension table with data would be easier task. Here I am making a simpler way to create a Time Dimension which would fit for any requirement with all the relevant columns.

Step1: Create a Time Dimension Table with the below DDL Query

# WC_TIME_D: /* DDL for the date dimension */

*********************************************

CREATE TABLE WC_TIME_D (

DATE_KEY SMALLINT NOT NULL,

FULL_DATE DATE,

DAY_OF_WEEK SMALLINT,

DAY_NUM_IN_MONTH SMALLINT,

DAY_NUM_OVERALL SMALLINT,

DAY_NAME VARCHAR(9),

DAY_ABBREV CHAR(3),

WEEKDAY_FLAG CHAR(1),

WEEK_NUM_IN_YEAR SMALLINT,

WEEK_NUM_OVERALL SMALLINT,

WEEK_BEGIN_DATE DATE,

WEEK_BEGIN_DATE_KEY SMALLINT,

MONTH SMALLINT,

MONTH_NUM_OVERALL SMALLINT,

MONTH_NAME VARCHAR(9),

MONTH_ABBREV CHAR(3),

QUARTER SMALLINT,

YEAR SMALLINT,

YEARMO INT,

FISCAL_MONTH SMALLINT,

FISCAL_QUARTER SMALLINT,

FISCAL_YEAR SMALLINT,

LAST_DAY_IN_MONTH_FLAG CHAR(1),

SAME_DAY_YEAR_AGO_DATE DATE,

PRIMARY KEY (DATE_KEY));

 Step2:Download the excel Sheet which has the prefilled with data of up to Year 2025.

Step3: Generating the Time Data for Years we want

To Get the the value up to the year you want, Just Open the downloaded excel sheet and select the last row of the first sheet and drag it down. It will automatically fill the the value with the prebuilt formula.

Step 4: Inserting Data into Table - There are 2 ways

Way 1: After we have generated the data,  just Copy and "Paste as values" in another sheet and insert from Excel directly to Oracle Database

Way 2: There is insert statement generated in the first sheet (Z column). Just drag up to the value you have created to create insert statement for all rows and then copy paste the query in SQL developer or put as an .sql file and run in DB to load.

Note:

If you face the below error while running insert statement then execute the alter statement and continue the insertion;

SQL Error: ORA-01861: literal does not match format string 01861

Solution:

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD'

Do not forget to commit after insert completes.

Cheers!

Bose