Business Intelligence Architect Bill
Pearson introduces Named Sets within the context of MSSQL Server 2005. In this
examination of the basics, we get hands-on practice defining a Named Set within
the Business Intelligence Development Studio.
About the Series ...
article is a member of the series Introduction to MSSQL Server Analysis
Services. The series is designed to provide hands-on application of
the fundamentals of MS SQL Server Analysis Services, with each installment
progressively presenting features and techniques designed to meet specific real
- world needs. For more information on the series, please see my initial
article, Creating Our First
Note: This article examines exciting new features of MSSQL
Server 2005. To
follow along with the steps we undertake, the following components, samples and
tools are recommended, and should be installed according to the respective
documentation that accompanies MSSQL Server 2005:
Server 2005 Database Engine
Server 2005 Analysis Services
Intelligence Development Studio
Server 2005 sample databases
The Analysis Services
Tutorial sample project and other samples, that are available with the
installation of the above.
successfully replicate the steps of the article, you also need to have:
within one of the following:
local group on the Analysis Services computer
role in the instance of Analysis Services.
Read permissions within any SQL
Server 2005 sample databases we access within our practice session, if
Note: Current Service Pack updates are assumed for the operating system, MSSQL
Server 2005 ("MSSQL Server"), MSSQL Server 2005 Analysis
Services ("Analysis Services"), MSSQL Server 2005 Reporting
Services ("Reporting Services") and the related Books
Online and Samples. Images are from a Windows 2003
Server environment, but the steps performed in the articles, together with
the views that result, will be quite similar within any environment that
supports MSSQL Server 2005 and its component applications.
In this lesson, we revisit Named Sets, a subject that
we undertook in my article
Analysis Services: Named Sets in MDX: An Introduction, in March of 2004. There, we introduced Named Sets
from the perspective of the MDX query language, having obtained brief
exposure to the concept of Named Sets earlier in the MDX in Analysis Services series (Using Sets in MDX Queries). We
examined Named Sets as they existed within Analysis Services 2000,
touching upon them from the perspective of Analysis Manager, the Cube
Editor, and related interfaces in Analysis Services.
Sets have changed
somewhat in Analysis Services 2005, but their definition has remained
the same: Named Sets are (MDX)
expressions that return sets, unsurprisingly, which are saved as part of
the definition of a cube in Analysis Services. As we have mentioned before,
Named Sets are created with reuse in mind. Named Sets consist of
one or more tuples. They can be made up of more than one dimension, because a
tuple can consist of one or more dimension members; only one member from each
dimension can be represented per tuple, however, as we have discussed in sessions over the last couple
this article, we will be focusing upon Named Sets that we create within the
MSSQL Server Business Intelligence Development Studio. First, we will
introduce the concepts behind Named Sets, and then we will undertake
illustrative practice examples, based upon a hypothetical business need, to
illustrate the value that Named Sets can offer us. Within the context of
our practice exercise, we will discuss the results we obtain, to reinforce the
subject matter in a way that activates the concepts involved, as well as to
perhaps suggest expanded uses in our own business environments.
Named Sets in Analysis Services
Sets enable the
enterprise to simplify queries, and to provide more useful aliases for complex,
commonly used set expressions and definitions. They are certainly among the
best friends of report developers / authors indeed, enterprise OLAP reporting
tools, such as Crystal Analysis and others rely heavily upon Named
Sets in their translation of the actions of report authors, within their
graphical interfaces, to the MDX ultimately created to retrieve the desired
data from Analysis Services cubes.
illustrative Named Set might be called Top 25 Customers, which
might contain the twenty-five customers, from, say, the Adventure Works cube,
with the highest values for the Sales Amount measure. Once the Named
Set is constructed, we can perform analysis and generate reports upon the Top
25 Customers by simply referencing the Named Set, eliminating the
requirement to provide the qualified names of all twenty-five customers in each
underlying query we assemble to return data about this customer group.
this article, we will:
the concept of Named Sets, and how we can leverage them in Analysis
Services for analysis and reporting purposes;
Create a clone
Analysis Services Database in SQL Server Management Studio, within which to conduct
our practice exercises;
Access the Analysis
Services Database in Business Intelligence Development Studio;
Create a Named
Set within an Analysis Services 2005 cube;
operation of our new Named Set by performing a browse of the cube.
Working with Named Sets in Analysis Services
Overview and Discussion
While the basic concepts
remain the same, the functional value of Named Sets has been extended in
Analysis Services 2005. Not only can Named Sets be used in MDX
queries, including those generated via reporting (such as Reporting
Services) and other applications, but Named Sets can be leveraged to
define sets within subcubes. Subcubes can be used as a means of
economizing cube space, specifically within the context of subsequent
statements, and we will examine them separately in more depth in prospective
articles. Suffice it to say, for now, that, in this area, as well as many
others within Analysis Services 2005, we are afforded more flexibility
and control over various aspects of cube operations. We can leverage Named
Sets definitions to include cube data in combination with functions,
operators and numerical values; their potential is significant for the
Named Sets are useful and reusable. They
allow us the luxury of simple aliases that we can call upon to represent sets
of dimension members. The underlying set expressions can be complex, and the
ability to enclose these expressions within a user-friendly alias means easier
query construction in general, most particularly when the composite expressions
are used on a recurring basis. As we might imagine, well-constructed and
intuitively titled Named Sets can mean less likelihood of error,
especially in the hands of users with limited MDX exposure. They are often
leveraged in enterprise reporting applications, most commonly when "editors"
are used to generate MDX queries.
In addition to acting as
surrogates for sets with which we work, or upon which we otherwise focus,
routinely, Named Sets also provide other advantages. They provide a
means of simplifying queries by allowing us to not only represent complex sets,
as we have noted, with simpler aliases, but also by providing a level of
abstraction in the representation of the logic behind involved set operations,
again benefiting the ultimate consumer through ease of use and less exposure to
error. Finally, Named Sets can mean more efficient query execution
whether defined within the cube structure via Business Intelligence Development
Studio, or through other means, or whether defined within a query that is
executed against a given cube, the Named Set is evaluated once, and the
values it contains are subsequently reused, a circumstance that can be
leveraged to increase overall operating efficiency.
Considerations and Comments
For purposes of the practice
exercises within this series, we will be working with samples that are provided
with MSSQL Server 2005 Analysis Services. These samples include,
predominantly, the Adventure Works DW Analysis Services database (with
member objects). The Adventure Works DW Analysis Services database and companion
samples are not installed by default in MSSQL Server 2005. The samples can
be installed during Setup, or at any time after MSSQL Server has
been installed. The topics "Running Setup to Install AdventureWorks
Sample Databases and Samples" in SQL Server Setup Help or
AdventureWorks Sample Databases and Samples" in the Books Online (both of which are included on
the installation CD(s), and are available from www.Microsoft.com and other sources, as well),
provide guidance on samples installation.
regarding the rights / privileges required to accomplish samples installation,
as well as to access the samples once installed, is included in the references
I have noted.
Let's get some
hands-on practice with Named Sets. To prepare, we will create a new Analysis
Services database, based upon the existing Adventure Works DW sample
database, to insulate the original sample database from modifications we will
make. We will accomplish the creation of the "clone" database from
within the SQL Server Management Studio, and then work with our new
database from the Business Intelligence Development Studio.
a Clone Analysis Services Database in SQL Server Management Studio
We will begin
our preparation within SQL Server Management Studio, where we will create
a clone of the sample Adventure Works DW database, which can be
installed by anyone installing MSSQL Server 2005 Analysis Services.
Click the Start
SQL Server 2005 within the Program group of the menu.
Server Management Studio, as shown in Illustration 1.
The Connect to Server
Services in the Server type selector.
Type / select
the server name / instance, if appropriate) into the Server name
authentication information, as required in your own environment.
The Connect to Server
dialog appears, with the appropriate input for our local environments, similar
to that depicted in Illustration 2.
Click the Connect
button to connect with the specified Analysis Services server.
The SQL Server
Management Studio opens.
Within the Object
Explorer (the leftmost pane of the Studio, by default), expand the server
in which we are working, if necessary, by clicking the "+" sign to
its immediate left.
Expand the Databases
folder that appears underneath the expanded server.
the Adventure Works DW database.
Up... from the context menu that appears, as shown in Illustration 3.
Right-click the Adventure Works DW Database Select Back Up ...
The Backup Database
Adventure Works DW dialog appears.
default name that appears in the Backup file box with the following:
ANSYS042 Adventure Works DW.abf
Uncheck the Apply
compression setting in the Options section.
Uncheck the Encrypt
backup file setting that immediately follows.
The Backup Database
Adventure Works DW dialog appears, as depicted in Illustration 4.
Illustration 4: The
Backup Database Adventure Works DW Dialog Appears
Click OK to
begin the backup.
The Backup Database
Adventure Works DW dialog grays, as the Executing symbol in the Progress
pane (lower left corner of the dialog) becomes active. The process may run
several minutes depending upon the resources available on the local PC. Once
completed, the dialog closes, returning us to the Management Studio.
We will now restore the
same backup under a different name, to create a copy of the existing sample
database a copy wherein we can make modifications without impairing the
existing sample, which we may wish to use to complete tutorials included with MSSQL
Server 2005 or elsewhere.
Within the Object
Explorer, right-click the Databases folder underneath the Adventure
Works DW database.
from the context menu that appears, as shown in Illustration 5.
Right-click the Databases Folder Select Restore ...
The Restore Database dialog
Click the Browse
button to the right of the box (second from the top) labeled From backup
The Locate Database
Files dialog appears.
the following backup file (where we located it in our backup steps above):
ANSYS042 Adventure Works DW.abf
Click the file
within the Select the file window, to place the file name into the File
name box, as depicted in Illustration 6.
Illustration 6: Locate
Database Files Dialog with Our Input ...
to accept the file path / name, and to close the Locate Database Files
We return to the Restore
Database dialog, where we see the file we have selected appear in the From
backup file box.
following into the Restore database box immediately above the From backup
ANSYS042 Adventure Works DW
The Restore Database
dialog, with our input, appears as shown in Illustration 7.
Illustration 7: The
Completed Restore Database Dialog
to initiate the restoration.
The Restore Database dialog
grays, as the Executing symbol in the Progress pane, once again,
becomes active. The process runs, and, once completed, the dialog closes,
returning us to the Management Studio. Here we see the new ANSYS042
Adventure Works DW database appear in the Object Browser, as
depicted in Illustration 8.
Illustration 8: The New
Database Appears ...
NOTE: If the new database does not appear immediately,
right-click the Databases folder and select Refresh from the
context menu that appears, as shown in Illustration 9.
Illustration 9: Refreshing
as Required ...
created the ANSYS042
Adventure Works DW
database, we can
now proceed with the practice portion of our session, and get some hands-on
experience with Named Sets in Analysis Services 2005. To do so,
we will move to the Business Intelligence Development Studio, closing SQL
Server Management Studio for the present.
--> Exit from the main menu in the SQL
Server Management Studio.
the Analysis Services Database in Business Intelligence Development Studio
access the new Analysis Services database we have created within the Business
Intelligence Development Studio, where we can best accomplish design and
development work in general. While we certainly might begin within the BI
Development Studio by creating an Analysis Services project, when
building all the components from scratch, we will take a few shortcuts here to more
rapidly get to the subject matter of our session, Named Sets.
Click the Start
button, once again.
SQL Server 2005 within the Program group of the menu.
Click SQL Server
Business Intelligence Development Studio, as depicted in Illustration 10.
The Microsoft Visual
Studio 2005 development environment opens, beginning with the Start page,
as shown in Illustration 11.
Click for larger image
Illustration 11: The
Start Page, Microsoft Visual Studio 2005 Development Environment (Compressed
Close the Start
--> Open on the Visual Studio main
Services Database from the cascading menu, as depicted in Illustration 12.
Illustration 12: Opening
an Analysis Services Database ...
The Connect to
Database dialog appears.
the radio button to the immediate left of Connect to existing database
Type the name
of the server / server with instance into the Server box. (Mine is MOTHER1\M1MSSQL2K5,
as seen in various illustrations throughout my articles)
Select the ANSYS042
Adventure Works DW database in the Database selector underneath the Server
10. Click the Browse button to
the right of the box labeled New Solution at the bottom of the Connect
to Database dialog.
The Browse for
directory ... dialog opens.
11. Navigate to a convenient place to
store the objects created within our solution.
12. Within the Browse for directory
... dialog, once it rests at a convenient place, right-click the white space
within the dialog.
13. Select New from the context
menu that appears.
14. Select Folder from the
cascading menu that branches off the New selection, as shown in Illustration
Illustration 13: Select
New --> Folder
A folder, with edit-ready
title New Folder, appears.
15. Name the new folder as follows:
ANSYS042 Intro Named Sets
The new folder appears as
depicted in Illustration 14.
Illustration 14: New
Folder to House Our Solution Files ...
16. With the folder selected, click
the open button.
The Browse for
directory ... dialog closes, and we return to the Connect to Database dialog,
which appears, with our input, similar to that shown in Illustration 15.
Illustration 15: The
Connect to Database Dialog with our Input
17. Click OK to accept our
input, and to connect to the Analysis Services database.
The Connect to
Database dialog closes, and we see our clone Analysis Services
database open, complete with all member objects, in Solution Explorer (which
appears, by default, in the upper right corner of the BI Development Studio),
as partially depicted in Illustration 16.
Illustration 16: The
Analysis Services Database Appears in Solution Explorer (Partial View)
We are now positioned to
get some practice with defining Named Sets within a cube. In the next
section, we will work within the Adventure Works cube to create a Named
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 Adventure Works cube in the Solution Explorer (within the Cubes
folder of the tree).
from the context menu that appears, as shown in Illustration 17.
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.
Click the Calculations
tab atop the Cube Designer, as depicted in Illustration 18.
Illustration 18: Switching
to the Calculations Tab ...
click Form View, whose icon appears just beneath the Calculations
tab label, as shown in Illustration 19.
Illustration 19: Click
Form View, If Necessary ...
the [High Discount Promotions] Named Set in the Script Organizer pane
(upper left corner of the Calculations tab).
Named Set from the context menu that appears as depicted in Illustration
Illustration 20: Selecting
an Entry Point ... for New Named Set Creation
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 ...
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.
following into the Name field of the Calculations form.
Type (or cut
and paste) the following MDX syntax into the Expression box underneath
the Name box.
[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
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
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.
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.
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
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
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
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
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.
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.
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.
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.
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.