Measures in Dimension?

View previous topic View next topic Go down

Measures in Dimension?

Post  bajopalabra on Tue Dec 11, 2012 2:45 pm

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

bajopalabra

Posts : 12
Join date : 2012-08-24
Age : 42

View user profile

Back to top Go down

Re: Measures in Dimension?

Post  BoxesAndLines on Tue Dec 11, 2012 9:53 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Facts in Dimension and Fact table simultanously

Post  Oleole on Wed Dec 12, 2012 4:34 am

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

Oleole

Posts : 12
Join date : 2012-02-15

View user profile

Back to top Go down

Example

Post  bajopalabra on Wed Dec 12, 2012 9:54 am

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!

bajopalabra

Posts : 12
Join date : 2012-08-24
Age : 42

View user profile

Back to top Go down

Re: Measures in Dimension?

Post  BoxesAndLines on Wed Dec 12, 2012 11:11 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Measures in Dimension?

Post  bajopalabra on Wed Dec 12, 2012 11:55 am

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


bajopalabra

Posts : 12
Join date : 2012-08-24
Age : 42

View user profile

Back to top Go down

Re: Measures in Dimension?

Post  Jeff Smith on Wed Dec 12, 2012 5:56 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Measures in Dimension?

Post  ngalemmo on Wed Dec 12, 2012 8:08 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Measures in Dimension?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum