Cross between same dimension

[dlb777]

If I have a fact table ex:
TBAnswer, have fields "Question", "Answer", "UserID"
Dimension table is
TBUser, have fields "UserID", "Name", "Address", "Age"(==>>varchar(40)).

If I want to count How many user whose answer "ABC" in question "AAAAAA". whose age is 20. And I want to cross that with answer "DEF" in question "BBBBB".

So i want to know how many user whose age 20 whose answer "ABC" in question "AAAAA" AND also answer "DEF" in question "BBBBB"

How do i must to design the cube?
I dont know if u know what i mean. But i've do my best to explain. Please help me. Thx.

 

[smin]

Here is one suggestion.

Aarrange your fact table as follows.

UserID, Q1, Q2, ... QN

So the number of column is equal to the number of questions plus 1 for userID. Once you arrange your fact table this way, then the rest is easy. Each question becomes your dimension, so there is going to be N dimensions. And the measure is the count(userid).

If you drop the two dimensions(i.e. the two questions) in the data browser, one in the row and the other in the column, then you will see how many answered 'ABC' in one question have answered 'DEF' in the other.

I am not sure I interpreted your problem correctly but this is how i have done in our school.

 

[dlb777]

thank you. But it didn't solve the problem.
the real situation is like this :
i have a program which can input many "Research"
So in every research dont have a same question number. Example in research "A" it have 5 question. But in research "B" it has 7 question.
So the fact table cannot be arrange like that (userid, q1, q2, q3, ... qn)
So the technique that i use is to make a dimension question for each research.
Plz help me.......
Thank you

 

[smin]

I see. You have many different researches.

Can I ask you a few questions then?
1. how many different researches are you dealing with?
2. Why would you have different research data on a same cube? Are they all related researches?
3. what is the range of the number of questions in different researches?
4. what are the data types and domain of answers in your researches?

if data type and domain of q1 in research A is int 1 - 4 and q1 in research B is yes/no then my suggestion would not work as you said becasue they dont belong to same column(dimension).

Depending on those info, the cube design can be different. You could have one cube for each research and several virtual cubes to combine any two related researches based on userid for example.

I may not be able to come up with a solution but gurus here will certainly be able to if things are a bit more clear.

Regards.