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.
IBM Cognos Adaptive Warehouse versions 8.x and IBM Cognos Adaptive Warehouse versions 10.x.
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 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||Stand-alone 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 stand-alone 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 Stand-alone Calculations
- 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
- 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 Business analytics on developerWorks
Experiment with new directions in software development.
Tips for improving outcome and controlling risk.
Software development in the cloud. Register today and get free private projects through 2014.
Evaluate IBM software and solutions, and transform challenges into opportunities.