Student Profile

[bajopalabra]

Hi

I trying to design an academic dw

I have created:

FACT Course Appointments
sk_student
sk_course
-----------
appointed  ( equals 1 always)

these sks conform a unique id for the fact table

the issue is that I need to record the student profile too, in such a case I have added sk_profile:

FACT Course Appointments
sk_student
sk_course
sk_profile
-----------
appointed  ( equals 1 always)
...

I don't know if it is right or wrong
because sk_profile doesn't has nothing to do with the "uniqueness" of the pk

Furthermore,
I'd need to add more profiles to the fact table, like:


FACT Course Appointments
sk_student
sk_course
sk_profile_academic
sk_profile_social
sk_profile_economic
sk_profile_health
...
-----------
appointed  ( equals 1 always)
...

I mean, they are not needed for the key to be unique

I have a temptation to add more sks like these
because it better explain the "transaction" (for a further analysis)

but I don't know how if it would cause me any problem or limitation in the future...

can anyone guide me?

TIA

[ngalemmo]

A PK has nothing to do with what dimensions a fact table contains.  If additional dimension are needed to provide context, so be it.  Include them in the fact.

A PK is a logical concept in ER modeling to uniquely identify a row.  It has nothing to do with dimensions in a fact fact table other than you may identify one or more of those columns as being part of the PK.  They are different concepts for different purposes.  There is no requirement that a fact table has a PK, unless you need it to update rows in the table.

[bajopalabra]

oh, thank you ngalemmno

that's the kind of authorized answer I needed

recent readings in some forums have lead me to confusion

However, following the previous logic...

in the case of "Academic Profile"
users need to filter and group by specific values
but they also need to average (or sum, etc) that values

then I'm thinking in placing the values of "Academic Profile"
in both Fact and Dim tables

I know that it's no such an elegant design
but I think it will be effective

please, what do you think about that?

thanks a lot

[ngalemmo]

If you have a profile dimension, keep the attributes there.  Do not put them in the fact table.  Making a fact table wider than it needs to be degrades performance.

[Sponsored content]