Redshift data warehouse
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.
- Identify the table types:
- Dimension tables β Named with the
Bi_Dimprefix (e.g.,Bi_DimCenter,Bi_DimUser,Bi_DimService). - Fact tables β Named with the
Bi_Factprefix (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.
- Identify the fact table containing your target measures (e.g., sales amounts, appointment counts).
- Identify the dimension tables containing descriptive attributes you need.
- 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
- 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.
- Identify the appropriate consolidated table for your analysis (e.g.,
Sales_Fact_Consolidated,appointment_fact_consolidated). - 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
- 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
groupidcolumn to identify the data load cycle. - Use the
PodNamecolumn 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_DimDateincludes 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.