August 20, 2007 at 11:49 am
I am in the process of re-designing a group of Analysis Services 2000 cubes and moving them over to AS2005. During this process, I have been examining changing the dimensions from having date fields (Year, Quarter, Month, Date).
In the AS2000 subes, the date fields are in each of the dimensions themselves. Sometimes multiple times (ContractStartDateYear, ContractEndDateYear, etc.). I seem to be drifting toward including the ID as a hidden dimension attribute and then using a referenced dimension relationship to include the date dimension repeatedly.
I seem to get a marginal performance gain querying when I use the single dimension and the reference relationship on the dimensions, but I find myself with the same dimension a lot of times and I wanted to find out if anyone else has run into any major pitfalls doing this.
August 20, 2007 at 11:07 pm
Not sure if when yuo talk of the Referenced Dimension you are talking about Role Playing dimensions, but if not, take a look at these to see if they can help out your situation.
On the inclusion (or not) of dates, (when not requiring anything at a lower grain than 'day') we've typically tended towards using integer keys for the date dimension (so the FK in the fact is also an integer) and using that key as the dim key, setting the display as a different attribute (sometimes a full date string). Using this approach, the one dim can be used multiple times (role playing) where it represents (say) Contract Start Date, Contract End Date, Some Other Time Dim etc.
HTH,
Steve.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply