Dimension or Fact?

Hi - I've got a dimensional modelling question that's got me stuck.
I have a "Student" entity and a "Student Transaction" entity.
The Student Transaction table will be a fact table -- that's easy.
And the Student will be a dimension of the Student Transaction table.
HOWEVER, the Student dimension is HUGE and has a lot of sub-dimensions
of its own -- things like State, Country, etc. It's basically what
Kimball calls a rapidly changing monster dimension!
First - isn't this snowflaking, and is that bad? Should the Student
be another fact? But then how do I relate the Student to the Student
Transaction if they're both facts.
Also, the student has attributes like test scores and GPA that seem
like measures -- but a dimension can't have a measure. So how could I
get average test scores and gpa, etc...
The other thing I was thinking of doing was treating the Student as
both a dimension AND a fact! Basically create 2 cubes:
Student Transaction cube, which has Student table as a dimension
Student cube, which actually uses the same Student table, but as a
fact.
This seems to work, but it seems awfully weird to use the same
physical table as both a fact and a dimension... even if it is in
different cubes...
IF anyone has insight, i would appreciate it very much!
thanksIn your case Stundent is a Dimension, average, etc are properties of the dim
ension not measures.
It's a better practice to design the dimension with star structure. You can
make a view to group all information an make the dimension based on that vie
w.
Also you can think several cubes that uses Student dimension and time dimens
ion, like test, etc. And then you can calcultate average and other. A way to
join these cubes is with virtual cubes.
Good Luck|||The "badness" of snowflaking is highly overrated. Conversely, the benefits
of denormalized stars are also overrated.
public @. the domain below
www.tomchester.net
"groove_sf" <dockv@.hot-NOSPAM-mail.com> wrote in message
news:cfpu70dvqh0s8c2p1etalnjdrvos9et1mf@.
4ax.com...
> Hi - I've got a dimensional modelling question that's got me stuck.
> I have a "Student" entity and a "Student Transaction" entity.
> The Student Transaction table will be a fact table -- that's easy.
> And the Student will be a dimension of the Student Transaction table.
> HOWEVER, the Student dimension is HUGE and has a lot of sub-dimensions
> of its own -- things like State, Country, etc. It's basically what
> Kimball calls a rapidly changing monster dimension!
> First - isn't this snowflaking, and is that bad? Should the Student
> be another fact? But then how do I relate the Student to the Student
> Transaction if they're both facts.
> Also, the student has attributes like test scores and GPA that seem
> like measures -- but a dimension can't have a measure. So how could I
> get average test scores and gpa, etc...
> The other thing I was thinking of doing was treating the Student as
> both a dimension AND a fact! Basically create 2 cubes:
> Student Transaction cube, which has Student table as a dimension
> Student cube, which actually uses the same Student table, but as a
> fact.
> This seems to work, but it seems awfully weird to use the same
> physical table as both a fact and a dimension... even if it is in
> different cubes...
> IF anyone has insight, i would appreciate it very much!
> thanks
>|||I disagree that the GPA and test scores should be part of the Student dimens
ion. They are semi-additive facts, and should be considered for a separate
star in your constellation (assuming you are using an architected DW with co
nformed dimensions.)
Think about it this way - if you have student transactions as one "business
process", thus is one star center, then GPA and test scores would merit its
own cetricity in a separate fact table, since GPA and test scores are not tr
ansactions, but are "Grades
", though GPA is going to need to be atomized into GPA factoids so it can be
calculated on the fly if you have other dimensions that will be used to sli
ce and dice. For example, you might want GPA by students by a particular ma
jor, or students in the cla
ss of 2005, etc.
Thus you would have Student as a conforming dimension to both a Transactions
fact table and a Grades fact table, thus the need for at least two stars in
your constellation that share the conformed Student dimension.