Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Modeling Survey Data Expand / Collapse
Author
Message
Posted Friday, December 7, 2012 9:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 20, 2013 9:14 AM
Points: 11, 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.
Post #1394107
Posted Friday, December 7, 2012 1:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:26 PM
Points: 5,446, Visits: 7,614
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
Post #1394216
Posted Friday, December 7, 2012 1:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 20, 2013 9:14 AM
Points: 11, 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?
Post #1394230
Posted Friday, December 7, 2012 1:54 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:26 PM
Points: 5,446, Visits: 7,614
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
Post #1394232
Posted Friday, December 7, 2012 2:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 20, 2013 9:14 AM
Points: 11, 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.
Post #1394246
Posted Friday, December 7, 2012 2:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:26 PM
Points: 5,446, Visits: 7,614
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
Post #1394249
Posted Friday, December 7, 2012 3:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 20, 2013 9:14 AM
Points: 11, Visits: 134
Same questions different vendors. Would you still recommend the snowflake?
Post #1394264
Posted Friday, December 7, 2012 6:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:26 PM
Points: 5,446, Visits: 7,614
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
Post #1394291
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse