Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSAS - Date Dimension ties wrong dates to hierarchy Expand / Collapse
Author
Message
Posted Monday, August 11, 2014 11:53 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 846, Visits: 1,309
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)



Post #1602005
Posted Tuesday, August 12, 2014 3:08 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 564, Visits: 1,092
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
Post #1602196
Posted Tuesday, August 12, 2014 8:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 846, Visits: 1,309
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



Post #1602303
Posted Tuesday, August 12, 2014 1:19 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 818, Visits: 2,067
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.


Post #1602443
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse