Prologika Forums

Hi Teo,

I have a data warehouse used to store data from a variety of different surveys.  One of these surveys includes several demographic questions (e.g. gender, ethnicity, sexual orientation).  Analysts want to slice and dice other questions by the answers users give to these demographic questions.

So my first thought was to create a Junk dimension that could then be related to the answers fact table.  Something like this:

DimDemographics
* DemographicID (int, pk)
* Gender (varchar(10))
* Ethnicity (varchar(50))
* SexualOrientation (varchar(50))

The problem, which I just realized, is that the "What is your ethnicity?" question allows users to enter MULTIPLE answers!  So what to do???

I was thinking of keeping the DimDemographics dimension but associating it to the DimResponse dimension (which stores information about the users who provided answers) in a many-to-many relationship ... with the DimResponse table being directly associated to the Answers fact table.

Does that solution sound solid?  Is there a better way?  I wish I had a DW/BI team to bounce things off of here at work ... but I'm all alone in this world :)

Thanks - Wayde
 

I was thinking of keeping the DimDemographics dimension but associating it to the DimResponse dimension (which stores information about the users who provided answers) in a many-to-many relationship ... with the DimResponse table being directly associated to the Answers fact table.

If one user can enter many answers and an answer can be selected by multiple users you have a many-to-many relationship between users and answers. In this case, chosing a many-to-many fact relationship over a factless fact table (DimResponse probably should be named FactResponse) sounds like the right thing to do.