Modelling of Questionnaire Survey data for different types of Surveys

Hi All,
We are modelling Questionnaire survey data for different types of Surveys within the Organisation and the generic survey model in the DW Toolkit fits for the most part our requirements.
There is a Survey Dimension for the different Surveys and a Question Dimension for the different questions which may change over time and a SurveyFact table containing response (can be numeric or text response).

However, the question I have is the data specific to a Survey which is unrelated to Questions e.g. Survey1 deals with lets say Cases, so in the Survey Fact table we have a Case No. That Case No. is specific to every Questionnaire sent out, so if we had 10 questions in that Questionnaire we would have 10 rows in the Fact table with that specific Case No. (1 row in fact table for each question on survey)
What if a completely different Survey, lets call it Survey2 doesn't deal with Cases but deals with lets say Purchases with a unique Purchase No. and also has some other information specific to that Survey that the Case Survey doesn't have.
My question is - how do I model this.
Do I need a separate fact table for each Survey (the same dimensions may be used) ?
If this can be modeled in the same fact table how do you deal with the fact that the Case questionnaire response would have no purchase details and vice versa ?

Hi prw

I normally model such data as if it was a Question and Response pair. So for your Survey1 the Question is: Case No and the response is the Case No value. For Survey2 you have a Question for: Purchase No and the response is that value.

In your ETL process you generate the extra rows as needed.

Good luck!
Mike

Hi Mike,

Thanks very much for your response.

I want to make sure that I have understood your reply correctly. In Survey1 I have 10 questions, so I have 10 records in the Dimension, one row for each question. In the Fact table I have one row for each question on the Survey so I would have 10 records for this survey, with each record containing a FK value that points to the PK record of the Question Dimension e.g. FactRow1 has value for PK row 1 on Question Dimension, FactRow2 has value for PK row2 on Question Dimension and so on.
Each FactRow also has a response value that corresponds to the value chosen as the response (assuming numeric value only).

Are you saying that I would have a further record in the Question Dimension, where the Question Label is actually the word 'Case No.', and there would be an extra FactRow record that points to this Dimension Record, with the Response value being the actual Case No ?

Yes that's exactly what I meant.

Thanks. I've modelled that with some example data and I think it's going to work well for what is required.