Displaying a sample from a random hierarchy

In the previous recipe you learned how to generate a random sample from a predefined hierarchy. In this recipe you'll learn how to generate a sample from a random hierarchy. The idea is to have an ever-changing report that shows the top 10 members of a random hierarchy from the pool of all hierarchies in the cube. The benefit is that we can just make a single report and be able to reuse it over and over again. All that is required is a mechanism to refresh the top 10 members from the randomly selected hierarchy.

Apart from its practical use, this recipe is also interesting from a technical point of view. It shows how to operate with hierarchies, how to navigate them, and get information about them, all without actually specifying any of the cube's hierarchy, other than measures. Even if you don't plan on implementing it, it can be helpful to understand the methods used.

Getting ready

Start SQL Server Management Studio and connect to your SSAS 2012...