Introduction to MSSQL Server Analysis Services: Named Sets Revisited - Page 5

Create a Named Set within an Analysis Services 2005 Cube

When we create a Named Set in an Analysis Services cube, we are actually creating a definition for storage within the cube's structure, as we have already discovered. In this section, we will get a feel for the steps in defining Named Sets within the BI Development Studio.

Let's open the Adventure Works cube, within the clone Analysis Services database we have created, and begin our practice with Named Sets.

The Cube Designer, one of several specialized variants within the BI Intelligence Development Studio (among which this is a member of the Analysis Services project subset) opens, defaulted to the Cube Structure tab. For purposes of this article, we will be working largely within the Calculations tab, where Named Sets are conveniently constructed and maintained.

The Calculations tab loads.

The Script Organizer serves as a place to list Calculations (which include Calculated Members, Named Sets, and other Script Commands). The relative positioning of a given calculation within the Script Organizer pane determines its order of execution. We can place the calculation where we want it from the moment of its creation, or we can create and move it at any time using the Move Up or Move Down buttons that are accessible, as shown in Illustration 21, via the arrow buttons on the Calculations tab toolbar, or via the context menu that appears when we right-click a calculation of interest within the Script Organizer.

Clicking New Named Set on the context menu earlier has opened a blank Calculations form to the right of the Script Organizer pane. It is here that we will define new Calculations, and where we will define our new Named Set within the steps that follow.

[Non-Hardware Subcategories]

DRILLDOWNMEMBER( {
 [Product].[Product Categories].[Category].[Accessories], 
    [Product].[Product Categories].[Category].[Clothing]},
              {[Product].[Product Categories].[Category], 
           [Product].[Product Categories].[Category].[Clothing].Children }
              )

The Calculations form, with our input, appears as depicted in Illustration 22.

For purposes of illustration, we are creating a Named Set that might answer the need of organizational information consumers who, say, wish to present values for a given measure or measures for Product Categories that lie outside the realm of the "hardware" (primarily Bikes and Components) within the AdventureWorks product offerings. The net effect, from a presentation perspective, will be to present, within Reporting Services, the summarized sales for Accessories and Clothing, among other options.

NOTE: For detailed information on the MDX DRILLDOWNMEMBER() function, see Set Functions: The DRILLDOWNMEMBER() Function, a member of my monthly MDX Essentials series at Database Journal.

We might have performed drag and drop from the Metadata tab (a source of the metadata components we might use within expression construction), or from the Functions tab (a source of various MDX and other functions we might leverage in building our expressions) within the Calculation Tools pane, which lies directly beneath the Script Organizer pane. Let's take a look at a couple of examples of how this might be done within the construction of the expression we have already input.

The Metadata tab, with our expansions, appears as partially shown in Illustration 23.

As an example of how we might use the Metadata tab (had we not already typed in the MDX syntax above), we could, at this stage, select, drag and drop the Accessories and Clothing categories at this point to the Expression box of the Calculations form. There, when we drop the respective items, the syntax for each would be substituted in the Expression box, alleviating the need for much of the typing.

We can similarly select functions we use in our expressions. Let's look at an example.

The Functions tab, with our expansion, appears as depicted in Illustration 24.

We might use the Function tab, in a manner similar to the way we used the Metadata tab earlier, by selecting, dragging and dropping the DRILLDOWNMEMBER function, in the specific Named Set example above, to the Expression box of the Calculations form – although we would probably do so before dragging in the members to which we applied the function. Once we dropped the function, its syntax would be substituted in the Expression box, alleviating the need for typing the function. We could then either drag and drop, or type, the Product Category members into the parentheses of the DRILLDOWNMEMBER() function (the parentheses would appear when we dropped the function into the Expression box) to approach completion of our expression.

Whatever means we have used to assemble our new Named Set, this is a good time to check the syntax.

If the expression syntax matches exactly that which I supplied above, a Check Syntax message box appears, indicating that the syntax check was successful, as depicted in Illustration 26.

Having defined our new Named Set, we are ready to verify its operation in the section that follows.