Time Dimension - doc serve

outstandingmaskΔιαχείριση Δεδομένων

29 Νοε 2012 (πριν από 4 χρόνια και 8 μήνες)

204 εμφανίσεις

In any data warehouse date and time plays a vital role in keeping track of when a
particular event has taken place. However, we do not store the time information in
the same way as we do in the OLTP database. In a dimensional model a separate
time dimensio
n is normally used. This time dimension contains separate attributes
for year, month, and day and so on. This time dimension is initially populated with
the data of a particular period like ten years. Whenever an event/transaction needs
to be recorded in t
he fact table the time key from this dimension for the
corresponding time is obtained from the time dimension.



Surrogate key for time dimension is auto incremented just like any other dimension but it
is always helpful to have it the right format like fo
r date: 05
-
Mar
-
2010 the time key
should be 05032010. This proves to be extremely useful as queries can be done directly
based on the time key and thus improves performance.

The following figures show how to create and populate time dimension in Sql Server
Analysis Services (SSAS):
















A datamart or data warehouse will usually need a time dimension to use for
date queries that look at periods, weeks, months, etc. Here is an example of
how to generate one from the basic date entry and the Oracle date functions.
The same idea ca
n be used in DB2, Informix, Postgresql, etc.

Load base dates from Excel or something that can generate a date series, these
dates are the base data of the rest of the time dimension that will be generated
using the built in SQL functions in Oracle SQL. Exp
ort the Excel data into an
ascii file and load the dimension_time.calendar_date field then run the
following scripts to populate the time dimension attributes.

This is hardly an Oracle only solution. Informix, DB2, Sybase, SqlServer, etc.,
all have similar

or the same SQL date and time functions that can be used to
generate a time dimension.


####################################cut here#########################

#!/bin/ksh

# Baseline schema objects


. ../BASELINE/etc/baseline_env # environment file for Oracl
e


make_time () {

sqlplus << EOF

schema_owner/password


drop table schema_owner.dimension_time;


--

The time dimension for a company

--

calendar dates, holidays, etc. for business rules

create table schema_owner.dimension_time (


calendar_date date,



day_of_year_number number,


day_of_week varchar2(9),
--

day name.


week_number number,
--

accounting week number(also known as
period).


week_ending date,
--

ends on friday.


accounting_month_end date,
--

last friday of month.


calendar_month_end date,
--

last day of month.


month_day_number number,
--

day of month.


month varchar2(9),
--

month name.


month_number number,
--

number of month in year.


quarter_number number,
--

ends on account
ing month end.


holiday varchar(1)
--

flag 'Y' is holiday, 'N' is no
holiday.

) tablespace time_data ;


GRANT SELECT ON schema_owner.dimension_time TO SELECT_ALL_TIME;

EOF


create index dimension_time_ix1


on dimension_time(calendar_date) ta
blespace time_data nologging;

}


######################

# Main


make_time


###########################cut here###########################

#!/bin/ksh

# Load base dates from Excel or something that can generate a date
series,

# export the Excel data into an
ascii file and load the

# dimension_time.calendar_date field then run the following to

# populate the time dimension attributes.


. ../BASELINE/etc/baseline_env # environment file for Oracle


sqlplus << EOF

schema_owner/password

--

Set the various time

values with the DATE format and to_char
function;


--

Weeks end on Friday.

update dimension_time set week_number = to_char((calendar_date
+2),'IW');


--

other functions to populate other fields:

--
select to_char((calendar_date),'W') from dimension_time;

-
-
select to_char((calendar_date),'WW') from dimension_time;

--
select to_char((calendar_date),'MONTH') from dimension_time;

--
select to_char((calendar_date),'YYYY') from dimension_time;

--
select to_char((calendar_date),'J') from dimension_time;

--
select to_c
har((calendar_date),'D') from dimension_time;

--
select to_char((calendar_date),'DD') from dimension_time;

--
select to_char((calendar_date),'DDD') from dimension_time;

--
select to_char((calendar_date),'DAY') from dimension_time

--

where calend
ar_date = '03
-
MAR
-
02';

EOF

Time Dimension Generator / Builder

Use this (free) service to generate a time dimension for data warehouse/OLAP applications.

The
generator outputs a relational table (as

INSERT
-
statements or comma
-
separated values; 45+ columns)
which
can be loaded into your data warehouse time table. The generator builds the following hierarchies:



Each hierarchy level contains an integer key (d
efining navigational paths and chronological sorting), a name (e.g.
"Q2"), a textual description (e.g. "Second Quarter"), and a number of different flags (e.g.

isWorkday

which
"flags" work days). Use the keys for building and sorting the hierarchies, the n
ame
-

and text columns for showing
the member values and the flags for your specific reporting requirements (all flags are integer

0
's or

1
's allowing
both
COUNT

and

SUM

aggregation). Use this dimension as a starting point for building a time dimension that
suits the specific needs of your data warehouse.

To build the time dimension, please review the configuration settings below and press the "Generate Time
Dimension"
-
button on the bottom of the page. To further configure the time dimension use

UPDATE
-
statem
ents
after loading the table into the database.

Day
-
Level Configuration

Please review the configuration settings for the day
-
level.


Day name format




Day text format




At the date/day level the following 12 columns are generated:

Day_Key, Day_Timestamp, Day_Name,
Day_Text, Day_WeekdayName, Day_IsWorkday, Day_NumberInWeek,
Day_NumberInMonth, Day_NumberInYear, Day_NumberInFiscalYear,
Day_NumberSinceBegin
ning, IsToday
. NOTE:

Day_Key

is the primary key of the dimension (DWID
in the figure above).


--
>

Note on the IsToday
-
column

Week
-
Level Configuration

The time dimension generator uses the ISO8601:
1988 standard for week enumeration. The first week of the year
is defined by ISO as the one which contains the fourth day of January. Please review the configuration settings
for the week
-
level.


Week name format



Ex: W29

Week text format


Ex: Week 29


At the week level the following 4 columns are generated:

Week_Key, Week_Name, Week_Text,
Week_NumberInYear
.

Month
-
Level Configuration

Please review the configuration settings for the month
-
level.


Month name format



Ex: 07

Month text format


Ex: July


At the month level the following 5 columns are generated:

Month_Key, Month_Name, Month_Text,
Month_NumberInYear, Month_NumberInFiscal
Year
.

Season
-
Level Configuration

There are four seasons in one year (spring, summer, fall, and winter) each season four months long. Please
select the month when spring begins.


Spring starts at



March 1st


At the

season level the following 3 columns are generated:

Season_Key, Season_Name, Season_Text
.

Quarter
-
Level Configuration

The quarter divides the year (calendar and fiscal) into four parts, each consisting of three months. Please review
the configuration sett
ings for the quarter
-
level (calender
-

and fiscal hierarchies).


Quarter name format



Ex: Q3

Quarter text format


Ex: 3rd Quarter


At the quarter level the following 8 columns are generated:

Quar
ter_Key, Quarter_Name,
Quarter_Text, Quarter_NumberInYear, FiscalQuarter_Key,
FiscalQuarter_Name, FiscalQuarter_Text,
FiscalQuarter_NumberInFiscalYear
.

Semester
-
Level Configuration

The semester divides the year (calendar and fiscal) into two parts, each co
nsisting of two quarters. Please review
the configuration settings for the semester
-
level (calendar
-

and fiscal hierarchies).


Semester name format



Ex: S2

Semester text format


Ex: 2nd Semester


At the semester level the following 8 columns are generated:

Semester_Key, Semester_Name,
Semester_Text, Semester_NumberInYear, FiscalSemester_Key,
FiscalSemester_Name, FiscalSemester_Text,
FiscalSemester_NumberInFiscalYear
.

Year
-
Level Configuration

The y
ear level is not configurable.


At the year level the following 3 columns are generated:

Year_Key, Year_Name, Year_Text
.

Fiscal Year
-
Level Configuration

A fiscal year spans 12 months like a "normal" (calendar) year. What differs is the date when a new year

starts. A
calendar year always starts at January 1st, a fiscal year can start at any month. As an example, Danish
companies often use a fiscal year that starts at July 1st and ends at June 30th the following year. Select the
calendar month that starts the

fiscal year, i.e. the very first month in a new fiscal year.


Fiscal year starts at



July 1st

Fiscal year name format




Fiscal year text format




At the fiscal year level the following 3 columns are generated:
FiscalYear_Key, FiscalYear_Name,
FiscalYear_Text
.

Output Configuration

Configuration settings.


Years of data to generate



5 years (approx. 0.7 MB)

Start

from year



a

2011

Output format





Generate and Download the Time Dimension

The time dimension will be printed in ASCII in this browser window when you press the

"Generate Time
Dimension"
-
button below. Select "Save As..." in the File menu to save the data to a local text file (REMEMBER:
Select file type *.txt when saving!).