- About the Series
- Introduction
- Introducing MDX Queries
- Key Concepts and Terminology
- Introducing Sets
- Understanding Metadata
- Working with MDX Queries
- Working with Member Sets
- Specifying Members in a Set
- Using the CrossJoin Function to Combine Multidimensional Sets
- Looking Ahead to Calculated Members
- Next in This Series
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Introducing Sets
We have a report that specifies total sales for a particular segment of the business, but little else that's useful to the information consumer. The rows and columns of a report provide a means of displaying multiple members from a single dimension. As discussed earlier, multiple members from a dimension can combine to form a set.
Drag the Product dimension to the section of the PivotTable list labeled Drop Row Fields Here.
The Products dimension becomes the row label—not just Products, but the entire Product hierarchy, as shown in Figure 13.
Figure 13 The product dimension added to the row axis.The report now contains a set of four general positions, each of which represents a member of the Product dimension, together with a Total position. The total for the three Product members also appears, and matches the total for the store type in the initial filtered version of the report in Figure 12.
Let's expand the set to create further positions beneath the original Product member positions. Right-click the Product Family label and select Expand.
The Products dimension expands, and the Product Department members appear. The set we've placed in the row axis now displays 24 positions, with two columns showing the levels of the Product dimension (see Figure 14).
Figure 14 The expanded product dimension displays more positions.Let's clean up the report to display only the top two levels of the Product dimension. Right-click each of the levels below the Product Department level and click Remove Field. (An alternate means of removing each level is to select it and then drag it off the PivotTable list.)
The report is compacted, as shown in Figure 15.
Figure 15 The report after removing the levels below Product
Department.Now we'll add another two positions to the column axis, to generate three perspectives within the report. Drag the Store Cost measure from the PivotTable field list to the left of the Store Sales column in the data area, and drag the Unit Sales measure to the right of the Store Sales column.
The report shows a set containing three positions in the column axis (see Figure 16).
Figure 16 Adding measures to the columns axis.
)
Figure 13)
Figure 14)
Figure 15)
Figure 16Each value in the report is made up of a tuple that includes a member from every dimension in the cube. This address exists for every cell displayed in the report, and can be complicated, as there can be many dimensions in a typical cube. Consider the circled value in Figure 17. The tuple for the selected Store Sales amount is composed of the elements shown in the following table:
|
Dimension |
Member |
|
Store Type |
Deluxe Supermarket |
|
Store |
All Stores |
|
Time |
1997 |
|
Product |
Baking Goods |
|
Other Dimensions |
Default |
)
Figure 17 Every cell has an address.
So far, we've dealt with simple addresses consisting of single-member tuples defining their respective rows and columns. Unlike a cube, a report has only two axes, so you'll often need to combine multiple dimensions from the OLAP data source onto one or both axes to retrieve data stored in tuples consisting of multidimensional intersections. Let's construct an example to illustrate the process of creating multiple-member tuples.
Drag the Store level from the filter area (at the top of the PivotTable list) to the left of the Product level (and the Product Family label).
Remove the Store Country, Store City, and Store Name levels from the report by right-clicking each and selecting Remove Field. Leave the Store State label in place, just to the left of the Product Family label.
Expand the Store State level by selecting it, right-clicking, and selecting the Expand item on the context menu.
The layout of the report should resemble that partially depicted in Figure 18.
Figure 18 Combining dimensions in the row axis (partial view).
)
Figure 18){kind=link}
The modified report combines the State and Product dimensions in the row axis. The row axis contains a set within which there are 47 positions; this time each position of the set is composed of an address from two dimensions, and contains a tuple made up of two dimensional members. We've modified the set from its earlier composition of single-member tuples to one of multiple-member tuples. We can thus juxtapose multidimensional intersections on a single row. Moreover, we can drag additional dimensional members to the row to achieve any level of multidimensional membership that's meaningful in meeting the needs of information consumers. While the filter axis can only contain a single tuple, either or both of the row and column axes can contain a set of tuples. This adds complexity to the report, and certainly gives it far more than the typical two-dimensional format of x and y coordinates, but still meets the requirement discussed earlier: Each value presented in the report must have a single underlying member representing each dimension in the cube making up its address.
More than one dimension can reside on either of the two axes, and any given dimension can exist on either axis (but never both). This fact, coupled with the capability to move the dimensional members about at will within the report, as well as drill down and zoom in on dimensional member levels, make multidimensional analysis possible.