Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 12, 2005

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

By William Pearson


Create Named Sets within an Analysis Services 2005 Cube

Perhaps the easiest way to monitor the success of handiwork, from our current position in the BI Development Studio, will be to browse the Adventure Works cube through the use of our new Non-Hardware Subcategories Named Set.

1.  Select Build --> Process from the main menu, as shown in Illustration 27.

Click for larger image

Illustration 27: Select Build --> Process ...

The Process Cube – Adventure Works dialog appears, with the Adventure Works cube appearing in the Object list in the upper half, defaulted to Full Process, as depicted in Illustration 28.

Click for larger image

Illustration 28: The Process Cube – Adventure Works Dialog

2.  Click Run, in the bottom left corner of the dialog, to begin processing.

The Process Progress viewer appears, with the various events of the processing cycle displayed as they are accomplished. Once processing finishes, we see a Process succeeded message appear in the Status bar at the bottom of the Process Progress viewer, as shown in Illustration 29.

Click for larger image

Illustration 29: Processing Completes Successfully ...

3.  Click Close to dismiss the Process Progress viewer.

4.  Click Close to dismiss the Process Cube – Adventure Works dialog.

We return to the Cube Designer, where we left to process the cube.

5.  Click the Browser tab, as depicted in Illustration 30.

Illustration 30: Click the Browser Tab ...

6.  Click the Reconnect button, in the toolbar just underneath the Cube Structure and Dimension Usage tabs atop the Cube Designer, as shown in Illustration 31.

Illustration 31: Click the Reconnect Button ...

7.  In the Metadata pane (left side of the Browser tab), expand the Measures group to expose the various underlying folders.

8.  Expand the Internet Sales folder, exposing the underlying measures and calculations.

9.  Drag the Internet Sales Amount measure to the data area, and drop it into the section marked Drop Totals or Detail Fields Here, as depicted in Illustration 32.

Illustration 32: Dragging the Measure to the Data Area ...

10.  In the Metadata pane, expand the Product dimension to expose the various underlying objects.

11.  Right-click the Product Categories hierarchy.

12.  Select Add to Row Area from the context menu that appears, as shown in Illustration 33.

Illustration 33: Adding Product Categories to the Row Area of the Browser ...

We see that Product Category occupies the row axis of the browser, as depicted in Illustration 34.

Illustration 34: Product Category Appears in the Row Axis

13.  Click the downward arrow on the Category heading of the row axis, to expand the selector.

The selector opens, wherein we see all selections checked.

14.  Expand each of the Categories to display its underlying children, as shown in Illustration 35.

Illustration 35: All Categories Expanded ...

15.  Click OK to accept the selection, and to close the Category selector.

At this point, we see all Product Categories, in rolled-up state. Let's drill into the underlying children of the Categories at this point.

16.  Expand each of the Categories in the browser by clicking the plus ("+") sign to its immediate left.

The data now appears drilled down to the Subcategory level, as depicted in Illustration 36.

Illustration 36: Browser View – Drilled Down to Product Subcategories

17.  In the Metadata pane, within the Product dimension, right click the new Non-Hardware Subcategories Named Set.

18.  Select Add to Subcube Area from the context menu that appears, as shown in Illustration 37.

Illustration 37: Adding the New Named Set to the Subcube Area

The new Named Set now appears within the Filter pane, just above the Data pane of the Browser tab. The resulting view within the Data pane appears (along with the Filter pane) as depicted in Illustration 38.

Illustration 38: The Effect of Adding the New Named Set as a Filter ...

We obtain these results because we are applying our new Named Set, Non-Hardware Subcategories, as a filter to our existing cube, and are therefore defining a subcube. This is one example of a use for a Named Set; it allows us to confirm that the Named Set operates as we expected. Within the data presented, we now see only the Accessories and Clothing categories, which we defined within the Named Set.

19.  Select File --> Save All to save our work in the foregoing steps.

20.  Select File --> Exit to leave the BI Development Studio, when ready.

And so, we see that, while the related concepts are similar between Analysis Services 2005 and its predecessor, the process of defining Named Sets in Analysis Services 2005 is different. There are also other approaches to doing so, which we will examine within relevant contexts we explore in prospective articles, along with myriad possibilities for leveraging the power of Named Sets to support business intelligence in our daily environments.


In this article, we examined Named Sets in the Analysis Services 2005 environment, discussing the concepts surrounding their use and ways that we can leverage them within Analysis Services for analysis and reporting purposes. After our introductory examination, we prepared for a hands-on session by creating a clone Analysis Services Database in SQL Server Management Studio to provide an environment whereby we could work while preserving the original Adventure Works DW sample database for use within the tutorials and other exercises that ship with MSSQL Server 2005 and its component applications.

Once we had created a clone Analysis Services Database, we accessed it in Business Intelligence Development Studio and began the procedures of our practice exercise. We created a Named Set within an Analysis Services 2005 cube, based upon a straightforward business requirement, discussing the steps involved and other considerations. We then verified the operation of our new Named Set by performing a browse of the cube.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM