Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wgpubs
Yak Posting Veteran
67 Posts |
 Posted - 2006-02-14 : 14:31:14
|
| Hi there,I'm working my way thru my first dw project analyzing survey responses from a generic front-end survey web application.Anyways, one of the architectural issues I'd like to get some input on regards the design of my Answers Fact table. Each row in this table respresents an answer to a question by a survey user. The obvious measure (at least I think so) is the answer itself ... the problem is ... the underlying data type for an answer can vary from question to question (e.g. one can be an integer, the next a currency, the next short text, a yes/no, a true/false, a decimal, or a date/time, etc...). So my question is ... "How should I represent the answer value in my actual dimensional model?"One approach recommended to me was to create a separate column for each possible data type ... so my Answers fact table would look something like below. Upside seems to be that the data is typed, most measures are addititive ... downside is that it seems like there will be alot of wasted space as only one of these fields will have a value per row.<foreign keys to dimension tables here...>AnsInteger (int)AnsMoney (money)AnsDateTime (datetime)AnsText (varchar(255))AnsYesNo (boolean)AnsTrueFalse (boolean)Another approach I came up with was to simply create a generic AnswerValue column of type varchar (255) and the indicate the *true* data type of the answer through an attribute in the related Question Dimension. My thought is that I could simply evaluate this attribute in my calculations to determine how to actually work with the data in the generic column. Downside of this approach seems to be lack of type-safety and a totally non-additive measure .... up-side seems to be reduction of space in the DW.Anyways, any thoughts? Is there a better way?Thanks - wg |
|
|
|
|
|
What to do: A Measure with varying data types