Transformer Input and Output Scale, Precision and Decimal Places

[JGirl]

Help!! Im losing my mind

In my transformer model, im reading a field that is stored in the SQL database as a decimal(5,2). For example, 9.09

I want this value to appear in my cube as 9.09% (ie, formatted as a percent with 2 decimal places shown). I'm guessing that transformer is expecting my input decimal to be more like 0.0909 to display it correctly.

I've been trying to correct this by changing the input and output scales and the precision, but I can't get it to work properly. I'm getting values like 90900%, 9% but never 9.09%, even though i've specified to format the value to 2 decimal places.

I've tried the following combinations where my input value is 9.09, and the measure is a 64-bit float, formatted as '0%', formatted to show 2 decimal places:

InputScale OutputScale Precision CubeDisplayValue
-----------------------------------------------------
-2 0 0 909%
-2 2 0 9%
-2 2 2 9%
0 0 0 90900%
2 0 0 9090000%

Why are my decimal places not being shown even though I've specified them to be shown, and how can I fix this?

Cheers
J

 

[drlex]

J,
Have you tried
Input Scale -2
Output scale 2
Precision 4
I find precision 4 best for all non-integer numbers.


soi la, soi carre

 

[JGirl]

Thanks for such a quick reply.

Yep, tried that too. Its still displaying 9% with no decimals (and rounding the percentages up and down to the nearest 1%).

J

 

[drlex]

The fact that you get no decimal places is unusual and suggests the formatting is not being retained in the model. What version of transformer are you using?

What happens if you try incorporating a test .csv file in a trial model? What displays in PP?

soi la, soi carre

 

[mayo04]

Hi JGirl,

Have you tried
Input scale 0
Storage type 64 bit
output scale 4
precision 2
Format 0% and decimal 2


Mayoman

 

[JGirl]

Yep, tried that combhination as well. Its still displaying 9% with no decimals (and still rounding the percentages up and down to the nearest 1%).

In every combination I've tried, i've always had the following:
storage type = 64 bit
format = 0%
decimal places = 2

I'll try using a csv file & see whats happening.

I'm using a mdl file, in transformer 7.1.98.0-0 on Win2K, reading from a MSSQL database field formatted as decimal (5,2).

I've also checked in the MDL file (in a text editor), and the measures are definitely formatted as percentages with 2 decimal places.

 

[mayo04]

Hello JGirl,

Thats very funny cause I know when you specify the format of 0% it multiplies by 100 but you normally compensate by setting Output scale to 4, have you though about taking the figure in as a whole ie 909 and setting the format with output scale and precision to 4 and 2 so you get 9.09. I work with informix so maybe it may be a bit different to MSSQL also is the Data class set to numeric.

mayoman

 

[JGirl]

Oh, you're telling me its funny! I know!

Its just totally not caring about my decimal place settings....in the meantime i've given up with my cube and having a reportnet report instead....

 

[DoubleD]

Try formatting your data without the % format. Take a look at your data. See if it's keeping the correct amount of digits initially.
Next, right-click on your datasource and do a Data Preview. This will show you a sample of data as it comes in to the model, but before any calculations are done.
Last, run your IMR from Impromptu and verify that the values are returning exactly as expected.

Pain is stress leaving the body.

DoubleD