VALUE Encoding doesn't work on integer column

[simoncui]

Many blogs say VALUE Encoding on integer column can save memory, but the truth is Power BI always sets the encoding to HASH.

Encoding Hint? It doens't help, maybe it can even save a little time if you set the encoding for HASH instead of VALUE, since BI starts from VALUE for Integer and often changes it to HASH.

I have done testing this way:

dim table:  generate ID numbers from 1~6,000,000  for dim_key column.

fact table:  dim_key column which values are from dim table, about 17,000,000 rows.

Build relationship between the two tables.

Publish to Premium,  dim_key column in both tables are in VALUE Encoding, excellent.

But the problem is, in our production we've setup incremental policy and there are paritions in fact table.  

After incremental policy is enabled,   they're changed back to HASH.

Any suggestion?

[GilbertQ]

Hi @simoncui 

This can happen because there is a time evaluation on a column to determine to use HASH or VALUE encoding. If this times out it very often will select HASH encoding.

When I have tested this in the past sometimes HASH encoding is actually better, even for an Integer column.

The only other thing I can think of is to go into Tabular Editor and make sure that the column data type is set to Integer?

[simoncui]

Thank you, I have done some additional testings.

I found the partition was not the problem. Even with single parition, it's changed back to HASH after a refresh. Even though it was VALUE after initial publish.

And if  IsAvailableInMDX  is set to False,   at least the key column in dimension table encoded as VALUE.

[GilbertQ]

Hey that makes sense as typically a dimension table is small so it can evaluate all the data to see which encoding would work.

[simoncui]

It seems column size is not the root cause to be HASH.  I have a dimension with only 3 rows, it's also encoded as HASH.😅

[GilbertQ]

Yeah for sure, the engine will decide what works best, unfortunately there is no way to force it to use VALUE encoding.