The best practice for data table in a data modeling, for large date sets?

[Anonymous]

Hi All,

so what is the best prectices for date table in a data model with lots of data?

There are a few sub questions to this:

Is it better to join on date, datekey(int), datekey(text)? and does data size or other factors impact this?

If all the dates in your model/tables are date type is it worth it to create datekeys?

Then there creation of the Date Table. Is it better to ingest it from the database? Create your own in M or in DAX?

Is there any documentation for best practices around date in the data model?

Here is my current interpetation:

Import > Direct Query. Manly for the increase in spead at run time. Thus for import method anything that improves the RAM use is the better option.  i.e. RAM > diskpace. Using Datekey means you can use the Mark as date table which improves the RAM usage as it removes all the hidden Date tables (i am assuming they are held in ram).  If you don't have lots of Dates in the tables then the question becomes.  Is joing on Date better then joing on txt or int? I am ware there are speed improvements in SQL does this translate to PBI?

When to creat the date table.

Based on my current experience, it seems better to have things sooner the better in the data pipline. Thus Database > M > DAX.

If anyone and ideas or thoughts around best practices with the date table. There is likely things i am missing that should be considered when modeling a Date table.

Edit: Based on the first response it seems I was not clear with my first sub question.

Is it better to join on date, datekey(int), datekey(text)? I.e. the both the primary key in the date table and the foreign key in the other tables could be either a date type, a datekey as an integer (like sql) or datekey as text type.

As a part of this edit I have added some changes to the sub-questions, but did not change the original on 15/01/2020.

[SivaMani]

Hi @Anonymous,

Here is the answer to your questions,

1. Is it better to join on date, datekey(int), datekey(text)? No. if you have matching values in both tables, it will work. However, it will create performance issues. So the best practice is to make sure that from and to columns have the same data type.  
2. If all the dates in your model/tables are date type is it worth it to create datekeys? Yes. It will improve the performance of the model.
3. Then there creation of the Date Table. Is it better to ingest it from the database? Create your own in M or in DAX?. Add it from the database if you use Direct Query mode. You can use DAX to create own date table in Import mode.

Hope it will help you!

Appreciate your kudos!

Regards,

Siva Mani

[Anonymous]

Hi SivaMani, thank you for responding and sharing your thoughs. It became clear that some of my questions are unclear. So I did some updates.

[SivaMani]

Hi ACreighton,

Yes, I got your question. As I mentioned in my previous response, It will work. But there is a concept called Expression Index. The DB will start to create an Expression Index for this case. It will create a performance issue if your fact table is a large one. 

Hope I answered your questions!

Appreciate your kudos!

Regards,

Siva Mani