SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Modeling Survey Data


Modeling Survey Data

Author
Message
MikeBrey
MikeBrey
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 134
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.
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20979 Visits: 7660
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. :-)

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
MikeBrey
MikeBrey
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 134
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?
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20979 Visits: 7660
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.


- 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
MikeBrey
MikeBrey
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 134
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.
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20979 Visits: 7660
MikeBrey (12/7/2012)
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.


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


- 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
MikeBrey
MikeBrey
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 134
Same questions different vendors. Would you still recommend the snowflake?
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20979 Visits: 7660
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.


- 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search