ZenotiHelp

Fact tables: Appointments, sales, and financial

ZTZenoti Docs Team Updated April 10, 2026 3 min readGuide

This article catalogs the fact tables for appointments, invoicing, collections, campaigns, gift cards, loyalty, guest activity, feedback, training, inventory, issues, and system reference.

Before you begin

  • Your organization must have Analytics — Amazon S3 enabled with data loaded.
  • Always filter WHERE void = 0 on fact tables to return only current, active records.
  • Use WID surrogate key columns to join fact tables to dimension tables in queries.

Reminder: Always filter WHERE void = 0 on fact tables to return only current, active records.

Appointments

Table Description Key measures Key dimension FKs Grain
Bi_FactAppointment Appointment-level detail with scheduling, service delivery, feedback saleprice, finalsaleprice, taxes, discount, serviceminutes centerwid, userwid, therapistwid, servicewid One row per appointment
Bi_FactAppointmentMultipleEmp Multi-employee appointment segments with commission/pricing commission, itemlistprice, servicelabourcost therapistwid, servicewid, centerwid One row per employee per appointment
Bi_FactAppointmentRedo Appointment redo tracking commissionfactor, discount originalappointmentwid, redoappointmentwid, centerwid One row per redo
Bi_FactDeletedAppointment Deleted appointment records saleprice, serviceminutes centerwid, userwid, therapistwid, servicewid One row per deleted appointment

Invoice and collections

Table Description Key measures Key dimension FKs Grain
Bi_FactInvoiceItem Line-item-level invoice detail saleprice, finalsaleprice, taxes, discount, tipamount centerwid, userwid, servicewid, productwid, itemtypewid One row per invoice line item
Bi_FactCollections Payment collection details per invoice item amount, adjustedamount, cctransactionfee centerwid, userwid, itemtypewid, orgpaymentoptionwid One row per collection
Bi_FactCashRegister Cash register closure records cashamount, creditcardtotal, adjustments centerwid, cashregisterwid One row per register closure
Bi_FactCashRegisterv2 V2 cash register closure transactions cashamount, creditcardamount centerwid, cashregisterwid One row per V2 closure
Bi_FactInvoiceitemTaxes Tax amounts per invoice item taxamount, taxpercentage invoiceitemwid, taxgroupwid One row per tax applied
Bi_FactEmployeeInvoiceItemRevenue Employee-level revenue breakdowns cashamount, membershipamount, giftcardamount employeewid, centerwid, itemtypewid One row per employee per invoice item
Bi_FactPettyCashDetails Petty cash transactions entryamount, openingbalance, closingbalance centerwid, pettycashcategorywid One row per petty cash entry
Bi_FactScheduledCollections Scheduled payment collection events expectedamount, actualamount centerwid, userwid, membershipwid One row per scheduled collection
Bi_FactInvoiceDonations Donation amounts collected donationamount centerwid, userwid, donationtypewid One row per invoice donation

Campaign and discount

Table Description Key measures Key dimension FKs Grain
Bi_FactCampaignDiscount Discount values applied through campaigns discountamount invoiceitemwid, campaignwid One row per campaign discount
Bi_FactCampaignSummary Summary metrics for campaign sends sentcount, deliveredcount, opencount, clickcount campaignwid, centerwid One row per campaign summary
Bi_FactChallenges Guest challenge participation achievementstatus userwid, challengeswid, centerwid One row per guest per challenge
Bi_FactMilestones Guest milestone achievements milestonedate userwid, milestoneswid, centerwid One row per guest per milestone

Gift card and loyalty

Table Description Key measures Key dimension FKs Grain
Bi_FactGiftCards Gift card sale and redemption saleprice, discount, tax, redemptionamount giftcardwid, centerwid, userwid One row per gift card transaction
Bi_FactLoyaltyPoints Loyalty point accrual and redemption points, pointvalue userwid, centerwid, loyaltyprogramwid One row per loyalty transaction

Guest activity

Table Description Key measures Key dimension FKs Grain
Bi_FactGuestCheckIn Guest check-in events checkintime userwid, centerwid One row per check-in
Bi_FactGuestMergeLog Guest profile merge history mergedate sourceuserwid, targetuserwid One row per merge
Bi_FactGuestPersonas Aggregated guest persona metrics visitcount, revenue userwid, centerwid One row per guest per center per year
Bi_FactGuestTierChanges Guest loyalty tier change history effectivedate userwid, fromtierwid, totierwid One row per tier change
Bi_FactTurnAways Guest turn-away events turnawayreason userwid, centerwid One row per turn-away
Bi_FactUpcomingGuestPersona Upcoming guest persona details servicepreferences userwid, centerwid One row per upcoming persona
Bi_FactUserGuestPass Guest passes issued by members passdate, expirydate userwid, inviteewid One row per guest pass

Feedback, forms, training, inventory, and issues

Table Description Key measures Key dimension FKs Grain
Bi_FactForms Form submission data fieldvalue userwid, therapistwid, centerwid, customformswid One row per form field submission
Bi_FactFeedBackV2 Feedback tag data tagvalue feedbackwid One row per feedback tag
Bi_FactRegistration Class/workshop registration saleprice, discount userwid, sessionwid, centerwid One row per registration
Bi_FactInstructorClass Instructor-to-class mappings — employeewid, trainingclasswid, centerwid One row per instructor per class
Bi_FactInventory Inventory transactions quantity, unitcost, totalcost productwid, centerwid, vendorwid One row per inventory transaction
Bi_FactIssues Issues/opportunities pricing, status userwid, centerwid, issuecategorywid One row per issue
Bi_FactAdoptionSummary Daily adoption summary metrics productsales, membershipsales centerwid One row per center per run date
Bi_FactOpportunityCustomFieldValues Custom field values for opportunities fieldvalue opportunitywid, customfieldwid One row per opportunity per field
Was this article helpful?
Your feedback helps us improve our documentation.