ZenotiHelp

Consolidated tables reference

ZTZenoti Docs Team Updated November 9, 2026 4 min readGuide

Consolidated tables are pre-joined, wide tables that combine fact data with its most commonly needed dimension attributes. They eliminate the need to write JOIN statements — you can query them directly and immediately access descriptive fields like center name, guest name, service name, and employee name alongside the numeric measures from the underlying fact table.

Before you begin

  • Your organization must have Analytics — Amazon S3 enabled with data loaded into your warehouse.
  • The consolidated tables are available alongside the standard star schema tables in your S3 bucket.

When to use consolidated tables

Consolidated tables are best suited for:

  • BI tool connections — most reporting tools (Tableau, Power BI, Looker, etc.) work well with flat, wide tables and do not require you to configure joins.
  • Ad hoc exploration — browse data quickly without needing to know which dimension tables to join.
  • Standard reports — pre-built dashboards and scheduled reports that use a fixed, well-understood set of columns.

Use the underlying fact and dimension tables directly when you need custom aggregations, non-standard join logic, or maximum query performance on very large datasets.

About the void column

Consolidated tables inherit the void column from their source fact table. Always filter WHERE void = 0 to exclude soft-deleted records.

Appointments

Table Description Source (fact + dimensions joined) Grain
appointment_fact_consolidated Complete appointment records with guest, employee, service, and center attributes Bi_FactAppointment + Bi_DimUser, Bi_DimCenter, Bi_DimService, Bi_DimDate One row per appointment
appointmentsegment_fact_consolidated Multi-employee appointment segments Bi_FactAppointmentMultipleEmp + Bi_DimUser, Bi_DimService, Bi_DimCenter One row per employee per appointment
appointmentredo_fact_consolidated Redo appointment records Bi_FactAppointmentRedo + Bi_DimCenter, Bi_DimUser One row per redo
deleted_appointments_fact_consolidated Deleted appointment records Bi_FactDeletedAppointment + Bi_DimUser, Bi_DimCenter, Bi_DimService One row per deleted appointment

Sales and collections

Table Description Source Grain
Sales_Fact_Consolidated Invoice line-item revenue with full context Bi_FactInvoiceItem + Bi_DimUser, Bi_DimCenter, Bi_DimService, Bi_DimProduct, Bi_DimDate One row per invoice item
Collections_Fact_Consolidated Payment collections Bi_FactCollections + Bi_DimUser, Bi_DimCenter, Bi_DimOrgPaymentOption One row per collection
cashregiser_fact_consolidated Cash register closures Bi_FactCashRegister + Bi_DimCenter, Bi_DimCashRegister One row per closure
cashregiserv2_fact_consolidated V2 cash register closures Bi_FactCashRegisterv2 + Bi_DimCenter, Bi_DimCashRegister One row per V2 closure
Disocount_Summery_Consolidated Discount summaries Bi_FactInvoiceItem + Bi_DimUser, Bi_DimCenter One row per discounted item
donations_fact_consolidated Invoice donations Bi_FactInvoiceDonations + Bi_DimUser, Bi_DimCenter One row per donation
pettycash_fact_consolidated Petty cash transactions Bi_FactPettyCashDetails + Bi_DimCenter One row per entry
scheduled_collections_consolidated Scheduled payment events Bi_FactScheduledCollections + Bi_DimUser, Bi_DimCenter, Bi_DimMembership One row per event

Employees

Table Description Source Grain
schedule_fact_consolidated Employee schedules Bi_FactEmployeeSchedule + Bi_DimUser, Bi_DimCenter One row per schedule entry
empcommission_fact_consolidated Employee commissions Bi_FactEmployeeCommission + Bi_DimUser, Bi_DimCenter One row per commission
empsalary_fact_consolidated Employee salary Bi_FactEmpSalary + Bi_DimUser, Bi_DimCenter One row per salary record
emphourlypay_fact_consolidated Employee hourly pay Bi_FactEmployeePay + Bi_DimUser, Bi_DimCenter One row per pay record
empdeclaretips_fact_consolidated Employee declared tips Bi_FactEmployeeDeclareTips + Bi_DimUser, Bi_DimCenter One row per declaration
employee_tips_ssg_consolidated Tips and SSG details Bi_FactEmployeeTipsAndSSGDetails + Bi_DimUser, Bi_DimCenter One row per record
employeeinvoiceitemrevenue_fact_consolidated Revenue per employee per item Bi_FactEmployeeInvoiceItemRevenue + Bi_DimUser, Bi_DimCenter One row per employee per item
employeeitemlevelcommissions_consolidated Item-level commissions Bi_FactEmployeeItemLevelCommissions + Bi_DimUser, Bi_DimCenter One row per config
multipleempcomissions_consolidated Multi-employee commissions Bi_FactMultipleEmployeeCommission + Bi_DimUser One row per commission
itemcomissiondeductions_consolidated Commission deductions Bi_FactItemCommissionDeductions + Bi_DimUser One row per deduction
Employee_Ranking_Consolidated Employee rankings Derived from multiple facts One row per ranking
Time_Card_Consolidated Time cards Bi_FactEmployeeSchedule + Bi_DimUser, Bi_DimCenter One row per time card
Aveda_Emp_Consolidated Aveda-specific employee data Custom join One row per Aveda record

Campaigns

Table Description Source Grain
campaign_fact_consolidated Campaign performance Bi_FactCampaignSummary + Bi_DimCampaign, Bi_DimCenter One row per campaign per center
campaigndiscount_fact_consolidated Campaign discounts Bi_FactCampaignDiscount + Bi_DimUser, Bi_DimCampaign, Bi_DimCenter One row per discount

Memberships

Table Description Source Grain
membership_fact_consolidated Membership status log Bi_FactMembershipStatusLog + Bi_DimUser, Bi_DimMembership, Bi_DimCenter One row per status change
membership_fact_consolidated_date Membership with date dimension Bi_FactMembershipStatusLog + Bi_DimDate One row per status change
membership_dunning_consolidated Membership dunning Bi_FactMembershipDunning + Bi_DimMembership, Bi_DimCenter One row per dunning record
membership_user_redemptions_consolidated Membership redemptions Bi_FactMembershipUserRedemptions + Bi_DimMembership, Bi_DimCenter One row per redemption

Packages, gift cards, and loyalty

Table Description Source Grain
package_user_fact_consolidated Package redemptions Bi_FactPackageUserRedemptions + Bi_DimUser, Bi_DimPackage, Bi_DimCenter One row per redemption
package_value_fact_consolidated Package values Bi_FactPackageValue + Bi_DimPackage, Bi_DimCenter One row per value record
giftcards_fact_consolidated Gift card transactions Bi_FactGiftCards + Bi_DimUser, Bi_DimGiftCard, Bi_DimCenter One row per transaction
loyaltypoints_fact_consolidated Loyalty point transactions Bi_FactLoyaltyPoints + Bi_DimUser, Bi_DimCenter One row per transaction

Other

Table Description Source Grain
turnaway_fact_consolidated Turn-away events Bi_FactTurnAways + Bi_DimUser, Bi_DimCenter One row per turn-away
Bi_DimUser_Consolidated Enriched user profile dimension Bi_DimUser + related dims One row per user
Daily_Traffic_Consolidated Daily center traffic Aggregated from multiple facts One row per center per day
registration_fact_consolidated Training registrations Bi_FactRegistration + Bi_DimUser, Bi_DimCenter One row per registration
inventory_fact_consolidated Inventory transactions Bi_FactInventory + Bi_DimProduct, Bi_DimCenter One row per transaction
issues_fact_consolidated Issues/opportunities Bi_FactIssues + Bi_DimUser, Bi_DimCenter One row per issue
forms_fact_consolidated Form submissions Bi_FactForms + Bi_DimCustomForms, Bi_DimCenter One row per submission
opportunities_fact_consolidated Sales opportunities Bi_FactIssues + Bi_DimUser, Bi_DimCenter One row per opportunity
sales_opportunities_consolidated Sales opportunity pipeline Derived from Bi_FactIssues One row per opportunity
adoption_fact_consolidated Adoption metrics Bi_FactAdoptionSummary + Bi_DimCenter One row per record
Monthly_Finance_Summery_Consolidated Monthly financial summary Aggregated from invoices + collections One row per center per month
Register_Summery_Consolidated Register closure summary Bi_FactCashRegister + Bi_DimCenter One row per summary

Querying a consolidated table

Because consolidated tables include dimension attributes inline, no JOIN is required:

SELECT
    centername,
    servicename,
    guestname,
    finalsaleprice,
    invoicedate
FROM Sales_Fact_Consolidated
WHERE void = 0
  AND invoicedate >= '2025-01-01'
ORDER BY invoicedate DESC;

Always include WHERE void = 0 to restrict results to current, active records.

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