INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Cube Design for Survey Data

Cube Design for Survey Data

(OP)
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.

RE: Cube Design for Survey Data

(OP)
UPDATE: I've found this article that seems to be close to what I'm looking for, but it's not quite all there. I'm not really sure how to implement it.

http://www.fcsm.gov/99papers/yost.pdf

RE: Cube Design for Survey Data

SSAS is a Business Intelligence tool....so it's kind of hard to utilize all of the Business Intelligence features while remaining completely generic.

If this was my project, I would create a standard Demographics dimension as well as a Location dimension (for your business unit example) in addition to other possibilities.  I would ETL the demo questions (what gender are you) to the Demographics dimension.  Obviously, not all surveys will have the same demographics questions, so you could end up with a bunch of UNKNOWN attributes.  Conversely, you could break this out further and store foreign keys in your fact table to individual dimensions such as Income Range, State of Residence, etc.  You would ultimately end up with a bunch of dimension records describing the survey and respondent in addition to some fact records describing the respondents' ratings.  In other words, not all questions are created equal.

This creates a lot more work on the ETL side, but I think if you look at this a Header/Response scenario instead of just a bunch of random questions on a form, you'll have an easier time analyzing the data.

RE: Cube Design for Survey Data

(OP)
Thanks for the reply,

If I'm following you correctly, you would have a dimension for each custom demographic (such as business unit). But every time we upload data, we could have new custom demographic questions, so a new dimension would need to be built for each of those. This seems like it would quickly spiral out of control. We'd like to have the number of dimensions fixed.  

RE: Cube Design for Survey Data

Nope.  I've never worked for a survey company, but I know they are out there, and I'm saying that I assume that they have figured out the baseline demographics.  They know that many businesses are interested in geographic region, and gender, and income level and "distance located to us."  So you would create those dimensions and attributes before-hand, and then map them to the proper places in your ETL.

Yes, you're going to get a lot of one-off, never-thought-of-that things.  In those cases, you have two choices--add them to your generic survey dimension and try to make due with your generic analysis, or modify your standard dimensions to include new attributes.

Again, since SSAS is designed mostly for use within the Organization, it's kind of hard to make much use out of it without including specifics about the Organization.  So if you go completely generic, you're going to end up with something like a two-level hierarchy of survey questions thrown together in a big mix and match list.  True, you could whip up some custom MDX for each client to produce their reports, but if you go that route, why even use SSAS?  Just use the relational database.

 

RE: Cube Design for Survey Data

I'd also be inclined to say that the usefulness of an SSAS application is a result of the effort and precision put into architecting the solution (in addition to other variables).  So if you want to just design something very basic, set it on auto-pilot, and expect to fulfill all of your clients' needs, I'm betting it won't cut the mustard.

RE: Cube Design for Survey Data

(OP)
ok, well regardless of the custom question issue. What if i wanted to cut by 2 standard demographics (say age and gender) if they are both in the dimDemographic dimension, I can't have  the same dimension on 2-axis in a query.

The reasoning for using SSAS is we have a lot of data that needs to be quickly filtered. Our fact table contains a few million rows, and this is growing steadily every month. The queries we right from the relational database are complex and slow.

I'm in the process of putting together some proof of concepts to see if SSAS will speed things up. So far it's been dramatically for the queries i've been able to run, but i haven't the solution to these issues you see in my original post.

Thanks for the comments, I would love to hear more suggestions.  

RE: Cube Design for Survey Data

Age and Gender would be two separate attributes, which means two separate hierarchies, which you can easily place one on the x-axis and one on the y-axis.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close