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