Date Dimension Won't Sort!

Question

Date Dimension Won't Sort!

rjames-833409

Valued Member

Points: 57
More actions
December 1, 2010 at 2:58 pm

#388349

I'm a newbie in building cubes and dimensions and am having difficulting fixing a sort issue.
I have a time dimension that won't sort - I've set the dimesion to order by Attribute Key and specified the variable to sort on. The data are stored as datetime.
When I regenerate the dimension it doesn't fix the problem. I've also tried the other order by options with no luck either.
Can anyone shed some light on this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 173045 More actions · Answer 1

I'm sort of new myself, having taken classes a good while ago, but not having had a chance to implement the information learned from them. So what I'm giving you is a half-remembered answer that might not be too clear. I apologize in advance.

The time dimension has an odd, backwards sort of dependence. You might have to reverse the order of the date parts. Day -> Week -> Month -> Year might have to be reversed to Year -> Month -> Week -> Day. ISTR that this part wasn't very intuitive and didn't follow the way most people thought it would follow.

Does that help?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

darth_vodka Default port Points: 1489 More actions · Answer 2

rjames-833409 (12/1/2010)
I'm a newbie in building cubes and dimensions and am having difficulting fixing a sort issue.
I have a time dimension that won't sort - I've set the dimesion to order by Attribute Key and specified the variable to sort on. The data are stored as datetime.
When I regenerate the dimension it doesn't fix the problem. I've also tried the other order by options with no luck either.
Can anyone shed some light on this?

ah, this old chestnut...if i had a tenner for every time April popped up as the first month of the year...

if you've sorted by attribute, well it should work, depends what your attribute is really, is it really datetime or is it varchar?

if in doubt, make sure you have a numerical primary key in your time table, like a julian date (the number of days from 0th jan 1900, what Excel uses) or the old classic YYYMMDD

sort by key and should be fine

use the numerical primary key, but use a nicely formatted attribute as the name

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 3

I have a time dimension that won't sort - I've set the dimesion to order by Attribute Key and specified the variable to sort on. The data are stored as datetime.

More information is needed here to accurately diagnose the issue. What are using using for the attribute key and what do you mean "specified the variable to sort on"?

AjieLeksono SSC Journeyman Points: 95 More actions · Answer 4

You can try the solution from Darren Gosbell

Usually I have a column for MonthName and another for month number using a YYYYMM format that looks something like the following:

MonthName MonthNumber

Jan 2011 201101

Feb 2011 201102

Mar 2011 201103

Then I setup MonthName as a related attribute to MonthNumber and I use MonthNumber as the key for my attribute and the MonthName as the name and then I set the OrderBy property of MonthName to "Key".