Forum Discussion
Creating my own Date Table
Hi PaulKraemer
I would also recommend turning off auto date/time as a general rule, in line with what you've read.
If enabled, it creates a hidden table for every date column in the model (excluding calc tables/columns), which can significantly bloat the model size and harm performance.
I would recommend defining hierarchies yourself in your own Date table, whether created with DAX or Power Query.
As far as how to create the Date table:
- CALENDARAUTO may work fine in simple models, but because it searches the model for date columns and expands include all years spanning dates in those columns, it may become larger than required.
- There are various Power Query date table functions online. See here for example.
- If using DAX, I recommend instead using a pattern similar to this:
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/ - You can also use the Bravo external tool to create a date table.
- If you would like to see an example of a Date table with hierarchies defined, you could download one of these Contoso Data Generator models from here.
For example download "pbix-100k.7z".
Hope that helps!
Hi PaulKraemer
In a Power BI semantic model, a column of type date is perfectly fine for relationships with a Date table. In fact, I would encourage it!
This SQLBI article provides a good analysis of the question of whether to use a date or integer (e.g. YYYYMMDD) column. Also see this video.
Key points:
- In a Power BI semantic model, there is no inherent difference in performance or storage based on whether columns of type date or integer are used.
- If fact tables represent dates using columns of type date rather than type integer, there is an advantage in terms of convenience of authoring calculations that need to refer to date values from those fact tables. If an integer column is used, it may need to be translated to a date value via the relationship with the Date table, which can result in some awkward code (and possible performance impact).
Taking this into account, I normally stick with date column types for all relationships with Date tables.
I understand that the use of an integer keys may be a more important consideration in certain databases, and has been something of a historical convention, but I don't think this should influence the choice of column types in a Power BI semantic model.
Regards