I have a design problem. Below is my database structure

PanelData
--AutoID
--PanelID (PK)
--QuestionID (FK)
--AnswerID (FK)

QuestiontoAnswer
--AutoId
--QuestionID (PK) (FK)
--AnswerID (PK) (FK)
--SortOrder

Question
--AutoiD
--QuestionID(PK)
--QuestionText

Answer
--AutoiD
--AnswerID(PK)
--AnswerText


In "Question" table we have one question on Gender , one for Ethnicity

In "Answer" table we have answer to the question for example Gender we
have i.e. male and female and so on for Ethnicity. Overall we have
around 30 questions I want to create 30 different Dimension.


How to I create separate Dimension for Gender , Ethnicity and so on ?
I can have all questions one dimension but I want separately so they
slice the data how they want how can I achieve that with this design?

If i create all the 30 questions has columns in "PanelData" table it
will work but i I want to use above structure.

Any help in this matter would be appreciated.

Thanks
Sam

Are you using SQL Server 2005 for relational storage? In that case, you
could use the new TSQL PIVOT clause to create a fact table view with a
column for each question - without changing the original table, like:

[1] AS Q1AnsID, [2] AS Q2AnsID, [3] AS Q3AnsID
FROM
(SELECT AutoID, PanelID, QuestionID, AnswerID
FROM PanelData) p
PIVOT
(
MAX(AnswerID)
FOR QuestionID IN
([1], [2], [3])
) AS pvt

http://msdn2.microsoft.com/en-US/lib...0(SQL.90).aspx

You can use the PIVOT and UNPIVOT relational operators to manipulate a
table-valued expression into another table. PIVOT rotates a table-valued
expression by turning the unique values from one column in the
expression into multiple columns in the output, and performs
aggregations where necessary on any remaining column values that are
desired in the final output. UNPIVOT performs the opposite operation to
PIVOT by rotating columns of a table-valued expression into column
values.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks

But i am not using Sql Server 2005 and actually I have around 200
questions. I want to divide 200 questions in sub category and may be
create 20 sub categories and if I want subcategory then I need data in
"rows" not in "columns". Problem with that is I can create
only one dimension. I want one dimension for each sub category and it
should slice against the fact table only.

Can you suggest any design which can achieve this?

Thanks for you help!!!
Sam

Sam