April 17, 2008 at 5:49 pm
I have a fact table and 10 dimension tables.
now my dimension table has a composite primary key..
SEQNO
and StudentID
Now when a student comes to a camp...
he is involved in many events like
social event..
fight event..
race event..
all these are my dim tables...
in my fact table I want to maintain history of all sttudents with their events..all events
a student can be into none or more than one events..
So when i lookup to dim tables
do i need to take student ID column from all the tables in my fact
or only one in fact....
pls let me know if u hav any idea...
how to do this..
Thanks a ton
Thanks [/font]
April 18, 2008 at 4:56 am
Hi,
What you should use is a surrogate key, rather than your composite key (you should be able to find a whole load of info on tinternet) on your dimensions. This is done to protect your datamart from changes to the source system and also to allow you to maintain history in a type 2 slowly changing dimension as well as other reasons too. (If you need to maintain uniqueness accross these columns then add a unique index to your dimension accross these columns.).
Before my next point, I just want to say that I don't know what the business process is that you're developing your datamart around, so it's hard to be definate about what the dimensional model should look like...
From the brief description you've written though, it looks like a students participation at an event constitutes a 'fact'. If that is the case, then you're talking about a 'factless fact' (again I refer back to previous paragraph as I don't know the business). I would have thought that one event dimension should cover all the events, whether they are social or fight events. Then you can add the attributes (say EventType, EventDate, EventName) so that you can run queries without too many joins. When loading your fact you'd then have to lookup the event_key on your event dimension, the student_key on your student dimension, and then place a 1 or a 0 as a fact to give the attendance. It might also be that you don't need to record every instance of an event and that the same event is scheduled say twice a week... in which case you'd have an eventType dimension and use the date_key, organiser_key/tutor_key to find out attendance on a given day etc
Its always hard to give definate answers when it comes to dimensional modelling as there is usually more than one way to map a business process, not to mention the various types of fact tables and dimensions (role playing, degenerate, bridging) etc But one of the things I'd definately do, is readup on all the theory and get the logical design worked out before you start you're coding. If the model is wrong and you want more detail after you start your loading it's many times more difficult to do it at that point.
Have a look at http://www.rkimball.com/html/kimballu.html which has plenty of info on data warehousing. Also lookup factless facts which are a common way of recording student attendance.
Hope this helps
(Edited to add links)
Kindest Regards,
Frank Bazan
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply