Hello, I have auto generated my time dimension using SSAS 2005's dimension wizard. Now when I use it the month names are out of order, while browsing it's no problem other than annoying but when I use the ParallelPeriod for example, it reports the wrong period back.
One example:
SELECT
(
ParallelPeriod([Dim Time].[YQMD].[MN], 1, [Dim Time].[YQMD].[MN].[September 2006]), [Measures].[Fact Project COST]) ON 0FROM
[BIDM]Returns the month July!!, and if I change the lagging-factor to 2 it returns August..
How do I order the hierarchy levels according to the date key
// Richard
Sorting of the auto generated time dimension, advice please
ShEi
Create two named queries in the data source view, in the time dimension table, that retrives the year number and the month number. If you already have these attributes you skip this step.
Check the TSQL Year-function or the Datepart function.
For the month atttribute in the dimension editor you will have to make a composite key of the year and the month attributes together.
In the sorting property check that the month attribute is sorted by key.
Set the name property of month to what ever you need.
Check also that the level type(properties) for month is month. Also check year and quarters in the same way.
HTH
Thomas Ivarsson
Fusion54
ChrisSmith
OK. You right click on the table in the datasource view(check the solution explorer) where you want to add a column. I used the wrong name yesterday. You use "new named calculation".
The remaining steps are done in the dimension editor and the properties pane, to the right.
If you get a message about redundant attribute relations this normally means that you have a user hierarchy, the window in the middle of the dimension editor. The attribute relations, that you can see of you expand a level in the hierarchy, here means that you can remove attribute relations with the dimension key(the edit window to the left) because they are already in your user hierarchy.
HTH
Thomas Ivarsson
sunny123
Well, I solved it now with the add business intelligence wizard, I added an ordering relation to the primary key, which is a timestamp to the Month column. This fixes the problem right now. I get a warning though:
This dimension contains one or more redundant attribute relationships. These relationships may prevent data from being aggregated when a non-key attribute is used as a granularity attribute in a cube. Verify the following relationships and delete those that are not needed: [Date] -> [Ordering MN on dimTime PK_Date]
But is that really a problem Since my granularity attribute is the key which is a timestamp
// Richard