RSS-Feed

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

2026-01-15 (erp_v2 release)

We are excited to introduce a new real-time data schema, erp_v2, which will replace the current batch schema. This update provides access to data with a latency of only a few minutes, enabling near real-time analytics. To ensure a smooth transition and avoid disruptions to your existing data pipelines, we have designed the new schema to be largely backward-compatible with the legacy one, requiring only minor adjustments to your existing data pipelines.

The migration is currently underway and will be rolled out to all customers throughout Q1 2026.

Migration Guide: Switching from Batch to Real-Time Schema

This guide provides a high-level overview of the steps required to migrate your data pipelines from the legacy batch schema to the new real-time schema. The changes are designed to be minimal, ensuring a quick and straightforward transition.

What stays the same? - Database Host and Credentials: You can continue using your existing database credentials. - Table and Column Names: The naming of tables and columns remains unchanged.

What is changing? - Schema Name: The primary change is the schema name. You will need to update your queries to reference the real-time schema, erp_v2, instead of the legacy batch one. - Data Freshness: The new schema provides data in near real-time (minutes delay) compared to the daily updates of the batch schema. - Column Types: To support the new real-time data model, we have made minor technical adjustments to some column types and business logic. These changes are designed to be seamless from a business perspective and are detailed for your technical teams in the "Action Required" section below.

Action Required: 1. Update Schema References: In your data pipeline scripts and queries, replace all references to the legacy schema with erp_v2. 2. Review Column Type Changes: Carefully review the "Changed" and "Removed" sections below and update your data pipelines accordingly. Here are the key areas to focus on: - ID Columns: Hash based ID columns have been changed from bigint to character varying. If your scripts perform type casting on these columns to integer types, you must update them to handle string-based IDs. - Data Type Changes: The super data type has been replaced with character varying for some columns, and the length of other character varying columns has been increased. Ensure your scripts can accommodate these changes. - Business Logic: The calculation for fct_lead_lifetime.lead_status has been updated slightly. Review your analytics and reports that use this field to ensure they align with the new logic. - Removed Columns: The dim_customer.age column has been removed. You will need to update any scripts that use this column to calculate age from dim_customer.date_of_birth instead. 3. Test Your Pipelines: Before switching over completely, test your updated pipelines in a staging environment to ensure everything works as expected.

By following these steps, you can seamlessly transition to our new real-time schema and take advantage of up-to-the-minute data insights.

Added

New schema erp_v2 created as near real time version of the DWH. It is largely compatible with the legacy schema,
Add new table dim_rate_term_configuration to store configuration rules for contract terms, extensions, cancellations, and idle periods associated with rates.
Add new table dim_contract_voucher_definition to store definitions of vouchers that can be generated and applied to contracts.
Add new table dim_contract_voucher_rate_discount_period to define the discount structure (e.g., percentage or absolute amount) for specific periods and rates of contract vouchers.
Add new table fct_contract_voucher to track the actual usage of vouchers, including redemption and withdrawal dates.
Add new table dim_company_code to store codes/tags that can be assigned to companies for categorization and filtering.
Add new table bridge_company_to_company_code to link companies to their assigned codes, enabling many-to-many relationships.
Add new table bridge_company_to_organization_unit to track which companies have access to which studios with their permission levels.
Add new column dim_company.chamber_of_commerce_number to store the chamber of commerce registration number.
Add new column dim_company.vat_number to store the VAT identification number.
Add new columns to dim_company regarding contact person information.
Add new columns to dim_company regarding invoice contact person information.

Deprecated

The legacy batch schema is deprecated and will be removed after a transition period. Please use the new real-time schema erp_v2 instead.

Removed

Column dim_customer.age has been removed. Users should calculate age from dim_customer.date_of_birth instead.

Changed

Columns *.last_updated now reflect the individual time a record last went through the real-time ETL process, instead of when the full table was last updated in the batch system. This gives a more granular view of data freshness.
Column fct_lead_lifetime.lead_status has changed its logic on updating the status - for customers that are not managed in the ERPs lead management, previously the status was changed from PENDING to LOSS if no contract was created for a customer after 7 days of customer creation. Now the 7 days logic was dropped and the status will remain PENDING indefinitely until a contract is created.
Column dim_customer.age is no longer populated - users should calculate age from dim_customer.date_of_birth instead.
Type Changes (44 Changes, minor technical impact):
- Column type changes from bigint to character varying (39 Changes) - Due to hash based ID columns changing their hashing algorithm:
  - dim_contract_cancellation_reason.contract_cancellation_reason_id
  - dim_contract_payment_frequency.contract_payment_frequency_id
  - dim_contract_term.contract_term_id
  - dim_contract_property.contract_property_id
  - fct_contract.contract_cancellation_reason_id
  - fct_contract.contract_payment_frequency_id
  - fct_contract.contract_property_id
  - fct_contract.contract_term_id
  - fct_contract_cancellation.contract_cancellation_property_id
  - fct_contract_cancellation.currency_id
  - dim_customer_communication_consent.customer_communication_consent_id
  - bridge_customer_to_customer_code.customer_to_customer_code_id
  - dim_currency.currency_id (Currency follows ISO 4217 standard)
  - fct_payment_run.currency_id (Currency follows ISO 4217 standard)
  - fct_payment_run.payment_run_id
  - fct_payment_run.payment_run_property_id
  - fct_idle_period.currency_id
  - fct_idle_period.idle_period_property_id
  - dim_product.product_id
  - fct_revenue.currency_id (Currency follows ISO 4217 standard)
  - fct_revenue.product_id
  - dim_purchased_contingent.purchased_contingent_id
  - fct_service_usage.service_usage_id
  - fct_trainer_class_appointments.trainer_appointment_status_id
  - dim_contract_cancellation_property.contract_cancellation_property_id
  - dim_opening_hours.opening_hours_id
  - fct_revenue_cash_based.product_id
  - fct_revenue_cash_based.currency_id
  - fct_employee_access.employee_access_id
  - dim_trainer_appointment_status.trainer_appointment_status_id
  - dim_rate_localized.rate_localized_id
  - dim_customer_custom_field.customer_custom_field_id
  - dim_cancellation_reason_localized.cancelation_reason_localized_id
  - dim_closing_hours.closing_hours_id
  - dim_campaign.campaign_id
  - fct_class_event.class_event_property_id
  - dim_class_event_property.class_event_property_id
  - dim_idle_period_property.idle_period_property_id
  - dim_payment_run_property.payment_run_property_id
- Column type changes from super to character varying (1 Change):
  - dim_customer_communication_consent.communication_channels
- Column length changes from character varying(256) to character varying(65535) (4 Changes):
  - dim_service.description
  - fct_revenue.description
  - fct_revenue_cash_based.revenue_description
  - dim_customer_custom_field.field_value

Changelog