Vertipaq Analyser (in Tabular Editor v3.0.9) shows incorrect datatype

[nariusp]

Steps to reproduce

1. In PBID, add a new table with one column (Enter Data is fine), give it a decimal value.
2. Use Tabular Editor to change the datatype to Currency / Fixed Decimal.
3. Save it and close Tabular Editor.
4. Open Tabular Editor and on the Vertipaq Analyser click Collect Stats. You should see that in the TOM Explorer the field shows as "Decimal" but in the Vertipaq Output is shows as "Int64".

Power BI Desktop used, Version: 2.94.781.0 64-bit (June 2021)

[otykier]

Thanks for reporting. We'll investigate.

[otykier]

VertiPaq Analyzer uses the $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMNS DMV to extract column information, including the data type. In this case, it seems that this DMV returns DBTYPE_I8 (which is Int64) for a column that was originally imported as Int64, and then modified to Currency/Fixed Decimal. This happens even if you do not use Tabular Editor to change the data type of the column.

I think this might be a bug in $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMNS, but it may also be that the engine does not physically change the data type used for storing the values in this case (since Fixed Decimals also use 64 bits of storage - in fact, they are similar to Int64, but divided by 10.000).

Will ask around and get back to you.

[nariusp]

Hi Daniel - note that DAX Studio returns the correct Datatype, "Decimal", where TE3 shows Int64 (just checked just now against the same dummy model).

[otykier]

Maybe we should rename this column in VertiPaq Analyzer to "Storage Data Type", and then have another column called "Logical Data Type". Thoughts?

[nariusp]

I think it needs to line up with DAX Studio as that is where users will be migrating from.

[otykier]

@nariusp agreed. Not sure what's going on, since DAX Studio is using the same code. Will ask Darren and Marco.

[otykier]

Fixed in 3.0.10. Thanks for reporting!

[nariusp]

Wonderful thanks Daniel and co!