# IBM Cognos Proven Practices: Adaptive Warehouse Target Model Guidelines: Building Custom Calculations

Product(s): IBM Cognos Analytical Applications; Area of Interest: Modelling

Learn about target model calculations, their purposes and results, and how to use them in Cognos Adaptive Warehouse.

Share:

Michael Adendorff, STSM, Architect - Adaptive Application Framework, IBM

Mike has been with IBM Cognos for 12 years. He is a founding member of the Cognos Analytic Applications team. He designed and developed several application modules and conceived and played the lead design role for the Adaptive Application Framework and Cognos Metrics Studio. He has in-depth exposure to data warehouse modelling and design, warehouse performance and tuning, ETL, Framework Manager and BI reporting. Prior to joining Cognos, Mike was an independent consultant focusing on Data Warehousing and BI.

18 April 2012

Also available in Chinese Russian

## 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.

CalculationBefore AggregationAfter 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 factsNot applicable. You cannot perform a calculation using components from multiple facts before aggregationStand-alone calculation.
e.g. admin expenses as a percentage of sales = sum (GL Admin Expenses) / sum (extended price)
Involving single or multiple dimensions, no factsCalculation 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 stand-alone calculation

### Creating a Calculation in the Business Layer

1. 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
2. Edit the definition of the query subject.
##### Figure 2 - The Query Subject Definition – Order_ dialog showing the query items and calculations
3. 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
4. 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
5. If the calculation is to be exposed as a new measure, include it in the appropriate measures dimension.

### Creating a Calculation in a Measures Dimension

1. 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
2. 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
3. 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
4. Set the Regular Aggregate property for the new calculated measure to Calculated. This will ensure that aggregation takes place after the calculation.

### Creating Stand-alone Calculations

1. Pick an appropriate namespace for the stand-alone calculation or create a new namespace. Stand-alone calculations are typically created for cross-fact calculations. If the dimensional scope of the calculated measure is the same as the scope of one of the facts, place the stand-alone 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
2. Create a new stand-alone 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 stand-alone calculation

Stand-alone calculations work differently from other query items or measures:

• There is no need to create scope relationships for stand-alone calculations.
• Stand-alone calculations must be used stand-alone. Do not reference them in the definitions of query items or measures.

## 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.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Big data and analytics, Information Management
ArticleID=810711
ArticleTitle=IBM Cognos Proven Practices: Adaptive Warehouse Target Model Guidelines: Building Custom Calculations
publish-date=04182012