Solved

Recomended data types for numeric in semantic models

  • 27 June 2023
  • 7 replies
  • 187 views

Userlevel 4
Badge +1

When 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?

 

 

 

icon

Best answer by rory.smith 29 June 2023, 00:34

View original

7 replies

Userlevel 6
Badge +5

Does it appear different if you specify the data format

 

Userlevel 4
Badge +1

@Thomas Lind What will be the option to get a “fix decimal number” in the model?

Userlevel 4
Badge +1

@Thomas Lind In the MDW you can fix the data type, for example:

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?

Userlevel 6
Badge +5

Hi @rvgfox which application did you take this screenshot from?

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.

Userlevel 4
Badge +1

@Christian Hauggaard with tabular editor, but you can see the same in the option “View metrics” of Dax Studio

Userlevel 6
Badge +5

@rvgfox OK I am not sure why Tabular Editor shows it this way. However it appears that the only number data types that are supported in tabular is “Decimal Number” and “Whole Number” as stated in the article. In other words, floating point does not apply to tabular. It is important to distinguish between data types and formats, for example, currency is a format and not a data type.

Userlevel 6
Badge +7

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.

 

Reply