7 Replies Latest reply: Jul 18, 2013 4:37 AM by Christian Berg

# Why do I have Calendar Date Dimension in my Logical Layer?

If I look in my logical layer and then looking at the models, each of my Fact tables have their corresponding dates such as Order Date, Date Updated, Date Shipped etc.  This is OK. But each single Fact, I see a Calendar Date which is a duplicate of another date dimension.  What I mean if I look in Fact A, I will have Date Transaction, Date Shipped, Date Ordered and Date Calendar.  Date Calendar has the same join relationship as Date Shipped.    Another Fact table Date Calendar has a same relationship as Date Transaction.

Is this a common modelling practice, is this only used for ease of use for business end users?  Do you know why would would do this and what the benefit is?

Thank you!

• ###### 1. Re: Why do I have Calendar Date Dimension in my Logical Layer?

The tables you see are not duplicate tables, rather aliases of a time dimension table. Yes, you will have multiple time dimensions in the way your physical model is designed. This helps to build reports based on Shipped/Ordered dates.

• ###### 2. Re: Why do I have Calendar Date Dimension in my Logical Layer?

Hi,

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.

Dhar

• ###### 3. Re: Why do I have Calendar Date Dimension in my Logical Layer?

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

and

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?

Thank you

• ###### 4. Re: Why do I have Calendar Date Dimension in my Logical Layer?

That is true. You can either join the dates or date ids.

As a best practice you can use date ids than dates which gives better performance since the joins are number datatype.

• ###### 5. Re: Why do I have Calendar Date Dimension in my Logical Layer?

Hi Rajesh,

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?

• ###### 6. Re: Why do I have Calendar Date Dimension in my Logical Layer?

That makes sense. That is the standard followed in designing the star schema in the RPD.

• ###### 7. Re: Why do I have Calendar Date Dimension in my Logical Layer?

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