Yesterday I received the task to change the order of our time dimension from ASC to DESC. As easy it is in SQL that difficult it is in AS. Or so it seemed. First I tried looking for a solution among the properties. After finding nothing I began thinking for sg in the DW or in the DSW.
I don't write down every detail of tryings only the solution (which is basically not mine but I found on the net somewhere):
I created a computed column in the source DW table with this SQL: DATEDIFF(day, MyDate, '2100.01.01') This gives back an integer number with the number of days being left from MyDate to 2100.01.01. And that is enough because the latest date gets the lowest number thus it becomes the first member...
Afterwards you only need to refresh your DSV and include the new column as an attribute in your dimension with a relationship to the attribute you want to be ordered and set the OrderBy property to AttributeKey and the OrderByAttribute property to the newly created attribute.
That's all 