Time dimension - best practices

[andrewpontes]

Hi There,

I seek help to find the best approach to the following business requirements:

• Query model contains multiple date type fields
• Report writer wishes time dimension linked to all date fields in all query models

eg. order date, payment date, ship date, received date, return date, ...

In the current approach, there is a 1..1 to 1..n relationship between time dimension and query subject linked by calendar_date and [query subject.date field]. If another date field in the same query subject, then an alias of the query subject is created and linked to time dimension using the date field.

The issue with the above approach is that if 6 date fields in the query subject, then 5 aliases would need to be created, thus increasing the model complexity.

I am wondering if there is a better approach to meet the above requirement.

Thanks,

[blom0344]

You need to use the alias approach. If you would reuse the calender for multiple datafields you risk data-loss and other join-related issues.
The model itself is not compremised in terms of complexity as each date field has its own reference to the calender lookup. More model query subjects does not automatically mean more complexity, especially with alias subjects that can be easily maintained