August 11, 2014 at 11:53 am
I'm using my own date dimension instead of the MS generated date dimension. I've got a table structure like this:
CREATE TABLE [dbo].[DateDim]
(
[DateID] [int] NOT NULL, --PK
[FullDate] [date] NOT NULL,
[Year] [smallint] NOT NULL,
[QuarterOfYear] [tinyint] NOT NULL,
[MonthOfYear] [tinyint] NOT NULL,
[DayOfMonth] [tinyint] NOT NULL,
)
Hierarchy created: Year, Quarter, Month, Day of Month
Relationships: DateDim->DayOfMonth->Month->Quarter->Year
Composite keys created for
DayOfMonth: Year, Month, DayOfMonth;
Month: Year, Month;
Quarter: Year, Quarter;
However, after creating and processing this, I get Year, Quarter, Month, and Day, but the FullDate associated with each seems to only be associated at the "Day of Month" level. The actual date has no bearing on the hierarchy. I know I must be missing something along the way, but not sure what I'm doing wrong.
Any pointers on how to set this up properly without creating redundant relationships or cloning columns? (I know the former is improper)
August 12, 2014 at 3:08 am
You need to include quarter in your composite keys, I would say, since it's part of the hierarchy. I have just created this dimension as a test and do not see any issues. If the FullDate is not in the hierarchy but is still used against singular years, quarters etc. then it will need to have composite keys too.
August 12, 2014 at 8:23 am
Well, I recreated the dimension completely and this time it worked. I don't know what I did differently, but I did mess around with the relationships quite a bit the first time. I also limited the attributes in the second attempt to the bare minimum, though I don't think that mattered. After creating the new dimension and processing it, the full date to hierarchy matched again. I'd like to know where I went wrong the first time, but not enough to try to make it happen again. I'd much rather just do it right each time. 🙂
For anyone reading in the future, my composite keys were:
Year/Month for Month
Year/Month/DayOfMonth for Day Of Month
Year/Quarter for Quarter
Names/Values set appropriately when I needed a composite key.
Relationship was set up as: Dimension -> Full Date -> Day of Month -> Month -> Quarter -> Year
August 12, 2014 at 1:19 pm
I wouldn't use composite keys at all ever. In this case, each month gets one field with a number corresponding to its place in the calendar and another field with its generic name (eg 1/January). The number serves as the key, the name as the caption. There are two more fields that identify the month and quarter uniquely within the database (eg 1/January 06; 14/February 07; 27/March 08). Again, the number serves as the key, the name as the name the users see.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply