Post reply

Time Dimension

  • June 21, 2011 at 7:37 am

    #145825

    I have created one generated time dimension and fact table

    like fact table column start_date

    How can i join fact table with time dimension.

  • June 21, 2011 at 7:49 am

    #1341844

    As you load the fact table you need to put data in a column that relates to the FK of the time dimension.

  • June 21, 2011 at 8:02 am

    #1341852

    Thanks for u r prompt response,

    Here my time dimension table columns are given below

    Date_ID

    Date

    Julian_Date

    Calendar_Year

    Financial_Year

    Calendar_Quarter

    Calendar_Quarter_ID

    Calendar_Quarter_Name

    Financial_Quarter

    Financial_Quarter_ID

    Financial_Quarter_Name

    Season

    Calendar_Month_ID

    Calendar_Month_Number

    Month_Name

    Month_Abbreviation

    Financial_Month_ID

    Financial_Month_Number

    Calendar_Week_ID

    Calendar_Week

    Financial_Week_ID

    Financial_Week

    Day_of_Calendar_Year

    Day_of_Financial_Year

    Day_of_Month

    Week_Day_Number

    Week_Day_Name

    Week_Day_Abbreviation

    Is_a_Weekend_day

    Is_a_Public_Holiday

    Is_a_Leap_Year

    Is_a_Special_day

    Days_In_Calendar_Year

    Days_In_Financial_Year

    Weekdays_In_Calendar_Year

    Weekdays_In_Financial_Year

    Workdays_In_Calendar_Year

    Workdays_In_Financial_Year

    Days_In_Calendar_Year_Sofar

    Days_In_Financial_Year_Sofar

    Weekdays_In_Calendar_Year_Sofar

    Weekdays_In_Financial_Year_Sofar

    Workdays_In_Calendar_Year_Sofar

    Workdays_In_Financial_Year_Sofar

    Days_In_Month

    Weekdays_In_Month

    Workdays_In_Month

    Days_In_Month_Sofar

    Weekdays_In_Month_Sofar

    Workdays_In_Month_Sofar

    in this Date_ID column is unique id.

    now i have to join with fact table having column start_date.

    for example

    value of Date_ID is 20070101

    value of startdate is 02/02/2007

    so how to join .

  • June 21, 2011 at 8:56 am

    #1341895

    Thanq I got Your point.

  • June 21, 2011 at 9:10 am

    #1341906

    As you load your fact table the startdate should be in this format: 20070101, not this format 01/01/2007.

  • June 22, 2011 at 4:10 am

    #1342292

    Yes i have done the same.

  • July 26, 2011 at 1:56 pm

    #1358809

    Although Kimball initially called his similarly structured dimension a time dimension, this is now (as Kimball also says) called a Date dimension. One day you may actually create a Time dimension, which would be the clock time. If you can still change it, I recommend that you do so.

    Also, this might be similar to other databasel discussions, but I highly recommend that Day 1 in the warehouse have a Date_ID of 1, and so on. the YYYYMMDD is visually human friendly, but the subtractions don't work without conversions. If you want, for example, to store the time it took to complete a job, it's easier to do 1531-1492 than 20070207-20061013.

  • August 2, 2011 at 7:10 am

    #1362130

    Isn't using daynumbers much easier ? In this way you don't have issues concerning dateformats ?

    Kind regards


    JV

  • August 2, 2011 at 7:14 am

    #1362134

    I don't think day numbers is much easier at all. It makes the date human friendly readable, but the users shouldn't be seeing that field anyway. As I said, it would make many routine math function difficult, in the sense of more involved.

    As I said, that argument may be a lot like natural key v autonumber and the like in the db world where everyone has their fans. All I can tell you is that it was worked well for me and I've been doing this for 8 years.

  • August 2, 2011 at 10:21 am

    #1362320

    b.narasimhakumar (6/21/2011)

    I have created one generated time dimension and fact table

    like fact table column start_date

    How can i join fact table with time dimension.

    I assume you mean date dimension table.

    In general the PK of a date dimension - let's call it DATE_ID - would be a surrogate numeric key starting at some point in the past and incrementing +1 per each day since. Note that among others each row of the date dimension includes the actual date stored in the proper date data type, this column has a unique index on it.

    On this scenario FACT table will include a DATE_ID column populated with the same DATE_ID value you find in your date dimension for the particular date.

    Slicing and dicing on on FACT table starts at the dimension level, user will provide a particular date which will be used to access the date dimension then DIM and FACT tables get joined by DATE_ID.

    Hope this helps.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • August 3, 2011 at 1:07 am

    #1362840

    that's what I mented 🙂

    It's much faster and more secure to get the dates right


    JV

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply