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
    • Cambiado Stephanie Lv jueves, 13 de octubre de 2011 12:27 (From:Database Design)
    martes, 11 de octubre de 2011 7:13


Todas las respuestas

  • 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.
    jueves, 13 de octubre de 2011 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.


    viernes, 14 de octubre de 2011 2: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.
    martes, 18 de octubre de 2011 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.

    jueves, 12 de abril de 2012 6: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....


    jueves, 12 de abril de 2012 11:54