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?
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.