Data Conversion Problem with Transformer

[ellswrth]

Disclaimer - I'm working with Transformer 6.6 - I know it's ancient, but it's legacy cubes that I have to deal with so ...

I'm attempting to get data from a SQL Server 2005 database into Transformer. I've created a linked table in an Access 2003 mdb, and have set that table as the data source for the cube.

There are a number of measures contained in the data which are being summed by time period and then used in calculations.

Measures which have the datatype int are imported correctly and work fine.

Measures which have other numeric datatypes (float, decimal) are not working correctly. SQL queries in the native SQL Server db give the correct results. Like Access queries in the Access mdb also give the same and correct results.

A count of records being processed by Transformer matches the rows being returned by SQL Server.

But the cubes generated by Transformer do not sum the columns correctly when the SQL datatype is float, and they do not sum the columns AT ALL if the datatype is decimal, instead displaying nothing but blank columns in the data source viewer and zeroes in the resulting cube.

I'm tearing my hair out trying to figure out why I'm not getting correct results.

My dataset, for what it's worth. is 240,000 records but I wouldn't expect that to be an issue.

Anyone got any suggestions as to where to look to get my numbers to add correctly?

TIA

[cognostechie]

I am not sure if this applies in your version or scenario but this is what I would generally check:

In the measure properties, check the 'Precision', 'Storage Type' and 'Output Scale' properties.

Ex: If the Storage Type is 64 Bit Floating Point and the data is 2 decimals and the Precision is 0, the aggregates can be wrong because the rounding off will not be accurate when rolling up.