SQL Server Performance Forum – Threads Archive

Wrong Chronological ordering – MDX help

Hi all I am having quite a frustrating problem. I have an MDX statement that looks like this: SELECT { Measures.members } on Columns ,
NON EMPTY {{[Business].[User].[Business Entity Id].[1583].Children}
* {[Time].[Calendar].[2002].[Quarter 2].[May]:
[Time].[Calendar].[2004].[Quarter 3].[July]}} on Rows
FROM UsageStats_Phase1 It will produce a report (using the Matrix report in .NET) that will show me all the users within Business Entity ID 1583 listed down along the left-hand side of the report, then along the top of the report it shows all the months specified within the range in the MDX statement. The actual grid of the report shows the amount of visits a user had to a website grouped according to the months, i.e. ————————————-
…………..|..2002…|..2003..|TOT|
…………..|Nov|Dec|Jan|Feb|……|
Bob Smith |_3_|_2_|_1_|_6_|…..|
Jon Smith |_4_|_8_|_3_|_3_|…..|
Hal Smith |_6_|_1_|_1_|_2_|…..|
————————————- My Time dimension for the report above is Year, Quarter,Month, Day.
The problem is that, when running the report from .NET Reporting Services, the dates are not coming out in correct chronological order! All months are showing up in the correct years, but the months aren’t ordered correctly within the years. So March may come before January, or December may show up before November. I have tried updating the Matrix report so that is also includes a field for Quarter, and not just for Year and Month (as in the report above). This has made a slight improvement, but there is still the odd month showing up in the wrong order. This makes me think the problem will need to be solved in the actual MDX statement. Does anyone have any ideas or suggestions as to how to order this correctly? Any little suggestion will be most appreciated!!! Thanks in advance,
Maria ***************

I dont think it is a problem with your MDX statement, it might be that your report is sorting by month and it is sorting alphabetically. Also in AS are you sorting by Dimension member names or Keys? Raulie
Hewlett-Packard Company

Hi Raulie, Thanks for your reply. I have just been looking at the report and what it is doing is pushing all months which do not have any hits to the end of the year. so, if, for 2002, February and March do not have any hits, the ordering of the months will be: January,April,May,June,July,August,September,October,November,December, February,March. and this ordering will be determined by the user appearing in the first row in the report. so in my example above, the first user didn’t visit the site in Feb and March, thus there weren’t any hits for that month. but his activity will determine the order of the columns for all other users within the report. so the ordering doesn’t seem alphabetical. it is based on whether there is data in that month or not. I tried removing the user in the first row and reran the report. the effect this had is that the order of the months changed to reflect the data of the next person on the list, so it is definitely going according to this. "Also in AS are you sorting by Dimension member names or Keys?" I am sorting by keys. i have a time dimension table that has a DateID, which is in YYYYMMDD as an integer. it also has these columns: Logdate datetime, YearName varchar, YearKey int, QtrName varchar, QtrKey int, MonthName varchar, MonthKey int, Day int So in my dimension editor in AS i have set each of the members (Year, quarter, month, Day) to be sorted by key. Am I missing something? Maybe the use of a "Sort" in the MDX statement. unfortunatetly i’m a newbie at all this so I can’t get my head around how to rearrange the mdx [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] Any ideas on where to go from here? thanks Maria

Is your time dimension Levels/members being derived from the fact table or are they in a seperate time dimension table?
Raulie
Hewlett-Packard Company

they are in a seperate table of their own. I have a central fact table called Hits which consists of LogDateID,UserAccountID,BusinessEntityID, and HitID it is set up as a star-schema. the dimension tables are LogDate, BusinessEntity, and UserAccount. all the date keys are in the LogDate table and this is where the dimension levelsmembers are being derived from. I do have a PreWarehouse database in which all the scrubbing takes place. in this database the Hits table does actually contain a datetime LogDate column. but the final Warehouse Hits fact table just consists of ID’s as integers… Thanks
Maria

does any1 have the answer to this query cos i have teh same problem!!!!

]]>