dcsimg

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

December 12, 2005

Verification

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.

Conclusion

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.

Introduction to MSSQL Server Analysis Services Series
Introduction to Security in Analysis Services
Cube Storage: Planning Partitions from a SQL Server Management Studio Perspective
Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective)
Cube Storage: Introduction to Partitions
Introduction to Cube Storage
Attribute Discretization: Customize Grouping Names
Attribute Discretization: Using the "Clusters" Method
Attribute Discretization: Using the "Equal Areas" Method
Attribute Discretization: Using the Automatic Method
Introduction to Attribute Discretization
More Exposure to Settings and Properties in Analysis Services Attribute Relationships
Attribute Relationships: Settings and Properties
Introduction to Attribute Relationships in MSSQL Server Analysis Services
Attribute Member Values in Analysis Services
MSSQL Analysis Services - Attribute Member Names
Attribute Member Keys - Pt II: Composite Keys
Attribute Member Keys - Pt 1: Introduction and Simple Keys
Dimension Attributes: Introduction and Overview, Part V
Dimension Attributes: Introduction and Overview, Part IV
Dimension Attributes: Introduction and Overview, Part III
Dimension Attributes: Introduction and Overview, Part II
Dimension Attributes: Introduction and Overview, Part I
Dimensional Model Components: Dimensions Part II
Dimensional Model Components: Dimensions Part I
Manage Unknown Members in Analysis Services 2005, Part II
Manage Unknown Members in Analysis Services 2005, Part I
Alternatively Sorting Attribute Members in Analysis Services 2005
Introduction to Linked Objects in Analysis Services 2005
Distinct Counts in Analysis Services 2005
Positing the Intelligence: Conditional Formatting in the Analysis Services Layer
Administration and Optimization: SQL Server Profiler for Analysis Services Queries
Mastering Enterprise BI: Time Intelligence Pt. II
Mastering Enterprise BI: Time Intelligence Pt. I
Design and Documentation: Introducing the Visio 2007 PivotDiagram
Actions in Analysis Services 2005: The URL Action
Actions in Analysis Services 2005: The Drillthrough Action
Mastering Enterprise BI: Introducing Actions in Analysis Services 2005
Mastering Enterprise BI: Introduction to Translations
Mastering Enterprise BI: Introduction to Perspectives
Introduction to the Analysis Services 2005 Query Log
Mastering Enterprise BI: Working with Measure Groups
Mastering Enterprise BI: Introduction to Key Performance Indicators
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. II
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. I
Process Analysis Services Objects with Integration Services
Usage-Based Optimization in Analysis Services 2005
Introduction to MSSQL Server Analysis Services: Named Sets Revisited
Introduction to MSSQL Server Analysis Services: Migrating an Analysis Services 2000 Database to Analysis Services 2005
Introduction to MSSQL Server Analysis Services: Introducing Data Source Views
Introduction to MSSQL Server Analysis Services: Reporting Options for Analysis Services Cubes: MS Excel 2003 and More ...
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Create Aging "Buckets" in a Cube
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part II
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube
Introduction to MSSQL Server Analysis Services: Process Analysis Services Cubes with DTS
Introduction to MSSQL Server Analysis Services: Presentation Nuances: CrossTab View - Same Dimension
Introduction to MSSQL Server Analysis Services: Point-and-Click Cube Schema Simplification
Introduction to MSSQL Server 2000 Analysis Services: Manage Distinct Count with a Virtual Cube
Introduction to MSSQL Server 2000 Analysis Services: Distinct Count Basics: Two Perspectives
Introduction to MSSQL Server 2000 Analysis Services: Semi-Additive Measures and Periodic Balances
Introduction to MSSQL Server 2000 Analysis Services: Performing Incremental Cube Updates - An Introduction
Introduction to MSSQL Server 2000 Analysis Services: Partitioning a Cube in Analysis Services - An Introduction
Introduction to MSSQL Server 2000 Analysis Services: Basic Storage Design
Introduction to MSSQL Server 2000 Analysis Services: Derived Measures vs. Calculated Measures
Introduction to MSSQL Server 2000 Analysis Services: Creating a Dynamic Default Member
Introduction to MSSQL Server 2000 Analysis Services: Another Approach to Local Cube Design and Creation
Introduction to MSSQL Server 2000 Analysis Services: Introduction to Local Cubes
Introduction to MSSQL Server 2000 Analysis Services: Actions in Virtual Cubes
Introduction to MSSQL Server 2000 Analysis Services: Putting Actions to Work in Regular Cubes
Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Part II
Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Professional, Part I
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services , Part II
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services, Part I
Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Toward More Sophisticated Analysis
Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Simple Cube Usage Analysis
Introduction to MSSQL Server 2000 Analysis Services: Build a Web Site Traffic Analysis Cube: Part II
Build a Web Site Traffic Analysis Cube: Part I
Reporting Options for Analysis Services Cubes: Cognos PowerPlay
Reporting Options for Analysis Services Cubes: MS FrontPage 2002
Reporting Options for Analysis Services Cubes: MS Excel 2002
Introduction to MSSQL Server 2000 Analysis Services: Drilling Through to Details: From Two Perspectives
Introduction to MSSQL Server 2000 Analysis Services: Custom Cubes: Financial Reporting - Part II
Introduction to MSSQL Server 2000 Analysis Services Custom Cubes: Financial Reporting (Part I)
Introduction to SQL Server 2000 Analysis Services: Exploring Virtual Cubes
Introduction to SQL Server 2000 Analysis Services: Working with the Cube Editor
Introduction to SQL Server 2000 Analysis Services: Parent-Child Dimensions
Introduction to SQL Server 2000 Analysis Services: Handling Time Dimensions
Introduction to SQL Server 2000 Analysis Services: Working with Dimensions
Introduction to SQL Server 2000 Analysis Services: Creating Our First Cube








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers