In our previous blog post, we explained how we can use VertiPaq Analyzer to analyse the amount of memory our model needs. This memory is also linked to the speed with which the model calculates and refreshes data.
In this blog series, we’ll discuss several ways to limit this memory usage.
Hidden date tables
We’ll start with the same file we used in the last blog post. It’s a 7.3 MB file with about 20 MB data speed.
If you look at the screenshot above, you’ll see several tables with similar names. They all start with LocalDateTable_.
How did these tables end up in our model? You can’t see them in Power BI, after all – at least, not at a glance.
For each date field in the model, Power BI creates a separate date table in the background, based on the table DateTableTemplate_. This screenshot shows that there are four date fields in our model. True enough, there are four fields in it that contain a date. You can unfold them to see the full date hierarchy:
Define your own date table
In most models, you’ll need to create your own date table The built-in date table usually doesn’t suffice:
- There are fields missing, such as week number, semester, weekend, …
- If you want to compare purchase and sales data, you’ll need a combined date table instead of two separate tables.
- In automatic date tables, the first quarter always consists of January, February and March.
This blog post isn’t about how to create your own date table so we’ll assume your model already contains a table that functions as a date table.
Mark your table containing dates as a ‘date table’ by selecting it and clicking Modeling – Mark as date table.
Note that Power BI will tell you that the built-in date tables of this table will be removed. If you click OK, you’ll see that there’s no longer a date table behind this field. The icon will also change to indicate that this is a date table.
Now, check your model’s memory usage by clicking Data – Refresh All in Excel.
As you can see, there’s one less table and memory usage has dropped from 19.8 MB to 19.5 MB.
Removing hidden date tables
There’s a more efficient way to do this, though. Since you’ve already defined a date table yourself, you don’t need Power BI to create a date table in the background for all other date fields in the model.
You can prevent the creation of these ‘hidden’ date tables as follows:
- Click File – Options and settings – Options.
- Go to Current Fileat the bottom and clickData Load.
- Uncheck Auto Date/Time.
If you refresh the data in Excel now, you’ll see that the underlying date tables and date template have disappeared from the model. Its size has gone down to 18 MB.
