Analytics β Amazon S3 overview
Analytics β Amazon S3 is Zenoti's data sharing platform that exports your business intelligence data to your own Amazon S3 bucket. The system delivers a complete star schema data warehouse with dimension and fact tables, plus consolidated tables for simplified querying, enabling you to build custom reports and dashboards using your preferred BI tools.
Before you begin
- Your organization must have an active Analytics Plus license and the S3 Data Share add-on enabled by Zenoti.
- Your S3 bucket and credentials are provisioned by the Zenoti Implementation team during onboarding.
How it works
Analytics β Amazon S3 processes your Zenoti data through an automated pipeline that extracts, transforms, and loads business data into a structured data warehouse. The system then exports this data as CSV files to your Amazon S3 bucket, where your BI tools can access it for analysis and reporting.
The pipeline operates in four stages:
- SQL Server Step β Extracts core data from the Zenoti operational system and performs initial transformations.
- Redshift Dim/Fact Load β Builds dimension and fact tables in the data warehouse.
- Redshift Consolidated Tables β Creates denormalized tables by joining dimensions and facts.
- S3 Sync β Exports data as CSV files to your S3 bucket.
Data warehouse structure
The Analytics β Amazon S3 data warehouse uses a star schema design with two main table types:
- Dimension tables β Reference data that describes business entities like guests, providers, services, and centers. These tables contain descriptive attributes and use surrogate key (WID) columns for joins.
- Fact tables β Transaction data that records business events like appointments, invoices, and payments. These tables contain metrics and foreign keys that link to dimension tables.
The system maintains approximately 97 dimension tables and 57 fact tables organized by business domain (appointments, invoicing, inventory, marketing, and more).
Consolidated tables
In addition to the star schema, Analytics β Amazon S3 provides approximately 47 consolidated tables that pre-join fact and dimension data into denormalized structures. These tables flatten relationships into single tables with human-readable column names, making them easier to query without writing complex joins.
Data refresh cycles
The system performs incremental data refresh cycles every 1β2 hours, pulling only changed records from your Zenoti database. Each refresh cycle is assigned a unique GroupID that helps track data lineage.
- Full load (GroupID 1) β The initial complete export of all historical data.
- Incremental loads (GroupID 2+) β Subsequent exports containing only new or changed records.
Data scoping
Analytics β Amazon S3 supports three levels of data scoping:
- Organization level β All data across your entire Zenoti organization.
- Zone level β Data filtered to specific zones within your organization.
- Center level β Data filtered to individual centers.
Each scope level creates a separate S3 bucket with data filtered to that scope.
Schema versioning
Zenoti manages schema evolution through versioned schema files delivered to your S3 bucket:
- SchemaFull β Complete table definitions for initial setup.
- SchemaUpgrade β Versioned change scripts for new columns or tables introduced in Zenoti releases.
Zenoti uses explicit column lists in all data exports, ensuring backward compatibility. Existing columns remain in stable positions, and new columns are appended at the end.
Data quality and error handling
Every table includes ETL metadata columns (etlcreateddate, EtlLastUpdatedOn, etlcreatedby) so you can track data freshness and identify stale records. Fact tables include a void column where void = 0 indicates current records and void = 1 indicates soft-deleted records.
During data loads, the system creates a temporary folder named <groupid_processing>. Only completed GroupID folders (without the _processing suffix) should be queried.
Data segregation
Data is segregated at the organization, zone, or center level based on your subscription scope. For center-level and zone-level subscriptions, organization-level reference tables (such as campaigns, categories, and discounts) are automatically replicated into each bucket.
Key features
- Star schema data warehouse β Structured dimension and fact tables optimized for analytical queries and BI tool integration.
- Consolidated tables β Pre-joined, denormalized tables that simplify querying for common business scenarios.
- Incremental refresh β Automated data updates every 1β2 hours with change tracking via GroupID identifiers.
- CSV file format β Universal file format compatible with all major BI tools and data analysis platforms.
- Schema versioning β Managed schema evolution with upgrade scripts to handle new columns and table structures.
- Data freshness tracking β ETL metadata columns in all tables enable you to monitor when data was last updated.
- Flexible data scoping β Organization, zone, or center-level data filtering to match your access requirements.
- S3 folder organization β Structured folder hierarchy with PODName and GroupID organization for data discovery.