This discussion is archived
1 Reply Latest reply: Jun 16, 2013 8:49 AM by JeromeFr RSS

advantage of using date dimension.

RG 007 Newbie
Currently Being Moderated
Hi Gurus,
I have a fact table with 5 different dates like shipping date, order date.
My question is
Case 1. in fact table I store date_keys(surrogate integer numbers) and create multiple date dimensions(alas) for each type of date like one date_dimension alias for shipping date , one alias for order date etc(in reporting model).

Case 2 : I just avoid using date dimension and just store date as date (not integers)in fact table.
In this way I will be saving 5 joins to 5 different date dimensions.
Note : I need these 5 joins in reporting model that will be used to create reports , not in ETL model, in ETL model only one date dimension will be there, but for reporting we need to create on alias date dimension for each type of date.

So, just wondering what is advantage of using date dimension?
  • 1. Re: advantage of using date dimension.
    JeromeFr Expert
    Currently Being Moderated

    This is more a designing/reporting question than an ETL/ODI question ;).

    The advantage of the dimension is that you can select or aggregate data per month, year, weekends, quarters, day of week, ...
    If you only keep the date you will have to had all these complex formula's in logical columns or calculated items (assuming you use OBIEE). Performances will be impacted and code will be duplicated at many locations.


    Hope it answers the question.

    Jerome

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points