Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-11-26 : 09:43:24
|
| Rajesh writes "This question is specific to design the fact tables in the OLAP.I need to store hourly data in the fact table .the hourly data consists of 24 hours of quantity and amount.My question is which is the best methodTo store with hour in the timefact which creates seperate records for every hour in the timefact and the sales fact table. ORTo store till the day in the timefact and relate that id in thesalesfact with which there will be only one row for every transaction but the sales fact will have a sum of quantity and amount for every hour which creates 48 columns for that alone.which will be the best method in case if there are millions of records for the sales table.ThanxRajesh" |
|
|
Tim
Starting Member
392 Posts |
Posted - 2001-12-03 : 00:18:30
|
Rajesh, I would suggest this:TimeDate table: ID, date, {other fields you want. eg: month description, public holiday flag etc.}TimeHour table: ID, hour, {other fields you want. eg: business hours flag, morning/afternoon/evening flag etc.)SalesFact table: ID, TimeDate_ID, TimeHour_ID, {other dimension keys and measures}This will give one fact table row per transaction. (note: Don't do summaries (count, sum etc.) in your Fact table unless you can't possibly process all your rows in your available processing window. Let the cube do aggregations and pack as much info into it as your performance goals permit)In your cube make two dimensions for time, one based on dates and the other on hours.This will give added flexibility for user also, because they will be able to see measures by selected dates (all hours) , selected hours (all dates) or combination of selected dates and hours. If you only have one time dimension down to the hour level, user loses ability to see what is best selling hour overall.Hope that helpsTim-------------------------------Visit nr's cursor wonderland  |
 |
|
|
|
|
|
|
|
OLAP TIME DIMENSION