Wyatt McNabb authored this article on Feb 26. You can follow him on LinkedIn.
As I have been reviewing a few dashboards from various sources over the past while, I am noticing a hidden performance killer in Power BI. That being the default setting of Power BI’s Auto Time Intelligence – Date/time intelligence setting:
For simple models with few date columns, this is not a major problem, but it certainly can be when dates are incorrectly entered. Let’s take a closer look at a model I recently came across.
On first appearance the model looks fine but when we look under the covers, we see a different story…
We have a few data tables surrounded by some filtering dimension tables. The problem arises with Auto Date/Time Intelligence is that Power BI has to create a hidden date table for every column it detects of type Date. Now in this model, there is also a user defined Date/Calendar table and with the auto generated date tables, there are at least 17 date tables in this model. This is verified with tools such as DAX Studio or Tabular Editor:
There are at least 40 hidden date tables created by Power BI in this model and there are two hidden date tables consuming 89.58% of the model.
Here is a snapshot of the model’s metrics:
As we can see, the model is 124 MB’s in 53 tables and is parsing 576 columns. Let’s discover why the two tables are consuming so much space. We will use DAX Studio to EVALUATE one of the hidden tables (something you cannot do in Power BI).
Power BI, using the auto Time Intelligence, determines the earliest date it detects in the column and then determines the maximum date in the same column and creates a date range for the first day of the earliest year to the last date of the last year. Let’s see the date table it created:
Now I may be relatively new to the company I work for (redacted), but I don’t believe we were operating in the year 220. With further investigation, it was discovered that someone had inadvertently entered the year 2020 as 0220! As this date was used in 2 columns, hence the massive date calendar twice.
Now this model did not require the Auto Time Intelligence feature as it has a date calendar generated by the creator. So, let’s turn this feature off and see how it effects the model.
Nice, no extra Local Date Calendars and now let’s see how it affected the model size:
WOW, that’s a drop in size of 115.62 MB’s. Now that is a huge performance saving!
So, what are the lessons learned here?
- Power BI’s Auto Time Intelligence is a Good feature but use it wisely! If you have more than a few date columns in your model, create and use your own date calendar and control your date ranges more precisely.
- Always-always-always ensure you have proper dates in your model. One small date error can cause huge performance and size issues if dates are not entered correctly. Clean your data first!
