Adding dummy Fact records to a Fact_SurveyAnswer table

[Grah31]

Hi,

I have a requirement to report on responses to a survey sent to users. I have created the model and have a Fact_SurveyAnswer table who’s grain is a single answer to a single question on a Survey.

The source survey ‘header’ table has one row for each survey sent out with a SurveyId business key. The Answers table has a number of rows, one for each survey answer. So once imported the Fact table has the same number of rows as the total number of answers. All well and good until a requirement came to report on the percentage of respondents to the survey.  I can do a get distinct on the SurveyId to get the total number of respondents but the people who have been sent the survey but haven’t responded have no answers and therefore no entries in the Fact table.

I had a thought that for these non-responders I could add a single record into the Fact table for each such Survey with the SurveyID  business key populated but (-1) in the answer surrogate keys. Then I could calculate the ratio of respondents from this fact table with a bit of maths.

My concern is the validity of putting such a ‘dummy’ answer in this Fact table based on the questionable grain of this record. You could argue it is an answer of sorts and you could argue that it doesn’t belong  here.

Thinking about it if I do implement this I will potentially have to bodge the ETL to check whether this -1 record exists in future in case a respondent does, in the end, answer the survey.  Nothing is ever easy.

Cheers,
Graham

[gvarga]

I would say there are 2 fact tables: Surveys and Answers.
During the ETL you will see wheather there are answers into a Survey. You have to mark the Survey in the Survey fact table as Answered or Not. You can now easily calculate the required ratio.

[ngalemmo]

Survey, Question and Answer (I assume these are multiple choice questions) are dimensions.

Surveys Sent would be a fact recording each send. One row per survey sent.

Surveys Received would contain one row per answer.

Survey Content would be a factless fact that relates survey, question and answer along with other degenerate values (such as question number and answer label).

[Grah31]

It's funny how spending half an hour just framing my question to try and make it easy for readers to understand helped give me further insight into and clarification of the problem. That plus a night of sleep and I've come to the same conclusion as gvarga. My original fact table should be named Fact_SurveyAnswer and a separate new Fact_Survey table with a 'Number of Responses' measure (to hold 0 or 1) and a 'RequestSentDate' and I'm good.

ngalemmo - You are correct in that it is a mostly a multi-choice survey. I already have question, answer and survey dimensions and those are working fine. Good to know that I was pretty close with the design. Just needed that last bit of a nudge to settle on the second Fact table. Now to code that and the tabular cube to query this new fact table.

Thanks for the replies and help guys,
Graham

[Sponsored content]