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

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.