Cube Design for Survey Data

Post Reply
andrewhharmon
Posts: 1
Joined: Mon Oct 05, 2009 6:44 pm
OLAP Product: MS Analysis Services
Version: 2008
Excel Version: 2007

Cube Design for Survey Data

Post by andrewhharmon » Mon Oct 05, 2009 7:08 pm

Hi All,

I'm struggling with the appropriate way to design my database/cube. I'll start with some background. I'm trying to develop a cube that will allow users to see results of survey data. This survey data consists of what we call demographic questions and metric questions. the demographic questions describe the respondent (ie. gender, age, geography) and the metric questions are the actual facts we are collecting and aggregating on (ie. On a scale of 1 - 7 how happy are you at work?). My current setup is a fact table with answers to the survey questions, FactResponse. So if it's a metric question, it will have a fk in to the question table. It will also have a responseValue column that you can aggregate on. This table includes a respondentID. So a respondent ID would have multiple rows in the fact table. (one for every question in the survey including the demographic questions) I have a few dim tables, dimSurvey (which of our many surveys this fact is from), dimOrganization (which company the response belongs to), dimDate (when the survey was completed) and where I'm struggling the most dimQuestion.

Details on dimQuestion: This table includes a row for every answer choice for every question. so the question, "On a scale of 1 - 7 how happy are you at work?" there are 7 rows. the columns of this table are basically a PrimaryKey, Question Text, QuestionAnswer. Keep in mind that this table also includes the demographic questions (ie. "What is your gender?" has 2 rows "Male" and "Female")

So what i need to develop is a cube where i can get the average of the question "How happy are you at work" and slice it by multiple demographics.

So how happy are males between the ages of 18-25? The answer would be the average of ResponseValue column of the FactReponse table for that particular question that matches those demographics. (of course the demographics are on seperate rows within that fact table)

So to me, while trying to design this, i find myself apparently needing to slice by the dimQuestion dimension multiple times.(so that i'm slicing by gender and age). I can't grasp in my head the appropriate way to set this up and i'd love to hear some opinions from people with more experience than me.

Some notes to consider. Each time a company takes a survey, they are able to add their own custom questions specific to their company. So they may add a business unit demographic question that has their specific BUs and i'd need to cut by that. (it would be an additional question in the survey) Likewise they could add a custom metric question that would need to be aggregated on.

Marcus Scherer
Community Contributor
Posts: 125
Joined: Sun Jun 29, 2008 9:33 am
OLAP Product: TM1
Version: 9.5 10.1 10.2
Excel Version: 2007
Location: Karlsruhe

Re: Cube Design for Survey Data

Post by Marcus Scherer » Tue Oct 06, 2009 7:32 am

Hi Andrew,

from my understanding your dimQuestion would only contain PrimaryKey and QuestionText. So "how happy are you at work" would only appear once in your dimension table. The QuestionAnswer = 1-7 is your metric and would only appear in the fact table, right?
The gender you could put in a separate dimension.
Some notes to consider. Each time a company takes a survey, they are able to add their own custom questions specific to their company. So they may add a business unit demographic question that has their specific BUs and i'd need to cut by that. (it would be an additional question in the survey) Likewise they could add a custom metric question that would need to be aggregated on.
To add custom questions you have to prepare your relational data source. You don't expect to add in SSAS directly?
You would need to build an application on your SQL server. And after updating your model you need to process dimensions and cube. So consider a delay in time.

HTH,

Marcus

heatheryorke
Posts: 1
Joined: Sun Jan 22, 2012 4:05 pm
OLAP Product: Oracle
Version: 9.5
Excel Version: 14.0.4760.1000

Re: Cube Design for Survey Data

Post by heatheryorke » Mon Jan 23, 2012 4:11 pm

Hey,
sorry, but i don't seem to understand, why after updating the model there is a need to process dimensions and cube ?
I think i have a cloud in my head /:
Thank you !!

Post Reply