How do I create an OLAP cube in Microsoft SQL 2000 Analysis services
which will generate the following types of data for a report:

Here are the source tables (in SQL 2000):
Note: not all fields are listed.

SURVEY
---------
(PK) Survey_ID int
Survey_Name varchar(200)

QUESTIONS
------------
(PK) Question_ID int
Question_Name varchar(200)

ANSWERS
------------
(PK) Answer_ID int
Answer_Choice_Text varchar(200)
Answer_Value int

RESPONDENTS_ANSWERS
---------------
(PK)Respondent_ID int
(PK)Question_ID int
(PK)Answer_ID int

I want to build reports which crosstab the count of respondent answers
against respondent answers of a different question.

For example: 
Question 1) Do you watch the NBA?
-Yes 
-No

Question 2) What is your favorite NBA Team?
-Mavs
-Lakers
-Kings
-Spurs

Here is the report I want to create via OLAP in SQL 2000 Analysis Services:

			What is your favorite team?
			Mavs	Lakers	Kings	Spurs	Total
		-----	---	---	---	---	---
Do you		Yes	5	5	5	5	20
watch NBA?	No	10	2	1	2	15
		-----	---	---	---	---	---
		Total	15	7	6	7	35

So far, I have my fact table as Respondent_Answers, and a dimension
that includes: Survey/Questions/Answers.  I need to somehow have
another dimension which includes Survey/Questions/Answers which I can
crosstab the actual Respondent_Values, or someting.  Please help.

The goal is to have the capability to crosstab any question vs any
other question.  I'm looking for a solution to compare 2 static
questions.

Thanks,
-Jonathan

---

Clarification of Question by jthoma1-ga on 05 May 2004 17:39 PDT

I meant to say, "I'm looking for a solution to compare more than 2 static
questions."