Opinion is split over Power BI’s ability to automatically create Date hierarchies in your model. Personally it drives me mad and I always turn it off, but I know a lot of people love the convenience of it. Whatever your feelings, though, it is important to be aware of the problems it can cause with the size of your model.
Imagine you have a .pbix file and you load just this one table into the data model:
Three columns, each containing three dates with long gaps in between, but only nine values overall. No other tables or queries, no measures, no visuals at all. When you save it results in a file that is a massive 4.7MB – but why, when there’s hardly any data?
Actually, there is a lot of data hidden in this file. If you connect to the .pbix file with DAX Studio you can see that the Auto Date/Time functionality has built three hidden Date tables whose names are prefixed with “LocalDateTable”, one for each date column in the original table above:
These tables can be queried in DAX Studio, and the following query reveals more about them (if you try this on your model you will need to alter the name of the table used in the query to match the names of one of the tables in your model):
[sourcecode language=”text” padlinenumbers=”true”]
EVALUATE
ROW (
"Hidden Date Table Rowcount",
COUNTROWS ( ‘LocalDateTable_17eac8aa-f559-4ade-971f-9a1ad5258fbe’ ),
"Min Date",
MIN ( ‘LocalDateTable_17eac8aa-f559-4ade-971f-9a1ad5258fbe'[Date] ),
"Max Date",
MAX ( ‘LocalDateTable_17eac8aa-f559-4ade-971f-9a1ad5258fbe'[Date] )
)
[/sourcecode]
In this case each of the three tables has 109938 rows. That’s one row for each date between the beginning of the year containing the earliest date in the source column and the end of the year containing the latest date in the source column – which is the best practice for building a Date table, but results in three very large tables in this case.
To stop Power BI automatically building these tables for you, in Power BI Desktop go to the File menu, select Options, then Data Load and deselect the Auto Date/Time option:
When you do this the automatically created date tables are removed from the model. In this case, after saving, the .pbix file shrinks to 181KB! Remember that, by doing this, you won’t get automatic date hierarchies created for you when you are designing your reports and you will have to build any Date tables and hierarchies you need manually.
This is an extreme example of course, but overall you should probably turn off Auto Date/Time if your model size is an issue and:
- You have a lot of date columns in your tables, or
- Your date columns contain large ranges of dates. Remember that some systems use 1/1/1900 as an ‘unknown’ date value, which can make things a lot worse.
Thanks Chris for your post, can we use the Time intelligence specified for one Date Column only?, as I am facing this problem, but I don’t want to build Date hierarchies on Power BI.
No, you can’t – it’s all columns or none. It would be great if you could choose which date columns it worked for.
Chris,
How SSAS Tabular and Power BI handle date/datetime columns leaves a lot to be desired for sure. Having spent many an hour crafting date dimensions in MDX, and being able to quickly add a suite of time-based calculations to any date “dimension” was powerful stuff. That being said it should be part of the product for end users to quickly slice by the natural hierarchies in any date/time field, without the developer needing to do anything. Other products do this quite well.
What I wish Power BI would do is the following:
1. Treat any date/datetime field as a full-class date dimension,
2. Provide Date and Time dimension that can be used with any date/time/datetime field.
It would need to determine the min/max date values for all date fields, but that should be trivial.
It should also allow for future dates (forecasts/projections).
3. Provide a dimension of standard date calculations (MTD, YTD, YOY, etc., leveraging a customizable template file of DAX prototypes), so that all of the typical date/time calculations can be made available for any measure, for any date column via right-click options. No need to expose a huge list of measure variations that clutter up the selection tree.
4. The dimension(s) should be auto-created, (once), and usable for all date fields, but should still allow for custom date dimension for retail or fiscal year calendars. A solid implementation with a standard set of date calculations would remove a huge amount of redundant effort.
I get that this would be messing with the underlying model for the in-memory cube structure and how queries are evaluated, but it is an exception to the rule that could come with the benefit of improved performance through reduced storage/memory needs as well as increased user satisfaction and productivity.
I use Tableau quite a bit and it’s so much easier to use, especially with dates.
Hi Chris, I am using my own custom calendar because I deal with a 4-4-5 Retail calendar. I’m assuming based on the above that I can kill these tables with no I’ll effect. I don’t use out of the box DAX time intelligence functions either
Yes, you should be able to turn off auto date/time and suffer no consequences.
Cheers Chris, that’s one less in the list of tables I see when I’m using Connect to Power BI from Excel. Have you used this functionality at all? Despite me hiding tables in PBI data model they appear in the list of tables when you pivot in Excel
I hadn’t noticed that it shows hidden tables – this is a bug if it does.
I wish there was a way to turn this off globally. Personally I always build my calendar dimensions manually, and never want this setting on. 🙁
I hate it when I add a date field as a row value to a Pivot Table and Power Query creates calculated columns for Years, Quarters and Months. Microsoft, if I wanted those columns, I would have created them myself or used columns from the related date table. I added the date field to the row value because I want the whole date. Is there a way to turn off this “feature”?
Yes, there is: https://support.office.com/en-us/article/Turn-off-time-grouping-in-PivotTables-in-Excel-2016-for-Windows-6be5afed-348c-4db2-9f87-5ac262d67b3f?ui=en-US&rs=en-US&ad=US&fromAR=1