CODEWITHSUNDEEP
sqloracle-databasedimensional-modeling
dwalker
dwalker

Reputation: 61

Creating and Populating the Time dimension

My shift_date column of my shift table has all the dates of the shift requests recieved by the agency.It contains other columns which shows whether the shift was filled or cancelled. I want to use this to create a time dimension and link to the fact table to find out how many shifts were filled each month,cancelled in a quarter,etc. i have created the time table as below. I am doing it in SQL developer and to understand dimensional modelling which I am having trouble getting my head around.

my shift table:

shift_date |shift_status|request_id|
-----------------------------------------
 09-01-2011|Filled      |8899
21-01-2011 |Cancelled   |6677

and so on. I created a time dimension table as below:

CREATE TABLE "DIM_TIME" 
       (    "TIME_KEY" NUMBER(10,0), 
        "FULL_DATE" DATE, 
        "DAY_NAME" VARCHAR2(9 BYTE), 
        "DAY_OF_WEEK" NUMBER(5,0), 
        "DAY_NUMBER_IN_MONTH" NUMBER(2,0), 
        "DAY_NAME_ABBREVATED" VARCHAR2(5 BYTE), 
        "WEEKDAY_FLAG" VARCHAR2(2 BYTE), 
        "WEEK_OF_THE_YEAR" NUMBER(5,0), 
        "WEEK_BEGIN_DATE" DATE, 
        "MONTH_NUMBER" NUMBER(3,0), 
        "MONTH_NAME" VARCHAR2(20 BYTE), 
        "MONTH_ABBREVATED" VARCHAR2(5 BYTE), 
        "QUARTER" NUMBER(5,0), 
        "YEAR" NUMBER(5,0), 
        "LAST_DAY_IN_MONTH_FLAG" VARCHAR2(2 BYTE)
       );

how can i extract the dates from the shift_date column to populate this time_dimension table? I am not expecting the whole code but if anyone can point me in the right direction it will be helpful as i am still learning.Thank you

Upvotes: 0

Views: 4921

Answers (1)

Kjetil S.
Kjetil S.

Reputation: 3787

This will create the rows in the DIM_TIME table:

create sequence s_time_key START WITH 1;

insert into dim_time (
  time_key,
  full_date,
  day_name,
  day_of_week,
  day_number_in_month,
  day_name_abbrevated,
  weekday_flag,
  week_of_the_year,
  week_begin_date,
  month_number,
  month_name,
  month_abbrevated,
  quarter,
  year,
  last_day_in_month_flag
)
select
  s_time_key.nextval,
  d,
  to_char(d,'Day'),   --Monday
  to_char(d,'D'),     --1-7, monday=2 in some countries, 1 in others (NLS)
  to_char(d,'DD'),    --1-31
  to_char(d,'Dy'),    --Su, Mo, ...
  decode(to_char(d,'Dy'),'Sa','N','Su','N','Y'),
  to_char(d,'IW'),    --week num ISO standard
--to_char(d,'WW'),    --week num other
  d+1-to_char(d,'D'), --first day in week, depending on NLS
  to_number(to_char(d,'MM')),
  to_char(d,'Month'),
  to_char(d,'MON'),
  to_char(d,'YYYYQ'),
  to_char(d,'YYYY'),
  decode(to_char(d+1,'DD'),'01','Y','N')
from (select distinct shift_date d from shift);

  • In Oracle 12c, instead of a sequence to get the time_key column, you could use create table dim_time ( time_key number(10) generated by default on null as identity, ... to autoincrement the key.

  • The select above will only populate table DIM_TIME with the dates that actually exists in table SHIFT. But often one will want all dates in a certain period, for instance all the days Jan 1st 2014 trough 2018 like this: replace the last line in the sql above with this:

.

from (
  select d from (
    select to_date('1970','YYYY')+level d from dual connect by level<=366*100
  )
  where to_char(d,'YYYY')
  between 2014 and 2018
);

Upvotes: 2

Related Questions