Model Design With Several 0 to Many Joins?

View previous topic View next topic Go down

Model Design With Several 0 to Many Joins?

Post  Freddie on Tue May 17, 2011 10:04 am

I am trying to find the right model design for incidents which occur in a correctional institution. Here is the basic flow of events:

- An incident occurs.
- Each incident involves zero to many offenders.
- Each offender in the system has a history of involvement in zero to many incidents.
- Each incident leads to zero to many disciplinaries. For incidents involving one or more offenders, each offender may receive a disciplinary – one distinct disciplinary per offender per incident.
- Each disciplinary will contain one or more offense charges.
- Most disciplinaries will lead to a hearing. The hearings will result in findings (guilty/not guilty/dismissed, etc.) for each offense charge.
- Each finding may be appealed – either by the offender or by the correctional officer.

The end result looks something like this… An incident occurs involving 20 offenders (15 suspects, 1 victim and 4 witnesses); the 15 suspect offenders each receive a disciplinary; each disciplinary will contain various combinations of charges from the incident (a total 15 disciplinaries and 65 charges from this one incident); at report time 10 of the disciplinaries have lead to hearings resulting in 41 decisions; six of the decisions were appealed; four of the appeals have already been heard; the remaining two appeals are still pending; the remaining five disciplinaries and 24 charges are yet to lead to hearings.

In the end it all boils down to this:
Each incident involves 0 to many offenders.
Each incident results in 0 to many disciplinaries.
Each disciplinary contains 1 to many offence charges.
Each disciplinary results in 0 to many hearings.
Each hearing results in one to many decisions.
Each decision may be appealed, again resulting with one decision per charge.

The users want a series of reports to count the number of events (incidents, disciplinaries, hearings, appeals and findings, etc.), measure the time lapses between various stages in this process, the time pending between events which have not yet been advanced to the next step in the process, and offender histories (both offenders who have been involved in incidents and those who are incident free). The date dimension for the reports will allow the users to view results based on date ranges of various events – incident date, date the disciplinary was filed, hearing date, etc.

I tried making a model with one fact table for each step of the process (incidents / disciplinaries / hearings) with a dimension table for each, then linking them all from dimension < - > fact < - > dimension < - > fact… This model has multiple fact tables, is full of 0 to many links and runs unbelievably slow.

I then tried a model with an incidents table acting as a central fact table, then joined to that all the other tables (disciplinaries, hearings, etc). This model runs faster but it’s a maintenance nightmare since it is essentially joining fact tables together and it too is full of zero to many joins between all the tables.

I am considering making one “mother-of-all-tables” which basically will contain all the counts, measures, etc. for each offense charged in each disciplinary to each offender involved in each incident. This one table will have all the information for all offense charges from start (incident occurs) to the final outcome (hearing or appeal decision). This really doesn’t conform to my idea of a good model design but it eliminates most outer joins.

Does anybody have a suggestion as to how to design a model to count the number of events that have occurred, measure the time lapse between the various events, and also count and measure time pending for cases which are still in progress but not yet advanced to the next level in this process, with each step along the way having a zero to many relationship?

Thanks.

Freddie

Posts : 2
Join date : 2011-05-17
Location : Utah

View user profile

Back to top Go down

It's all about granularity

Post  AndreLinssen on Tue May 17, 2011 4:05 pm


I would suggest the following.
The lowest level of granularity, if I read it correct, is "offender". (this is tricky)
Each offender is connected to one incident in a certain moment in time (you can be as specific as you need to: seconds, minutes, hours). Therefore, incident is a dimension.
Each offender that is involved in an incident receives one distinct disciplinary. Therefore, disciplinary is a dimension. (Connected to the offender, not the incident)
Each offender that is involved in an incident receives one distinct disciplinary that that is connected to one or more charges. Therefore, "charges" is a dimension. But each charge is connected to a combination of offender, incident, disciplinary, time. (You can add as many distinct charges to this combination)
Each offender that is involved in an incident receives one distinct disciplinary that is connected to one or more charges is connected to a hearing. Therefore "hearing" is a dimension. But a hearing is connected to a combination of offender, incident, disciplinary, charge, time. You can add as many distinct hearings to the combination of offender, incident, disciplinary, charge, time.
Well, since I don't like to repeat myself, I'd say that you could do the same "trick" for the appeals, and correctional officer (yes, I'd consider those a dimension too).

This would mean you would end up with a fact table like this:

offender, incident, disciplinary, charge, hearing, appeal, correctional officer, time, (and probably a reference to the file you have been loading).

And if I read it correct, offender and incident are mandatory (as well is "time").
Of course, you add a surrogate key as "id".

Basically, what you have is a fact table with no facts (unless somebody would add something like "fine" or some other numeric value)
Good thing is: you can "read" the fact table from left to right:
First time you might receive only an offender and an incident, next time you receive offender, incident, and disciplinary. And so on.












avatar
AndreLinssen

Posts : 6
Join date : 2011-02-15
Age : 51
Location : Netherlands

View user profile http://www.linssen-it.nl

Back to top Go down

Thanks

Post  Freddie on Wed May 18, 2011 9:04 am

Thanks Andre. I'll give that a shot.
I was approaching it from the incident, not the offender.
I appreciate the time you spent on this and your input.

Freddie

Posts : 2
Join date : 2011-05-17
Location : Utah

View user profile

Back to top Go down

Follow up on this

Post  bogs811 on Mon Mar 19, 2012 12:52 pm

Hi Freddie,

I'm interested in this topic as I am working on something similar. Based on the suggestion, the fact table record is about the "offender" and not the incident. I want to know how the fact table get's loaded? I'm confused about the fact table granularity on the suggestion. Can you provide some details on how you've handled this?

Thanks

bogs811

Posts : 2
Join date : 2012-02-18

View user profile

Back to top Go down

Re: Model Design With Several 0 to Many Joins?

Post  Vishy on Tue Mar 20, 2012 1:35 am

Let us understand the flow of this.

1) Offernder commits an offence

-- offender goes to the fact

2) Offender commits an offence at particular time

-- Time goes to fact

3) disciplinary is assgined to offender

-- disciplinary goes to fact

4) Charges framed against offender

--Charges goes to fact

5) Hearing is done

-- hearing goes to the fact

6) Appearl is raised

--appeal goes to the fact

...
.....


Point (1) happening creates an Incident and that will work or works as a reference point for us.

1 to 6th point are also works as dimensions.

(Offender ID + Incident ID + disciplinary ID+ Charges ID ) works like primary key for this fact.

Now we have to make it a cumulative fact so that all the time related columns can come from the same row rather then from different rows which is like getting into more complexity of reporting as well as understanding of our design.

now you can add following colums to this fact for time related information.

Offend date,disciplinary assigned date,charges framed date,hearing date etc.


Vishy

Posts : 75
Join date : 2012-02-28
Location : India

View user profile

Back to top Go down

Offender lifecycle events

Post  bogs811 on Tue Mar 20, 2012 9:44 am

Hi,

Wouldn't it be better if each business processes have its own fact table (multi-fact)? Then join the various fact tables on conformed dimensions.

During my analysis, I created both a single fact table (similar to the proposed solution) and a multi-fact tables design. I ended up doing the multi-fact tables design since these are offender lifecycle events and they are distinct business processes (more familiar with).

I'm very curious about the single fact design because of it's simplicity but I need to understand more of how the design works. Based on the first response, I thought that each lifecycle event will have a single fact row and the date key on the fact table refers to this event. If I'm understanding your response correctly, we will add the various lifecycle event dates to the fact table. What is the grain of the fact table? I appreciate if you can explain further?

Thank you for your assistance.




bogs811

Posts : 2
Join date : 2012-02-18

View user profile

Back to top Go down

Re: Model Design With Several 0 to Many Joins?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum