O'Reilly logo

MDX with Microsoft SQL Server 2008 R2 Analysis Services: Cookbook by Tomislav Piasevoli

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

Quite often it is necessary 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. Again, it's sort of a report reduction where the important combinations of members are preserved and the rest of the cross-join is left out.

This recipe shows how to create a TopCount() calculation relative to another hierarchy.

Getting ready

Start SQL Server Management Studio and connect to your SSAS 2008 R2 instance. Click on the New Query button and check that the target database is Adventure Works DW 2008R2.

In this example we're going to use the Sales Territory dimension and the Reseller ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required