This is an interesting question and here is an interesting answer. Have you heard of role playing dimensions? If not, here it is
What is a role playing dimension?
It is just like any other dimension, but takes up different forms based on the fact context. For example in your case, Ordered Date and Shipped Date, both being dates could be served just by a single dimension (Technically, you could just pick the keys only from a single table, right!!). However, if you understand the context, ordered date is the date on which the product had been ordered and shipped is the one on which it is shipped to the customer; which are completely different. So, if you would like to pull out a report with customer order date as 1-Jul-2013, but delivery date as 20-Jul-2013, we just cannot have a single dimension with both of these conditions at the same time(physically). So, an intelligent design is to alias the time dimension, to churn out two logical dimensions; Order Date dimension and Shipped Date dimension.
Hope I was helpful.
Thanks for your replies, I understand the Alias concept. Let me write it a different way.
Dim_Date = Physical Table
W_DATE_ORDERED_D, W_DATE_SHIPPED_D, W_DATE_TRANSACTION_D and W_DATE_UPDATED_D are all alias that come from my DIM_DATE Physical Table. Each of these Aliases are joined to my fact tables.
FACTTABLE1.ORDERDATEID joined to W_DATE_ORDERED_D.DATEID AND FACTTABLE1.SHIPPEDDATEID joined to W_DATE_SHIPPED_D.DATEID
FACTTABLE2.TRANSACTIONDATEID joined to W_DATE_TRANSACTION_D.DATEID AND FACTTABLE2.UPDATEDDATEID joined to W_DATE_UPDATED_D.DATEID
The above part I understand fully, what I also have is this:
FACTTABLE1.ORDERDATEID joined to W_DATE_CALENDAR_D.DATEID
FACTTABLE2.TRANSACTIONDATEID joined to W_DATE_CALENDAR_D.DATEID
Documentation states that calendar dates are used more for business logical reasons for the end users, and that the Calendar dates joined to the fact date ids, the fact date ids are the more commonly used dates for reporting.
Does that make sense?
That doesn't answer my question. Does it make sense that every star model has a Calendar Date which is the most common date that will be used for reporting. In this case, the fact tables above its, Order Date and Transaction Date are the most common ones so the Calendar Date is join to these two and I ALSO have a Order Date and Transaction Date. But supposely the Calendar Date is labelled for ease of use reasons. Maybe for Cross Functional reporting too?
Not necessarily. I suggest you check out Jeff's preso from a couple of years back where he talks about the concepts on canonical vs. non-canonical time: https://s3.amazonaws.com/rmc_docs/biforum2011/Mcquigg_Metadata.pdf