Displaying random values

Random values are fun, but there's something more to them. They are great for learning how the SSAS engine evaluates a query and its parts. Even if you're not interested in getting random values, you'll still benefit from reading this recipe by learning important concepts in MDX that you can apply in other situations.

This recipe shows how to get random values in queries that operate on cubes. Subsequent recipes show how to use those random values to sample hierarchy members or to generate interesting reports.

Getting ready

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

In this example, we're going to use the Product dimension. Here's the query we'll start from:

SELECT
    { [Measures].[Sales Amount] } ON 0,
    { [Product].[Product Model Lines].[Model].MEMBERS } ON 1
FROM
    [Adventure Works]

Once executed, the query returns 120 models. Most of the...