Database Properties
Database Type: Redshift - 8.0.2
Schema preview
PerfectGym Next Enterprise Data Warehouse
This read-only schema provides selected business relevant data of PerfectGym Next in an unaggregated per-event grain in the form of multiple fact and dimension tables (star/galaxy/fact-constellation schema).
It’s targeted to use for analytical purposes in BI tools like PowerBI, time uncritical direct queries or as source for a custom pipeline, where data can be further enriched or prepared for fast access.
Authentication
- Connection is restricted to a static IP address we need from you
- A single
USER&PASSWORD+HOSTare provided - User has access to a single database of the same name
Connection
Please fill in the arguments, as provided by us.
Via odbc see aws documentation
Driver={Amazon Redshift (x64)};Server=<HOST>;Database=<USER>;UID=<USER>;PWD=<PASSWORD>;Port=5439
Via jdbc see aws documentation
jdbc:redshift://<HOST>:5439/<USER>
Updates
Tables are synced incrementally (inserts/updates/deletes) every ~5 minutes
Non-Breaking Changes
- New tables are announced in the Changelog on the same date they are added
- New columns on existing tables are announced in the Changelog on the same date they are added
Breaking Changes
- Tables are marked as deprecated in their documentation before they are deleted, to allow users to adapt beforehand
- Columns are marked as deprecated in their documentation before they are deleted, to allow users to adapt beforehand
- Deprecations are announced in the Changelog on the same date
Anonymization / Access Level
Generally all data-privacy settings from the source System, like anonymizing checkins after a certain time period, are also applied on this Data Warehouse.
Additionally, there are 3 different types of access level we provide:
- Owner: Client that owns the whole tenant in the source System. All data is provided.
- Franchisee: Client that owns a subset of the tenants studios in the source System. Only fact data of selected studios is provided. Dimensional data of other studios can appear but is anonymized.
- Franchise System: Client that owns the whole tenant in the source System but doesn’t operate studios directly. All fact data is provided but anonymized.
Your accounts access level will be defined during setup with your account manager.
Best Practices
Using Views
To ensure our ETL process can properly refresh the underlying tables, it is critical that you create views using the WITH NO SCHEMA BINDING option.
This type of view, also known as a late-binding view, does not create a dependency between the view and the underlying tables. This allows us to drop and recreate tables without affecting your views.
To create a late-binding view in Redshift, use the following syntax:
CREATE VIEW public.your_view_name
AS
SELECT
column1,
column2,
...
FROM
perfectgym_next.dim_or_fact_table_name
WITH NO SCHEMA BINDING;
By adhering to this practice, you will prevent issues with our data refresh process and ensure the continuous availability of your data.
Contact
- For feedback and issues, please contact your account manager
Tables
| Table / View | Children | Parents | Columns | Type | Comments |
|---|---|---|---|---|---|
| fct_trainer_class_appointments | 0 | 7 | 9 | Table | DEPRECATED. Use fct_trainer_appointment instead. Stores all appointments for trainers |
| dim_inclusive_contingent | 3 | 0 | 8 | Table | A contingent defining how often a service can be used, that is included in a contract (rate) of a customer. |
| fct_payment_run | 0 | 5 | 12 | Table | This table contains all payment run bookings together with information about the payment run itself. A payment run is a scheduled event where a financial institution attempts to collect money from the customers. It consists of individual payment run bookings, those that are regular, those that failed and those that were already excluded before and therefore technically do not represent actually processed bookings |
| dim_daytime | 0 | 0 | 6 | Table | This table contains all times of a day from |
| dim_payment_run_property | 1 | 0 | 6 | Table | A set of basic payment run booking properties |
| dim_idle_period_property | 1 | 1 | 9 | Table | Additional dimensional information about an idle period. A junk dimension containing the properties that describe how an idle period was configured and what its effects are on the customer’s contract and gym access. |
| dim_closing_hours | 0 | 1 | 9 | Table | Irregular or recurring closure times for an organization unit (gym). Defines exceptions to the regular opening hours, including one-time closures and yearly recurring closures (holidays, maintenance, etc.). YEARLY entries are expanded into specific datetime ranges based on creation/archival dates. |
| dim_employee | 5 | 1 | 17 | Table | Table contains all employees and their relevant information |
| dim_contract_voucher_definition | 2 | 3 | 21 | Table | A definition of a voucher that can be generated into voucher codes and applied to a contract to get a discount or credit. Vouchers can be used to grant discounts on contracts, waive fees, or provide credit. |
| fct_revenue_cash_based | 0 | 6 | 28 | Table | This table records actual cash inflows related to revenue, providing a cash-basis accounting view. It captures when cash was physically received, including payment method details and electronic payment transaction information. |
| dim_purchased_contingent | 3 | 0 | 7 | Table | A contingent defining how often a service can be used, that is paid for (not included in a customer’s contract). |
| dim_revenue_group | 2 | 0 | 4 | Table | A hierarchical categorization of revenues into a broad main_group and a finer grained sub_group. |
| fct_idle_period | 0 | 8 | 11 | Table | A timeframe in which a customer does not have to pay for their contract(s). Includes member idle periods, contract idle periods, trial periods, and bonus periods. |
| dim_date | 27 | 0 | 16 | Table | Date dimension covering years 1900-2099 |
| dim_contract_payment_frequency | 1 | 0 | 7 | Table | Defines how often and how much is paid for an individual contract. |
| dim_contract_term | 2 | 0 | 4 | Table | The term of the contract. |
| fct_employee_access | 0 | 2 | 6 | Table | Role based access control information showing which employee has access to which organization unit (studio) through which role. This table represents the access permissions configured in the ERP system. |
| bridge_organization_unit_to_organization_unit_code | 0 | 2 | 4 | Table | Link organization unit (gym) to all its organization unit codes (tags). |
| dim_device | 1 | 1 | 6 | Table | A device connected to the system, which enables registering customers using certain areas (the whole gym, the gym sauna) or machines (eg a massage bed) of a gym. |
| dim_class_event_property | 1 | 0 | 5 | Table | The table contains simple properties of class events. This is a junk dimension that stores distinct combinations of event status, source, and booked_for_free flag. |
| dim_trainer_appointment_status | 1 | 0 | 3 | Table | Lookup table for status values of trainer assignments to appointments |
| dim_customer_code | 1 | 0 | 7 | Table | Dimension table containing customer codes (tags) used to categorize customers. Customer codes help organize and filter customers based on business-defined categories. Each customer code has a unique identifier and can be associated with multiple customers. |
| dim_rate_term_configuration | 0 | 1 | 20 | Table | Configuration of contract terms, extensions, cancellations and idle periods for a rate. This table details the rules governing the lifecycle of a contract associated with a specific rate, including initial duration, renewal policies, cancellation notice periods, and allowances for pausing the contract (idle periods). |
| dim_rate_localized | 0 | 1 | 6 | Table | The localized version of a rate, defining translated information of a rate per locale. One rate can have multiple localized versions. |
| fct_contract_voucher | 1 | 4 | 22 | Table | A voucher code that can be applied to a contract to get a discount or credit. Only includes vouchers that have been redeemed or withdrawn. Represents the actual usage of a voucher. |
| dim_campaign | 3 | 0 | 7 | Table | Campaign dimension |
| dim_organization_unit | 25 | 0 | 15 | Table | A gym. The physical location at which people go to work out. But also the abstract entity to which customers sign up to. |
| dim_cancellation_reason_localized | 0 | 1 | 5 | Table | The localized version of a cancellation reason, defining translated information of a cancellation reason per locale. One cancellation reason can have multiple localized versions. |
| fct_customer_studio_history | 0 | 4 | 6 | Table | Tracks the history of customers’ associations with studios over time, including start and end dates of these relationships. |
| dim_contract_cancellation_reason | 1 | 1 | 8 | Table | Contract cancellations with their reasons. Built from actual contract cancellations that occurred in the system. Includes information about how the cancellation was initiated, the type of cancellation, and the reason. |
| dim_company_code | 1 | 0 | 5 | Table | Company codes (tags) that can be assigned to companies for categorization and filtering purposes. Similar to customer codes but for partner companies. |
| dim_contract_cancellation_property | 1 | 0 | 4 | Table | DEPRECATED. Use
|
| dim_opening_hours | 0 | 1 | 7 | Table | General opening hours for organization units (gyms). Defines the regular operating hours for each gym by day of week in a range. |
| fct_customer_appointment | 0 | 7 | 16 | Table | An appointment (class, trial session or personal training) by the customer(s) taking part in it. Note: There can be multiple customers per appointment, leading to multiple entries of the same |
| dim_cancellation_reason | 3 | 0 | 5 | Table | DEPRECATED. Use
|
| bridge_company_to_company_code | 0 | 2 | 4 | Table | Bridge table linking companies to their assigned company codes (tags). A company can have multiple codes and a code can be assigned to multiple companies. |
| bridge_customer_to_customer_code | 0 | 2 | 4 | Table | Link customer to all his customer codes. |
| dim_organization_unit_code | 1 | 0 | 4 | Table | A tag/code assigned to organization units (gyms) to group them. |
| dim_contract_voucher_rate_discount_period | 0 | 3 | 12 | Table | A period during which a discount is applied for a certain rate in the context of a contract voucher. This defines the discount structure (e.g., 100% off for the first 3 months). |
| fct_checkin | 0 | 6 | 11 | Table | The event of a customer visiting a gym (organization_unit) owned by you. |
| dim_company | 5 | 0 | 35 | Table | A ‘partner company’ that contracts can be related to for the Corporate Fitness program. Only includes companies that are currently whitelisted for the studios related to this DWH instance. |
| fct_contract_cancellation | 0 | 9 | 12 | Table | Contract cancellation fact table |
| dim_class | 3 | 0 | 9 | Table | A class that a customer can participate in, e.g. yoga, dance class, etc |
| dim_product | 2 | 0 | 8 | Table | An article that is sold physically or virtually. It is either defined with multiple attributes beforehand or just with a name being specified ad hoc when a sale is made (further referenced as dynamic product). |
| fct_lead_lifetime | 0 | 3 | 13 | Table | Representation of the lead conversion process of every customer (PROSPECT/MEMBER/FORMER_MEMBER), by the start and end date of the conversion/loss. Sourced by both Lead Management section and a fallback logic (see |
| fct_trainer_appointment | 0 | 4 | 17 | Table | An appointment (class, trial session or personal training) by the trainer(s) leading it. Note: There can be multiple trainers per appointment, leading to multiple entries of the same |
| dim_service | 3 | 0 | 10 | Table | A general service (a class, a personal training, the replacement of a member card…), that can be provided to a customer. The service could be included in the customers contract(s), with a limiting contingent (eg when its a Sauna subscription) or be booked individually (eg when its a class). |
| dim_currency | 5 | 0 | 5 | Table | All available currencies |
| fct_contract | 7 | 17 | 27 | Table | The contract between a customer and a gym (organization_unit). Includes different contract_types (CONTRACT, MODULE, FLAT_FEE see |
| dim_customer_custom_field | 0 | 1 | 8 | Table | Custom field values assigned to customers on lead or member level. |
| dim_customer_communication_consent | 0 | 2 | 14 | Table | Dimension table that tracks customer communication consent preferences across different message categories. This table combines customer-level and organization-level communication configurations, with customer-level settings taking priority over organization-level defaults. |
| fct_contract_price_history | 0 | 3 | 18 | Table | The new price and price change information whenever the price of a contract is adjusted. The current_price of a contract can be built with condition |
| fct_service_usage | 0 | 6 | 12 | Table | The individual usage event of a service by a customer at a studio (organization_unit). |
| dim_contract_property | 1 | 0 | 9 | Table | A set of basic contract properties, e.g whether or not contract is signed, via which service it was created, is contract disabled, which payment type was used for the contract, etc. |
| dim_rate | 8 | 0 | 6 | Table | A standard tariff a contract is created with. |
| fct_revenue | 0 | 8 | 16 | Table | The event of an accrued income (positive/negative) of contracts, sales and everything else. Nightly updated with entries of the past day. NOTE: multi-purpose vouchers (de: ‘Wertgutscheine’) are only considered once they are used or expired. |
| dim_appointment | 6 | 0 | 9 | Table | The unique event of an appointment on the calendar with its properties. |
| bridge_company_to_organization_unit | 0 | 2 | 8 | Table | Bridge table linking companies to organization units (gyms) they are whitelisted for. Represents access permissions for gyms on companies. |
| fct_contract_term_dates | 0 | 3 | 6 | Table | Contract term dates fact table |
| fct_class_appointment | 0 | 6 | 14 | Table | Class appointment fact table |
| fct_class_event | 0 | 10 | 13 | Table | Stores all customer events for class appointments such as booking or cancellations |
| dim_customer | 17 | 0 | 30 | Table | A customer is a person who signed up to a studio owned by you or your franchise. |