...

Null Calculations with measures from multiple fact tables Proven Practice

by user

on
Category: Documents
1

views

Report

Comments

Transcript

Null Calculations with measures from multiple fact tables Proven Practice
Proven Practice
Null Calculations with measures
from multiple fact tables
Product(s): IBM Cognos 8
Area of Interest: Modeling
Null Calculations with measures from multiple fact tables
2
Copyright
Copyright © 2008 Cognos ULC (formerly Cognos Incorporated). Cognos ULC
is an IBM Company. While every attempt has been made to ensure that the
information in this document is accurate and complete, some typographical
errors or technical inaccuracies may exist. Cognos does not accept
responsibility for any kind of loss resulting from the use of information
contained in this document. This document shows the publication date. The
information contained in this document is subject to change without notice.
Any improvements or changes to the information contained in this document
will be documented in subsequent editions. This document contains
proprietary information of Cognos. All rights are reserved. No part of this
document may be copied, photocopied, reproduced, stored in a retrieval
system, transmitted in any form or by any means, or translated into another
language without the prior written consent of Cognos. Cognos and the
Cognos logo are trademarks of Cognos ULC (formerly Cognos Incorporated)
in the United States and/or other countries. IBM and the IBM logo are
trademarks of International Business Machines Corporation in the United
States, or other countries, or both. All other names are trademarks or
registered trademarks of their respective companies. Information about
Cognos products can be found at www.cognos.com
This document is maintained by the Best Practices, Product and Technology
team. You can send comments, suggestions, and additions to
[email protected]
IBM Cognos Proprietary Information
Null Calculations with measures from multiple fact tables
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
1.3
PURPOSE .............................................................................................................. 4
APPLICABILITY ....................................................................................................... 4
EXCLUSIONS AND EXCEPTIONS .................................................................................... 4
2
PROBLEM DESCRIPTION .............................................................................. 4
3
THE REPORT STUDIO ROUTE ........................................................................ 8
4
THE FRAMEWORK MANAGER ROUTE .......................................................... 14
APPENDIX A............................................................................................................ 18
IBM Cognos Proprietary Information
Null Calculations with measures from multiple fact tables
4
1 Introduction
1.1
Purpose
Calculations with measures from different fact tables can be very challenging
if a query uses columns from different dimensions which don’t have
corresponding entries in each fact table. Joining multiple fact tables with a
full outer join can cause NULL values in measure columns. In that case the
NULL values prevent us from calculating the correct results when the report
uses a calculation with measures from different fact tables.
1.2
Applicability
This proven practice example is designed and tested for IBM Cognos 8 BI
server. The author tested this approach with Version 8.1 and 8.3. The
attached examples included in this document are related to IBM Cognos 8 BI
version 8.3 and the delivered standard example for GOSALES.
1.3
Exclusions and Exceptions
This document describes an approach where the report author expects the
calculation to come from the model.
There are other ways to solve the described issue with modelling a report in
Report Studio without changing the model project.
2 Problem Description
Usually the Framework Manager model is designed to create full outer joins
to fetch the measures from multiple fact tables. In this case there is at least
one entry for a specific member or primary key of a dimension in one fact
table but no entry with the same key in the second fact table. The values for
the measures of the second fact table will then show a NULL value when
combined in one single list.
IBM Cognos Proprietary Information
Null Calculations with measures from multiple fact tables
Example:
The next picture shows the data model with the two fact tables where the
two different measures come from.
On the left is the fact table Sales with the measure Quantity. On the right is
the fact table Product_Forecast with the measure Expected_Volume.
When the report author creates a calculation with e. g. Quantity and
Expected volume then the result of this calculation will be NULL if either one
of the original measures is NULL.
IBM Cognos Proprietary Information
5
Null Calculations with measures from multiple fact tables
The above screen shot shows a filtered list in IBM Cognos 8 Query Studio
with dimension Product and two measures from different fact tables. The
measure Quantity doesn’t have entries for some specific product numbers at
the specified filtered month. For this reason the value is NULL.
IBM Cognos Proprietary Information
6
Null Calculations with measures from multiple fact tables
The next picture shows how the report author will create this calculation:
The next picture shows the report result after implementing the query
calculation Quantity – Expected Volume in the report:
IBM Cognos Proprietary Information
7
Null Calculations with measures from multiple fact tables
8
The list in the picture shows that where one of the measures Expected
volume or Quantity is NULL then the result of the calculation is also NULL.
The reason for this behaviour is that calculations on a null value will always
return a result of null. The calculation expression needs a condition to convert
NULL values to zero values which can be used in calculations.
3 The Report Studio route
Finding a solution for this issue is more easily demonstrated when converting
the report example to Report Studio.
This picture shows the Query Studio list report opened in Report Studio.
In Report Studio the column name ‘Quantity – Expected volume’ has the
calculation expression:
(total([Sales (query)].[Sales].[Quantity])) - (total([Product forecast
(query)].[Product forecast].[Expected volume]))
Now change the calculation expression for column name ‘Quantity – Expected
volume’:
(
IF ((total([Sales (query)].[Sales].[Quantity])) is missing)
then (0)
ELSE ((total([Sales (query)].[Sales].[Quantity])))
)
(total([Product forecast (query)].[Product forecast].[Expected volume]))
IBM Cognos Proprietary Information
Null Calculations with measures from multiple fact tables
The calculation expression should looks like this:
IBM Cognos Proprietary Information
9
Null Calculations with measures from multiple fact tables
The picture below shows the report result after changing the calculation
expression for ‘Quantity – Product volume’ and it shows that the issue with
NULL values still exists:
Why doesn’t the changed calculation expression affect the report result?
IBM Cognos Proprietary Information
10
Null Calculations with measures from multiple fact tables
11
The best way to find an answer to this question is to look at the generated
SQL for this report:
For readability purposes only the relevant part of the SQL are displayed:
The important thing to note here is that there are at least two sub queries,
one for each different fact table to fetch the two measures, joined with a
FULL OUTER JOIN (marked in blue) and a top query to fetch the result
columns for the list. The calculated column ‘Quantity – Expected volume’ is
marked with the first red rectangle. As you can see the IF-condition is not
there. It was moved to the sub query of the related fact table for Quantity
and this is the reason why the calculation doesn’t work. The NULL values we
see on the report are the result of the Full Outer Join so the calculation
should be done after the join.
To solve this behaviour the report must instruct the query engine to do the
If-Null calculation after the Full Outer Join.
One way to do this is to combine the measure from the second fact table in
the calculation expression. Then it is not possible to move the conditional
function to one of the sub queries so the calculation runs after the Full Outer
Join has completed.
IBM Cognos Proprietary Information
Null Calculations with measures from multiple fact tables
In the calculation expression ‘Quantity – Expected volume’ replace the IFcondition with the Case statement like this:
case
when [Quantity] is missing and [Expected volume] is missing then (0)
when [Expected volume] is missing then ([Quantity])
when [Quantity] is missing then ([Expected volume] * (-1))
ELSE ([Quantity]- [Expected volume])
END
The next picture shows the new calculation expression in Report Studio:
IBM Cognos Proprietary Information
12
Null Calculations with measures from multiple fact tables
The report result gives now the correct values.
IBM Cognos Proprietary Information
13
Null Calculations with measures from multiple fact tables
14
The generated SQL of this report looks like this:
…
Now the Null-If condition is calculated after the Full Outer Join and gives the
correct result.
4 The Framework Manager route
The next step describes how the model administrator can implement this
calculation into the Framework Manager package so that report authors can
select it directly from the package when working with Query Studio.
It is important to mention that the calculation expression in the Report Studio
example above refers to the aggregated sums of each measure. The same
should also be prepared in the Framework Manager model before
implementing the calculation.
IBM Cognos Proprietary Information
Null Calculations with measures from multiple fact tables
15
The next picture show the final result how this can looks like in a Framework
Manager model:
It is important to note that the calculation ‘Quantity – Product Volume’ must
use only calculations to ensure that only values from the same aggregation
level are used and correct results are returned.
IBM Cognos Proprietary Information
Null Calculations with measures from multiple fact tables
16
Here is the content of each calculation:
Calculation
Name
Calculation expression
Expected
Volume
(Product
Forecast)
[Product forecast (query)].[Product forecast].[Expected
volume]
Quantity
(Sales)
[Sales (query)].[Sales].[Quantity]
Quantity –
Product
Volume
CASE
WHEN [Sales (query)].[Quantity (Sales)] is missing and
[Sales (query)].[Expected Volume (Product Forecast)] is
missing
THEN (0)
WHEN [Sales (query)].[Quantity (Sales)] is missing
THEN ([Sales (query)].[Expected Volume (Product
Forecast)] * (-1))
WHEN [Sales (query)].[Expected Volume (Product
Forecast)] is missing
THEN ([Sales (query)].[Quantity (Sales)] - [Sales
(query)].[Expected Volume (Product Forecast)] )
ELSE ([Sales (query)].[Quantity (Sales)] - [Sales
(query)].[Expected Volume (Product Forecast)] )
END
Ensure that the property Usage is set to ‘fact’ and Regular Aggregate
property is set to ‘Automatic’ for each calculation.
IBM Cognos Proprietary Information
Null Calculations with measures from multiple fact tables
After implementing the new calculations into the report model and
republishing the package the result of the new calculation should look like
this:
IBM Cognos Proprietary Information
17
Null Calculations with measures from multiple fact tables
Appendix A
Framework Manager Model (IBM Cognos 8.3):
great_outdoors_sales.zip
Deployment with sample Reports (IBM Cognos 8.3):
deployment.zip
IBM Cognos Proprietary Information
18
Fly UP