Star or Snowflake i.e., 1 or 2 Dim tables?

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.


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)

Published Monday, February 9, 2009 1:50 PM by MartinIsti
Filed under: , , , ,


# re: Star or Snowflake i.e., 1 or 2 Dim tables?

Monday, February 9, 2009 2:23 PM by Miky Schreiber

I think that your design is better because there always should be only one FK from the fact to the dimension. I assume that each answer has only one question (parent) and in this situation your design is better.

# re: Star or Snowflake i.e., 1 or 2 Dim tables?

Monday, February 9, 2009 8:44 PM by simonsabin

Given an answer of 100 has no meaning with out the context of the question I would agree with you.

# re: Star or Snowflake i.e., 1 or 2 Dim tables?

Tuesday, February 10, 2009 11:37 AM by MartinIsti

Thanks for the comments Miky and Simon, as a beginner blogger it's good to know that there are people out there who read my posts!

In the meanwhile we've chosen to implement my version! :)

# The Answers Group |

Tuesday, March 29, 2011 7:57 AM by The Answers Group |

Pingback from  The Answers Group |

Powered by Community Server (Commercial Edition), by Telligent Systems