Data Model - More than one fact table?

[WBscooby]

Hi

I would really appreciate some support with my data model. It is quite complex and I'm getting in a bit of a muddle! I've attached a snapshot but it's a bit scruffy and I've attempted to blank out some sensitive info.
Essentially it follows offenders, commiting an offence, throught to the offence outcome and then an intervention in some instances.

I have the following tables:

• Offender
• Date calendar
• Offences
• Outcomes
• Interventions
• Bridge for outcome offence
• Bridge for outcome intervention

To summarise how the data is applied:

• An offender commits an offence on a date and can commit many offences
• The offence with then have an outcome on a specific date. An offender can have many offences and each offence can have many outcomes
• Some, but not all outcomes, will then lead to an intervention. An offender can many interventions. 

In terms of usage, I mainly query the data on outcome dates so the date calendar is related to the date of outcome in the outcome table. However, I also query the intervention table by the intervention end date. I have created an inactive relationship to the date calendar and use USERELATIONSHIP in measures.

The above all seems to work quite well. However, when I query the intervention table and try to look at the applicable offenders in a basic table, I'm getting odd results.  Most of my relationships are one-directional as I believe this to be best practice. These can obviously be changed but I do wonder if the real problem is my data model. Any advice on this would be very much appreciated. 
Thank you!

[WBscooby]

Sorry, this is helpful advice but I'm still slightly going around in circles. Intervention is a relatively important table as there are several measurements related to completing the intervention.

I think a combination of an offence ID and outcome ID should be a FACT table with Outcome date.
However, the same comination of offence ID and outcome ID will be linked to an intervention. I'm still a bit confused re how I would represent this without recreating the same combination in both FACT tables

[WBscooby]

Hi again

Yes, this is indeed the scenario and that does make perfect sense! How would you deal with the offence/outcome being applicable to to both the intervention table and the outcome FACT table? Does my design above look correct with the bridge between FACT tables? At the moment this is solely on outcomeID but I initially built the Outcome FACT table with the OffOutcome ID as a FK and could extend this out to the Intervention table also? It's dealing with multiple FACTs but shared dimensions that I am particularly struggling with!

Thank you again!  

[DOLEARY85]

Hi,

The bridge table does look correct. It has been a while since i dealt with this particualr type of system but from what i remember they're not particualrly good at getting the info out in a good format. To join the Offence/Outcome to both the outcome fact and intervention is probably going to be tricky. 

A lot of the time not much detail was needed from the intervention beyond start/end dates and how it closed. Each outcome should list an intervention id if it ended with an intervention so i'd bring that into the fact outcomes table and link the intervention to that. However, this could depend on the software, some of the providers were much worse than others and giving you access to the fields you need.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[DOLEARY85]

Hi,

i used to work with youth offending data so this looks fairly similar to the data model in the recording system. I think you might need a link from the intervention table to the person table too and make that active when you are querying that table. 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[WBscooby]

Hi

Thanks for responding! I think I can make this work. Do you think this would be better practice than creating one main FACTtable that contains both the outcomeoffence and the intervention? 

Thank you 

[DOLEARY85]

I think when using this data it probably would be better to it with multiple FACT tables. From what i remember not every intervention will have all the offences/outcomes attached so it can make a single FACT table very messy and have a lot of duplicates which you'd have to find a way to filter out when using it in PBI.

Hope this helps,

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍