Data Modelling sequential business processes

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.