Modelling an Event Flow

Hi All,

I'm pretty new to data modelling and don't know the best approach to this particular problem.

I have a user who can go through multiple events before completing a process, each event could be performed multiple times or skipped completely. With the completion of each event (or repeat of each event) a record of information gathered at the event will be gathered. The content of each event is different from event to event.

e.g. Event flow could be 1 - 2 - 2 - 3 - (event 4 omitted) - 5 - 5 - 6 (end)
and at each event gathers a different set of data.

Hope someone out there can take pity on me somewhat and gently push me in the right direction.

Many Thanks in advance,

Andy

If each event is completely different then you may need a seperate FACT table for each Event Type to caputure the specific details about what happened for each occurence for that event.

However you could then probably do with a Process Summary FACT table as well which would capture the FACTS about the Process

maybe something like this

ProcessSK	CustomerSK	StepNumber	EventSK	DateSK	StepCompleted	ProcessCompleted
1	10	1	1	20110101	1	0
1	10	2	2	20110105	1	0
1	10	3	2	20110107	1	0
1	10	4	3	20110110	1	0
1	10	5	5	20110114	1	0
1	10	6	5	20110119	1	0
1	10	7	6	20110123	1	1
2	20	1	1	20110104	1	0
2	20	2	2	20110108	1	0
2	20	3	3	20110112	1	0
2	20	4	4	20110114	1	0
2	20	5	4	20110121	1	0
2	20	6	6	20110128	1	1

If each event was NOT COMPLETELY different and was just a change of status, then how would we model this?

Specifically how would one Model Status changes in dimensional modelling?

Regards,
Franklin