Displaying a random sample of hierarchy members

In the previous recipe you learned how to generate random values per row. This recipe teaches you how to take it one step further and use those random values to sample the data.

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 sample the Product dimension, the same one used in the previous recipe. To remind you, this was the query we started from:

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

It returned 120 models. Now let's see how to get a sample from that pool.

How to do it...

Follow these steps to display a random sample of hierarchy members:

1. Add the WITH part of the query.

2. Create the H on axis calculated set using the set on rows:

   SET [H on axis] AS
       { [Product].[Product Model Lines].[Model].MEMBERS...