Last week I had to design and sketch a more or less simple OLAP solution during which I had to face a certain dilemma. But before going into details I'd like to share some background.
The OLTP source system is in Oracle:
The first fact table row points to the "company size" question which has 3 possible answers from which the "small/medium" is the answer in the case. There are questions that have no predefined answers like number of employees. In these cases the fact table contains exact values instead of answer codes and the answer group column is NULL in the question table.
Report / OLAP side:
In the required report (that comes from a cube) I have to display only counted numbers e.g.:
Dimension: Question-Answer, Measure: count FactID
Company size \ small/medium - 1 (row)
SQL Server DW side:
I decide to create a single Dimension table with a query similar to this one:
SELECT q.QuestionID, q.QuestionText, a.AnswerID, a.AnswerText
FROM Question q JOIN Answer a ON q.AnswerGroup = a.AnswerGroup
These 4 columns + an additional Question-AnswerID form the DimQA table:
Then the DW fact table has only 1 relationship because it contains the QA_ID as foregin key. This one column show the questions and the answers (where exists one) as well.
Dilemma:
One of my collegaues insists on using two separate dimension tables and two separate dimensions based on them, one for question and one for answer. But that would not be a real snowflake schema becuase then the DW fact table should contain both foreign key ID-s and there would be one more starlike dimension.
So I intend to use my approach but as far as I don't really know the commonly used best practices (if they exist) for this case so I'd greatly appreciate any comment and/or help! :o)