Forum Discussion
The best practice for data table in a data modeling, for large date sets?
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)? a...
SivaMani
Resident Rockstar
Resident RockstarHi Anonymous,
Here is the answer to your questions,
- 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.
- 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.
- 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
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