TOPN using SSAS MD

[parry2k]

Hello

Here is another interesting question? How we can use TOPN with SSAS MD cube.

Thanks,

P

[fbrossard · Accepted solution]

Hi @parry2k

You should use :

- first of all a technical dimension to list the N value you want for your top N (1..n). Let's call it Top N

declare a default value for your Top N attribute ( for example 10)

- use dynamic set to implement you top depends on your Top N attribute :

CREATE DYNAMIC SET CURRENTCUBE.[Top N Products]
 AS TopCount
    (
       [Product].[Product Name].[Product Name].MEMBERS,
       [Top N].[Top N].currentMember.member_caption,
       [Measures].[Sales Amount]
    ), DISPLAY_FOLDER = 'Named Sets';  

Unfortunately PBI doesn't expose MDX Sets so you have to create a calculated measure to identify in my example each product name existing in your Top N :

CREATE MEMBER CURRENTCUBE.[Measures].[Is Top N Product]
AS NULL;

SCOPE ([Product].[Product Name].[Product Name],[Measures].[Is Top N Product]);
THIS = IIF(COUNT(EXISTING EXISTS([Product].[Product Name].currentMember,[Top N Products])) > 0, 1, NULL);
END SCOPE;

Open you PBI Desktop, connect to you MD Cube in Direct Connect, put your Top N attribute as a slicer, your product name as a table and add a filter on [Is Top N Product] = 1

[parry2k]

Hello,

Thanks for the solution, I tested and it worked fine but finding it very slow. I'm running on test cube with may be around 10000 rows but we have a very large database, around 300-400 millions rows, not sure what will be performance but I will surely test.

Once again thanks for taking time out and putting the solution together.

Thanks,

P

[Phil_Seamark]

Make sure you have aggregations in place to suit the queries you run against this.  Use a combination of SQL Profiler and the OlapQueryLog to extract the binary values that perfectly match your query.

[parry2k]

Hello,

Thanks for the reply and the solution. I'm not very familiar with calculate sets and how to use it in PowerBI but based on your solution I will try it in my environment and let you know the result and/or further question.

Cheers,

P

[Phil_Seamark]

You'd probably have to build those smarts into your MD cube as calculated measures using the RANK and ORDER funtions