SQL Server Performance

Sorting MDX

Discussion in 'Analysis Services/Data Warehousing' started by stuck1234, Mar 4, 2005.

  1. stuck1234 New Member

    I am aware there is an ORder function to sorting data using three parameters: the set of members to oredr, value to sort by, and sort order....

    is there any function for me to sort the date, would this work??

    SELECT {[Measures].Members } ON COLUMNS,
    { Order ({[Accounting Date]. Members } ,( [Accounting Date].[Account Year]) , BASC ) } ON ROWS

    FROM
    [Accounts Sales]

    please help...im quite new to MDX..... so bit clueless!
  2. Vania New Member

    Maybe you should better clarify how your dimension is built and how you would like to sort...
  3. stuck1234 New Member

    sorry for being so unclear....

    I have a query that displays measurses such as VAT on the column against dates, the date dimension is made up of Year, Quarter Month, Week No.
    The year and quarter are fine but some months do not appear in the correct order eg feb comes befor jan, and so the weeks are not in correct order either. How do I correct this??
  4. Vania New Member

    Has the Month attribute both a key and a name, different from key? Have you set the Order property of the attribute on the key?
  5. stuck1234 New Member

    sorry im a bit new to this and dont no too much about keys....

    the order by has been set to Name, and Month is a member name same as Year, Quarter, Week No and day

    I have set it wrong/ shoul dit be sorted by keys???
  6. Vania New Member

    If you have, for instance, for the month attribute:
    Key | Name
    01 | January
    02 | February
    03 |March
    ...
    Of course you should order by key
  7. stuck1234 New Member

    i think I understand what ur sayin, so if I had the data sorted by key ( like in SQL tables u often have a primary key)... then I could sort the data by the key, correct???

    Problem is, the Date dimension has Year, Quarter, Month, Week No, Day... the user has 4 options, they can either see teh data on yearly basis, or monthly basis, or month basic, or week basic... how can I correct a order function to order the data depending on what the user chooses??? each table would have a different primary key rite....

    When writing the Order function, I have three parameters: what I want to sort, order on what, and on what order... so would that be the date dimension, maybe month, in asc key order??? eg

    SELECT {[Measures].Member} ON COLUMNS,
    {Order ([Date].Members, [Date].[Month], ASC ) } ON ROWS
    .....

    would something like this work..... currently (without any sort) all of the data is in correct order, apart some months, and hence the weeks are wrong....

    HOPE U CAN HELP
    (a thread had been started with the samme problem by someone else but they dont explain if they got it worked out!!!)

    THANKS
  8. Vania New Member

    If you have several levels/attributes for a dimension (like month, week, and so on), you can set the order property in the "Advanced properties" for each level. Further, you have a member key and a member name for each level.
    I suggest you to buy a good book teaching using Analysis Services!
  9. stuck1234 New Member

    yeah i plan to buy 1...ta!!

    u got any suggestions for any good books,
    thanks!!!
  10. Vania New Member

    Microsoft Offical Course book, named "Designing and Implementing OLAP Solutions with Microsoft SQL Server 2000" is very good. Don't know if it can be bought without participating to the course. You should try to discover it.
  11. Raulie New Member

    The Analysis Services 2000 Ops guide and documentation on MSDN is also helpful.

    Raulie
    @hp

    All postings are provided “AS IS” with no warranties for accuracy.
  12. Joozh New Member

    Hi ALL,

    Please do share if you are aware of a place from where the book suggested by Vania is available.


    Hi Raulie,

    Can you kindly share the URL from where I can go through the
    'The Analysis Services 2000 Ops guide'


    Thanks.

Share This Page