locked
subset function in mdx query RRS feed

  • Question

  • HI,

    I need other function act like subset function using mdxquery.

    In my mdxqueries,contain subset,order functions are avilable.

    subset and order is getting poor query performance.

    Is there any solution for that?

    please help me.


    indu

    Monday, May 18, 2015 12:37 PM

Answers

All replies

  • Hi Sriindu,

    According to your description, you want to use other functions to replace subset() and order() function for improving query performance. Right?

    In MDX, a good workaround is using TopCount()/BottomCount() function to replace subset() and order(). Since it doesn't need to query all set and order all records, it can improve the performance a lot. Please refer to links below:

    https://msdn.microsoft.com/en-us/library/ms144792.aspx

    https://msdn.microsoft.com/en-us/library/ms144864.aspx

    http://blog.crossjoin.co.uk/2013/02/09/topcounts-with-ties-in-mdx/

    Regards,


    Simon Hou
    TechNet Community Support




    Tuesday, May 19, 2015 9:33 AM
  • HI Simon,

    thank you for reply.

    In my requirement,i need starting 1200 record,then 30 records.

    so i gave subset(expression,1200,30)

    that is the reason i took subset.

    topcount retrive top records only.

    but how it will work "topcount"?

    so any suitable function like subset for my requirement.

    could you please give me your suggestion.


    indu


    • Edited by Sriindu Tuesday, May 19, 2015 11:57 AM
    Tuesday, May 19, 2015 11:53 AM
  • Hi,

    maybe this helps:

    {{SetExpression}.Item(1200):{SetExpression}.Item(1230)}

    or

    {{SetExpression}.Item(1200):{SetExpression}.Item(1200).Lag(-30)}

    Tuesday, May 19, 2015 12:23 PM
  • Hi,

    I tried your solution. but that resultset and previous resultset as what  i am using with order and subset are not matched.

    that is not suitable for my requirement.

    could you please give me exact solution.


    indu


    • Edited by Sriindu Wednesday, May 20, 2015 11:01 AM
    • Proposed as answer by SureshArumugham Tuesday, July 7, 2015 1:27 PM
    • Unproposed as answer by SureshArumugham Tuesday, July 7, 2015 1:27 PM
    Tuesday, May 19, 2015 12:57 PM
  • Hi Sriindu,

    could you share your code or replicate your code in advWork.

    Regards,

    Manish



    Friday, July 3, 2015 5:35 AM
  • Hi Sriindu,

    try the below code:


    SELECT {[Measures].[Reseller Gross Profit]} on 0,

    nonempty(
    (nonempty([Product].[Product Categories].[SubCategory].members,[Measures].[Reseller Gross Profit]).item(0)):
    (nonempty([Product].[Product Categories].[SubCategory].members,[Measures].[Reseller Gross Profit]).item(5))
    ,[Measures].[Reseller Gross Profit])
    ON 1
    FROM [Adventure Works]


    Thanks and Regards Rajesh

    Friday, July 3, 2015 8:52 AM
  • Hi Indu,

    Reply by yger might work. Could you give your expression or the dimension that you are trying to do a subset on?

    Regards,

    Suresh

    Tuesday, July 7, 2015 1:28 PM
  • thanks simon.

    topcount and bottomcount is workingfine.


    indu

    Wednesday, July 8, 2015 5:33 AM