Vertipaq Column encoding - HASH v VALUE

[martinrowe]

Is there anyway to understand why vertipaq chooses to encode a column in HASH v VALUE? I have 2 tables where 2 column values are sequential primary keys in MySQL database, one is also a foreign key in the other table;

Transaction_History_ID - Primary key in Transaction_History
Subscription_ID - Primary Key in Applications (also foreign key in Transaction_History)

When the model was first created the values were encoded as VALUE

I then amended the model to remove Gateway_Transaction_ID from the Transaction_History table and refreshed. Now after this refresh it has decided to encode the same 2 columns in HASH rather than VALUE and as a result the dictionary size has balooned.

Why would it do this?

To me the obvious choice would be to set this as VALUE given the nature of the source column. I have tried to use Tabular Editor to set the encoding hint as VALUE however it doesnt seem to act on this and has decided that HASH is now required.

The below 2 articles I have looked at, but I dont think they provide an insight as to why this would happen when between the 2 refreshes when the data model is essentially the same bar removal of 2 columns.

https://community.powerbi.com/t5/Desktop/Vertipaq-Engine-VALUE-vs-HASH/m-p/690874#M333145

https://www.mssqltips.com/sqlservertip/5150/improve-analysis-services-tabular-2017-processing-with-e...

[AnonymousPerson]

@martinrowe I have this same exact question on disobeyed encoding hints. Did you ever figure out an answer about your quest for the smallest dictionary size?

[mbujtor]

Hi Martin,

I have the same issue:

- the primary key in my fact table (FactKey) got value encoding

- the primary key in my dimension table (DimKey) - also a foreign key in the fact table- got hash encoding

of course both colums are Int64 type.

Did you find any solution/explanation for this?

Thank you,

Máté

[v-xicai]

Hi @martinrowe ,

If you still have this issue for Power BI, you'd better create a support ticket in Power BI Support , Scroll down and click "CREATE SUPPORT TICKET", to get further help.

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.