cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
simoncui
Frequent Visitor

VALUE Encoding doesn't work on integer column

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?

5 REPLIES 5
GilbertQ
Super User
Super User

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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.

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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.😅

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors