Data Modelling sequential business processes

[scoobymoo1]

I really need some help with designing a star schema for a series of sequential 'business' process events.

I have the following clear dimensions:

• date calendar,

• children,

• interventions types,

• referral types,

• referral action types

A child can have multiple interventions, each with a start and an end date.
A child can then have multiple referrals each with a start and end date
A child may then have multiple referral programmes each with a start and end date
Each programme can have multiple referral actions which are specific to the programme type.

Each of these processes is hierarchical i.e. a child cannot have a referral programme without first having a referral and a child cannot have a referral without having first had an intervention.

Ultimately, I need to be able to find all interventions ending in a given period and show all related referral, referral programmes and referral action data. Ideally I want to show all possible programme actions and whether or not a child that has a specific referral has completed these actions. I'm not worried about the dates as I will probably use power query to resolve this.

I am struggling to model this in a star schema. I assume that each of the processes is a FACT table. I know it is bad practice to link FACT tables. I have moved on to thinking it may work to have child referral programme actions as the main fact table and each of the earlier processes as dimensions i.e

• Dim Interventions: intervention key, child, type, start, end date

• Dim Referrals: Referral key (composite of Intervention key and referral type), intervention key, referral type, start, end date,

• Dim Referral Programme: Referral Programme (composite of Referral key and referral intervention type), referral intervention key, referral intervention type, start, end date

FACT: Unique key, Child Key, date key, Referral key, referral programme key, Referral Action.

Would this work? Would I then relate to the other dimensions giving types to the FACT table or these dimensions?

The alternative approach would be to create each of the processes as a separate FACT and relate each to the appropriate dimensions and then build a dax measure to summarise all of the detail (not that I'm sure my dax is up to that!).

Are either of these viable? I have gone down a couple of rabbit holes already and found I couldn't quite filter my data correctly!

[powerbidev123 · Accepted solution]

Hi @scoobymoo1 ,

Your scenario is quite complex due to the hierarchical nature of interventions, referrals, programs, and actions. Let's break it down and explore the best approach.

Key Considerations:

1. Hierarchical Dependency: A child must first have an intervention before getting a referral, then a referral program, and finally actions within that program.
2. Fact-Dimension Structure: Ideally, fact tables store measurable events, while dimension tables provide descriptive attributes.
3. Filtering and Aggregation: You need to be able to analyze interventions and see all related referrals, programs, and actions.

---

Option 1: Single Fact Table (Fact_ReferralProgramActions)

This is the approach you proposed—having one central fact table with each previous step as a dimension.

Schema

• Fact_ReferralProgramActions (Granularity: One row per referral program action)

  • ReferralProgramActionKey (PK)
  • ChildKey
  • DateKey
  • ReferralKey
  • ReferralProgramKey
  • ReferralActionTypeKey
  • Completed (Yes/No)

• Dim_Children (Child-related data)

• Dim_Calendar (Dates for analysis)

• Dim_Interventions (InterventionKey, ChildKey, Type, Start, End)

• Dim_Referrals (ReferralKey, InterventionKey, Type, Start, End)

• Dim_ReferralPrograms (ReferralProgramKey, ReferralKey, Type, Start, End)

• Dim_ReferralActions (ReferralActionTypeKey, ReferralProgramKey, Action Name)

Pros

• Simple structure, easier filtering.
• Query performance may be better since everything is in one fact table.
• Easier to ensure all actions are linked to a specific referral and intervention.

Cons

• Could become large depending on the number of referral program actions.
• Harder to analyze interventions separately without referral data.

---

Option 2: Multiple Fact Tables (Fact_Interventions, Fact_Referrals, Fact_ReferralPrograms, Fact_ReferralActions)

This follows a more traditional approach, separating measurable business events.

Schema

• Fact_Interventions (Granularity: One row per intervention)

  • InterventionKey
  • ChildKey
  • InterventionTypeKey
  • StartDateKey
  • EndDateKey

• Fact_Referrals (Granularity: One row per referral)

  • ReferralKey
  • InterventionKey
  • ReferralTypeKey
  • StartDateKey
  • EndDateKey

• Fact_ReferralPrograms (Granularity: One row per referral program)

  • ReferralProgramKey
  • ReferralKey
  • ProgramTypeKey
  • StartDateKey
  • EndDateKey

• Fact_ReferralActions (Granularity: One row per action in a program)

  • ReferralActionKey
  • ReferralProgramKey
  • ReferralActionTypeKey
  • Completed (Yes/No)

• Common Dimensions

  • Dim_Children
  • Dim_Calendar
  • Dim_InterventionTypes
  • Dim_ReferralTypes
  • Dim_ReferralProgramTypes
  • Dim_ReferralActions

Pros

• More flexible analysis: you can analyze interventions alone, referrals alone, etc.
• Easier to track participation at each stage independently.

Cons

• More complex filtering required to ensure referrals only appear if an intervention exists.
• You may need DAX measures to relate data across multiple fact tables, which can impact performance.

---

Recommendation

• If your primary goal is tracking completion of referral actions and you always need to analyze in the full context (intervention → referral → program → action), Option 1 (Single Fact Table) is likely the better choice.
• If you need more granular analysis at each level independently, Option 2 (Multiple Fact Tables) is more flexible but requires strong DAX modeling.

[scoobymoo1]

Thank you, I think I'll go with the single FACT table and hope it works this time!

[3CloudThomas]

I suggest trying your idea and see if you get the correct measure results you are looking for. I think people on here can give you at least 3-4 variations of a design (and some experience with some things that have failed). But, until you try something, you will never know.

[scoobymoo1]

Thanks, I'll give it a go. I'm just worried about spending too much time building dud models! I've already created a couple of failures!