Columns with source data type money behaving incorrect

Hi,

Money types are somewhat problematic in certain cases. Microsoft's own documentation states: "You can experience rounding errors through truncation, when storing monetary values as money and smallmoney. Avoid using this data type if your money or currency values are used in calculations. Instead, use the decimal data type with at least four decimal places.” See: (https://learn.microsoft.com/en-us/sql/t-sql/data-types/money-and-smallmoney-transact-sql?view=sql-server-ver16). More details to be found here: https://stackoverflow.com/questions/224462/storing-money-in-a-decimal-column-what-precision-and-scale .

I assume the problem stems from the fact that Parquet has no Money type and TimeXtender will either convert this to String or some Decimal type (hopefully with the correct scale and precision). Subsequently, when loading from ODX into DWH there may be another conversion to a Decimal(38,4) which is incorrect. Given that TimeXtender nowadays usually correctly applies precision other than 38 when defined in the source, I am expecting the Parquet format to be the problem here. Otherwise it would be Decimal(19,4). This may introduce differences in rounding because the Money type uses Banker's rounding and Decimal applies truncation (your deviating results are always lower than the expected). This is why you usually store one more decimal than you require; how many you require depends on the compliancy environment you are working in. Any calculation with Money (and with Decimals) requires you to use enough space to capture the result without truncation, see also: https://learn.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=sql-server-ver16 for precision and scale under mathematical operations.

I would check to see what data type is allocated to Money in the Parquet file, if it is something other than Decimal(19,4) then that will be a cause for deviation.

Hi @rory.smith ,

Thanks for your reply.
Only way for me to check the Parquet file is the program ParquetViewer. This has a built in metadata viewer which shows the following:

{
"Path": "Bedrag",
"Type": "FLOAT",
"TypeLength": null,
"LogicalType": null,
"RepetitionType": "OPTIONAL",
"ConvertedType": "",
"Children": []
},
{
"Path": "Korting",
"Type": "FLOAT",
"TypeLength": null,
"LogicalType": null,
"RepetitionType": "OPTIONAL",
"ConvertedType": "",
"Children": []
},
{
"Path": "NettoBedrag",
"Type": "FLOAT",
"TypeLength": null,
"LogicalType": null,
"RepetitionType": "OPTIONAL",
"ConvertedType": "",
"Children": []
}

It clearly isn't a decimal, but i'm totally unsure what i'm looking at. Is there another way to determine data types in Parquet files?

What do you think is the right way to move forward? Should override data types provide a solution? Or is a Query Table the only way to solve this?

Hi,

so the data is written to Parquet as Float apparently. That is incorrect (assuming the source is SQL Server and the column has a Money type) and will lead to interpretation issues. Floats and Decimals are both approximations of real numbers with different drawbacks and advantages for each. In this case, to conserve the exact value it should go: Money(source) → LogicalType: Numeric(19,4) (ODX) → Numeric(19,4) (DWH)

See: https://github.com/apache/parquet-format/blob/master/LogicalTypes.md

For now I would use QueryTables to work around this.

@Christian Hauggaard : I think this requires some investigation

Hi Rory,

Thanks for your advice. We'll go forward with Query Tables and converting the money data types to DECIMAL(19,4). 

I'm also curious if this is considered a flaw in ODX and if this can be mitigated.

Kind regards,

Jogchum

Hi @JogchumSiR 

I am unable to reproduce the issue you describe in 6698.1 

I have added a table with the following 2 columns on my local SQL server, and inserted the following values

 Then I created a TX SQL data source and mapped it to my ODX with data lake storage

Then I executed the sync and the transfer task, and brought this table into my Azure SQL db

Do you notice anything different between our setups? Which data source are you using?

Hi @Christian Hauggaard ,

for other types of data source it may be different but for SQL Server the money and smallmoney type should not be converted to decimal(38,4). That looks like the parquet file is not getting the complete metadata or the ODX → DWH conversion is not taking the metadata into account. While that should not influence the value getting loaded into the DWH, it will trip people up once they start multiplying or dividing those fields and data type conversions can be performance killers.

If the source system has some kind of money type that has more accuracy than SQL's 4, you will potentially get rounding issues in the conversion as 147.817430 will become 147.8174 but 147.817460 will not due to truncation vs. banker's rounding (in the case of (19,6)). I would expect any system dealing with multiple currencies to store with (x,6) as this is a standard used internationally.

Hi Both,

Only thing i can think of is the specific setting on the column and the SQL Server version of the source. Our source is running SQL Server 2016 (On-Prem). Also our data set size is about 45 mln rows, so there may also something going wrong because of the size of the data set (just guessing).

Kind regards,

Jogchum

Hi @JogchumSiR 

I tested with SQL Server 2016 as the source

 And I still get the following values in my Azure SQL db DW

I am using the following properties (default) for money and small money source fields, are you using the same or different settings?

Hi @Christian Hauggaard,

Those precisions are matching those of my columns. Also i want to notice not every row was being wrong. Some did go through just fine. I'm sorry but i couldn't tell you more on how to recreate the issue. We did not change the precision or any other field setting ourselves.

Hi @JogchumSiR ,

are any of the money/smallmoney fields in your source calculated columns?

Hi @rory.smith ,

The columns are purely values, only populated by software. There is no calculation definition set on the column itself

Hi @JogchumSiR can you please try to setup a simple test as I have done with one record by creating the same table as you have currently (i.e. running the create table as script and giving the table a new name) and then insert only one record with a value 147.8174? 

Do you get the same rounding error with this test?

Hi @Christian Hauggaard ,

I've retested this.
I created a seperate table in SQL for this testing purpose:
 

Loaded into ODX and transferred from ODX to a testing Data Area in our dev environment:
 

The data is now different as shown from the preview:
 

Hope this helps.