Hi,

I am very new to data warehousing. I need to construct a data warehouse for a metrics application which can generate dynamic reports from millions of data. These reports can be hourly, daily, weekly, monthly and so on.

I know I will have to create a time dimension table storing a time key (integer) and other information like day of month, week , month, year, etc.

My question is should i create a single fact table that stores all the information at lowest grain (i.e. hour) and then query this fact table to generate all kinds of reports?

I see a disadvantage to this approach: If I need a weekly report, then lot of time would be taken to query such huge data in way of grouping and summing. So should i be creating a different fact tables which capture the calculated data for different time grains.. like a fact table for weekly reports, another one for monthly reports, and so on? Or should I create just one fact table and have different indexed views for better performance?

Please bear with me as I am a novice and would greatly appreciate any help that comes my way.

Thanks so much, Whoever!