Dynamic Measure

[dlb777]

I'm working with olap for research/polling data.
Example : I have a polling with 3 questions. Number one have 3 answer (a,b,c), Number two have 2 answer (a,b), and number three have 4 answer (a,b,c,d).
If i want to build a cube. I need a measure. Usually i use a fixed measure (profit, income, expenses, etc). But in this case in question number one i have 3 measure (a,b,c). In question number two i have two measure (a,b) and in question number three i have 4 measure (a,b,c,d).
What shoud i do (??
I want to build a dynamic measure for a cube. Do u have any suggest for me? The question is not only three, may be about 40 question for each research/polling.

 

[MDXer]

With a quick look I say I might approach this a couple of diffrent ways depending on the needs of the user. I think properly designed option 1 would be the best.

1) Create an Answers Dimension That would contain a member for the Distinct Answer Labels A,B,C. Key these to your fact table so you would end up with something like the following.

dimAnswers

AnswerID AnswerName
-------- ----------
1 A
2 B
3 c

FactTable

testNumber QuestionNumber AnswerID
---------- -------------- --------
1000 1 3
1000 2 1
1000 3 2
1001 1 2
1001 2 1
1001 3 1

2) Create a Measure for each answer which defeats it being dynamic so you would have A-Count B-Count C-Count

testNumber QuestionNumber A B c
---------- -------------- - - -
1000 1 1
1000 2 1
1000 3 1
1001 1 1
1001 2 1
1001 3 1



"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck