Performance Effect of Auto Date/Time Feature – Power BI

  • Published
  • Posted in Power BI
  • Updated
  • 2 mins read
  • Tagged as

Performance of a report is very important concern for the end users, if the performance is good the end users get satisfied and can use the report very smoothly.

One of the performance improvement techniques will be learnt from this post. The time intelligence feature in Power BI Auto date/time turned on, creates one date table for each date column in dataset, which increases the size of the dataset and effects the performance of the report.

Let us understand by the example below:

A sample table is imported into the Power BI desktop as dataset and the sample of the data is shown below, it contains multiple date columns i.e., Order Date, Due Date, Ship Date.

This table has just 190 records and when data is imported the size of the BI file is 58 KB, this is when the Auto date/time feature is turned on by default. (Please remember that Auto date/time feature is turned on by default, to investigate it, follow this path in Power BI desktop, file > options and settings > options > data load (current file/global)).

By default, this option is enabled for globally (for each file), one can disable this globally or for current file based on requirement.

Now, it is important to observe the date tables created automatically by BI for each date column (this is done by BI to help in slice and dice the date data).

Observe that for each date column a date hierarchy is created automatically this is nothing but date table in background for each column.

Imagine if there are multiple tables with multiple date columns then it would create for all, is it required for us? You might think it is required as BI does to provide slice and dice, but when it comes to reality it is not required. A common date dimension table for all tables (/columns) is enough.

So, create a common date dimension table (role-playing dimension date table) which will solve this performance problem.

Now, a date table is created in BI and turned off the Auto date/time feature, the size of the file reduced to the 40 KB, it is massive reduction when comes to large volume data set.

Hope this post helps, thank you.

Addend Analytics is a Microsoft Gold Partner based in Mumbai, India, and a branch office in the U.S.

Addend has successfully implemented 100+ Microsoft Power BI and Business Central projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia.

Get a free consultation now by emailing us or contacting us.