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

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 (AT) hot-NOSPAM-mail (DOT) com> wrote