Kotahi PBI

Background

Kotahi is a Power BI app designed to provide insights into the Kotahi CMS system. It offers an overview of client demographics, staff performance, and service utilisation.

The app currently includes three pages:

• Clients overview – Summarises key client demographics and details.
• Clients distribution(map) – Visualises client localities through a geographical graph.
• Services Overview Page – Highlights service utilisation and staff performance metrics.
• Contacts summary – Summarises contacts made
• Contacts detail – Contacts made in detail
• Activities detail – details all activities completed.
• Appointments – Appointments booked

Definitions

• Active/Inactive - If the last activity date is inside 90 days, then client is labelled as active.

Configuration

The app is currently accessible by PBI Aiga group.

Technical Information

Data sources

• Kotahi data supply

Materialized Views

kotahi.mvw_client_services

Purpose: Aggregates client service registrations and discharge information

Source Tables:

• kotahi.clientservicestatus (service enrollment status)
• kotahi.clients (client demographics)
• kotahi.services (service definitions)
• kotahi.activities (activity records)
• kotahi.users (staff information)

Key Columns:

Column Name	Description	Data Type
service_registered_name	Name of enrolled service	text
registereddate	Service enrollment date (YYYY-MM-DD)	date
dischargedate	Discharge date from exit activities	date
exitby	Staff who discharged client (Firstname Lastname)	text

Business Rules:

1. Discharges are identified by activity types 5 or 33 (configured in activitytypes)
2. Uses 90-day activity window for active/inactive status
3. Discharge dates pulled from activity EnteredDate
4. Maintains historical records through LEFT JOIN to preserve inactive enrollments

kotahi.mvw_client_summary

Purpose: Provides demographic overview and activity status for clients.

Source Tables:

• kotahi.clients (base client data)
• kotahi.ethnicities (prioritized ethnicity details)
• kotahi.activities (activity history)

Key Columns:

Column Name	Description	Data Type
prioritisedethnicity	Client's primary ethnicity	text
lastactivitydate	Most recent activity timestamp	timestamp
agegroup	Age category bracket	text
clientstatus	Active/Inactive status	text
ResidentialAddress_Suburb	Client's residential suburb	text

Business Rules:

1. Age Group Calculation:
   • Uses DOB to calculate age brackets:
     • 0-4, 5-14, 15-24, 25-34, 35-44, 45-54, 55-64, 65+
   • Returns 'Unknown' if DOB is missing
2. Activity Status:
   • Active = Last activity within 90 days
   • Inactive = No activity in past 90 days or never active
3. Data Aggregation:
   • Groups by client attributes to ensure unique client records
   • Uses LEFT JOIN to preserve clients without activities

kotahi.mvw_activities_summary

Purpose: Aggregates activity details with participant information and custom field data for reporting.

Source Tables:

• kotahi.activities (base activity records)
• kotahi.activitytypes (activity categorization)
• kotahi.services (service associations)
• kotahi.users (staff information)
• kotahi.clients (client details)
• kotahi.activityadditionalstaff (multi-staff activities)
• kotahi.activityadditionalclient (multi-client activities)

Key Columns:

Column Name	Description	Data Type
additionalstaffnames	Comma-separated list of non-primary staff	text
additionalclientnames	Comma-separated list of secondary clients	text
contactmethod	Phone/Email/In-person from JSON	text
contacttype	Classification from JSON (e.g., Follow-up)	text
duration_unit	Minutes/Hours from JSON	text

Business Rules:

1. Participant Aggregation:
   • Excludes primary activity completer from additional staff list
   • Uses array_agg with DISTINCT to prevent duplicates
2. JSON Field Handling:
   • Extracts 6 key fields from CustomFieldDataJson:
     • client_present, contact_location, notes, etc.
3. Activity Context:
   • Links to service through activitytypes → services hierarchy

Data Relationships:

graph LR A[activities] --> B[activitytypes] A --> C[users]:::completedby A --> D[clients]:::mainclient A --> E[activityadditionalstaff] E --> F[users]:::additionalstaff A --> G[activityadditionalclient] G --> H[clients]:::additionalclient B --> I[services]:::service classDef completedby fill:#e6f3ff,stroke:#0066cc; classDef mainclient fill:#e6ffe6,stroke:#009900; classDef additionalstaff fill:#fff0e6,stroke:#cc5200; classDef additionalclient fill:#ffe6ff,stroke:#990099; classDef service fill:#C0C0C0,stroke:#991199;

Refresh Logic:

• Materialized view refreshed daily via scheduled cronjob

kotahi.mv_calendar_appointments

Purpose: Provides appointment-level data for Power BI calendar and scheduling visuals.

Source Tables:

• kotahi.calendarevents – main event data
• kotahi.calendarschedules – schedule names (e.g. Whānau Ora, Psychologist)
• kotahi.calendareventclient – client associations
• kotahi.clients – client name and ID
• kotahi.users – staff information (CreatedByUser)

Key Columns:

Column Name	Description	Data Type
EventId	Unique appointment/event ID	integer
EventName	Title or type of appointment	text
ScheduleName	Calendar or service schedule label (e.g. Whānau Ora, Psychologist)	text
DateStart	Start time of appointment	timestamp
DateEnd	End time of appointment	timestamp
StaffName	Full name of staff who created the appointment	text
ClientId	Unique ID of the client	integer
ClientName	Full name of the client	text
IsConfirmed	Whether the appointment is confirmed	boolean
IsCancelled	Whether the appointment was cancelled	boolean
EventNotes	Notes or comments added to the appointment	text

Business Rules:

1. Only includes appointments where IsDeleted = false
2. Excludes events created by staff named "Alex Poor"
3. Joins calendar events to schedules, clients, and users
4. Supports filtering in Power BI by schedule name, date, staff, and client

Use Cases:

• Power BI visuals for daily/weekly appointment volume
• Staff-specific calendars
• Client service engagement timelines
• Monitoring cancellations and no-shows

Refresh Logic:

• Materialized view is refreshed nightly via cronjob to ensure up-to-date appointment data.

TODO

• Implement Hauora assessment page
• Implement group activities page