Monday, October 18, 2010

Dealing with Input/Output Scale in Cognos Transformer 8.3

Prerequisites:
Scale and Precision do not have the same meaning for Cognos and SQL Server.
In SLQ Server :
- scale: number of digit at the right of the decimal point.
- precision: maximum number of digit in a  number.
Another good point to mention is the difference between Float and Decimal data type in SQL Server. A DECIMAL data type is a fixed precision data type which means that all the values in range can be represented exactly with scale and precision properties. This blog shows a good explanation.
In Cognos:
- precision: number of decimal places that the reporting tool uses for rollup calculations. Precision is only available when storage type is set to 64-bits.
- output scale: the source values are divided by ten taken to the power of the Output Scale value. If Output Scale is set to 3, then:
150,701 is shown in reports as 150,701/10^3; that is 150.701. If precision is set to 0, it will show 151 because of the rounding.
- input scale: Is a positive or negative power of ten by which the source values are to be multiplied as they are read from the data source.
150,701 is read in Transformer as 150,701*10^3; that is 150701000 if Input Scale is set to 3.
Dealing with Input/Output Scale in Cognos Transformer 8.3 with a float datatype
One of the requirements from business people is to make sure that the data they see in cubes is accurate.  Most of the time, when they compare report results from cubes with data from operational systems, they always find differences in measures. Differences can come from a bad design in the ETL process to the datawarehouse, or (if we are confident in our ETL) roundings happening at the lowest level of aggregation that add up and make the summary calculation very different from what they expect to see.
In my experience, the DBA used SQL Server float data type. Unfortunately, when you define the column data type as float, SQL automatically sets precision property to 53 and scale property to 0. Scale property set to 0 doesn't mean that the float number is truncated or rounded when stored in the database, but when the float is queried from an external application (Transformer in our case), and the result is used by another application (Cognos Analysis), the final result is rounded: there is a loss of precision somewhere in the process as you'll see in the following screenshots.

In SQL Server, Scale1 column data type has been set to float.

In Transformer, when testing with Data Source Viewer, Scale 1 column outputs as expected. (In this case, Transformer is retrieving our database table through a published Framework Manager package. Scale and Precision properties are read-only in Framework Manager.)


In Transformer, Input Scale property is set to 0.

In Transformer, Output Scale property is set to 0 as well as Precision. Storage type is 64-bits.

 In Analysis studio, Scale 1 is rounded.
To get the business's expected result, we need to change scaling properties in Transformer. Setting Input Scale property to 3, Output Scale property to 3 and Precision to 2 (when your data is of float type) will give you the desired result.

Analysis Studio displays Scale 1 measure correctly.



1 comment:

Hanh said...

You made a good definition and good sample as well. Thank you!
Hanh