Wednesday, September 17, 2014

QV16 Create calendar dimension using min max date

In this post i have created the calendar dimension, if we have fact table, from we can get minimum and maximum date. Using these dates we can create calendar dimension which will include quater information , year, month, day, date and others.
 
ODBC CONNECT TO [oracle_scott;DBQ=ORCL ];

Base:
LOAD EMPNO,
    ENAME,
    JOB,
    MGR,
    date(HIREDATE,'DD/MM/YYYY') as HIREDATE,
    SAL,
    COMM,
    DEPTNO;
SQL SELECT *
FROM SCOTT.EMP;


minMaxdate:
Load min(HIREDATE,'DD/MM/YYYY') as minDate, max(HIREDATE,'DD/MM/YYYY') as maxDate Resident Base;

Let vminDate = num(peek('minDate',0,'minDate'));
Let vmaxDate = num(peek('maxDate',0,'maxDate'));

cal1:
load
    IterNo() as num1,
    $(vminDate) + IterNo() - 1 as Num,
    date($(vminDate) + IterNo()-1) as TempDate
AutoGenerate 1 While
$(vminDate)+IterNo()-1 <= $(vmaxDate);

cal2:
load
    Num as DateSeq,
    TempDate as TheDate,
    Month(TempDate) as month,
    num(Month(TempDate)) as MonthSeq,
    Year(TempDate) as yearSeq,
    day(TempDate) as DaySeq
Resident cal1
order by TempDate ASC;

//drop table cal1;

cal3:
LOAD
    DateSeq,
    TheDate as HIREDATE,
    yearSeq,
    month,
    MonthSeq,
    DaySeq,
    MonthSeq + (yearSeq - 1) *12 as MonthSeq1,
    Ceil(MonthSeq/3) as quarter,
    'Q'&     Ceil(MonthSeq/3) as quarter1,
    WeekDay(TheDate) as DayName
Resident cal2
order by TheDate ASC;



No comments:

Post a Comment

web stats