|
Return to Newsletter Contents...
Non-Standard or Varying Time Dimensions in SSAS
by:
Charles Tournear, Senior Consultant,
MCT, MCSE, MCSD, MCDBA, CRCP
A time dimension is the most commonly used dimension when building cubes with
SQL Server Analysis Services. A
time dimension is a dimension type whose attributes represent time periods, such
as years, semesters, quarters, months, and days. The periods in a time dimension
provide time-based levels of granularity for analysis and reporting.
Some cubes may use multiple time dimensions or multiple hierarchies
within the time dimension.
There are two ways to define a time dimension.
By using a predefined time table from the database or defining a server
time dimension. Time hierarchies
are generally referred to as calendars. The Dimension Wizard provides several
hierarchy templates that can be used to automatically generate several types of
calendars when you create a time dimension or server time dimension.
-
Standard Calendar is a twelve-month Gregorian calendar starting on January 1
and ending on December 31st. However
using the wizard you can determine the start date and end date of a year and
the first day of a week.
The
following calendars are only available if you use the Dimension Wizard to create
a server time dimension.
-
Fiscal calendar is a twelve-month calendar. When you select this calendar,
you specify the starting day and month for the fiscal year used by your
organization.
-
Reporting calendar (or marketing calendar) is a twelve-month calendar that
includes two months of four weeks and one month of five weeks in a repeated
three-month (quarterly) pattern. When you select this calendar, specify the
starting day and month and the three-month pattern of 4–4–5, 4–5–4, or 5–4–4
weeks, where each digit represents the number of weeks in a month.
-
Manufacturing calendar is a calendar that uses 13 periods of four weeks,
divided into three quarters of three periods and one quarter of four
periods. When you select this calendar, you specify the starting week
(between 1 and 4) and month for the manufacturing year used by your
organization, and also identify which quarter contains four periods.
-
ISO 8601 Calendar is the International Organization for Standardization
(ISO) Representation of Dates and Time standard calendar (8601). This
calendar has an integral number of 7-day weeks. The new year may start
several days before or after the start of the new year, based on the
Gregorian calendar. The first week of this calendar is determined by the
first week of the Gregorian calendar which contains a Thursday. Therefore,
the first day of this week, the Sunday, may actually fall within the
previous year.
If the relationship between a date and a period such as year or week is
inconsistent from year to year then
using a predefined time table in the database will become the easiest way to
define the time dimension. Below is
an example of transact SQL code that uses the Manufacturing style of calendar
but where the definitions of week and year have varied over time to add data to
a time dimension table.
--Calendar is defined as 13 periods per year and 4 weeks per period with 7 days
per week
set nocount
on
declare @datevalue
as datetime
set @datevalue
= '3/14/05'
declare
@period as int
declare @perioddate
as datetime
declare @holdperiod
as int
set @holdperiod
= 1
declare
@week as int
declare
@year as int
set @perioddate
= '3/14/05'
while @datevalue
< '3/12/08'
begin
--Year 2006 began on 3/14/2005 and the week began on a Monday
if @datevalue
between '3/14/05' and '3/14/06'
begin
set @period = datediff(day,
'3/14/05', @datevalue)/28 + 1
if @holdperiod <> @period
begin
set @holdperiod = @period
set @perioddate = @perioddate + 28
end
set @week = (datediff(day, @perioddate , @datevalue)/7 + 1 )
--In
the following year the week will begin on a Wednesday, so two days are added to
the last period to allow for the change
if @datevalue = '3/13/06' or @datevalue = '3/14/06'
begin
set @period = 13
set @week = 4
end
set @year = 2006
end
--Year
2007 began on 3/15/2006 and the week began on a Wednesday
if @datevalue
between '3/15/06' and '3/13/07'
begin
if datediff(day, @datevalue,'3/15/06')=0
begin
set @holdperiod = 1
set @perioddate = '3/15/06'
end
select @period = datediff(day,
'3/15/06', @datevalue)/28 + 1
if @holdperiod <> @period
begin
set @holdperiod = @period
set @perioddate = @perioddate + 28
end
set @week = (datediff(day, @perioddate , @datevalue)/7 + 1 )
set @year = 2007
end
--Year 2008 began on 3/14/2007 and the week began on a Wednesday
if @datevalue
between '3/14/07' and '3/11/08'
begin
if datediff(day, @datevalue,'3/14/07')=0
begin
set @holdperiod = 1
set @perioddate = '3/14/07'
end
select @period = datediff(day,
'3/14/07', @datevalue)/28 + 1
if @holdperiod <> @period
begin
set @holdperiod = @period
set @perioddate = @perioddate + 28
end
set @week = (datediff(day, @perioddate , @datevalue)/7 + 1 )
set @year = 2008
end
--Add
new date record to table and increment date by 1
INSERT INTO
TimeDim
(Year, Period, Week, DateValue)
SELECT
@year, @period, @week, @datevalue
set @datevalue
= @datevalue + 1
end
The above example shows one option that allows you to define a calendar
hierarchy any way you want. With
the options available to you in SQL Server 2005 Analysis Services you should be
able to create appropriate time dimensions to meet any situation.
Go to Top |
Return to Newsletter Contents
|
