- Terminology
- Pivoting Cube Data
- Member Names and Keys
- Calculated Members
- Member Properties
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Pivoting Cube Data
You can browse cube data directly from the Analysis Manager by selecting the desired cube and clicking the data tab. However, this window cannot display the structure of the cube. A more practical place to view the cube data is within the Cube Editor.
The Cube Editor window can be activated by right-clicking a cube and choosing the Edit option. The sales cube within the FoodMart2000 database looks like the following figure.
)
Figure 1
First, notice that you can easily switch to a different cube by simply choosing the cube name from the drop-down box in the upper-right corner. This is a nice feature if you have shared dimensions and you want to make changes to different cubes before reprocessing all of them. Note that if you change a shared dimension's structure, you must reprocess all cubes that include that dimension.
Now if you look at the bottom of the right pane on the previous screen, you'll see that the Cube Editor has two tabs: schema and data. Most people have seen the schema tab, but not the data tab. If you click the data tab, you can browse cube data directly from the Cube Editor, as shown in the following figure:
)
Figure
2The data grid contains some members that have no data: empty members. In this example, you view data in the tiny sample database. In the real world, you're likely to have many more empty cells. Viewing such empty cells can be quite annoying and inconvenient. Fortunately, you can click anywhere in the gray area of the data grid to get a small option box as shown below.
)
Figure
3If you uncheck Show empty members, the data grid will condense to only the nonempty members.
How could you go about doing the same using MDX? You could use the NON EMPTY function, as follows:
SELECT {[measures].members} ON COLUMNS,
NON EMPTY {[customers].[country].members} ON ROWS
FROM sales
The following figure shows the result you will see in MDX sample application.
)
Figure
4Let's go back to the Cube Editor. If you hold the left mouse button and drag the measures dimension to the time dimension, Analysis Manager will pivot the data based on the selected measure. For instance, instead of seeing all measures broken down by year, you can view the value of store sales for every store type and for every year in the time dimension, as shown below:
)
Figure
5This feature is particularly helpful if you're having trouble with one of your MDX queries and you want to browse the cube data to find the correct values. How do you pivot dimensions and measures within MDX? Simply treat measures as you would any other dimension. You can specify the measures on ROWS, on COLUMNS, or in the WHERE clause. For example, suppose that you wanted to see the breakdown of store sales for each store type during 1997 and 1998. The MDX query would look like this:
SELECT {[store type].members} ON ROWS,
{[time].[year].[1997], [time].[year].[1998]} ON COLUMNS
FROM sales
WHERE (measures.[store sales])
The output would be identical to what we saw in the Cube Editor on the previous screen shot.
If you wanted to see the value of all measures for each store type during 1997, you'd have to change the query slightly:
SELECT {[store type].members} ON ROWS,
{[measures].members} ON COLUMNS
FROM sales
WHERE ([time].[year].[1997])
The results are shown here:
)
Figure
6If you look closely at the MeasuresLevel heading within the Cube Editor you see that it includes calculated members profit and sales average; if you run an MDX query returning measures.members on columns (or rows), the output does not include calculated members. You can easily fix this by explicitly specifying the calculated members in the SELECT statement as follows:
SELECT {[store type].members} ON ROWS,
{measures.members, measures.profit, measures.[sales average] } ON COLUMNS
FROM sales
WHERE ([time].[year].[1997])
Alternatively, you could use the allmembers function to accomplish the same thing:
SELECT {[store type].members} ON ROWS,
{measures.allmembers } ON COLUMNS
FROM sales
WHERE ([time].[year].[1997])
NOTE
I discuss calculated members in greater detail later in this article.