Forum Discussion
data modelling help
Hi, Anonymous
Generally, your data model will contain a calendar table. It is usually better to aggregate data by year and month using columns of a calendar table (containing one row for each day) instead of extracting the date parts from a single column of type date or datetime in calculated columns.
There are a few reasons for this choice. You obtain a model wherein all date attributes are included in a separate table making it easier to browse data using a generic client, and you can use special DAX functions that perform time intelligence calculations. Moreover, most of the time intelligence functions require a separate Date table to work correctly. Defining a separate Date table is a common practice in any star schema. You should use this technique for any model, even if you do not have a star schema as a starting point. Whenever you have a date column you want to analyze, you should create a relationship with a Date table. If you have multiple date columns in a table, you can create multiple inactive relationships to the Date table in addition to a single active one. You can also choose to create a different Date table for each date column.
You may create a calculated table by CALENDAR or CALENDARAUTO function if you do not have a data table in your data source. For further information, you can refer to the following links.
https://docs.microsoft.com/en-us/dax/calendar-function-dax
https://docs.microsoft.com/en-us/dax/calendarauto-function-dax
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
some suggest to create a separate Date table what is the use of it and if at all if i create a date table what difference is it going to make in performance and the realationship from new Date table needs to be linked up with which table? v-alq-msft Anonymous
Hi, Anonymous
Generally, your data model will contain a calendar table. It is usually better to aggregate data by year and month using columns of a calendar table (containing one row for each day) instead of extracting the date parts from a single column of type date or datetime in calculated columns.
There are a few reasons for this choice. You obtain a model wherein all date attributes are included in a separate table making it easier to browse data using a generic client, and you can use special DAX functions that perform time intelligence calculations. Moreover, most of the time intelligence functions require a separate Date table to work correctly. Defining a separate Date table is a common practice in any star schema. You should use this technique for any model, even if you do not have a star schema as a starting point. Whenever you have a date column you want to analyze, you should create a relationship with a Date table. If you have multiple date columns in a table, you can create multiple inactive relationships to the Date table in addition to a single active one. You can also choose to create a different Date table for each date column.
You may create a calculated table by CALENDAR or CALENDARAUTO function if you do not have a data table in your data source. For further information, you can refer to the following links.
https://docs.microsoft.com/en-us/dax/calendar-function-dax
https://docs.microsoft.com/en-us/dax/calendarauto-function-dax
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.