Elevated subtotals in Power Play Cube

[fens3]

I am building a cube for our finance dept. After the cube is created ,I try to validate against the source table, and my cost measure total comes out elevated. Initially when loading the source table, I grouped and summarized the data so that the cube creation would be more efficient. This resulted in an elevation of the cost grand totals of approx. $700,000. So then I loaded the source table without the grouping and summarization and the cost grand totals still were elevated by about $150,000. I have also tried various combinations in the Transformer model from having all of the original 4 dimensions to deleting some and then trying just time and the Cost Category which groups the elevated cost measure and still no luck.
Any suggestions on how to solve this issue ?

 

[drlex]

Fens,
What are the properties for your measure? (Right-Click on teh measure and choose properties) Under General Tab, select "Storage Type" 64-bit Floating Point and "Precision" 4.
lex

 

[fens3]

lex,
Thanks for the reply, however that did not solve the issue. I did try adjusting the Rollup Settings for the measure but did not have any luck with that either.

 

[BruceReed]

I'm not sure if this is your problem but are you using different sources for your data?

I have found when i use mutiple sources (Excel, iqd, access) for my model, every number comes in as a different power of ten. $150.03 might come in as 15003000.00 or as 0.0001503.

You can fix this up in the "input scale" property of the number.

Also you should NOT summerize/ group/ sort in Impromtu if you want transformer to do this, it just wastes CPU time & you might double count soething.

Good luck
Bruce

 

[Kaiaualad]

A simple way to see exactly what is happening is to filter the fact iqd down to one account number or row,then build the cube.
Transformer is an aggregation engine- dont put aggregated values into it .
It sounds like you are double counting,but go back to the iqd and look at what its bringing into the model first.

 

[ChillAPAC]

We've found that whenever pulling in currency or decimal data we use round-near and normally use 2 decimal places in Impromptu (if using SQL use the or_round function). Then in Transformer use 64-bit floating point Storage Type with a Precision 2.

Bill