Date key Data type for Date Dimension

[ntimmerman]

Hi all,

Just asking some advice here:

For a new to-be-designed data warehouse: Would you go with an integer date key or a Date date key for referencing the Date dimension?

If I understand it well, both are possible. I just need to mark the date dimension as a date table and have a Date column marked as such. However, I did a test with an integer column but the TotalYTD dax function doesn't seem to work with that.

Any advice?

Thanks

[v-nuoc-msft · Accepted solution]

Hi @ntimmerman 

@AnalyticPulse Thank you very much for your prompt reply. Allow me to add something here.

Using an integer date key is a traditional approach where dates are represented in a format. This method is beneficial for performance reasons, as joining on integers is generally faster than dates.

However, it can make date calculations more complex, as you’ve noticed with the function not working properly with integer keys.

On the other hand, a Date data type key is more intuitive and aligns with the natural representation of dates.

Given your experience with the function not working with an integer column, it seems that using a Date data type might be more suitable for your needs.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ntimmerman]

Thank you @v-nuoc-msft  and @AnalyticPulse ! I will do some more experimenting but will probably change to using a date field as date key. Thanks again

[AnalyticPulse]

if you use the Date format will simplify your  date dimension because it is easiert work with date datatype when you want to debug something in your file. if the data source use the integer date key you can consider to cintinue with same for uniformity so you can avoid transformation complexity. integer keys are also space efficient . 

https://analyticpulse.blogspot.com/2024/04/case-study-banking-and-finance.html

My blog:
https://analyticpulse.blogspot.com/

https://analyticpulse.blogspot.com/2024/04/case-study-powerbi-dashboard-developer.html

See my Pins :
https://pin.it/5aoqgZUft
https://in.pinterest.com/AnalyticPulse/