Forum Discussion

ctmullins's avatar
ctmullins
Advocate II
4 years ago
Solved

Forcing Vertipaq to update data type encoding

Hi guys!   I'm working on a PBIX with a large dataset, and trying to keep under the 1GB compressed limit.  My largest fact table has a cardinality of 28 million, with an integer primary key.   Pr...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    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 356

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

ctmullins's avatar
ctmullins
Advocate II
4 years ago

Thanks jdbuchanan71 !  But - already tried that.  I had seen the mention of ranking them in some Microsoft documentation somewhere, but that too failed to force the engine to switch it to VALUE.  Do they need to be consecutive, with no gaps in between?  Do the values need to be spaced evenly (consistent gap between them)?  Or is it sufficient for them to simply be ranked in ascending order?  Once again, the documentation doesn't state, and the behavior doesn't match what the documentation does state.

 

I'm still wondering exactly what it takes to trigger a re-evaluation by the engine.  One document says that the engine will evaluate based on the "first scan" of the table.  Does that mean that it uses those values from then on, regardless of changes that I make in the upstream layer?  Or does "first scan" mean "first scan after each change is made" (which doesn't exactly make much sense to me)?

 

I will look into the "Available in MDX"; haven't seen mention of that before, so thanks!