cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

Vertipaq Engine: VALUE vs HASH

Hi guys,

this time I need a help from you.

I'm working on a dataset which size became about 600 MB and consumes three times more RAM when loaded. That's not what I'd expect so I've downloaded the great tool Vertipaq Analyzer (from @AlbertoFerrari, @marcorusso ) and tried to find out which table/column consumes the most RAM. Till now it's boring staff, but then I saw that Int64 columns don't use value encoding but dictionary encoding. And it also means that the size used by these columns explodes.

After some experiments I have found out that if I have an Int64 column with 225 000 rows, Vertipaq still uses a value encoding, but if it is ~ 240 000+ rows it uses dictionary encoding and the consumed memory is 8x bigger than expected, see the screenshot below.

Does anybody know the algorithms or rules used in Vertipaq? Can we influece those somehow in PowerBI?
EDIT: And I've read the Vertipaq part of Definitive Guide to DAX, The: Business intelligence with Microsoft Excel, SQL Server Analysis Servic... and The VertiPaq Engine in DAX.

 

Unbenannt.PNG

 

Unbenannt2.PNG

 

3 REPLIES 3
Mholsen Member
Member

Re: Vertipaq Engine: VALUE vs HASH

I'm facing the same problem with the HASH encoding leading to a much larger column size than an equivalent column with VALUE encoding (between 3 and 4 times larger).

I'm testing if we can force the encoding to be VALUE using encoding hints, as suggested in this article:

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

I will update my answer if I get a solution.

Super User
Super User

Re: Vertipaq Engine: VALUE vs HASH

Hi @Mholsen,

you can't force it, but you can give a hint to the Vertipaq Analyzer, that it should use Value instead of Hash. It can be set by using Tabular Editor in a PBIT file. You can also write a simple C# code which does the transformation for all columns, which supports Value encoding and so on. I will write a blog post about that, the topic is waiting in a queue 😉

 

You can use for example following snippet in the Tabular Editor:

// for all Int64 columns set EncodingHint
foreach(var column in Model.Tables.SelectMany(t => t.Columns)) 
{
    if(column.DataType == DataType.Int64)         
        column.EncodingHint = EncodingHintType.Value;
}
Mholsen Member
Member

Re: Vertipaq Engine: VALUE vs HASH

Cool, look forward to that article and I will take a look at hinting using Tabular Editor

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,526)