Measures in FTs or Descriptions in DTs

View previous topic View next topic Go down

Measures in FTs or Descriptions in DTs

Post  bajopalabra on Thu Feb 07, 2013 3:07 pm

Hi

I have a bunch of measures that should be present in every Fact Table of the model

My question is if I really have to repeat each bunch of measures in every Fact Table? Isn't it awkward?

My case is an academic dw, where the measures are like:

Code:
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 ...

There are a lot of combinations among the measures
but there would be groups of combinations that would represent the "state the student"
(we don't know now what these groups are)

So I'm thinking in a Junk Dim where a unique id describe each combination
this way, I can add a simple SK to refere to that "state"

The problem is that i won't take measures (counts, sums, etc)
and I don't know if users can put filters like : "number or exams left < 5"
because I guess the Dims must have only character fields (but SKs) ...

id
000001 10 3 9 8 1 ...
000002 10 3 9 8 2 ...
000003 10 3 9 0 1 ...
...

I don't know what to do
Maybe someone can clarify me please

thanks


bajopalabra

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

View user profile

Back to top Go down

Re: Measures in FTs or Descriptions in DTs

Post  ngalemmo on Thu Feb 07, 2013 9:47 pm

Fact tables represent business events or states. Taking a course and taking an exam are two different events that occur at different frequencies and are of a different purpose. There would be different fact tables for course enrollment and exams. These facts may share dimensions, for example student and course would be dimensions of both facts. Each fact would have different measures.

The measures you describe do not make sense to exist together in the same fact because the granularity is different. Number of courses passed and number of exams passed don't belong on the same row. The former makes sense as an aggregate of the student, but the latter would make sense for a course. The number of exams a student passes in his/her tenure doesn't mean much.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

I'm back

Post  bajopalabra on Thu Feb 28, 2013 11:22 am

Thanks for the reply!

the issue in this case is that, data like "number of exams passed" is relevant

because authorities want to track the "state" of each student at any point in time
to analyze the development of his/her career
and then tune academic rules to enhance the graduate/applicant(entrant) ratio

I thought in taking periodic snapshots of that "state"
instead of calculating it, just for simplicity for the user

also, social and eco data would be added to the snapshot

they need to filter and drill-down by this attributes

for these reasons I think in put this "measures"/states in a dim rather than a the fact
that is my conflict, and it's difficult to me to explain

if I place the "measures/states" in a dim
I could use a junk dim, but the combination of states may be huge

Moreover, user should be able to query, for example, the avg( exams_passed ) ....
which is clearly a measure and it should be placed on the fact...
that's the contradiction I can't solve

thanks again ngalemmo!
I expect your suggestions


Last edited by bajopalabra on Thu Feb 28, 2013 4:30 pm; edited 1 time in total (Reason for editing : added a paragraph)

bajopalabra

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

View user profile

Back to top Go down

Re: Measures in FTs or Descriptions in DTs

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