Date key in datawarehouse


  • Hi

    Is it manadtory that all dates should be a part of a seperate dimension table and use only datekeys in our dimension and fact tables?

    I have a problem of finding date diff between two date fields in my same dimesion tables. so i need to join with date dimension twice to get actual two dates. why not we save only date fileds directly in dimension table itself rather than keeping it out in seperate dimesion table?

    Ravi.S Chennai, Tamil Nadu, India
    • 已移動 Stephanie Lv 2011年10月13日 下午 12:27 (From:Database Design)
    2011年10月11日 上午 07:13



  • Hi Ravi,

    It seems that the topic is related to Data Warehouse, i would like to move this thread to that forum for better assistance.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    2011年10月13日 下午 12:29
  • Hi Ravi,

    It is recommanded that use the smart date key like YYYYMMDD in integer data type, e,g. 20090115 for January 15th 2009. So, you can create the named calculation with SQL functions to convert the YYYYMMDD to date type to do the date diff in your facts.

    See the "3. Smart Date Key"  part of the article  for more informaiton about its avantage using YYYYMMDD format.


    2011年10月14日 上午 02:22
  • It is suggested that we use smart datekeys in Date dimension and fact tables. However, personally me (and I am sure there are quite a few people out there who would support this idea) prefer to use date as date datatype directly in the fact table and make it a key in date dimension as well. This is true if the granularity is at date level. There are few reasons as below

    1. Smart Key for date will be int datatype  will take 4 bytes, date datatype on the otherhand take 3 bytes fixed.

    2. For simple queries on fact table (like give me sales for Jan 12 2010 etc) , I really don't have to join back to date dimension. It can be fulfilled from fact table itself.

    3.I can't use all the date functions like datepart,datedadd etc. directly from  fact table, I need to join to date dimension everytime.

    Plus if my column days SalesDate and I see value like '2001-01-23' rather than '20010123' make me feel good.. :P


    Please mark posts as answer or helpful when they are.
    2011年10月18日 上午 10:27
  • It is not mandatory or recommended that all dates be date keys.

    Foreign keys should definitely be datekeys. Columns such as Effective_From and Effective_To dates, market_launch_date etc. should remain as date or datetime datatypes.

    2012年4月12日 上午 06:37
  • hi all,

    iam newibes.....pls to Designing a database for a tennis league .

    how to design and Architecture and tables and everything on tennies game.

    pls guide to me....


    2012年4月12日 上午 11:54