Dimension tables: Guests, centers, and appointments
Dimension tables provide descriptive attributes β names, codes, categories, and reference data β that give context to your measurable business data. This article covers the dimension tables for guests, user profiles, centers, locations, and appointment-related reference data in Analytics β Amazon S3.
Before you begin
- Your organization must have Analytics β Amazon S3 enabled. This is configured by Zenoti during onboarding β contact your Zenoti account representative to confirm access.
- S3 data must be loaded into your local data warehouse or BI tool before you can query these tables.
- Use surrogate key (WID) columns to join dimension tables to fact tables. The naming convention is: a fact table column named
<dimension>widjoins to the corresponding dimension table column named<dimension>wid. For example,centerwidin a fact table joins tocenterwidinBi_DimCenter.
On this page
- About dimension tables
- Guests and users
- Centers and locations
- Bi_DimCenter
- Bi_DimCenterAggr
- Bi_DimCenterHolidays
- Bi_DimCenterRoyaltyCategory
- Bi_DimCenterRoyaltySettings
- Bi_DimCenterTiming
- Bi_DimCenterWorkingHoursExceptions
- Bi_DimServiceCenter
- Bi_DimTrainingCenter
- Bi_DimOrgRoyaltyCategory
- Bi_DimUtcToCenterTime
- Bi_DimUtcToCenterTimeAggr
- Bi_DimAppointmentCategories
About dimension tables
Dimension tables provide the descriptive attributes that give context to your measurable business data. They contain names, codes, categories, settings, and reference data β everything that answers "who," "where," and "what" in a query.
All dimension tables in Analytics β Amazon S3 use surrogate keys called WIDs (Warehouse IDs). For example, centerwid in a fact table links to centerwid in Bi_DimCenter. Always join on WID columns rather than on source system IDs when building queries that span fact and dimension tables.
Note: CSV files delivered to your S3 bucket do not include column headers. Use the schema files in the
SchemaFullfolder of your S3 bucket to determine column order and data types for each table.
This article covers dimension tables for guests, user profiles, centers, locations, and appointment-related reference data.
Joining dimensions to facts
The star schema in Analytics β Amazon S3 connects fact tables to dimension tables through WID foreign keys. When writing a multi-table query, join on the WID column that corresponds to the dimension you want:
SELECT d.centername, SUM(f.saleprice)
FROM Bi_FactInvoiceItem f
JOIN Bi_DimCenter d ON f.centerwid = d.centerwid
GROUP BY d.centername
To scope results to a specific center or organization, filter by centerid or organizationid on the dimension table. To add calendar-based analysis, join date columns to Bi_DimDate. You can join to three or more dimension tables in a single query to build multi-faceted reports β for example, combining Bi_DimCenter, Bi_DimUser, and Bi_DimService to report revenue by center, provider, and service type.
Tip: Organization-level dimension tables β such as
Bi_DimCenterandBi_DimUserβ are replicated to all center and zone S3 bucket paths, so they are always available regardless of your subscription scope.
Guests and users
Bi_DimUser
Master dimension for all users (guests and employees).
| Property | Detail |
|---|---|
| Description | Master user and employee record including demographics, employment details, and organizational hierarchy |
| Grain | One row per user |
| Key columns | userwid (PK), userid, firstname, lastname, email, centerid, gender, jobname, startdate |
| Common use cases | Staff performance reporting, employee demographic analysis, org hierarchy lookups |
Note:
Bi_DimUseris one of two dimension tables populated during the initial data processing stage before data flows to your S3 bucket. It includes both guest and employee user records. UseBi_DimGuestwhen you need a guest-only view with guest-specific attributes.
Bi_DimUserAggr
Aggregate employee dimension for simplified employee-level reporting.
| Property | Detail |
|---|---|
| Description | Aggregated view of user records suitable for employee-level reporting across centers and zones |
| Grain | One row per user (aggregated) |
| Key columns | useraggrwid (PK), userid, firstname, lastname, gender, jobname |
| Common use cases | Cross-center employee reporting, zone-level staff summaries |
Bi_DimGuest
Guest profile dimension with demographic and communication preference data.
| Property | Detail |
|---|---|
| Description | Guest profile records with demographics, contact information, and communication preferences |
| Grain | One row per guest |
| Key columns | guestwid (PK), userid, firstname, lastname, email, dob, gender |
| Common use cases | Guest demographics analysis, communication targeting, retention reporting |
Bi_DimGuestCoupon
Coupon instances issued to individual guests.
| Property | Detail |
|---|---|
| Description | Records of coupons issued to guests, including redemption status and expiry |
| Grain | One row per guest coupon instance |
| Key columns | guestcouponwid (PK), couponcode, printdate, expirydate, isredeemed |
| Common use cases | Coupon redemption rates, coupon expiry analysis, promotion effectiveness |
Bi_DimGuestLoyaltyTier
Loyalty tier assignments for guests with effective date ranges.
| Property | Detail |
|---|---|
| Description | Tracks the loyalty tier assigned to each guest, including when the tier assignment started and ended |
| Grain | One row per tier assignment per guest |
| Key columns | guestloyaltytierwid (PK), userwid, tierwid, startdate, enddate |
| Common use cases | Loyalty tier movement analysis, tier-based segmentation, retention by tier |
Bi_DimUserNotes
Notes and alerts associated with guest or user records.
| Property | Detail |
|---|---|
| Description | Notes attached to user or guest profiles, including alert flags for staff visibility |
| Grain | One row per user note |
| Key columns | usernotewid (PK), userwid, notetype, alertflag |
| Common use cases | Guest preference tracking, alert analysis, service personalization reporting |
Bi_DimUserUpdateHistory
Audit history of changes to user and guest profile fields.
| Property | Detail |
|---|---|
| Description | Change history for user profile fields, recording the old and new value for each update |
| Grain | One row per profile field change |
| Key columns | userupdatehistorywid (PK), userwid, fieldname, oldvalue, newvalue |
| Common use cases | Data quality auditing, profile change tracking, compliance reporting |
Centers and locations
Bi_DimCenter
Primary center/location dimension with organizational hierarchy, timezone, and currency.
| Property | Detail |
|---|---|
| Description | Center master record including organizational hierarchy placement, operating timezone, and currency |
| Grain | One row per center |
| Key columns | centerwid (PK), centerid, centername, organizationid, timezone, currency |
| Common use cases | Center-level performance comparison, geographic analysis, currency-aware reporting |
Note:
Bi_DimCenteris one of two dimension tables populated during the initial data processing stage before data flows to your S3 bucket. It is an organization-level table replicated to all center and zone S3 bucket paths.
Bi_DimCenterAggr
Aggregate center dimension for zone-level reporting.
| Property | Detail |
|---|---|
| Description | Aggregated center dimension that includes zone assignments, suitable for zone-level roll-ups |
| Grain | One row per center (aggregated) |
| Key columns | centeraggrwid (PK), centerid, centername, organizationid, zoneid |
| Common use cases | Zone-level aggregations, regional dashboards |
Bi_DimCenterHolidays
Holiday definitions configured per center.
| Property | Detail |
|---|---|
| Description | Holiday dates defined for each center, including whether the holiday recurs annually |
| Grain | One row per center holiday |
| Key columns | centerholidaywid (PK), centerwid, holidaydate, description, isrecurring |
| Common use cases | Capacity planning, appointment availability analysis, holiday impact on revenue |
Bi_DimCenterRoyaltyCategory
Center-level royalty percentages by item type.
| Property | Detail |
|---|---|
| Description | Royalty percentage configurations assigned to item categories at the center level |
| Grain | One row per royalty category per center |
| Key columns | centerroyaltycategorywid (PK), centerwid, itemtype, royaltypercent |
| Common use cases | Franchise royalty calculations, royalty reporting by center |
Bi_DimCenterRoyaltySettings
Default royalty settings for a center.
| Property | Detail |
|---|---|
| Description | Default royalty configuration settings at the center level |
| Grain | One row per royalty setting per center |
| Key columns | centerroyaltysettingswid (PK), centerwid, itemtype, defaultpercent |
| Common use cases | Royalty baseline configuration, fallback royalty analysis |
Bi_DimCenterTiming
Operating hours for each center by day of the week.
| Property | Detail |
|---|---|
| Description | Configured operating hours for each center, broken down by day of the week |
| Grain | One row per center per day of week |
| Key columns | centertimingwid (PK), centerwid, dayofweek, opentime, closetime |
| Common use cases | Scheduling analysis, capacity utilization by time-of-day |
Bi_DimCenterWorkingHoursExceptions
Exception dates that override a center's standard operating hours.
| Property | Detail |
|---|---|
| Description | Specific dates on which a center's standard operating hours do not apply |
| Grain | One row per exception date per center |
| Key columns | centerwheexceptionwid (PK), centerwid, exceptiondate |
| Common use cases | Appointment availability analysis, scheduling edge cases |
Bi_DimServiceCenter
Service-to-center associations including center-specific pricing.
| Property | Detail |
|---|---|
| Description | Maps services to the centers where they are offered, including the price and tax group at each center |
| Grain | One row per service-center combination |
| Key columns | servicecenterwid (PK), servicewid, centerwid, price, taxgroup |
| Common use cases | Center-level pricing analysis, service availability by location |
Bi_DimTrainingCenter
Training class-to-center associations with pricing.
| Property | Detail |
|---|---|
| Description | Maps training classes to the centers where they are offered, including the price at each center |
| Grain | One row per training class-center combination |
| Key columns | trainingcenterwid (PK), trainingclasswid, centerwid, price |
| Common use cases | Training program availability analysis, class revenue by center |
Bi_DimOrgRoyaltyCategory
Organization-level royalty category definitions.
| Property | Detail |
|---|---|
| Description | Royalty category names and definitions at the organization level |
| Grain | One row per org royalty category |
| Key columns | orgroyaltycategorywid (PK), organizationid, categoryname |
| Common use cases | Org-level royalty structure reference, category-level royalty roll-ups |
Bi_DimUtcToCenterTime
UTC-to-local time offset mapping for each center.
| Property | Detail |
|---|---|
| Description | Maps each center to its UTC time offset, used to convert timestamps to local center time |
| Grain | One row per center UTC offset |
| Key columns | utctocentertimewid (PK), centerwid, utcoffset |
| Common use cases | Timezone-aware reporting, converting appointment times to local time |
Bi_DimUtcToCenterTimeAggr
Aggregate UTC-to-local time offset mapping.
| Property | Detail |
|---|---|
| Description | Aggregated version of the UTC-to-center time mapping |
| Grain | One row per center (aggregated) |
| Key columns | utctocentertimeaggrwid (PK), centerwid, utcoffset |
| Common use cases | Aggregated timezone-aware reporting across zones or the organization |
Bi_DimAppointmentCategories
Appointment category definitions.
| Property | Detail |
|---|---|
| Description | Appointment category classification used for grouping and filtering appointments |
| Grain | One row per appointment category |
| Key columns | appointmentcategorieswid (PK) |
| Common use cases | Appointment reporting by category, category-based filtering |