Understand the star schema and data model
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
- Your organization must have Analytics β Amazon S3 enabled and configured.
- Review the Analytics β Amazon S3 overview before diving into the data model.
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
voidcolumn 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,centerwidinBi_DimCenter). - In a fact table, the foreign key column that references a dimension uses the same name:
centerwidlinks toBi_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.centerwiduses the shared WID column name. WHERE f.void = 0filters 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.