ZenotiHelp

Star schema relationships

ZTZenoti Docs Team Updated April 10, 2026 3 min readGuide

Navigate and query the star schema structure in Analytics β€” Amazon S3 using dimension-fact relationships and surrogate keys.

Before you begin

  • Your organization must have Analytics β€” Amazon S3 enabled and configured.
  • You need access to your organization's S3 bucket with the exported data files.
  • You should have a SQL-compatible analytics tool (such as Tableau, Power BI, or a SQL client) to query the data.
  • Basic understanding of star schema concepts (dimensions, facts, and joins) is recommended.

Steps

Understand the star schema structure

Analytics β€” Amazon S3 organizes data using a star schema design with dimension tables (Bi_Dim*) containing descriptive attributes and fact tables (Bi_Fact*) containing measurable events and transactions.

All relationships between dimension and fact tables use surrogate key columns called WIDs (Warehouse IDs). These numeric keys provide consistent joins across the entire data warehouse.

Join fact tables to dimensions using WID columns

  1. Identify the fact table containing the measures you need (for example, Bi_FactInvoiceItem for sales data).

  2. Determine which dimensions provide the descriptive attributes you want (for example, Bi_DimCenter for center names, Bi_DimUser for guest details).

  3. Join the tables using the WID naming convention:

  • Fact table column: <dimension>wid
  • Dimension table column: <dimension>wid

For example: `sql SELECT d.centername, SUM(f.saleprice) AS total_sales FROM Bi_FactInvoiceItem f JOIN Bi_DimCenter d ON f.centerwid = d.centerwid WHERE f.void = 0 GROUP BY d.centername

Use the user dimension for multiple relationship types

The Bi_DimUser dimension serves as the primary user dimension and connects to fact tables through multiple WID columns:

  • userwid β€” References the guest who received the service.
  • therapistwid β€” References the provider who performed the service.
  • createdbywid β€” References the staff member who created the record.
  • soldbywid β€” References the sales representative.

Join to the same dimension table multiple times using table aliases:

SELECT
    guest.firstname AS guest_name,
    provider.firstname AS provider_name,
    f.saleprice
FROM Bi_FactInvoiceItem f
JOIN Bi_DimUser guest ON f.userwid = guest.userwid
JOIN Bi_DimUser provider ON f.therapistwid = provider.userwid
WHERE f.void = 0

Join to the date dimension for time-based analysis

Connect fact tables to the Bi_DimDate dimension using date columns and the datewid key:

  1. Identify date columns in your fact table (such as startdateincenter, enddateincenter, creationdateincenter).

  2. Join to Bi_DimDate to access calendar attributes like month names, quarters, and fiscal periods:

`sql SELECT d.monthname, d.year, COUNT(*) AS appointment_count FROM Bi_FactAppointment f JOIN Bi_DimDate d ON f.appointmentdatewid = d.datewid WHERE f.void = 0 GROUP BY d.monthname, d.year

Use the center dimension for location-based filtering

The Bi_DimCenter dimension appears as a foreign key (centerwid) in virtually all fact tables. Use it to filter data by specific centers or analyze performance across locations:

SELECT
    c.centername,
    c.timezone,
    COUNT(a.appointmentwid) AS total_appointments
FROM Bi_FactAppointment a
JOIN Bi_DimCenter c ON a.centerwid = c.centerwid
WHERE a.void = 0
GROUP BY c.centername, c.timezone

Use consolidated tables for simplified queries

Instead of writing multi-table joins, query consolidated tables that pre-join fact and dimension data:

  1. Identify the appropriate consolidated table (for example, Sales_Fact_Consolidated).

  2. Query the consolidated table directly without joins:

`sql SELECT centername, guestfirstname, servicename, saleprice, invoicedate FROM Sales_Fact_Consolidated WHERE void = 0 AND invoicedate >= '2024-01-01'

Consolidated tables include denormalized dimension attributes with human-readable column names, eliminating the need for WID-based joins.

Tips

  • Always use WID columns for joins between dimension and fact tables β€” avoid joining on natural keys (GUIDs) for better performance.
  • The centerwid foreign key appears in almost every fact table, making it the most commonly used dimension join.
  • When joining to multiple instances of the same dimension (like different user roles), use descriptive table aliases to keep your queries readable.
  • Consolidated tables offer simpler queries but may not include all available dimensions β€” use star schema joins when you need complete flexibility.
Was this article helpful?
Your feedback helps us improve our documentation.