Consolidated tables reference
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.