How would I build a data warehouse when each report output is unique (like a survey)?

Question

I use SQL Server 2008 R2 to store survey questions and answers from a Web application.

For report performance reasons, I was looking into creating a data warehouse outside of my transactional database.

The report I need is fairly simple:

• Each column header represent a survey question
• Every row represent answers to the question

Example of two different surveys:

Since each survey is unique -- authors can ask any question they want and get distinct answers -- does it make sense to store these results in a typical warehouse model? If so, how would you suggest the design be?

Note: Each survey ties back to its own ID.

Thanks!

Answer 1

Because this is a data warehouse, I would make answer the fact. There's no need to normalize since you are inserting and selecting only.

Answer
------
Answer Text
Question Text
Survey ID

The primary key is (Answer, Question, Survey ID). There's another unique index on (Question, Survey ID).

If the date and time of the survey is available, I'd add a Survey Time Stamp to the Answer table.

This table will answer the following questions.

• How often are certain answers given?
• How often are certain questions asked?
• How often are certain answers given to certain questions?

If you're looking for a normalized transactional database, then the following tables would be more appropriate.

Survey
------
Survey ID
...

If there's no other survey information, this table isn't needed.

Question
--------
Question ID
Survey ID
Question Text
...

Question ID is an auto-incrementing integer. Survey ID is a foreign key back to the Survey table.

Answer
------
Answer ID
Question ID
Answer Text
...

Answer ID is an auto-incrementing integer. Question ID is a foreign key back to the Question table.

This table design will answer the following question.

• What did the survey look like?