Aggregate tables- basic advice

Howdy,

We are building a datamart to report on a customer satisfaction survey- approximately 40-50 questions, with the survey taken by roughly 1000 customers daily, based on visits to one of about 150-160 stores.
About half of the questions are rating questions (1-10). The rest are about evenly split between long answers and relevant metadata (transaction amount, date of visit, store visited, time of visit, etc).

We chose to have two fact tables, one for the rating questions, and one for the rest, with grain down to the individual customer response, and dimensions for date of store visit, date that the survey was taken, time of day of visit, question, location, junk dimension with ID for managing each taking of the survey.

We have to provide a report at the lowest level that essentially replicates the questions asked to the customer and their answers, hence the choice of grain.

We also have to report on, for example, the percentage of customers who rated their visit highly (7-10) , rolled up by stores and store groups, for a given time period (weeks, months, quarters and year to date).

So we are wrestling with how to design an aggregate fact table to handle this. One idea was to define timespans- one for each timespan to be measured, such as quarters, weeks, months, etc. and end up with a table like so:
TimespanID Score HierarchyID
1 60.3 4 (score for store group 2 for fiscal quarter 1)
1 58.8 210 (score for store 85 for fiscal quarter 1)
3 70.9 210 (score for store 85 for fiscal week 10)

We also need to report based on two different dates: the date of the customer visit, and the date the customer took the survey. One suggestion was to define two timespans for the same time period, but one for visit date, and one for report date- meaning you would have twice as many entries in the table. The other suggestions were to add another column for date type, which would also double the entries in the table., or have separate tables. Finally, we need to report on several ratings questions. Does it make sense to add a QuestionID dimension to this aggregate table, or have separate tables for each question?

As you can probably tell, this is all fairly new to us. Are we on the right track here? We appreciate your input; if you can recommend a guide to designing aggregate fact tables, even better.

Thank you for your time and attention.

This is what I could infer:
1. You are tracking the rating, process measures and comments kind of data all for each customer visit
2. Process measures like transaction amount is being captured as summary per visit. Not at individual item level

So I think you may have a single fact table for storing all these measures as they fall under same grain. You need to build multiple aggregate tables as per the level of time series i.e monthly, yearly summary. weekly and quarterly summary can be derived from the granular fact and monthly aggregated fact respectively.

-JR