Database Properties
Database Type: Redshift - 8.0.2
Schema magicline
Magicline Enterprise Data Warehouse
This read-only schema provides selected business relevant data of Magicline 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.
Note: See our showcase on how to use PowerBI!
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 fully rebuilt every ~4-8 hours
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 Magicline, 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 magicline. All data is provided.
- Franchisee: Client that owns a subset of the tenants studios in magicline. 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 magicline 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.
Contact
- For feedback and issues, please contact your account manager
Tables
| Table / View | Children | Parents | Columns | Rows | Type | Comments |
|---|---|---|---|---|---|---|
| fct_trainer_class_appointments | 0 | 7 | 9 | -1 | Table | DEPRECATED. Use fct_trainer_appointment instead.Stores all appointments for trainers |
| dim_inclusive_contingent | 2 | 0 | 8 | -1 | Table | A contingent how often a service can be used, that is included in a contract (rate) of a customer. |
| dim_cancellation_reason | 1 | 0 | 5 | -1 | Table | DEPRECATED. Use |
| bridge_customer_to_customer_code | 0 | 2 | 4 | -1 | Table | Link customer to all his customer codes. |
| fct_payment_run | 0 | 5 | 12 | -1 | 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 | -1 | Table | This table contains all times of a day from |
| dim_payment_run_property | 1 | 0 | 6 | -1 | Table | A set of basic payment run booking properties |
| fct_checkin | 0 | 6 | 10 | -1 | Table | The event of a customer visiting a gym (organization_unit) owned by you. |
| dim_idle_period_property | 1 | 0 | 8 | -1 | Table | Additional dimensional information about a fct_idle_period. |
| dim_company | 1 | 0 | 7 | -1 | Table | A ‘partner company’ that contracts can be related to for Magicline’s Corporate Fitness program. |
| dim_employee | 3 | 0 | 16 | -1 | Table | Table contains all employees and their relevant information |
| dim_purchased_contingent | 2 | 0 | 7 | -1 | Table | A contingent how often a service can be used, that is paid for (not included in a customers contract). |
| dim_revenue_group | 1 | 0 | 4 | -1 | Table | A hierarchical categorization of revenues into a broad main_group and a finer grained sub_group. |
| fct_contract_cancellation | 0 | 10 | 13 | -1 | Table | DEPRECATED. Use
|
| dim_class | 3 | 0 | 9 | -1 | Table | A class that a customer can participate in, e.g. yoga, dance class, etc |
| fct_idle_period | 0 | 8 | 11 | -1 | Table | A timeframe in which a customer doesnt have to pay for his contract(s). |
| dim_date | 34 | 0 | 16 | -1 | Table | All dates from |
| dim_contract_payment_frequency | 1 | 0 | 6 | -1 | Table | Defines how often and how much is paid for an individual contract. |
| dim_product | 1 | 0 | 8 | -1 | 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 | 4 | 12 | -1 | 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 Magiclines Lead Management section and a fallback logic (see |
| fct_trainer_appointment | 0 | 6 | 17 | -1 | 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_contract_term | 1 | 0 | 4 | -1 | Table | The term of the contract. |
| dim_service | 2 | 0 | 10 | -1 | 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 | 4 | 0 | 5 | -1 | Table | All available currencies |
| fct_contract | 2 | 16 | 21 | -1 | Table | Stores all the contracts of all the customers. |
| dim_device | 1 | 0 | 6 | -1 | Table | A device connected to magicline 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 | -1 | Table | The tables contains simple properties of the class events. |
| dim_contract_property | 1 | 0 | 9 | -1 | 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 | 4 | 0 | 6 | -1 | Table | A standard tariff a contract is created with. |
| dim_trainer_appointment_status | 1 | 0 | 3 | -1 | Table | Statuses of the employee for a specific appointment |
| fct_revenue | 0 | 10 | 14 | -1 | 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 | 5 | 0 | 9 | -1 | Table | The unique event of an appointment on the calendar with its properties. |
| dim_customer_code | 1 | 0 | 7 | -1 | Table | A tag or ‘member code’ set on customers to organize them into groups. |
| dim_organization_unit | 13 | 0 | 13 | -1 | 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_contract_cancellation_reason | 1 | 0 | 7 | -1 | Table | A set of basic properties of the cancellation such as origin, if the cancellation was extraordinary and the reason for the cancellation. |
| fct_class_appointment | 0 | 6 | 14 | -1 | Table | Stores all appointments for a particular class |
| dim_contract_cancellation_property | 1 | 0 | 4 | -1 | Table | DEPRECATED. Use |
| fct_customer_appointment | 0 | 8 | 16 | -1 | 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 |
| fct_class_event | 0 | 10 | 13 | -1 | Table | Stores all customers events for class appointments such as booking or cancellations |
| dim_customer | 10 | 1 | 30 | -1 | Table | A customer is a person who signed up to a studio owned by you or your franchise. |