Forum Discussion
Forcing Vertipaq to update data type encoding
After the first scan, if it sees an outlier that can trigger a reencoding.
"Once the decision is made, SSAS start to compress the column using the chosen algorithm. Occasionally, values that were not in the original sampling can cause the process to require reevaluation. For example, SAS might read a few million rows in which the values are in the range of 100-200, making value encoding the best choice. However, after those millons of rows, an outlier might suddenly appear - for example a large number like 60,000,000 ~ triggering a reencoding of the column."
This is from "Tabular Modeling in Microsoft SQL Server Analysis Services" 2nd edition. p 356Based on that I think you want them to be as close to sequential as they can be. We have even gone so far as to replace a key column that was Client_Claim_Seq_ID like "ABC000012357007" with an integer identity on the master table where the data is inserted so it would help it to be VALUE encoded since the identity on the table increments by 1 with each record insert.
Since trial and error seems to be the only way to truly understand how PBI works under the hood, I'm spending more time today tinkering.
Theory: The encoding depends upon the absolute cardinality.
Experiment: Load the same columns from the same table, including my integer PK, but differing number of rows.
Outcomes:
- Loading just the first 100,000 rows: VALUE encoding
- Loading just the first 5,000,000 rows: VALUE encoding
- Loading just the first 15,000,000 rows: VALUE encoding
- Loading just the first 20,000,000 rows: VALUE encoding
- Loading just the first 28,000,000 rows: VALUE encoding
- Loading all 28,032,233 rows: HASH encoding
However, based on what jdbuchanan71 said upthread, I noticed that the top 1% of my rows - the last 28,000 or so - have integer PKs that are significantly larger than the majority of the table. In other words, there's a big gap in my PK structure.
So I created a surrogate PK, using ROW_NUMBER(), that guarantees that the surrogates are a) much smaller in value, and b) absolutely sequential and consecutive. Re-loading all 28,032,233 rows and including the new surrogate PK column alongside the original PK from the database tables, we have a winner! In this screenshot, the "ResultID" column is my original PK from the database, and the "ResultPK" column is my new sequential surrogate PK. That's a nice size savings!
I have also learned from this exercise that the Vertipaq engine will re-assess the encoding with each change of source query and reload. That's good to know.
That is a great space save! Glad you were able to get it to work.