|
Home > Archive > MS SQL Data Warehousing > March 2006 > general question about handling times and calendars
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
general question about handling times and calendars
|
|
|
| Hi all,
Looking for some suggestions to implement time and calendar data within a
DWH.
I've done a fair bit of investigating best practices for implementing time
as a dimension within a DWH, but none of them talk about breaking time down
by calendar types and i'm wondering if there is a reason for it.
I mean, of course we can easily implemtn the standard calendar into a time
dimension, but when it comes to hosting more than 1, i.e. Fiscal, Financial
and other Calendars that business may use, isn't there a need here to hold
daily information broken down into different calendars? I mean, there will
be different start and end dates, different groupings within these, i.e.
Qtrs, Months and Weeks that may start and end on different dates to the
normal calendar.
So my question is, is the best way to implement TIME as a dimension by
having an ID and then many attributes about this date, which means having
many columns to perform different calculations OR is it best to introduce a
calendar type system, for example, StandardCalendar, FiscalCalendar,
FinanicalCalendar etc... where each holds its own information that can be
joined back to a TimeID+CalendarID?
I have a sneaky suspicion i've just rambled on and made no sense to anyone ?
:)
Many thanks
Immy
| |
|
| there is many ways to answer this requirement.
first, if you have only new way to group your dates then adding attributes
to an existing time dimension is enough. (and creating all required
hierarchies)
default are year, quarter, month, week, fiscal year, reporting year...
AS2005 has a good time dimension generator, there is more then 50 attributes
available in 1 click.
if you want to create a complete new calendar which use the same group name
but for different dates and if you have AS2005, the many-many feature will
help you.
For example, the company had a fiscal calendar from january to december, but
in 2001 the company decide to change to a new fiscal calendar starting in
june and the users want to view aggregated data using the old or the new
fiscal periods, a many-many is good for you.
"Immy" < therealasianbabe@hot
mail.com> wrote in message
news:uG8kJMGRGHA.1688@TK2MSFTNGP11.phx.gbl...
> Hi all,
>
> Looking for some suggestions to implement time and calendar data within a
> DWH.
>
> I've done a fair bit of investigating best practices for implementing time
> as a dimension within a DWH, but none of them talk about breaking time
> down by calendar types and i'm wondering if there is a reason for it.
>
> I mean, of course we can easily implemtn the standard calendar into a time
> dimension, but when it comes to hosting more than 1, i.e. Fiscal,
> Financial and other Calendars that business may use, isn't there a need
> here to hold daily information broken down into different calendars? I
> mean, there will be different start and end dates, different groupings
> within these, i.e. Qtrs, Months and Weeks that may start and end on
> different dates to the normal calendar.
>
> So my question is, is the best way to implement TIME as a dimension by
> having an ID and then many attributes about this date, which means having
> many columns to perform different calculations OR is it best to introduce
> a calendar type system, for example, StandardCalendar, FiscalCalendar,
> FinanicalCalendar etc... where each holds its own information that can be
> joined back to a TimeID+CalendarID?
>
> I have a sneaky suspicion i've just rambled on and made no sense to anyone
> ? :)
>
> Many thanks
> Immy
>
|
|
|
|
|