|
Home > Archive > MS SQL Server OLAP > April 2005 > Multiple Time Dimensions
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 |
Multiple Time Dimensions
|
|
|
| Hi
I have a Fact Table with a Time Id field which plugs into my Time Table.
The Time Table contains Year, Quarter, Month, Week and Day.
I currently have a dimension that has Year, Q, M and Day. I would like a
Year, Week, Day (Day not totaly necessary) time dimension as well. I can't
figure out how to accomplish this. Is there a way to accomplish this in the
same cube?
--
RRR
| |
| Deepak Puri 2005-04-26, 8:24 pm |
| There's a couple of options that come to mind - the most obvious would
be to create an alternate time hierarchy:
http://msdn.microsoft.com/library/d...y/en-us/dnsql2k
/html/sql_analservbp.asp[color=darkred]
Best Practices for Business Intelligence Using the Microsoft Data
Warehousing Framework
...
Using Multiple Hierarchies in Analysis Services
Analysis Services lets you create multiple hierarchies for a single
dimension to offer alternate views of dimension members. The Time
dimension is a good example; Accounting might want to look at data by
fiscal year, while Marketing is interested in the calendar year.
Using multiple hierarchies has several advantages over creating separate
dimensions.
Better usability: Users see the hierarchies (such as time hierarchies)
grouped in a logical way.
Shared aggregates: If you define a dimension with multiple hierarchies
rather than building separate dimensions, Analysis Services builds a set
of aggregations that are useful to both hierarchies. For example, if the
time dimension has both YMD and YQD hierarchies, the two hierarchies can
share aggregates built at the year level.
Shared key: Sharing a single key in the fact table reduces the size and
complexity of the fact table.
...[color=darkred]
Another option would be to define Member Properties like Week on the
main Time Dimension, and create a Virtual Dimension from the appropriate
properties.
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.developersdex.com ***
|
|
|
|
|