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 5

By William Pearson

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.

1.  Right-click the Adventure Works cube in the Solution Explorer (within the Cubes folder of the tree).

2.  Select Open from the context menu that appears, as shown in Illustration 17.

Click for larger image

Illustration 17: Open the Adventure Works Cube ...

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.

NOTE: I perform in-depth examinations of many of the specific capabilities and features of the SQL Server Business Intelligence Studio (which provides significant support in helping us to work with Analysis Services projects, and to integrate Analysis Services projects with Reporting Services and Integration Services), as well as the SQL Server Management Studio, in other articles of my series' here at Database Journal. In the interest of time and focus, we will limit our discussion in this article to relevant portions of this highly evolved development environment. For more on the features of each of the studios, I invite you to visit my series index page.

3.  Click the Calculations tab atop the Cube Designer, as depicted in Illustration 18.

Illustration 18: Switching to the Calculations Tab ...

The Calculations tab loads.

4.  If necessary, click Form View, whose icon appears just beneath the Calculations tab label, as shown in Illustration 19.

Illustration 19: Click Form View, If Necessary ...

5.  Right-click the [High Discount Promotions] Named Set in the Script Organizer pane (upper left corner of the Calculations tab).

6.  Select New Named Set from the context menu that appears as depicted in Illustration 20.

Illustration 20: Selecting an Entry Point ... for New Named Set Creation

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.

Illustration 21: Moving a Calculation Changes Its Execution Order ...

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.

7.  Type the following into the Name field of the Calculations form.

[Non-Hardware Subcategories]

8.  Type (or cut and paste) the following MDX syntax into the Expression box underneath the Name box.

 [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.

Illustration 22: The Calculation Form for the New Named Set

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.

9.  On the Metadata tab of the Calculation Tools pane, expand the Product dimension by clicking the "+" sign to its immediate left.

10.  Expand the Product Categories hierarchy.

11.  Expand the Category level of the hierarchy.

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

Illustration 23: The Metadata Tab (Partial View) with Our Expansions ...

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.

12.  Click the Functions tab of the Calculation Tools pane.

13.  Expand the UI folder by clicking the "+" sign to its immediate left.

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

Illustration 24: The Functions Tab with Our Expansion ...

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.

14.  Click the Check Syntax button atop the Calculations tab (immediately under and to the right of the Calculations label on the tab itself), as shown in Illustration 25.

Illustration 25: The Check Syntax Button

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.

Illustration 26: We Receive Indication of a Successful Syntax Check

15.  Click OK to close the Check Syntax message box.

16.  Select File --> Save All to save the Project with our work to this point.

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

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