SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSAS - Date Dimension ties wrong dates to hierarchy


SSAS - Date Dimension ties wrong dates to hierarchy

Author
Message
Peter Schott
Peter Schott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1919
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)
PB_BI
PB_BI
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1977 Visits: 2518
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.




I'm on LinkedIn
Peter Schott
Peter Schott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1919
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. Smile

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
RonKyle
RonKyle
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2768 Visits: 3501
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search