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.