In this tutorial, you’ll learn how to remove a hidden date table in Power BI to reduce RAM usage and improve performance. Power BI automatically creates hidden date tables when you have the Auto date/time option enabled.
To enable the Auto date/time, go to Options and then to the Data Load tab. Under the Time Intelligence option, enable the Auto date/time option.
After Power BI loads, you’ll notice that the date table isn’t visible in the Fields pane.
You can view it either in DAX Studio or the Tabular Editor.
Auto Date/Time Option In Power BI
Launch DAX Studio. In the Metadata pane, you can see multiple local date tables.
Go to the Advanced tab and click View Metrics. You can see that currently, the local date tables aren’t consuming a big quantity of RAM.
Let’s visualize what the data model will look like if it has more date values in it.
Click the External tools tab and create a New table. This new calculated table will GENERATE a table that contains dates from January 1, 1907 to December 31, 2070. Then, the ROW function will create columns containing the date.
To understand how the Auto date/time option affects your RAM, let’s replicate the ROW seven times.
When you run this, DAX Studio generates a table containing 8 columns and almost 60,000 rows.
When you click the View Metrics option again, you’ll see that this new table consumes 4.6MB of RAM for each column.
If you click on the Summary tab, you can see that the size of the data model increased to 173MB.
Through this example, you can see how enabling the Auto date/time option in Power BI can increase your RAM consumption.
When you’re dealing with realistic data, you might encounter historic attributes and decide to keep them for future reference. But this can cause your program to consume a considerable amount of RAM especially if you enable the Auto date/time option.
You can optimize your date tables by turning off this option and creating your own auto date/time table by either using Power Query or DAX code. You can also create it using the data source in SQL.
Disabled Auto Date/Time Option For Date Table In Power BI
Let’s compare how much this table will consume if the Auto date/time option is disabled.
Go back to Power BI and turn off the Auto date/time option under the Data Load tab.
Then, launch DAX Studio and click View Metrics. In the Summary tab, you can see that from 173MB, the RAM consumption decreased to 133MB.
By turning off the Auto date/time option, you were able to save 40MB of RAM.
***** Related Links *****
Power BI With Python Scripting To Create Date Tables
Sorting Date Table Columns In Power BI
The Importance Of Power BI Date Tables
Conclusion
Hopefully, this tutorial helped you understand the importance of configuring Power BI DAX Studio before beginning your work.
Though the Auto date/time option is a helpful time intelligence feature, it has significant effect on your RAM usage. Instead, it’s recommended to manually create a date table using DAX code.
Removing hidden date tables in Power BI is one of the ways you can optimize your report and improve performance. There are more optimization techniques to learn in the Enterprise DNA website.
Enterprise DNA Experts
