Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Handling Multidimensional Data with MDX
Earlier, we touched on the meaning of the term member in a cube environment. In addition to the standard member, a calculated member (a member generated via MDX) can range in sophistication from the simplest specifications (a constant value) to a complex formula that's customized to meet a complicated business need. To learn some of the characteristics of members, we'll examine their properties and how they interact with dimensions in general. We'll first create a calculated member using a constant value. Our exploration will progress to more advanced calculations later, but for now the point is to gain an understanding of the following:
The nature of members and how they fit into the OLAP scheme of things
The mechanisms used to create a calculated member
The navigation of members, including the display of properties and other information
The interaction between dimension members and MDX expressions
Creating a Basis for Exploration: Simple Calculated Members
Let's create a simple calculated member to get a look at its properties in a general way. We'll also use our newly created calculated member to explore other facets of MDX as we progress through the tutorial. From the Analysis Manager console, at the Cubes folder within the FoodMart 2000 database sample, follow these steps:
Expand the Cubes folder by clicking the plus (+) sign to the left of the folder (see Figure 3).
Figure 3 Sample cubes provided with the Analysis Services
installation.Right-click the Warehouse cube and click Edit in the context menu.
When the Cube Editor screen appears, click the Data tab at the lower left of the pane. to activate the preview pane.
If "Product Family" does not appear in the lower pane, drag the Product dimension to the rows area (the left column of the lower pane). The Cube Editor preview pane should appear as partially shown in Figure 4.
Figure 4 The Cube Editor preview pane.Select Insert Calculated Member (from the top drop-down menu) under Insert. Figure 5 shows the associated toolbar button, an alternative means of initializing the Calculated Member Builder.
Figure 5 The Insert Calculated Member toolbar button.When the Calculated Member Builder appears, type MyCalcMem into the Member Name box. In the Value Expression box, type the phrase "Check for Updates" (the quotation marks are required). The Value Expression box should appear as shown in Figure 6.
Figure 6 An inserted string in the Value Expression box.Click OK.
Review the newly created MyCalcMem column, comparing it to Figure 7.
Figure 7 The new MyCalcMem column.To change the expression for MyCalcMem, go to the Calculated Members folder in the Cube tree in the left pane of the Cube Editor (partially shown in Figure 8). Click MyCalcMem within the Calculated Members folder.
Figure 8 MyCalcMem now appears in the Calculated Members folder.Right-click MyCalcMem and select Edit.
Type 11+2 and click OK.
The number 13 fills the MyCalcMem column cells, replacing the "Check for Updates" string.
NOTE
Standard mathematical operations apply here. For details, see the Books Online for Analysis Services / Microsoft SQL Server 2000, which are installed by default on the PC as part of a Typical installation. The Books Online provide a substantive reference surrounding many aspects of the applications, in an accessible, cross-referenced format. This resource typically becomes a highly valued tool in working with Analysis Services and Microsoft SQL Server 2000, for developers and administrators alike.
)
Figure 3)
Figure 4)
Figure 5)
Figure 6)
Figure 7)
Figure 8To perform a text concatenation, follow these steps:
Select the Properties pane at the bottom of the left side of the Cube Editor, making sure that MyCalcMem (in the Calculated Members folder of the Cube tree) remains highlighted.
Select the Value property for MyCalcMem to edit its properties.
Click the ellipsis (...) button. When the Calculated Member Builder appears, clear the Value Expression box.
Type the following expression:
"MyCalcMem" + "-" + "REVISION1"
The contents of the Value Expression box should resemble those in Figure 9.
Figure 9 Text concatenation in the Value Expression box.Click OK. Compare the results to Figure 10.
Figure 10 Text concatenation results for MyCalcMem in the preview
pane.
)
Figure 9)
Figure 10The result of placing a plus (+) sign between two numbers in an MDX expression is the sum of the numbers, while the plus (+) sign used between two strings returns a concatenated string. (Remember that strings need to be enclosed in quotation marks.)
"Getting to Know the Membership" with MDX Expressions
Next we'll explore the use of MDX to display member-related information. The Functions tree of the Calculated Member Builder contains a list of many functions that are available within MDX. We can view the types of functions as the folders that compose the tree. Each folder name reflects the category of return values (the types of values returned by the functions within the respective folder).
Follow these steps:
Select the Value property (go to Properties at the lower-left corner of the Cube Editor) for the MyCalcMem calculated member.
Click the ellipsis (...) button.
When the Calculated Member Builder initializes, clear the Value Expression box.
We can select functions to build our expressions in the Value Expression box. Functions are added to the Value Expression box by dragging the selected function to the box, by selecting the desired function and clicking the Insert button, or by double-clicking the desired function. Figure 11 shows a partial view of the Member folder within the Functions tree (expanded by clicking the plus sign to the left of the folder).
Figure 11 Partial view of the Functions tree.Expand the String folder in the Functions tree.
Double-click the UniqueName–Member function.
Click the <<Member>> token (a placeholder) in the Value Expression box to highlight it (see Figure 12).
Figure 12 Highlight the <<Member>> token in the Value Expression box.As we're working with members, we now go to the Member folder within the Functions tree, expanding it. To free up real estate, we can collapse the String folder by simply clicking the minus (-) sign to its left.
Expand the Member folder in the Functions tree.
Double-click the CurrentMember function.
A <<Dimension>> token appears in front of the expression in the Value Expression box (see Figure 13).
Figure 13 The Value Expression box after adding the CurrentMember
function.Click the <<Dimension>> token to highlight it; then double-click Product in the data tree (to the left of the functions tree) to change the expression to the following:
[Product].CurrentMember.Dimensions
Click OK.
)
Figure 11)
Figure 12)
Figure 13The results that are returned show the MyCalcMem column to be populated with the unique name of the current member, which includes its hierarchy (in brackets and delimited by single decimals). This represents a sort of "qualified name" whereby no member of a cube is without unique identification.
Let's perform the same process with the Name-Dimension function.
Select the Value property (lower-left corner of the Cube Editor).
Click the ellipsis (...) button.
Clear the Value Expression box when the Calculated Member Builder appears.
Expand the String folder in the Functions tree.
Double-click the Name-Member function (<<Member>>.Name should appear).
Click the <<Member>> token in the Value Expression box to highlight it.
Expand the Member folder in the Functions tree.
Double-click the CurrentMember function.
The Value Expression box should display the following expression:
<<Dimension>>.CurrentMember.Name
Click the <<Dimension>> token to highlight it, and then double-click Product in the data tree (see Figure 14).
Figure 14 The Product dimension in the expression.Click OK. Notice that the MyCalcMem column now lists the Member Name, as partially shown in Figure 15 (exploded view):
Figure 15 The new MyCalcMem Values, using the Member Name
Function.Now let's drag the Store dimension from the top pane down to replace the Product dimension in the row axis. You can drop the icon that appears over the Product Family headings currently in place—a small, double-headed arrow appears at the "drop" point. The objective is to swap Product to the top and Store below, in its old place.
Notice that the context of MyCalcMem has become "for All Products." The (newly positioned) Product dimension is now serving as a part of the filter list, set to All Products.
Select Alcoholic Beverages in the Product filter list, by scrolling down through the hierarchy (see Figure 16).
Figure 16 The product hierarchy as an exploded selection.The current member of the Product dimension is now Alcoholic Beverages, so the MyCalcMem value becomes Alcoholic Beverages. ("Current Member" is the value that appears on the column or row axis, assuming that the dimension itself appears on the column or row axis, respectively; if not, and the dimension appears in the filter section, the Filter box displays the "Current Member.")
Return the Product dimension to its original position in the row axis, simply "swapping" it again with the Store dimension, which returns to the top.
Double-click the Product Family hierarchy to expose the Product Department level to the right of the Product Family level. (Scroll over to the far right; the row axis remains fixed.) Figure 17 shows part of the results.
Figure 17 The Product Department appears as the first level of
drill-down.
)
Figure 14)
Figure 15)
Figure 16)
Figure 17Notice that MyCalcMem has assumed the values at the Product Department level (except for rollups/totals, where it displays the Product Family names). The current member of a dimension can exist at any level of the hierarchy.