+4When we creating a numeric field in a MDW table, TX suggets use a numeric(38,6) for the data type.
When the field it’s translated to the semantic model it’s detected in TX:
But in the model:
And one of the best practice rules says “Do not use floating point data types”.
How can I follow this rule?
Best answer by rory.smith
Hi all,
there is quite some detail to unpack here and this is a favourite topic of mine...
tl;dr:
The reason for Tabular Editor's esoteric typing lies here: “This type corresponds to how Excel stores its numbers, and TOM specifies this type as DataType.Double Enum.” (from the Power BI Desktop Data Types link posted earlier).
Long-winded explanation:
So in Tabular Editor's code this is a Double (as can be seen here: https://docs.tabulareditor.com/api/TabularEditor.TOMWrapper.DataType.html). As this is basically how Excel “stores its numbers” and Excel follows - more or less - the IEEE 754-1985 binary64 standard (see here: https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result), it is also referred to as a Float by oldtimers (nomenclature for these things has changed since IEEE 754-2008). So floating point (pre-2008 standard, all warts included) does apply in a broader sense.
In the distant past floats would usually refer to binary32 IEEE 754-1985 (see: https://en.wikipedia.org/wiki/IEEE_754-1985) types and doubles to the binary64 type: double the bits, hence the name.These days the IEEE 754-2008 revision (see: https://en.wikipedia.org/wiki/IEEE_754-2008_revision) add the decimal32/64/128 types to avoid some of the drawbacks of binary calculation among other things. For more details on that, I can recommend “What Every Computer Scientist Should Know About Floating-Point Arithmetic” by David Goldberg from ACM Computing Surveys, Vol 23, No 1. March 1991.
The Fixed Decimal Number / Currency types should only be used at the presentation layer really as they are integers internally with the eventual precision of a Decimal (19,4) but have side-effects depending on the calculations you use them in (see: https://stackoverflow.com/questions/582797/should-you-choose-the-money-or-decimalx-y-datatypes-in-sql-server).
TimeXtender implementation effects:
I wonder how the conversion between SQL Server's Numeric types and Tabular's types are done as the maximum precision for the latter is 15 and TimeXtender's fallback default is 38.
+5Does it appear different if you specify the data format
+4
And in the physical table you will get:
What will be the setup to get in the physical table a numeric field or currency field data type?
+5Hi
When I import my tabular model into Visual Studio 2019 it looks like this
Please see this article for more info on data types supported in tabular models.
+4+5
+7Hi all,
there is quite some detail to unpack here and this is a favourite topic of mine...
tl;dr:
The reason for Tabular Editor's esoteric typing lies here: “This type corresponds to how Excel stores its numbers, and TOM specifies this type as DataType.Double Enum.” (from the Power BI Desktop Data Types link posted earlier).
Long-winded explanation:
So in Tabular Editor's code this is a Double (as can be seen here: https://docs.tabulareditor.com/api/TabularEditor.TOMWrapper.DataType.html). As this is basically how Excel “stores its numbers” and Excel follows - more or less - the IEEE 754-1985 binary64 standard (see here: https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result), it is also referred to as a Float by oldtimers (nomenclature for these things has changed since IEEE 754-2008). So floating point (pre-2008 standard, all warts included) does apply in a broader sense.
In the distant past floats would usually refer to binary32 IEEE 754-1985 (see: https://en.wikipedia.org/wiki/IEEE_754-1985) types and doubles to the binary64 type: double the bits, hence the name.These days the IEEE 754-2008 revision (see: https://en.wikipedia.org/wiki/IEEE_754-2008_revision) add the decimal32/64/128 types to avoid some of the drawbacks of binary calculation among other things. For more details on that, I can recommend “What Every Computer Scientist Should Know About Floating-Point Arithmetic” by David Goldberg from ACM Computing Surveys, Vol 23, No 1. March 1991.
The Fixed Decimal Number / Currency types should only be used at the presentation layer really as they are integers internally with the eventual precision of a Decimal (19,4) but have side-effects depending on the calculations you use them in (see: https://stackoverflow.com/questions/582797/should-you-choose-the-money-or-decimalx-y-datatypes-in-sql-server).
TimeXtender implementation effects:
I wonder how the conversion between SQL Server's Numeric types and Tabular's types are done as the maximum precision for the latter is 15 and TimeXtender's fallback default is 38.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
