I’m currently working with a bunch of data about real estate. The properties are identified by an ugly string. Of DOOOM! There are quite a few relationships built on this ugly sucker, which led me to wonder… what if I mapped them all into a simple integer?
I whipped up some totally bogus sample data to test this, using Property Ids that look like “WA534879” and “CA35849”. Properties have attributes like SqrFt, # of Beds, and what region it is in. And I hooked up a few million rows of “facts” about occupancy: actuals, budgeted and forecasted, all on a per month basis. Model here:
With about a half million rows of properties (a bit absurd, but it’s hard to make power pivot slow 
) and 3 million rows in each of the fact tables, I looked at the memory usage and here is how she looks:
Not surprisingly, the property IDs are taking up nearly all the memory, and certainly requiring a hash table in each table to hold the strings, as discussed in this post.
Our plan is pretty is simple, assign integers starting with zero to each of the property IDs then use those integers throughout the model. We can include an extra lookup table to get back from the integers to the original property IDs if users still need to see them (on rows, slicers, whatever).
For clarity, all of this mapping is happening in the original data sources — and I really hope that is SQL or you are probably having an exceptionally bad day 
The model is not interestingly different – we added the table to map from the integers back into original string-style property IDs. The real difference is that instead of those string id’s, we are using the simple integer for all the relationships (now called just “Id” because I am a naming WIZARD).
And here are the results from a memory utilization standpoint:
Not bad! Each of our tables dropped from ~22mb to about 9mb. And if you are to believe the Tiny Lizard, “in Power Pivot, all performance problems are memory problems”, so we should expect this to lead to better performance.
To test this, I … well, I had a really un-fun evening trying to find a measure that wasn’t “too fast to measure” and not “so slow that I would eat a bag of chips between measurements”. I have a snacking problem.
In the end I found a measure to get the count of properties where the average occupancy (actual) was greater than the budgeted and forecasted occupancies, split out by state… then I would click a slicer for the Month. Total. Science.
The results were that the “better” model (that uses integers for relationships)… was indeed faster. It wasn’’t crazy faster, but the time to slice my pivot table dropped from 25 seconds to 15 seconds. Which is at least… interestingly faster?
I have to say, this kind of feels like a problem that Power Pivot should have handled for me natively. Like, if it had a concept of a “global hash table” that was shared across tables… I wouldn’t have to jump through these hoops? It’s a theory.
- The streak is alive! – August 10, 2017
- DAX KEEPFILTERS Plus Bonus Complaining! – July 20, 2017
- VAR: The best thing to happen to DAX since CALCULATE() – July 11, 2017
- Review: Analyzing Data with Power BI and Power Pivot for Excel – June 20, 2017
- Power BI Date Table – June 2, 2017
Scott Senkeresty here… Founder and Owner of Tiny Lizard. Yes, I have done some stuff: A Master’s Degree in Computer Science from California Polytechnic State University in beautiful San Luis Obispo, California. Over twelve years developing software for Microsoft, across Office, Windows and Anti-Malware teams… Read More >>
Very interesting Scott. This creates more questions (for me) than it answers. I understand from Marco and Alberto that PowerPivot uses both Run Length Encoding and also Dictionary Encoding. I would have expected that Dictionary Encoding would create the exact same benefits as you have extracted here by replacing the unique keys with integers. So why is that? Maybe a question for the experts from Italy!
Also I note from watching Marco/Alberto TV that it is possible to load up your data model into SSAS Tabular and then turn on the profiler tools to accurately time the query performance for the various approach (clear the cache between events). Now unfortunately I am not a SQL expert, so I would love to see someone provide a tutorial on how to do this :-).
So… before I adjust the model, there would have been dictionary (hash) encoding on each table. So, if I need to join 2 tables… I am doing a hash lookup on each side to go from the “just a few bits” in the table… and find the actual value to join on. Those lookups are slower… than NOT doing a lookup 🙂 And these hash tables are 1 per table… so there are many of them.
Go re-read my post on How PowerPivot Stores and Compresss Data, and look at value vs hash encoding.
Ya, you can use profiler tools if running tabuluar (which I was), but you can ALSO enable tracing in your power pivot options dialog… run a scenario… then open the trace file in your profiler. So, doable in straight excel too! But, who wants to compare 250ms to 150ms? 🙂