Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003

    Question Unanswered: Need help to create my Time Dimension


    I have a fact table which contains the transaction date, ProductID, QTySold, TotalSaleAmount, etc...

    Since I am new to OLAP therefore I need help to now create the table for TIME on which I will be basing my time dimension... I have read a few articles and have gathered that at the end of the exercise my fact table should have a 'timeid' column which will be linked to the same column in the table being used for the time dimension...

    I have gone through the tutorial of MS-Analysis Service and FoodMart example have some idea about what he structure of this table will be.

    My questions are:

    1. I need guidance on how to create the table for time. One option is to just copy the table used in the Foodmart example but thought that might work but my concept will not be clear

    2. The structure of the table to be used for time dimension is quite clear (i think this part is easy). What I want to understand is that how do i POPULATE this table which data? Can some one provide me with scripts, SPs, or whatever to do this.... This is the area where I am lost...

    3. How will I enter the "TheDate" column in my fact table and link it with my table for time dimension...

    Looking forward to someone's help.

    BTW, I would like to share a very good article which i recently found in one of the newsgroups. Some of you might like appreciate it too:

  2. #2
    Join Date
    Feb 2004
    I'm not expert but:

    1. create table time_dimension
    with id column (timeKEY-?)
    copu your dates to the time_dimension
    (eventualy create levels year,quater,month)

    for example:

    select distinct
    Year = year(date_kol),
    quarter= DatePart(qq,date_kol),
    month = DatePart(mm,date_kol)
    from table

    2. after that you create fact table also with id column (timeKEY-?)

    timekey = (select t.timekey from base.dbo.time_dim t

    --fact kolumns--

    from table k

    first finish copying data to time table before begining coping data to fact table

  3. #3
    Join Date
    Oct 2003

    Exclamation Calling the experts too for help - PLEASE


    Thanks for the reply. Your reply helps but some more clarity is requested (becuase of my average/below average SQL skills). Let me list down my questions point by point and with more clarity... I'll be gratefull for help from you as well as others.

    Creating the tblTime is easy and I plan to use the same structure that is being used for the FoodMart's time table. I'll also add 2 more fields in addition i.e. IsWeekDay, and IsPublicHoliday. What I am finding difficult to solve are the following two things:

    1) Populate all the fields of tblTime using dates betwen 01-Jan-1999 to 31-Dec-2005. Any guidance from anyone regarding any "watchouts" for this particular point?

    2) Link a field called Timeid in my tblfact with a same named field in tblTime

    How do I populate the following fields of my tblTime (based on the 'TheDate' field which I would copy from tblFact:
    TheDay (Monday, Tuesday, etc.)
    TheMonth (January, February, ...)
    TheYear (1999, 2000, 2001, ...)
    TheQuarter (Qtr1, Qtr2, Qtr3, or Qtr4)

    Basically what I need help with is the proper SQL statements which will do the above. The SQL statements that you have suggested are like pseudo-SQL statements... I hope my question is clear? Givem my skills, this is THE BIG challenge for me

    I'll just use a counter for the TimeID columns in tblTime and tblFact, set it to int, make it identity, and set the seed to 1... That should do it BUT the last request is the proper SQL statement which will link the TimeID column of tblTime with the TimID column of tblFact.

    I hope my post clarifies things. I'll appreciate replies to help me out. I do realize that a reply to my questions will take a decent amount of time (from who ever replies)

    Many thanks.


  4. #4
    Join Date
    Feb 2004
    I think it's wrong sequence

    you would like to create time table "from" fact table with all datas from year to year. it 's maybe posible bat you could have problem.
    if in your fact tabe will be 2 the same datas you should have 2 the same datas in time table with diferent id.

    Maybee i'm wrong but i sugest (I'm dooing that )

    create new empty fact tabe and dimension tables
    Use DTS to populate data to the dimension tables like I've wrote before

    and next populate fact table with id kolumns from yours dimension tables.

    I can send you a simple exaple of this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts