Introduction
Purpose
There are many different ways of modelling target model calculations in IBM Cognos Adaptive Warehouse. Each has a specific purpose and delivers different results. Use of the wrong type of calculation for a particular situation may result in incorrect query results. This document explains which modelling technique to use in which circumstance and provides details on the modelling techniques.
Applicability
IBM Cognos Adaptive Warehouse versions 8.x and IBM Cognos Adaptive Warehouse versions 10.x.
Prerequisites
Calculations are defined in the IBM Cognos Framework Manager Target model within IBM Cognos Adaptive Warehouse. This document assumes that the reader is familiar with IBM Cognos Framework Manager.
Exclusions and Exceptions
There are no known exceptions and exclusions at the time this document was created.
Calculation Types
Calculation types are distinguished by the source of the components used in the calculation and the timing of the computation relative to aggregation. Calculation components can be sourced from a single fact, multiple facts or a combination of facts and dimensions. Timing of computation denotes whether the calculation is done before aggregation or after aggregation. The table below lists the appropriate calculation type according to source of components and desired timing.
Calculation  Before Aggregation  After Aggregation 

Involving a single fact  Calculation in business layer of fact. e.g. extended price = sum (order.unit price * order.units sold)  Calculation in measures dimension (regular aggregate is calculated). e.g. sales margin = sum (order.extended price – order.extended cost)/sum (order.extended price) 
Involving a fact and dimension  Calculation in business layer of fact. e.g. extended cost = sum (product unit price * order.units sold)  Calculation in measures dimension (regular aggregate is calculated). e.g. yield per product = sum (extended price)/count (product.id) 
Involving multiple facts  Not applicable. You cannot perform a calculation using components from multiple facts before aggregation  Standalone calculation. e.g. admin expenses as a percentage of sales = sum (GL Admin Expenses) / sum (extended price) 
Involving single or multiple dimensions, no facts  Calculation in business layer of dimension. e.g. adjusted price = sum (product unit * producttype.standard rate)  This is not commonly used calculation type, but could conceivably be performed using a standalone calculation 
Creating a Calculation in the Business Layer
 Locate the business layer query subject. It can be found in the IBM Cognos Framework Manager Target model in Adaptive Warehouse in the <Warehouse Object> namespace. The query subject name is <Warehouse Object>_. In this example, the namespace is Order and the query subject being used is Order_.
Figure 1  The Project Viewer pane showing Order_ query subject in the target model
 Edit the definition of the query subject.
Figure 2  The Query Subject Definition – Order_ dialog showing the query items and calculations
 Add a Calculated Item and define an expression using items from this query subject or the business layer query subject of any related dimension. For example, the new calculated item named Extended Cost might be as follows:
Extended Cost = [Order].[Order_].[Units Sold] * [Order].[Item].[ItemId].[ItemPrice]
Figure 3  An example of the expression for Extended Cost which is calculated as units sold * unit price
 Confirm the value of the Regular Aggregate property of the new calculated item. In this case the value is Sum.
Figure 4  The Regular Aggregate property for the calculated item Extended Cost set to a value of Sum
 If the calculation is to be exposed as a new measure, include it in the appropriate measures dimension.
Figure 5  The Dimension Definition dialog for the Order Measures measures dimension showing the Extended Cost measure added to existing measures
Creating a Calculation in a Measures Dimension
 Locate the measures dimension. It can be found in the IBM Cognos Framework Manager Target model in Adaptive Warehouse in the <Warehouse Object> Namespace. The measures dimension name is <Warehouse Object> Measures. In this example, the namespace is Order the measures dimension being used is Order Measures.
Figure 6  The Order Measures measures dimension in the Project Viewer pane including the UnitsSold, UnitPrice, ExtendedCost and ExtendedPrice measures
 Edit the definition of the measures dimension.
Figure 7  The Dimension Definition dialog of the Order Measures measures dimension including the UnitsSold, UnitPrice, Extended Cost and Extended Price measures
 Add a new measure. Select only measures from this measures dimension as components in the calculation. For example, the calculation for a new measure named Sales Margin might be as follows:
Sales Margin = ([Order].[Order Measures].[Extended Price]  [Order].[Order Measures].[Extended Cost]) / [Order].[Order Measures].[Extended Price]
Figure 8  The Calculation Definition dialog for the Sales Margin measure definition of Extended Price – Extended Cost / Extended Price
 Set the Regular Aggregate property for the new calculated measure to Calculated. This will ensure that aggregation takes place after the calculation.
Creating Standalone Calculations
 Pick an appropriate namespace for the standalone calculation or create a new namespace. Standalone calculations are typically created for crossfact calculations. If the dimensional scope of the calculated measure is the same as the scope of one of the facts, place the standalone calculation in that fact's namespace, otherwise create a new namespace. When creating a new namespace, also create star schema groupings to denote the dimensional scope of the namespace. In the example below, the only dimension in scope for the calculation is the Calendar. There was no existing namespace with Calendar scope, so the new namespace Sales Ratios was created for the calculated measure.
Figure 9  The Sales Ratios namespace shown in the Project Viewer with Calendar Star Schema grouping
 Create a new standalone calculation. Use measures from any measures dimension when defining the calculation. For example, the calculation for a new measure named Admin Expenses as a percent of Sales might be as follows:
Admin Expenses as a percent of Sales = ([GL Balance].[GL Balance Measures].[Admin Expenses Amount] / [Order].[Order Measures].[Extended Price])
Figure 10 – The Calculation Definition dialog showing the definition of the Admin Expenses as a Percentage of Sales standalone calculation
Standalone calculations work differently from other query items or measures:
 There is no need to create scope relationships for standalone calculations.
 Standalone calculations must be used standalone. Do not reference them in the definitions of query items or measures.
Comments
Dig deeper into Big data and analytics on developerWorks

developerWorks Premium
Exclusive tools to build your next great app. Learn more.

developerWorks Labs
Technical resources for innovators and early adopters to experiment with.

IBM evaluation software
Evaluate IBM software and solutions, and transform challenges into opportunities.