ZenotiHelp

Build common business reports

ZTZenoti Docs Team Updated November 9, 2026 6 min readReference

This article provides ready-to-use SQL query recipes for common business reports against your Analytics -- Redshift Data Share data warehouse. Each recipe includes a star schema version that joins fact and dimension tables, plus a note about the equivalent consolidated table when one is available.

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



On this page


1. Revenue by center

Description: Total revenue across all centers, ranked by highest earner. Useful for regional performance reviews and benchmarking.

Tables: Bi_FactInvoiceItem, Bi_DimCenter

SELECT
  d.CenterName,
  SUM(f.finalsaleprice)         AS total_revenue,
  COUNT(DISTINCT f.invoiceid)   AS invoice_count
FROM Bi_FactInvoiceItem f
JOIN Bi_DimCenter d ON f.centerwid = d.centerwid
WHERE f.void = 0
  AND d.void = 0
GROUP BY d.CenterName
ORDER BY total_revenue DESC;

Consolidated alternative: Use Sales_Fact_Consolidated. No joins required -- "center name" is included directly. Consolidated tables name their columns in plain words, so wrap each column name in double quotes.

SELECT
  "center name",
  SUM("total revenue") AS total_revenue
FROM Sales_Fact_Consolidated
GROUP BY "center name"
ORDER BY total_revenue DESC;

Sales_Fact_Consolidated carries several revenue measures -- for example "total revenue", "sale price", and "revenue (cash)" -- and they are not guaranteed to equal finalsaleprice in the star schema query. Check the table's column reference before you compare the two results.


2. Appointments by service

Description: Counts appointments by service and appointment status. Useful for identifying the most popular services and planning resource allocation.

Tables: Bi_FactAppointment, Bi_DimService, Bi_DimCenter, Bi_vwDimStatus_AppointmentStatus (status names)

SELECT
  c.centername,
  s.servicename,
  st.statusdisplayvalue        AS appointment_status,
  COUNT(f.factappointmentwid)  AS appointments
FROM Bi_FactAppointment f
JOIN Bi_DimService s ON f.servicewid = s.servicewid
JOIN Bi_DimCenter  c ON f.centerwid  = c.centerwid
JOIN Bi_vwDimStatus_AppointmentStatus st ON f.appstatuswid = st.dimstatuswid
WHERE f.void = 0
  AND s.void = 0
  AND c.void = 0
GROUP BY c.centername, s.servicename, st.statusdisplayvalue
ORDER BY c.centername, appointments DESC;

An appointment's status is stored as a key (appstatuswid), so the query joins the status view to show its name.

Consolidated alternative: Use appointment_fact_consolidated for a pre-joined view that includes center and service attributes.


3. Membership status summary

Description: Tracks membership status transitions -- activations, expirations, cancellations. Useful for monitoring membership health and churn.

Tables: Bi_FactMembershipStatusLog, Bi_DimMembership

SELECT
  m.membershipname,
  f.status,
  COUNT(f.factmembershipstatuslogwid) AS status_changes
FROM Bi_FactMembershipStatusLog f
JOIN Bi_DimMembership m ON f.membershipversionwid = m.membershipversionwid
WHERE f.void = 0
  AND m.void = 0
GROUP BY m.membershipname, f.status
ORDER BY m.membershipname, f.status;

The status log records each status as a numeric code in status, and it joins to the membership through membershipversionwid. For status names in plain text, use the consolidated table below.

Consolidated alternative: Use the membership consolidated table, which carries the membership name and its status as text. In the Redshift data warehouse this table is named membership_fact_consoidated -- note the spelling -- so query it by that name:

SELECT
  "membership name",
  "membership status",
  COUNT(*) AS memberships
FROM membership_fact_consoidated
GROUP BY "membership name", "membership status"
ORDER BY "membership name", "membership status";

4. Employee commission report

Description: Summarizes commission earnings per employee. Useful for performance reviews, incentive planning, and payroll reconciliation.

Tables: Bi_FactEmployeeCommission, Bi_DimUser

SELECT
  u.firstname,
  u.lastname,
  u.jobname,
  SUM(f.commission)                   AS total_commission,
  COUNT(f.factemployeecommissionwid)  AS commission_records
FROM Bi_FactEmployeeCommission f
JOIN Bi_DimUser u ON f.employeewid = u.userwid
WHERE f.void = 0
  AND u.void = 0
GROUP BY u.firstname, u.lastname, u.jobname
ORDER BY total_commission DESC;

employeewid in the commission table holds the employee's user key, so it joins to userwid in Bi_DimUser -- not to Bi_DimEmployee.

Consolidated alternative: Use empcommission_fact_consolidated. The employee's name and job name are included directly, as "employee first name", "employee last name", and "employee job name".


5. Gift card usage

Description: Tracks gift card value and redemptions. Useful for liability reporting and promotion analysis.

Tables: Bi_FactGiftCards, Bi_DimCenter

SELECT
  c.centername,
  COUNT(f.factgiftcardwid)  AS gift_card_records,
  SUM(f.redemamount)        AS redeemed_amount
FROM Bi_FactGiftCards f
JOIN Bi_DimCenter c ON f.centerwid = c.centerwid
WHERE f.void = 0
  AND c.void = 0
GROUP BY c.centername
ORDER BY redeemed_amount DESC;

Consolidated alternative: Use giftcards_fact_consolidated for figures per gift card. It carries the card's code, value, and redeemed amount directly:

SELECT
  "gift card code",
  MAX("gift card value")  AS gift_card_value,
  SUM("redeemed amount")  AS redeemed_amount
FROM Giftcards_Fact_Consolidated
GROUP BY "gift card code"
ORDER BY redeemed_amount DESC;

6. Guest visit frequency

Description: Counts visits per guest over a given period. Useful for identifying loyal guests, at-risk guests, and visit frequency trends.

Tables: Bi_FactAppointment, Bi_DimUser, Bi_vwDimStatus_AppointmentStatus (status names)

First, list the appointment status names your data uses, and pick the one that means a completed visit:

SELECT DISTINCT statusdisplayvalue
FROM Bi_vwDimStatus_AppointmentStatus
WHERE void = 0;

Then count visits per guest, replacing your completed status with that name:

SELECT
  u.firstname,
  u.lastname,
  u.email,
  COUNT(f.factappointmentwid)  AS visits,
  MAX(f.startdateincenter)     AS last_visit_date
FROM Bi_FactAppointment f
JOIN Bi_DimUser u ON f.userwid = u.userwid
JOIN Bi_vwDimStatus_AppointmentStatus st ON f.appstatuswid = st.dimstatuswid
WHERE f.void = 0
  AND u.void = 0
  AND st.statusdisplayvalue = 'your completed status'
GROUP BY u.firstname, u.lastname, u.email
ORDER BY visits DESC;

Consolidated alternative: Use appointment_fact_consolidated, which includes "guest first name", "guest last name", "guest code", and "appointment status". Group by the guest columns to get visit counts without a separate JOIN.


7. Inventory activity

Description: Inventory actions by product and action type. Useful for stock management, reorder planning, and shrinkage reporting.

Tables: Bi_FactInventory, Bi_DimProduct

SELECT
  p.productname,
  p.categoryname,
  f.actiontype,
  COUNT(f.factinventorywid)  AS actions,
  SUM(f.actionquantity)      AS quantity
FROM Bi_FactInventory f
JOIN Bi_DimProduct p ON f.productwid = p.productwid
WHERE f.void = 0
  AND p.void = 0
GROUP BY p.productname, p.categoryname, f.actiontype
ORDER BY p.categoryname, p.productname, f.actiontype;

The inventory table records several kinds of inventory action, so group by actiontype rather than adding quantities from different kinds of action together.

Consolidated alternative: Use inventory_fact_consolidated. Product name, category, and action type are included directly, as "product name", "product category", and "action type".


8. Using consolidated tables for quick reports

Consolidated tables combine fact and dimension data into a single flat structure. They are ideal for quick reports when you do not need custom joins or multi-fact-table combinations.

Consolidated tables name most of their columns in plain words with spaces -- for example "center name" -- so wrap column names in double quotes. Most consolidated tables, including all of those below, do not carry a void column, so a void = 0 filter does not apply to them.

Commonly used consolidated tables:

Consolidated Table Subject Area
Sales_Fact_Consolidated Invoice items, revenue, payments
appointment_fact_consolidated Appointments, bookings, visit data
membership_fact_consoidated (spelled this way in the data warehouse) Memberships, their status, and their dates
empcommission_fact_consolidated Employee commissions
giftcards_fact_consolidated Gift card sales and redemptions
inventory_fact_consolidated Inventory actions by product
campaign_fact_consolidated Marketing campaign metrics
Daily_Traffic_Consolidated Appointment and look-and-walk counts by date, hour, and employee

Example -- daily traffic summary:

SELECT
  "ct date",
  "center name",
  SUM("Appointment count")    AS appointments,
  SUM("Look and walk count")  AS look_and_walk
FROM Daily_Traffic_Consolidated
GROUP BY "ct date", "center name"
ORDER BY "ct date" DESC, "center name";

Choosing between star schema and consolidated tables

Scenario Recommended approach
Maximum flexibility and custom filtering Star schema joins
Simplest queries with fewer joins Consolidated tables
Combining metrics across multiple subject areas Star schema (join across multiple fact tables)
Prototyping or ad-hoc analysis Consolidated tables for speed

Both approaches use the same underlying data. Consolidated tables are pre-joined for convenience -- they do not provide additional data beyond what is available in the corresponding fact and dimension tables.


Tips for building reports

  1. Start with consolidated tables when exploring a new subject area. They let you see all available columns in one place without needing to understand the full join structure.

  2. Move to star schema joins when you need to combine data from multiple subject areas or apply advanced filtering on dimension attributes.

  3. Always filter void = 0 on fact and dimension tables to avoid counting soft-deleted records.

  4. Use WID columns for joins, not natural key GUIDs. WID columns are the designated surrogate keys for the star schema.

  5. Check data freshness before presenting reports by querying MAX(etlCreatedDate) on your primary fact table -- fact tables usually carry etlCreatedDate rather than EtlLastUpdatedOn -- or the refresh log described in Data freshness and refresh cycles. Data reflects the state as of the last data refresh cycle.

These patterns cover the most common reporting needs — start with a consolidated table, move to star-schema joins as your analysis grows, and always filter out voided records.

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