Hello!

In my project, I got several DateTime fields I need to use as dimension.

For SSAS 2005 I do split them into D1_Year, D1_Month and D1_Day using
"computed/virtual columns" in the data source view with SQL commands
DATEPART(year, D1) and DATEPART(month, D1) and of course DATEPART(day, D1).
Using the "preview data" of the datasource view in project designer, I can
see the date D1 as DateTime column, I see it split apart in its year, month
and day part also.


I do build my dimension on these columns, and a hierarchy to it, and get a
dimension that unfolds like this:

2006
1
1 (that is 1. of January)
2
2
3
4 (this would be 4. of February)

This works well, but I'd prefer to get the months name and the year on the
2nd level of the dimension, and the day, month and year in the 3rd level,
like this:

2006
Jan
1/Jan/06
2/Jan/06
Feb
3/Feb/06
4/Feb/06


To get there I added some more virutal columns to the datasource view, a
D1_FullMonth and D1_FullDay that are calculated by SQL in this way (for day
this is): CONVERT(char(2), DATEPART(day, D1)) + '/' + DATENAME(month, D1) +
'/' + CONVERT(char(4), DATEPART(year, D1))

That also works very nice when I use the "preview data" again. In addition
to the columns mentioned above I do have the ones that are reassembled back
into readable form. I verified that "Month 01" does convert to "January"
several times, but:

I now switched the NameColumn property of the day attribute to use this
column (FullDay) instead of the one that only contains the days' number.
Result is like that:

2006
Jan
1/Feb/06
2/Mar/06
Feb
3/Jun/06
4/Feb/06

The months are completly messed up. I verified that data counted for these
days does really belong to the month it is displayed under, meaning that the
1/Feb/06 for example IS actually 1/Jan/06, just the name is messed up.
Also each month only has the days in it where data exists for - that is also
totally correct, they are displayed with wrong month names.

I verified a couple of times that I did not mix up columns, everything is
correct there. I do not understand what is wrong with my setting. My best
guess is that using the NameColumn property is not what I want to change.

It looks to me as if all 1sts of all months are "the same" when it comes to
determining the name column, all 2nds are the same, all 3rds... and for
every "type" of day with the same number only the month name is used that is
first that is listed in the table, by random or by order of earlier
insertion.

Again, when the data is aggregated, everything works perfect and only data
belonging to a specific day is displayed with it.


Can you make sense of that? How could I fix it?
Sorry this got so long!

Ralf

Ralf,

You need to uniquely identify your keys in your hierarchy to prevent
this problem - it should also be more efficient:

Instead of:

2006
1
1 (that is 1. of January)
2
2
3
4

Set up your keys as:

2006
200601
20060101 (that is 1. of January)
20060102
200602
20060201
20060202

Hope this helps

Vaughan Powell
Information Management Group


Ralf Mayer wrote: