Modeling Survey Data

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Strategies and Ideas » Modeling Survey Data

Modeling Survey Data

Rate Topic

Display Mode

Topic Options

Author

Message

MikeBrey

Posted Friday, December 07, 2012 9:03 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 3:29 PM
Points: 11, Visits: 117

I work for a distributor and biannually we send out a survey to our employees to gauge how well our vendors are doing. After the employees take the survey we compile a report and share it with the vendor. Right now it is an access database and a crystal report. I am looking to get it into SSRS but am having difficulties determining how to model the data. This is what I have so far:

Dim_Vendor
Dim_Question (survey questions have set values and comments)
Dim_Employee
Dim_Date (start-end date of survey)
Fact_Survey

Do I have two fact tables one for the comments and the other for the set values? The Dim_Question table seems a bit light. All I can think of putting in it is the question, category of question and comment y/n.

Another part of this vendor process is that we give them a report on total sales we have for their product lines. Any help is appreciated. Thanks.

Post #1394107

Evil Kraig F

Posted Friday, December 07, 2012 1:14 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:47 PM
Points: 5,678, Visits: 6,130

Well, without the DDL for the tables it's hard to answer your question directly. I'm having a real hard time following what your question actually is, unfortunately. That's probably because of a lack of familiarity with what you're looking at, I haven't seen it. Smile

Can you provide some more particular details into exactly what's troubling you?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1394216

MikeBrey

Posted Friday, December 07, 2012 1:47 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 3:29 PM
Points: 11, Visits: 117

Dim_Vendor
Ven_PK
VenNum
VenName

Dim_Question
Quest_PK
QuestNum
QuestText
Comment (Y/N)
Category

Dim_Employee
Emp_PK
EmpNum
EMpName

Dim_Date
Dat_PK
Date

Fact_Survey
Ven_FK
Quest_FK
Emp_FK
StartDat_FK
EndDat_FK
Response
ResponeValue
CommentText

Is the structure I am thinking about. I guess my question is how have others modeled survey data in a data warehouse?

Post #1394230

Evil Kraig F

Posted Friday, December 07, 2012 1:54 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:47 PM
Points: 5,678, Visits: 6,130

That looks about right to me, however I'd have the survey itself linked off as a snowflake for survey/question dimensions (so I could pull up the actual question for reports and the like) and link that way. The only date I'd keep in the fact is when the employee submitted the survey for survey x.

Post #1394232

MikeBrey

Posted Friday, December 07, 2012 2:19 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 3:29 PM
Points: 11, Visits: 117

These surveys run for only two weeks twice a year and sometimes for the same vendors. The employee response date they are not too concerned about. Wouldn't you be able to get the question for the Dim_Question.QuestText. I am a bit confused on your snowflake comment.

Post #1394246

Evil Kraig F

Posted Friday, December 07, 2012 2:23 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:47 PM
Points: 5,678, Visits: 6,130

MikeBrey (12/7/2012)

Well, most surveys I assume have different questions, thus I'd chain from question to survey.

Really, it'd look more like this for how I usually organize that type of data chain (psuedo-design, obviously):

Fact_Answers
EmpID
DateAnswered
QuestID
Response
Comment

Dim_Question
QuestID
SurveyID
QuestionText
ResponseType (Y/N, multipart with list, etc)

Dim_Survey
SurveyID
VendorID
DateStart
DateEnd

Post #1394249

MikeBrey

Posted Friday, December 07, 2012 3:14 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 3:29 PM
Points: 11, Visits: 117

Same questions different vendors. Would you still recommend the snowflake?

Post #1394264

Evil Kraig F

Posted Friday, December 07, 2012 6:18 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:47 PM
Points: 5,678, Visits: 6,130

MikeBrey (12/7/2012)

Same questions different vendors. Would you still recommend the snowflake?

Yes and No. I'd probably move Vendor over to the Fact table but stay with the same general pattern. The idea being that the survey is really a dimension/description of the question, which is the only concern in the Fact. You could, for ease of use, put both QuestionID and SurveyID into the Fact table but it results in the same concept, just duplication of data for ease of reporting. Depending on how large your fact table will get will help you decide if an extra INT is going to significantly affect processing times.

Post #1394291

« Prev Topic | Next Topic »

Permissions