Measures in Dimension?

Hi,
I have a lot of measures (almost all, semiadditive)
The user could average, or just query, these measures
but the user could also wants to use them to filter and group...

my question is,
- I need to keep measures in the fact table for calculating
- I need to keep measures like generic data i dimension for filtering and grouping

how can I allow both requirements?
should data be on both Fact and Dim tables?

(I thought about classifing the measures in some ranges for each measure
but user would lose precision)

I'm pretty confused with it
I'll appreciate your posts
TIA

Can you provide an example of measures needed for filtering facts? The only one I can think of would be banding dimensions which aren't really measures.

Do it both ways. As Kimball says in design tip 97, your goal is ease of use - not elegance.

it's an academic DW

I have a dimension for Academic State, like that

sk_date (data is sampled at a regular basis of about 3 months)
sk_student
sk_career
sk_cohort ( the entry year )
number of courses requested
number of courses finished
number of courses passed
number of courses not passed
number or courses left
number of exams appointed
number of exams requested
number of exams passed
number of exams not passed
number or exams left
... etc ...

users need to answer the question : "what's the average exams left for cohort 2009 ?"
but they need to answer : "what's the number of students having more than 26 exams passed?"

THANKS!

I think what is missing here is a fundamental understanding of how dimensional modeling works. You don't summarize measures in the dimensions. Measures change every day. That's why they reside in the fact where they can be summarized using dates, students, courses, etc.

sure, I know that
but the user need some way to group and filter by the measures

I know it's weird
I thougth about grouping each in categories, like { low, medium, high } to put in the dimension

this is intended for the user to play
they need to filter and group based on that measures
that is, they should use the measures (not the measures, I mean the "labeled measures", in the dimension)
as a source for drag&drop columns and rows

please tell me if I didn't explain it well

THANKS

The best way is to create an aggregate table.

Or use queries that filter on the query result. Having such measures in a dimension changes the dimension from a slowly changing dimension to s frequently changing dimension.

bajopalabra wrote:it's an academic DW

I have a dimension for Academic State, like that

sk_date (data is sampled at a regular basis of about 3 months)
sk_student
sk_career
sk_cohort ( the entry year )
number of courses requested
number of courses finished
number of courses passed
number of courses not passed
number or courses left
number of exams appointed
number of exams requested
number of exams passed
number of exams not passed
number or exams left
... etc ...

users need to answer the question : "what's the average exams left for cohort 2009 ?"
but they need to answer : "what's the number of students having more than 26 exams passed?"

THANKS!

What you are describing above is an aggregate fact table, not a dimension. You have a bunch of measures and some dimension surrogate keys. That is a fact table. In this particular case, it is an accumulating snapshot.