Holiday flag to time dimension table

[reddyanita]

Hi,

I have created a time dimension table in one of our datamart with several columns and populated data from 1995 to 2010. But as per the business requirement we need to add holiday flag in that table. As we are not aware of the future holidays, what is the best procedure to add holiday flag to this table.

Any help on this is highly appreciated.

Thanks,
Anita

 

[Hans63]

To model this, a simple indicator filed in the time (or date) dimension would be sufficient.

As for populating the holiday flags, there are several routines available on the web. Search for easter holiday algorithms in the language (C - PLSQL - Java - etc) of your choice.
You can include it or adjust it to your specific needs.

If you have to deal with a wild variety of holidays which can not be determined beforehand, you might want to develop a small interface to a time table, which will than act as a seperate source for the time dimension.

Success

 

[reddyanita]

Thank you for your reply.

I did some search on google.com for easter holiday algorithms using PL/SQL. Unfortunately i haven't find any pointers. Coulld you please let me know if anyboday have pointers or sample code for designing the holiday calender using PL/SQL.

Appreciate your help.

Thanks,
Anita

 

[johnherman]

Since you are only dealing with 15 years, it might be easier to write 15 update statements to handle Easter.

http://www.maa.mhn.de/StarDate/publ_holidays.html

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[Hans63]

Here is a sample code I got from the Internet (I am sorry, I do not know the link where I got it from)

FUNCTION Easter_Sunday(yr IN NUMBER) RETURN DATE IS
a NUMBER;
b NUMBER;
c NUMBER;
d NUMBER;
e NUMBER;
m NUMBER;
n NUMBER;
day_ NUMBER;
month_ NUMBER;

BEGIN
IF yr < 1583 or yr > 2299 THEN
RETURN NULL;
END IF;

IF yr < 1700 THEN
m := 22;
n := 2;
ELSIF yr < 1800 THEN
m := 23;
n := 3;
ELSIF yr < 1900 THEN
m := 23;
n := 4;
ELSIF yr < 2100 THEN
m := 24;
n := 5;
ELSIF yr < 2200 THEN
m := 24;
n := 6;
ELSE
m := 25;
n := 0;
END IF;

a := mod (yr,19);
b := mod (yr, 4);
c := mod (yr, 7);
d := mod (19*a + m, 30);
e := mod (2*b + 4*c + 6*d + n,7);

day_ := 22 + d + e;
month_ := 3;

IF day_ > 31 THEN
day_ := day_-31;
month_:= month_+1;
END IF;

IF day_ = 26 and month_ = 4 THEN
day_ := 19;
END IF;

IF day_ = 25 and month_ = 4 and d = 28 and e = 6 and a > 10 THEN
day_:=18;
END IF;

RETURN to_date(
to_char(day_, '00') || '.' ||
to_char(month_, '00') || '.' ||
to_char(yr, '0000'),
'DD.MM.YYYY'
);

END; -- easter_sunday_