Skip to content

Legacy Changelog

RSS feed

Subscribe to schema changes via the RSS feed.

All changes done on the content or schema of the PerfectGym Next Enterprise Data Warehouse are described in the following list, beginning November 2023.

2026

2025

2024

2023

2026-10-21

Summary

Contract cancellation records can now be loaded when the contract end date before cancellation is unavailable.

Changed

  • fct_contract_cancellation.pre_cancellation_end_date_id accepts NULL values.

2026-01-15

Summary

Introduced the new erp_v2 schema with near real-time data processing capabilities, replacing the legacy schema. The migration is currently underway and will be rolled out to all customers throughout Q1 2026.

Deprecated

  • Legacy schema is deprecated and will be removed after a transition period. Use schema erp_v2 instead.

2025-10-30

Summary

Add customer_studio_history fact table.

Added

  • Add new table fct_customer_studio_history to track customers moving to different studios.

2025-10-23

Summary

Add cash-based revenue fact table.

Added

  • Add new table fct_revenue_cash_based to provide a cash-basis accounting view of revenue.

2025-10-22

Summary

Enhanced customer custom fields to include member level assignments.

Changed

  • Enhanced dim_customer_custom_field to include custom field values from member profiles in addition to lead questionnaires.
  • Add new column dim_customer_custom_field.level to distinguish between custom fields captured during lead acquisition (LEAD) and custom fields assigned to active members (MEMBER).

2025-10-14

Summary

Add employee access and username information to employees.

Added

  • Add new column dim_employee.username to store the username of employees in the ERP system.
  • Add new table fct_employee_access to track which employees have access to which studios through which roles, enabling analysis of role-based access control permissions.

2025-10-08

Summary

Add localized cancellation reasons and customer custom fields.

Added

  • Add new table dim_customer_custom_field to store custom field values assigned to customers through additional fields.
  • Add new table dim_cancellation_reason_localized to store translated cancellation reason names for different locales, enabling users to view cancellation reasons in their preferred language.
  • Add new column dim_contract_cancellation_reason.cancellation_reason_id to link to the localized cancellation reasons.

2025-09-30

Summary

Add latitude, longitude, opening hours and closing hours information about dim_organization_unit for geospatial analysis and effective opening hours tracking.

Added

  • Add new column dim_organization_unit.latitude to store the latitude coordinate of gym locations in decimal degrees (WGS84).
  • Add new column dim_organization_unit.longitude to store the longitude coordinate of gym locations in decimal degrees (WGS84).
  • Add new table dim_opening_hours to store the general opening hours for organization units by day of week.
  • Add new table dim_closing_hours to store irregular or recurring closure times for organization units that are exceptions to regular opening hours.

2025-09-18

Summary

Add organization unit code dimension and bridge.

Added

  • Add new table dim_organization_unit_code for codes/tags assigned to organization units (studios).
  • Add new table bridge_organization_unit_to_organization_unit_code to link studios to their codes.

2025-08-27

Summary

Add main_contract_id relation to fct_contract to link module/flat-fee contracts to their main contract.

Added

  • Add new column fct_contract.main_contract_id to link module/flat-fee contracts to their main contract.

2025-08-26

Summary

Add fct_service_usage table to track usage of various services by customers. (Already available since 2025-08-13).

Added

  • Add new table fct_service_usage to track usage of services by customers.

2025-08-25

Summary

Add columns to refer to third party systems.

Added

  • Add new column dim_customer.third_party_id to refer to a customer in a third party system
  • Add new column fct_contract.third_party_id to refer to a contract in a third party system

2025-05-08

Summary

Add new column to fct_contract

Added

  • Add new column fct_contract_price_history.last_date_before_contract_renewal to store date of last possible cancellation of contract

2025-05-07

Summary

Add documentation on how to create late-binding views.

2025-04-23

Summary

Add dim_rate_localized table to provide localized public rate names.

Added

  • Add new table dim_rate_localized to provide localized public rate names.

2025-04-22

Summary

Add new columns to fct_contract_price_history

Added

  • Add new column fct_contract_price_history.company_price to store the part of the cost paid by the company
  • Add new column fct_contract_price_history.customer_price to store the part of the cost paid by the customer
  • Add new column fct_contract_price_history.company_price_change to store the change from the previous company price
  • Add new column fct_contract_price_history.customer_price_change to store the change from the previous customer price

2025-02-13

Summary

Add campaigns to fct_lead_lifetime.

Added

  • Add new column fct_lead_lifetime.campaign_id to enable tracking of leads by campaign
  • Add new table dim_campaign to store campaign information

Deprecated

  • Deprecate fct_lead_lifetime.source_campaign with mixed content in favor of fct_lead_lifetime.campaign_id

2025-01-20

Summary

Fix fct_revenue not including payment abandonments (CHARGES_SALES>ADDITIONAL_CHARGE) that have been reversed.

2025-01-09

Summary

Include whitelisted companies in dim_company table.

Misc

  • Adapted dim_company table to include companies that are whitelisted for the studios related to this DWH instance.

2025-01-07

Summary

Improvements on fct_contract table regarding is_reversed and company_id columns.

Added

  • New column fct_contract.is_reversed moved from dim_contract_property.disabled for better visibility

Misc

  • Adapted docs on fct_contract and dim_contract_property.disabled
  • Adapted fct_contract.company_id to also include companies associated using contract vouchers

Deprecated

  • Deprecate column dim_contract_property.disabled in favor of fct_contract.is_reversed

2024-10-10

Summary

Adding new table fct_contract_term_dates

2024-10-04

Summary

Adding adjustment_type to fct_revenue

Added

  • Add new column fct_revenue.adjustment_type to determine adjustment or discount

2024-09-12

Summary

Add imported_contract_number to fct_contract

Added

  • Add new column imported_contract_number to fct_contract table, to identify contracts imported from a previous system

2024-09-06

Summary

Adding description to fct_revenue

Added

  • Add new column fct_revenue.description to describe the revenue entry

2024-08-29

Summary

Add dim_customer_communication_consent table.

Added

  • Add new table dim_customer_communication_consent to enable querying categories and channels over which communication to a customer is allowed.

2024-08-24

Summary

Adapt various references and docs to be more agnostic of the source system data is coming from.

Added

  • dim_appointment.source: WEBCLIENT
  • dim_contract_property.sales_source: WEBCLIENT
  • dim_customer.customer_origin: WEBCLIENT
  • fct_customer_appointment.booking_source: WEBCLIENT

2024-07-31

Summary

Fix certain entries in fct_revenue mapping to UNCATEGORIZED/UNKNOWN dim_revenue_group.

2024-07-29

Summary

Add contract price history and current price.

Added

  • Add new table fct_contract_price_history to enable analytics on contract prices, price changes and the reasons leading to those changes
  • Add new column fct_contract.current_monthly_price to enable easy access to the current (adjusted) normalized price of a contract
  • Add new column dim_contract_payment_frequency.payment_frequency_current_price to store the current (adjusted) price that will be booked at related frequency

2024-06-18

Summary

Add contract relation to fct_idle_period and email verification status to dim_customer.

Added

  • Add new column fct_idle_period.contract_id to enable uniquely relating to idle periods to contracts
  • Add new column dim_customer.email_verification_status to identify email addresses that are verified or unverified

2024-05-06

Summary

Add service period and direct contract relation to fct_revenue.

Added

  • Add new column fct_revenue.service_period_start_date to get the service period revenue is generated for
  • Add new column fct_revenue.service_period_end_date to get the service period revenue is generated for
  • Add new column fct_revenue.contract_id to enable uniquely relating to the contract generating revenue (Fct to Fct join possible here due to n:1 relation)

2024-04-26

Summary

Add studio_prefix and imported_customer_number.

Added

  • Add new column dim_organization_unit.studio_prefix as a human-readable identifier of a studio
  • Add new column dim_customer.imported_customer_number to identify customers imported from a previous system

2024-04-09

Summary

Add ways to identify customers and contracts by customer codes and Corporate Fitness companies.

Added

  • Add new table dim_customer_code for codes that are set on customers
  • Add new table bridge_customer_to_customer_code to link between customers and customer codes
  • Add new table dim_company for companies of Corporate Fitness program
  • Add new column fct_contract.company_id to link between contracts and companies

2024-04-05

Summary

Fix fct_contract.rate_id missing on MODULE/FLAT_FEE contracts

2024-03-11

Summary

Cover essential lead conversion metrics.

Added

  • Add new table fct_lead_lifetime

2024-03-08

Summary

Add customer_origin to dim_customer

Added

  • Add new column customer_origin to dim_customer table

2024-03-06

Summary

Add locale to dim_customer

Added

  • Add new column locale to dim_customer table

2024-02-22

Summary

Add payment_frequency information to contracts.

Added

  • Add new table dim_contract_payment_frequency
  • Add new columns to fct_contract table:
  • contract_payment_frequency_id
  • original_monthly_price
  • original_total_price

2024-02-05

Summary

Enable this Changelog to inform about updates.

2023-11-16

Summary

Improvements of contract related tables.

Added

  • Add following new columns to fct_contract table:
  • cancellation_fee
  • cancellation_date_id
  • cancellation_receipt_date_id
  • pre_cancellation_end_date_id
  • contract_cancellation_reason_id
  • Add new column contract_status to dim_contract_property table.
  • Add new dim_contract_cancellation_reason table.
  • Add column fct_contract.cancellation_date_id (renamed from cancelation_date_id)

Removed

  • Remove fct_contract.cancelation_date_id (renamed into cancellation_date_id)

Deprecated

  • Mark fct_contract_cancellation table as deprecated.
  • Mark dim_contract_cancellation_property table as deprecated.
  • Mark dim_cancellation_reason table as deprecated.