December 7, 2012 at 9:03 am
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.
December 7, 2012 at 1:14 pm
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?
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 7, 2012 at 1:47 pm
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?
December 7, 2012 at 1:54 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 7, 2012 at 2:19 pm
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.
December 7, 2012 at 2:23 pm
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
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 7, 2012 at 3:14 pm
Same questions different vendors. Would you still recommend the snowflake?
December 7, 2012 at 6:18 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply