Forum Discussion
Data Modelling sequential business processes
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!
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:
- Hierarchical Dependency: A child must first have an intervention before getting a referral, then a referral program, and finally actions within that program.
- Fact-Dimension Structure: Ideally, fact tables store measurable events, while dimension tables provide descriptive attributes.
- 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.
4 Replies
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!
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:
- Hierarchical Dependency: A child must first have an intervention before getting a referral, then a referral program, and finally actions within that program.
- Fact-Dimension Structure: Ideally, fact tables store measurable events, while dimension tables provide descriptive attributes.
- 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.
Thank you, I think I'll go with the single FACT table and hope it works this time!
