Calculated member and measure examples

IBM® Cognos® Cube Designer allows for the definition of dimensional calculated members and measures. Such expressions were previously defined only in the reporting environment. When defined in a dynamic cube, the calculated members are accessible in all of the IBM Cognos Analytics studios. You can use calculated measures to determine constant or weighted values. You can create calculated members that represent an N period rolling window of data relative to a current period member.

Constant and weighted allocation

Measures in base dynamic cubes must have the same grain since each base cube is constructed from a single fact table. In a virtual cube, it is possible for a measure from one base cube to be valid only for a subset of the levels of a virtual hierarchy.

In this example, the virtual cube Sales Inventory is built from two base cubes: Sales and Inventory. The Sales cube has the measure Sales Amount and its Time hierarchy contains Year and Quarter levels. The Time hierarchy in the Inventory cube contains a Year, Quarter, and Month levels. When the Sales Inventory cube is created, the virtual Time hierarchy contains the Year, Quarter, and Month levels.

In this situation, any Sales Amount value that is computed in the virtual cube at the Day level is null since there is no value in the Sales cube at the Month level.

In the following diagram, the Sales Amount measure has no values at the Month level but the Stock measure, from the Inventory cube, does. Only partial data is used to show the hierarchy.

Figure 1. Example of differences in the time hierarchy for two cubes

You can use calculated measures to compute constant or weighted values for a measure such as Sales Amount. A constant allocation allocates a measure value from a higher level evenly across all of its descendants at each level below the in scope level. The in scope level is typically the lowest at which the measure is valid.

Using constant allocation, the following diagram shows the Sales Amount values. The values from the Quarter level are evenly distributed across the descendants at the Month level. Only partial data is used to show the allocation.

Figure 2. Example of the use of constant allocation

A weighted allocation allocates values to the descendants relative to the values of another measure that is in scope, and that is correlated with the measure being allocated so the allocation is reasonable.

For example, the Sales Amount values are allocated based on the weights of the Stock measure from the Inventory cube.

Using weighted allocation, the following diagram shows the Sales Amount values. The values from the Quarter level are distributed using the same weighting as the Stock measure. Only partial data is used to show the allocation.

Figure 3. Example of the use of weighted allocation

Constant and weighted allocation expressions

Note: To create the expression for a calculated measure, the database objects must be dragged from the Project Explorer into the Editor. In the example code, the bold text represents metadata objects such as hierarchies, levels, and measures that are dragged and dropped into the expression editor. The code is visible in the Expression property but cannot be entered as text.

The following expressions can be used to create calculated measures in the sample virtual cube gosldw_sales_and_target. Because the Sales Target data at the month level exists in the sample cube, these expressions are not necessary but are shown to illustrate how the expressions are constructed.

In this constant allocation example, the Sales Target measure is used.

if (roleValue
('_levelNumber', currentmember
( [gosldw_sales_and_target].[Time].[Time])) > 2 ) 
then
(
tuple( [gosldw_sales_and_target].[Measures].[Sales target], 
ancestor(currentmember( [gosldw_sales_and_target].[Time].[Time]),
 [gosldw_sales_and_target].[Time].[Time].[Quarter]))
/
count(1 within set descendants
(ancestor(currentmember( [gosldw_sales_and_target].[Time].[Time]),
 [gosldw_sales_and_target].[Time].[Time].[Quarter] 
),
roleValue('_levelNumber', currentmember
( [gosldw_sales_and_target].[Time].[Time])) - 2, self ) ) )
else
(
 [gosldw_sales_and_target].[Measures].[Sales target] 
)

In this weighted allocation example, the Sales Target values are allocated based on the weights of the Revenue measure.

if (roleValue
('_levelNumber', currentmember( [gosldw_sales_and_target].[Time].[Time])) > 2 ) 
then
(
tuple( [gosldw_sales_and_target].[Measures].[Sales target], 
ancestor(currentmember( [gosldw_sales_and_target].[Time].[Time]),
 [gosldw_sales_and_target].[Time].[Time].[Quarter])
)*
tuple( [gosldw_sales_and_target].[Measures].[Revenue], 
currentmember( [gosldw_sales_and_target].[Time].[Time])
)/ 
tuple( [gosldw_sales_and_target].[Measures].[Revenue], 
ancestor(currentmember( [gosldw_sales_and_target].[Time].[Time]),
 [gosldw_sales_and_target].[Time].[Time].[Quarter])
)
)
else
(
 [gosldw_sales_and_target].[Measures].[Sales target] 
)