Home > Creating Dimensional Objects > Creating Measure Dimensions...
![]() Previous |
![]() Next |
A pivot cube uses a measure dimension that enables you to reference all measures in the cube simultaneously. This is particularly useful when generating calculated measures, because you can apply the calculation to all measures at once. Otherwise, you use a pivot cube exactly the same as any other cube.
To create a pivot cube:
From the Tools menu, choose OLAP, and then Create Fact View with Measure Dimension.
Complete the Create Fact View with Measure Dimension dialog box:
Click Select Source to display the Source Selection dialog box. Move the fact table with the data for the cube to the Selected pane, then click OK to return to the Create Fact View with Measure Dimension dialog box.
Select the Measure Column check box for the columns in the fact table that contain the facts (not the dimension members) for the cube. For example, you would select COST
, SALES
, and UNITS
from GLOBAL.UNITS_FACT
, and leave blank the rows for ITEM_ID
, MONTH_ID
, and so forth.
Select Create Measure Dimension Table, unless you already have a similar table you can use. See "About Pivot Cubes" for a description of measure dimension tables.
Notice the names of the fact view and the measure dimension table; you need them when creating a cube.
The default names are fact-table
_MEASURE_VIEW
for the fact view, and fact-view
_MEASURE_DIM
for the measure dimension table. You can change them if you wish.
Click Create.
Create a measure dimension the same way that you would create any other cube dimension, but with these requirements:
On the General page, select Standard for the dimension type, and Measure Dimension as the dimension class type.
Levels and Hierarchies pages blank. A measure dimension is flat, with no levels or hierarchies.
On the Map page, map the measure dimension to the measure dimension table that you created in Step 2.
Create the cube the same way that you would create any other cube, but with these requirements:
On the General page, list the measure dimension first under Selected Dimensions.
On the Aggregation Rules page, set the operator to Nonadditive (Do Not Summarize) for the measure dimension.
On the Measures page, change the name of the initial measure to a meaningful name that represents all the measures in the measure dimension, such as "All Measures." Also ensure that Detect Data Type is selected. Do not create any additional measures.
On the Map page, select the measure view created in Step 2 as the source. Map the measure dimension to the MEASURE_DIM
column, and the measure to the MEASURE_VALUE
column. Map the other dimensions the same as you would for any other cube.
Build the cube.
Create calculated measures.