Isolating the best N members in a set
Hierarchies can contain a lot of members. In this recipe, we are going to show you how to extract only the significant members: ones with the highest value for a certain measure.
This requirement is often necessary because not only does it allow end users to focus their efforts on a smaller set of members, but it also makes the queries much faster.
We will base our example on the TopCount()
function, a function that returns the exact number of members as specified. In addition to that function, MDX has two more similar functions, namely TopPercent()
and TopSum()
. Contrary to the TopCount()
function, these functions return an unknown number of members. In other words, they are designed to return a set of members ...
Get MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition now with the O’Reilly learning platform.
O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.