I am trying to build a fiscal time dimension for my cube right now what I use is a standard dimension linked to the date filed that gives me columns for year, quarter, month, week. I would like to set this up as a time dimension to take advantage of MDX functions available for time dimensions. If this is not possible are there equivilent functions for standard dimensions?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
fiscal calendars
- Thread starter JohnEck
- Start date
I have a script that creates a dimTime Table that has every date broken down to the various levels. I use this to create surrogate keys when building the fact record. In AS I simply create the time dimension as I need it and join the appropriate key. Having AS determine the time dim can be a total pain and time consuming. I am currently modifing the script to create ime in the various Fiscal periods 5-4-4 4-5-4 4-4-5.
Shoot Me! Shoot Me NOW!!!
- Daffy Duck
Shoot Me! Shoot Me NOW!!!
- Daffy Duck
- Thread starter
- #3
You may want to use two Time Dimensions:
1. Calendar with Year, Quarter, Month, Day (Week omitted because it doesn't roll up into months evenly).
2. Fiscal with Year, Quarter, Period, Week, Day. In this case the weeks would have to roll up evenly into the Periods. Do something like MDXer suggested 5-4-4 or 4-4-5.
I would create the Time Table Keys so that I didn't have to alter it again. Once less dimension that you have to bother with when rebuilding your cubes.
1. Calendar with Year, Quarter, Month, Day (Week omitted because it doesn't roll up into months evenly).
2. Fiscal with Year, Quarter, Period, Week, Day. In this case the weeks would have to roll up evenly into the Periods. Do something like MDXer suggested 5-4-4 or 4-4-5.
I would create the Time Table Keys so that I didn't have to alter it again. Once less dimension that you have to bother with when rebuilding your cubes.
- Status