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
    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
    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
    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
    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.
    Figure 5 - The Dimension Definition dialog for the Order Measures measures dimension showing the Extended Cost measure added to existing measures
    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

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

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


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