Ability to Customize Decimal Scale In Numeric Fields

It would also be beneficial for us to define smaller scale numeric values for more efficient data storage.  Having it always default to a scale of 38 is unnecessary.

Actually this feature is sort of present in TX when synchronizing a source but it is undocumented and unsupported. It involves updating the projectRepository db manually, hence unsupported.

TX was kind enough to help us out in the past with this when we encountered some very funky databases that did not even respect ODBC standards :).

I guess Product Management and developpers should perhaps see how they can turn this into a supported documented feature?

With regards to controlling the precision of numerical data types, there's also a possible issue when ODBC / AnySource source has many numericals and you need them all. If the source itself is too generous with its numeric precision but isn't bound to the limits of MS SQL, you could run into an error when trying to create the stage db. Many numeric fields with maximum precision can exceed the maximum table row size supported in SQL server. One such annoying source we ran into for example is Progress.

Therefore, both for other sources than SQL and for sizes of stage & DW, controlling precision as well would be a very handy feature.

FYI I do think people are mixing up precision and scale here.

Precision is the number of digits which is always 38 for TX. Scale is the number of digits right of the comma or decimal point. So you can create a number 22,16 just by setting 16 in the custom numeric field.

But if you only need a 8,2 number TX effectively creates a 38,2 and wastes valuable bytes.

A workaround for this is to create a view in the stage and use that as basis for your data movement to the stage. In the view you would have to explicitly write for every numeric field:

CAST(YourField AS decimal(8,2)) AS YourField

It is a lot of work though ;-).

I agree that a Numeric value with a default precision of 38 might use more storage than necessary (i.e. in SQL Server it is always 17 storage bytes for every fixed-point Numeric). We could save storage space if it was less than 38. In TX DWA the precision of 38 has been chosen as default for simplicity, and to make sure people would always have the highest accuracy.

However, like my predecessor, I disagree that you loose accuracy. It is not true that precision of 38 reduces accuracy. According to MSDN, scale (which you set to 16) means the number of decimal digits that will be stored to the right of the decimal point. The scale digits will be subtracted from the precision, i.e. 0 <= scale <= precision , where the type is defined as numeric(precision, scale). Which means that precision of 38 is the highest precision you can get. See the link:

https://msdn.microsoft.com/en-us/library/ms187746.aspx

Can you explain what do you mean that you loose accuracy?

If you right click on the image that Jake posted, you should be able to open it in a new tab and will be able to see the problem.

The issue encountered is that when you perform mathematical operations with max sized numeric/decimal fields, SQL automatically reduces the number of decimal places to 6.

To prevent SQL from truncating the decimals at 6 places, the data type precision has to be reduced.

Hi David and Jake,

You are absolutely right. I was not aware of this behavior. I am sorry for doubting you. :)

I made some tests, and to be honest, I don't see that what Microsoft states on MSDN (the link in my previous comment) is what SQL Server actually does.

It is true what you wrote, that the problem with accuracy is not the data type itself, but performing calculations on the Numeric type by SQL Server.

However, even the description of the Numeric data type on MSDN is misleading. Microsoft states that the number of storage bytes depends only on the precision (the first number in the type declaration), but tests in SSMS reveal that it is not true. I run the following SQL in SSMS:

It is not easy to understand the strange results (in green), there is nothing on MSDN I could find which would explain this. Anyway, I believe it is the storage byte length that affects precision. In Jake's example:

select cast(1 as numeric(22,16)) / cast(6 as numeric(22,16))
select cast(1 as numeric(38,16)) / cast(6 as numeric(38,16))

the first result 0.1666666666666666 has a higher precision, and I can see there are 12 storage bytes in the result, and the second result 0.166666 has lower precision, with the number of storage bytes of only 8.

These tests reveal that the assumption that the default precision of 38 in TX DWA would result in the highest accuracy is wrong.

BTW. There is an article on MSDN that explains the rules of calculating precision and scale when dividing two numeric values. The precision and scale of the result when dividing two numeric values is calculated using a complex formula:

Result precision = p1 - s1 + s2 + max(6, s1 + p2 + 1)

Result scale = max(6, s1 + p2 + 1)

Here is the link:

https://msdn.microsoft.com/en-us/library/ms190476(v=sql.130).aspx

The final explanation why this problem happens in SQL Server, as you can read in the link I posted earlier, is scale truncation. When dividing two Numeric numbers which both have precision equal to 38 (which is the default in TX DWA), the result precision will always be grater than 38 (see the formula "Result precision" above). And Microsoft states that:

"When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated."

Which basically means that when dividing two Numeric values with precision equal to 38, SQL Server will reduce the number of fractional digits (scale). The reason for this is that when dividing or multiplying two Numeric values, the integral part of the result (digits to the left of the decimal point) may have more digits than any of the two numbers being multiplied or divided (note that dividing by e.g. 1/100 is the same as multiplying by 100). The integral part is more significant than the fractional part, so it is better to loose some accuracy in the least significant digits than getting an overflow. Which is the reason why Jake experienced the accuracy loss.

It looked a bit strange in the beginning, but the explanation is quite logical. Thanks for pointing this out.

This behaviour makes using numeric fields useless if divisions are applied and > 6 decimal places are required.

I strongly support having the ability to specify precision as well as scale for numeric fields.

Using float as a stopgap runs into issues as well (apart from potential inaccuracy): https://stackoverflow.com/questions/33322778/avg-of-float-inconsistency <-- see designated answer. We have seen real effects of parallel float-based calculations being different between query runs, so that is not a solution either.

Could you give us a status update on this issue? 

The feature has been planned for what we internally refer to as DH14 (we are just about to release DH12).

So I'm guessing that "DH12" was version 18.10 (released autumn 2018), which would make DH13 = 19.2 (March 2019).

So is DH14 = 19.6 (just recently released)? 

Did this feature make it into 19.6? I can't see it in the release notes. 

If not, is there any update on when this feature might be available? 

We had to push this feature to a later release. I will get back with an expected release date.

Do you have any new when this feature will be implemented?

Thanks, Natalia

This feature was added in version 20.5.1