ZenotiHelp

Understand the star schema and data model

ZTZenoti Docs Team Updated November 9, 2026 4 min readGuide

Analytics β€” Amazon S3 delivers data in a star schema design. Understanding this structure helps you write efficient queries and decide when to use dimension and fact tables versus consolidated tables.

Before you begin

What is a star schema

A star schema organizes data into two types of tables:

  • Fact tables sit at the center and record measurable business events β€” invoices, appointments, payments, and other transactions. They contain numeric metrics and date references.
  • Dimension tables surround the facts and provide descriptive context β€” who the guest was, which center processed the transaction, what service was performed.

The resulting diagram resembles a star: one fact table in the middle, multiple dimension tables radiating outward. All joins between facts and dimensions use a common surrogate key called the WID (Warehouse ID).

Dimension tables

Dimension tables carry the prefix Bi_Dim*. There are approximately 97 dimension tables in total.

What they contain:

  • Descriptive attributes β€” names, codes, categories, statuses, and configuration data.
  • A BIGINT surrogate key column (*WID) as the primary key, used for all joins.
  • The original natural key (GUID or source ID) preserved for cross-referencing with other systems.

How they work: When a record changes in the source system (for example, a guest updates their contact information), the updated record replaces the existing row in the dimension table. New records are inserted. This update-insert mechanism keeps dimension data current.

Key dimension tables

Table Description
Bi_DimUser Guests and employees β€” unified profile table covering all person types
Bi_DimCenter Centers (locations) including address, zone, and organizational hierarchy
Bi_DimDate Calendar dimension with date attributes for date-based analysis
Bi_DimService Services offered, including categories and pricing attributes
Bi_DimProduct Retail products including categories, brands, and inventory attributes
Bi_DimEmployee Employee dimension with job, department, and compensation details
Bi_DimMembership Membership version definitions with pricing, billing, and benefits

Fact tables

Fact tables carry the prefix Bi_Fact*. There are approximately 57 fact tables in total.

What they contain:

  • Measurable numeric values β€” sale amounts, quantities, durations, counts.
  • Date and time references.
  • Foreign key WID columns that link to dimension tables.
  • ETL metadata columns β€” etlcreateddate, EtlLastUpdatedOn, etlcreatedby.
  • A void column indicating the current state of the record.

The void column: Fact tables use a delete-insert mechanism for incremental updates:

void value Meaning
0 Current, active record β€” include this in your queries
1 Soft-deleted record β€” exclude this from your queries

When filtering fact tables, always include WHERE void = 0 to exclude stale records.

WID β€” the surrogate key

WID (Warehouse ID) is a BIGINT auto-incrementing surrogate key assigned to every row in every dimension table. All joins between fact and dimension tables use WID columns.

Naming convention:

  • In a dimension table, the primary key column is named <dimension>wid (for example, centerwid in Bi_DimCenter).
  • In a fact table, the foreign key column that references a dimension uses the same name: centerwid links to Bi_DimCenter.centerwid.

Natural keys are preserved: Every dimension table also retains the original GUID or source ID from the Zenoti operational system. Use natural keys when you need to cross-reference data with other Zenoti integrations or external systems.

Consolidated tables

Consolidated tables are pre-joined combinations of fact and dimension data. There are approximately 47 consolidated tables in total.

When to use them:

  • When you want to avoid writing JOIN statements.
  • When you are connecting a BI tool that works best with flat, wide tables.
  • When you are exploring data and want to see all relevant attributes in one place.

When to use dimension and fact tables directly:

  • When you need to build custom metrics or aggregations that the consolidated tables do not cover.
  • When you need full control over filter conditions and join logic.
  • When query performance on large datasets requires targeted joins.

Writing a join query

To join a fact table to a dimension table, match the WID column name:

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
ORDER BY total_sales DESC;

Key points from this example:

  • The join condition f.centerwid = d.centerwid uses the shared WID column name.
  • WHERE f.void = 0 filters to active records only.
  • All descriptive attributes (like centername) come from the dimension table.

Business domains

Analytics β€” Amazon S3 covers data across all major areas of the Zenoti platform:

Domain Representative tables
Guests Bi_DimUser, Bi_DimGuest, Bi_FactGuestPersonas
Centers Bi_DimCenter, Bi_DimCenterTiming
Employees Bi_DimEmployee, Bi_FactEmployeeSchedule, Bi_FactEmployeeCommission
Services Bi_DimService, Bi_FactAppointment
Products Bi_DimProduct, Bi_FactInventory
Invoicing Bi_FactInvoiceItem, Bi_FactCollections
Memberships Bi_DimMembership, Bi_FactMembershipStatusLog
Packages Bi_DimPackage, Bi_FactPackageUserRedemptions
Gift cards Bi_DimGiftCard, Bi_FactGiftCards
Campaigns Bi_DimCampaign, Bi_FactCampaignSummary
Issues Bi_FactIssues
Loyalty Bi_FactLoyaltyPoints
Training Bi_FactRegistration
Inventory Bi_DimProduct, Bi_FactInventory

For a complete list of all available dimension, fact, and consolidated tables, refer to the table catalog in the SchemaFull folder of your S3 bucket.

Was this article helpful?
Your feedback helps us improve our documentation.