MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition

Identifying the best/worst members for each member of another hierarchy

Sales territory country and reseller are two different hierarchies in Adventure Works DW. To analyze sales, we might choose not to look at every reseller in every country. Instead, we often only need to look for the top or bottom N resellers per country.

In this case, we can view the country as the outer hierarchy, and the reseller as the inner hierarchy. Quite often, we need to analyze the combination of hierarchies in a way that the top or bottom N members of the inner hierarchy are displayed for each member of the outer hierarchy.

Displaying only the top or bottom N members of the inner hierarchy for each member of the outer hierarchy is sort of a report reduction, where ...