ZenotiHelp

Redshift data warehouse

ZTZenoti Docs Team Updated April 10, 2026 2 min readGuide

Access and query your organization's data using the Redshift data warehouse star schema architecture with consolidated tables for simplified analysis.

Before you begin

  • Your organization must have Analytics β€” Amazon S3 enabled and configured.
  • You should be familiar with SQL querying and basic data warehouse concepts.
  • Your Zenoti Implementation team must have completed the initial data warehouse setup.

Steps

Access the star schema architecture

The Redshift data warehouse uses a star schema design with fact tables at the center and dimension tables providing descriptive context.

  1. Identify the table types:
  • Dimension tables β€” Named with the Bi_Dim prefix (e.g., Bi_DimCenter, Bi_DimUser, Bi_DimService).
  • Fact tables β€” Named with the Bi_Fact prefix (e.g., Bi_FactInvoiceItem, Bi_FactAppointment).
  • Consolidated tables β€” Pre-joined tables for simplified querying.

Query using fact and dimension tables

For analysis requiring multiple data points, join fact tables with dimension tables using surrogate key (WID) columns.

  1. Identify the fact table containing your target measures (e.g., sales amounts, appointment counts).
  2. Identify the dimension tables containing descriptive attributes you need.
  3. Write your query joining tables via WID columns:

`sql 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

  1. For date-based analysis, join date columns to Bi_DimDate:

`sql SELECT dd.year, dd.monthname, COUNT(*) AS appointment_count FROM Bi_FactAppointment f JOIN Bi_DimDate dd ON f.appointmentdatewid = dd.datewid WHERE f.void = 0 GROUP BY dd.year, dd.monthname

Use consolidated tables for simplified analysis

For routine reporting, query consolidated tables that combine fact and dimension data without requiring joins.

  1. Identify the appropriate consolidated table for your analysis (e.g., Sales_Fact_Consolidated, appointment_fact_consolidated).
  2. Query the consolidated table directly:

`sql SELECT centername, servicename, SUM(saleprice) AS total_sales FROM Sales_Fact_Consolidated WHERE void = 0 AND appointmentdate >= '2024-01-01' GROUP BY centername, servicename

  1. Filter using the denormalized dimension attributes already included in the table.

Filter data by organization and center

All tables include tenant identification columns for multi-organization deployments:

  • Use the groupid column to identify the data load cycle.
  • Use the PodName column to identify the source pod in multi-tenant deployments.

Access filtered dimension views

Use Redshift views for simplified access to dimension status codes and lookup values:

  • Bi_vwDimStatus_AppointmentStatus β€” Appointment status codes.
  • Bi_vwDimStatus_PaymentType β€” Payment method types.
  • Bi_vwDimStatus_MembershipStatus β€” Membership status codes.
SELECT
    status.statusname,
    COUNT(*) AS appointment_count
FROM Bi_FactAppointment f
JOIN Bi_vwDimStatus_AppointmentStatus status
    ON f.appointmentstatuswid = status.statuswid
WHERE f.void = 0
GROUP BY status.statusname

Tips

  • Start with consolidated tables β€” They eliminate complex joins and provide faster query development for standard reporting needs.
  • Use dimension views for status filtering β€” The filtered views provide cleaner access to status codes.
  • Use the date dimension β€” Bi_DimDate includes pre-calculated fields like fiscal quarters, weekdays, and holiday flags for time-based analysis.
  • Monitor query performance β€” Large fact tables may require filtering by date ranges or specific centers to optimize performance.
Was this article helpful?
Your feedback helps us improve our documentation.