How to reduce your Power BI model size by 90%!
Have you ever wondered what makes Power BI so fast and powerful when it comes to performance? Learn on a real-life example about data model optimization and general rules for reducing data model
Have you ever wondered what makes Power BI so fast and powerful when it comes to performance? So powerful, that it performs complex calculations over hundreds of millions of rows in a blink of an eye.
In this article, we will dig deep to discover what is “under the hood” of Power BI, how your data is being stored, compressed, queried, and finally, brought back to your report. Once you finish reading, I hope that you will get a better understanding of the hard work happening in the background and appreciate the importance of creating an optimal data model in order to get maximum performance from the Power BI engine.
First look under the hood — Formula Engine & Storage Engine
First, I want you to meet the VertiPaq engine, the “brain & muscles” of the system behind not only Power BI, but also SSAS Tabular and Excel Power Pivot. Truth to be said, VertiPaq represents only one part of the storage engine within the Tabular model, besides DirectQuery, which we will talk about in one of the next articles.
When you send the query to get data for your Power BI report, here is what happens:
Formula Engine (FE) accepts the request, processes it, generates the query plan, and finally executes it
Storage Engine (SE) pulls the data out of the Tabular model to satisfy the request issued within the query generated by the Formula Engine
Storage Engine works in two different ways to retrieve requested data: VertiPaq keeps the snapshot of the data “in-memory”. This snapshot can be refreshed from time to time, from the original data source.
On the opposite, DirectQuery doesn’t store any data. It just forwards the query straight to the data source for every single request.
Data in the Tabular model is usually stored either as an in-memory snapshot (VertiPaq) or in DirectQuery mode. However, there is also a possibility of implementing a hybrid Composite model, which relies on both architectures in parallel. I’ll discuss the Composite models in more detail in one of the next articles.
Formula Engine — “Brain” of Power BI
As I already stressed, Formula Engine accepts the query, and since it’s able to “understand” DAX (and MDX also, but it is out of the scope of this article), it “translates” DAX into a specific query plan, consisting of physical operations that need to be executed in order to get results back.
Those physical operations can be of different nature: joins between multiple tables, filtering, or aggregations. It’s important to know that Formula Engine works in a single-threaded way, which means that requests to Storage Engine are always being sent sequentially.
Storage Engine — “Muscles” of Power BI
Once the query has been generated and executed by the Formula Engine, the Storage Engine comes into the scene. It physically goes through the data stored within the Tabular model (VertiPaq) or goes directly to a different data source (SQL Server for example, if DirectQuery storage mode is in place).
When it comes to specifying the storage engine for the table, there are three possible options to choose from:
Import mode — based on VertiPaq. Table data is being stored “in-memory” as a snapshot. Data can be refreshed periodically
DirectQuery mode — data is being retrieved from the data source at the query time. Data resides in its original source before, during, and after the query execution
Dual mode — a combination of the first two options. Data from the table is being loaded into memory, but at the query time it can be also retrieved directly from the source
As opposed to Formula Engine which doesn’t support parallelism, the Storage Engine can work asynchronously.
Meet VertiPaq Storage Engine
As we have drawn a big picture previously, let me explain in more detail what VertiPaq does in the background to boost the performance of our Power BI reports.
When we choose Import mode for our Power BI tables, VertiPaq performs the following actions:
Reads the data source, transforms data into a columnar structure, encodes and compresses data within each of the columns
Establishes a dictionary and index for each of the columns
Prepares and establishes relationships
Computes all calculated columns and calculated tables and compresses them
Two main characteristics of VertiPaq are:
VertiPaq is a columnar database
VertiPaq is an in-memory database
As you can see in the illustration above, columnar databases store and compress data in a different way than traditional row-store databases. Columnar databases are optimized for vertical data scanning, which means that every column is structured in its own way and physically separated from other columns!
Without going into a deep analysis of the advantages and drawbacks of row-store vs column-store databases, since it would require a separate series of articles, let me just pinpoint a few key differentials in terms of performance.
With columnar databases, single-column access is fast and effective. Once the computation starts to involve multiple columns, things become more complex, as intermediary steps’ results need to be temporarily stored in some way.
Simply said, columnar databases are more CPU exhaustive, while row-store databases increase I/O, because of many scans of useless data.
So far, we painted a big picture of the architecture that enables Power BI to fully shine as an ultimate BI tool. Now, we are ready to dive deeper into specific architectural solutions and consequentially leverage this knowledge to make the most of our Power BI reports, by tuning our data model to extract the maximum from the underlying engine.
Inside VertiPaq in Power BI — Compress for success!
As you might recall from the previous part of this article, we scratched the surface of VertiPaq, a powerful storage engine, which is “responsible” for the blazing-fast performance of most of your Power BI reports (whenever you are using Import mode or Composite model).
3, 2, 1…Fasten your seatbelts!
One of the key characteristics of the VertiPaq is that it’s a columnar database. We learned that columnar databases store data optimized for vertical scanning, which means that every column has its own structure and is physically separated from other columns.
That fact enables VertiPaq to apply different types of compression to each of the columns independently, choosing the optimal compression algorithm based on the values in that specific column.
Compression is achieved by encoding the values within the column. But, before we dive deeper into a detailed overview of encoding techniques, just keep in mind that this architecture is not exclusively related to Power BI — in the background is a Tabular model, which is also “under the hood” of SSAS Tabular and Excel Power Pivot.
Value Encoding
This is the most desirable value encoding type since it works exclusively with integers and, therefore, requires less memory than, for example, when working with text values.
How does this look in reality? Let’s say we have a column containing a number of phone calls per day, and the value in this column varies from 4.000 to 5.000. What the VertiPaq would do, is to find the minimum value in this range (which is 4.000) as a starting point, then calculate the difference between this value and all the other values in the column, storing this difference as a new value.
At first glance, 3 bits per value might not look like a significant saving, but multiply this by millions or even billions of rows and you will appreciate the amount of memory saved.
As I already stressed, Value Encoding is being applied exclusively to integer data type columns (currency data type is also stored as an integer).
Hash Encoding (Dictionary Encoding)
This is probably the most used compression type by a VertiPaq. Using Hash encoding, VertiPaq creates a dictionary of the distinct values within one column and afterward replaces “real” values with index values from the dictionary.
Here is an example to make things more clear:
As you may notice, VertiPaq identified distinct values within the Subjects column, built a dictionary by assigning indexes to those values, and finally stored index values as pointers to “real” values. I assume you are aware that integer values require way less memory space than text, so that’s the logic behind this type of data compression.
Additionally, by being able to build a dictionary for any data type, VertiPaq is practically data type independent!
This brings us to another key takeaway: no matter if your column is of text, bigint or float data type — from VertiPaq perspective it’s the same — it needs to create a dictionary for each of those columns, which implies that all these columns will provide the same performance, both in terms of speed and memory space allocated! Of course, by assuming that there are no big differences between dictionary sizes between these columns.
So, it’s a myth that the data type of the column affects its size within the data model. It affects dictionary size only. On the opposite, the number of distinct values within the column, which is known as cardinality, mostly influences column memory consumption. Again, we will examine cardinality in more depth in one of the next articles.
RLE (Run-Length-Encoding)
The third algorithm (RLE) creates a kind of mapping table, containing ranges of repeating values, avoiding storing every single (repeated) value separately.
Again, taking a look at an example will help to better understand this concept:
In real life, VertiPaq doesn’t store Start values, because it can quickly calculate where the next node begins by summing previous Count values.
As powerful as it might look at first glance, the RLE algorithm is highly dependent on the ordering within the column. If the data is stored the way you see in the example above, RLE will perform great. However, if your data buckets are smaller and rotate more frequently, then RLE would not be an optimal solution.
One more thing to keep in mind regarding RLE: in reality, VertiPaq doesn’t store data the way it is shown in the illustration above. First, it performs Hash encoding, creating a dictionary of the subjects, and then applies the RLE algorithm, so the final logic, in its most simplified way, would be something like this:
So, RLE occurs after Hash Encoding, in those scenarios when VertiPaq “thinks” that it makes sense to compress data additionally (when data is ordered in a way that RLE would achieve better compression).
Re-Encoding considerations
No matter how “smart” VertiPaq is, it can also make some bad decisions, based on incorrect assumptions. Before I explain how re-encoding works, let me just briefly iterate through the process of data compression for a specific column:
VertiPaq scans sample of rows from the column
If the column data type is not an integer, it will look no further and use Hash encoding
If the column is of integer data type, some additional parameters are being evaluated: if the numbers in the sample linearly increase, VertiPaq assumes that it is probably a primary key and chooses Value encoding
If the numbers in the column are reasonably close to each other (the number range is not very wide, like in our example above with 4.000–5.000 phone calls per day), VertiPaq will use Value encoding. On the contrary, when values fluctuate significantly within the range (for example between 1.000 and 1.000.000), then Value encoding doesn’t make sense and VertiPaq will apply the Hash algorithm
However, it can happen sometimes that VertiPaq makes a decision about which algorithm to use based on the sample data, but then an outlier pops up and VertiPaq needs to re-encode the column from scratch.
Let’s use our previous example for the number of phone calls: VertiPaq scans the sample and chooses to apply Value encoding. Then, after processing 10 million rows, all of a sudden it found a 500.000 value (it can be an error, or whatever). Now, VertiPaq re-evaluates the choice and it can decide to re-encode the column using the Hash algorithm instead. Surely, that would impact the whole process in terms of the time needed for reprocessing.
Finally, here is the list of parameters (in order of importance) that VertiPaq considers when choosing which algorithm to use:
Number of distinct values in the column (Cardinality)
Data distribution in the column — column with many repeating values can be better compressed than one containing frequently changing values (RLE can be applied)
Number of rows in the table
Column data type — impacts only dictionary size
Reducing the data model size by 90% — real story!
After we laid the theoretical ground for understanding the architecture behind the VertiPaq storage engine, and which types of compression it uses to optimize your Power BI data model, it’s the right moment to make our hands dirty and apply our knowledge in real-life case!
Starting point = 777 MB
Our data model is quite simple, yet memory-exhaustive. We have a fact table (factChat) which contains data about live support chats and one dimension table (dimProduct), that relates to a fact table. Our fact table has around 9 million rows, which should not be a big deal for Power BI, but the table was imported as it is, without any additional optimization or transformation.
Now, this pbix file consumes a whopping 777 MB!!! You can’t believe it? Just take a look:
Just remember this picture! Of course, I don’t need to tell you how much time this report needs to load or refresh, and how our calculations are slow because of the file size.
…and it’s even worse!
Additionally, it’s not just 777 MBs that takes our memory, since memory consumption is being calculated taking into account the following factors:
PBIX file
Dictionary
Column hierarchies
User-defined hierarchies
Relationships
Now, if I open Task Manager, go to the Details tab, and find the msmdsrv.exe process, I will see that it burns more than 1 GB of memory!
Oh, man, that really hurts! And we haven’t even interacted with the report! So, let’s see what we can do to optimize our model…
Rule #1 — Import only those columns you really need
The first and the most important rule is: keep in your data model only those columns you really need for the report!
That being said, do I really need here both the chatID column, which is a surrogate key, and sourceID column, which is a primary key from the source system? Both these values are unique, so even if I need to count the total number of chats, I would still be fine with only one of them.
Let me check how the file looks now:
By removing just one unnecessary column, we saved more than 100 MB!!! Let’s examine further what can be removed without taking a deeper look (and we will come later into this, I promise).
Do we really need both the original start time of the chat and UTC time, one stored as a Date/Time/Timezone type, the other as Date/Time, and both going to a second precision??!!
Let me get rid of the original start time column and keep only UTC values.
Another 100 MB of wasted space is gone! By removing just two columns we don’t need, we reduced the size of our file by 30%!
Now, that was without even looking into more details of the memory consumption. Let’s now turn on DAX Studio, my favorite tool for troubleshooting Power BI reports. As I already stressed a few times, this tool is a MUST if you plan to work seriously with Power BI — and it’s completely free!
One of the features in DAX Studio is a VertiPaq Analyzer, a very useful tool built by Marco Russo and Alberto Ferrari from sqlbi.com. When I connect to my pbix file with DAX Studio, here are the numbers related to my data model size:
I can see here what are the most expensive columns in my data model and decide if I can discard some of them, or do I need to keep them all.
At first glance, I have few candidates for removal — sessionReferrer and referrer columns have high cardinality and therefore can’t be optimally compressed. Moreover, as these are text columns and need to be encoded using a Hash algorithm, you can see that their dictionary size is extremely high! If you take a closer look, you can notice that these two columns take almost 40% of my table size!
After checking with my report users if they need any of these columns, or maybe only one of them, I’ve got a confirmation that they don’t perform any analysis on those columns. So, why on Earth should we bloat our data model with them??!!
Another strong candidate for removal is the LastEditDate column. This column just shows the date and time when the record was last edited in the data warehouse. Again checked with report users, they didn’t even know that this column exists!
I removed these three columns and the result is:
Oh, God, we halved the size of our data model by just removing a few unnecessary columns.
Truth be said, there are a few more columns that could be dismissed from the data model, but let’s now focus on other techniques for data model optimization.
Rule #2 — Reduce the column cardinality!
As you may recall from my previous article, the rule of thumb is: the higher the cardinality of a column, the harder for VertiPaq to optimally compress the data. Especially, if we are not working with integer values.
Let’s take a deeper look into VertiPaq Analyzer results:
As you see, even if the chatID column has higher cardinality than the datetmStartUTC column, it takes almost 8 times less memory! Since it is a surrogate key integer value, VertiPaq applies Value encoding, and the size of a dictionary is irrelevant. On the other hand, Hash encoding is being applied for the column of date/time data type with high cardinality, so the dictionary size is enormously higher.
There are multiple techniques for reducing the column cardinality, such as splitting columns. Here are a few examples of using this technique.
For Integer columns, you can split them into two even columns using division and modulo operations. In our case, it would be:
SELECT chatID/1000 AS chatID_div
,chatID % 1000 AS chatID_mod……….
This optimization technique must be performed on the source side (in this case by writing a T-SQL statement). If we use the calculated columns, there is no benefit at all, since the original column has to be stored in the data model first.
A similar technique can bring significant savings when you have decimal values in the column. You can just simply split values before and after the decimal as explained in this article.
Since we don’t have any decimal values, let’s focus on our problem — optimizing the datetmStartUTC column. There are multiple valid options to optimize this column. The first is to check if your users need granularity higher than the day level (in other words, can you remove hours, minutes, and seconds from your data).
Let’s check what savings would this solution bring:
The first thing we notice is that our file is now 255 MB, so 1/3 of what we started from. VertiPaq Analyzer’s results show that this column is now almost perfectly optimized, going from taking over 62% of our data model to just slightly over 2.5%! That’s huuuuge!
However, it appeared that day-level grain was not fine enough and my users needed to analyze figures on the hour level. OK, so we can at least get rid of minutes and seconds and that would also decrease the cardinality of the column.
So, I’ve imported values rounded per hour:
SELECT chatID
,dateadd(hour, datediff(hour, 0, datetmStartUTC), 0) AS datetmStartUTC
,customerID
,userID
,ipAddressID
,productID
,countryID
,userStatus
,isUnansweredChat
,totalMsgsOp
,totalMsgsUser
,userTimezone
,waitTimeSec
,waitTimeoutSec
,chatDurationSec
,sourceSystem
,subject
,usaccept
,transferUserID
,languageID
,waitFirstClick
FROM factChat
It appeared that my users also didn’t need a chatVariables column for analysis, so I’ve also removed it from the data model.
Finally, after disabling Auto Date/Time in Options for Data Load, my data model size was around 220 MB! However, one thing still bothered me: the chatID column was still occupying almost 1/3 of my table. And this is just a surrogate key, which is not used in any of the relationships within my data model.
Since there would be no benefit in keeping the chatID column at all, because it’s not being used anywhere in our data model, I’ve removed it from the model entirely, and that saved additional space!
And one last time, let’s check the pbix file size:
Please recall the number we started at: 777 MB!
So, I’ve managed to reduce my data model size by almost 90%, applying some simple techniques that enabled the VertiPaq storage engine to perform more optimal compression of the data.
And this was a real use case, which I faced a few years ago!
General rules for reducing data model size
To conclude, here is the list of general rules you should keep in mind when trying to reduce the data model size:
Keep only those columns your users need in the report! Just sticking with this one single rule will save you an unbelievable amount of space, I assure you…
Try to optimize column cardinality whenever possible. The golden rule here is: test, test, test…and if there is a significant benefit from, for example, splitting one column into two, or substituting a decimal column with two whole number columns, then do it! But, also keep in mind that your measures need to be rewritten to handle those structural changes, to display expected results. So, if your table is not big, or if you have to rewrite hundreds of measures, maybe it’s not worth splitting the column. As I said, it depends on your specific scenario, and you should carefully evaluate which solution makes more sense
Same as for columns, keep only those rows you need: for example, maybe you don’t need to import data from the last 10 years, but only 5! That will also reduce your data model size. Talk to your users, and ask them what they really need, before blindly putting everything inside your data model
Aggregate your data whenever possible! That means — fewer rows, and lower cardinality, so all nice things you are aiming to achieve! If you don’t need hours, minutes, or seconds level of granularity, don’t import them! Aggregations in Power BI (and the Tabular model in general) are a very important and wide topic, which is out of the scope of this series, but I strongly recommend you to check Phil Seamark’s blog and his series of posts on creative aggregations usage
Avoid using calculated columns whenever possible, since they are not being optimally compressed. Instead, try to push all calculations to a data source (SQL database for example) or perform them using the Power Query editor
Use proper data types (for example, if your data granularity is on a day level, there is no need to use Date/Time data type. Date data type will suffice)
Disable the Auto Date/Time option for data loading (this will remove a bunch of automatically created date tables in the background)
Conclusion
After you learned the basics of the VertiPaq storage engine and the different techniques it uses for data compression, I wanted to wrap up this article by showing you a real-life example of how we can “help” VertiPaq (and Power BI consequentially) to get the best out of report performance and optimal resource consumption.
Thanks for reading, hope that you enjoyed the article!
How can you check if RLE has been applied? In Dax Studio I can see onlny HASH and VALUE encoding.