Magicline Enterprise Data Warehouse

TABLES 44

COLUMNS 439

Constraints 111

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 + HOST are 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 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.

Contact

For feedback and issues, please contact your account manager

Tables

All Tables Views Comments

Search:

Table / View	Children	Parents	Columns	Type	Comments
bridge_customer_to_customer_code	0	2	4	Table	Link customer to all his customer codes.
dim_appointment	5	0	9	Table	The unique event of an appointment on the calendar with its properties.
dim_campaign	1	0	7	Table	All campaigns or ‘marketing initiatives designed to promote contract sales’, that are whitelisted for this DWH instance. A campaign can be associated to customers (fct_lead_lifetime), contracts and vouchers.
dim_cancellation_reason	1	0	5	Table	DEPRECATED. Use `dim_contract_cancellation_reason` instead. A reason for contract cancellation
dim_class	3	0	9	Table	A class that a customer can participate in, e.g. yoga, dance class, etc
dim_class_event_property	1	0	5	Table	The tables contains simple properties of the class events.
dim_company	1	0	7	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.
dim_contract_cancellation_property	1	0	4	Table	DEPRECATED. Use `dim_contract_cancellation_reason` instead. A set of basic properties of cancelation such as origin, if the cancelationwas extraordinary, etc
dim_contract_cancellation_reason	1	0	7	Table	A set of basic properties of the cancellation such as origin, if the cancellation was extraordinary and the reason for the cancellation.
dim_contract_payment_frequency	1	0	7	Table	Defines how often and how much is paid for an individual contract.
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_contract_term	1	0	4	Table	The term of the contract.
dim_currency	4	0	5	Table	All available currencies
dim_customer	13	1	30	Table	A customer is a person who signed up to a studio owned by you or your franchise.
dim_customer_code	1	0	7	Table	A tag or ‘member code’ set on customers to organize them into groups.
dim_customer_communication_consent	0	2	14	Table	The message categories a customer gave consent to be messaged for, over the specified communication channels. Consent could be given directly on a specfic customer or as a default via the Studio Settings.
dim_date	37	0	16	Table	All dates from `1900-01-01` to `2099-12-31` with some precalculated values for common operations.
dim_daytime	0	0	6	Table	This table contains all times of a day from `00:00:00` to `23:59:59` with the granularity of one second.
dim_device	1	0	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_employee	3	0	16	Table	Table contains all employees and their relevant information
dim_idle_period_property	1	0	8	Table	Additional dimensional information about a fct_idle_period.
dim_inclusive_contingent	2	0	8	Table	A contingent how often a service can be used, that is included in a contract (rate) of a customer.
dim_organization_unit	16	0	13	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_payment_run_property	1	0	6	Table	A set of basic payment run booking properties
dim_product	1	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).
dim_purchased_contingent	2	0	7	Table	A contingent how often a service can be used, that is paid for (not included in a customers contract).
dim_rate	4	0	6	Table	A standard tariff a contract is created with.
dim_revenue_group	1	0	4	Table	A hierarchical categorization of revenues into a broad main_group and a finer grained sub_group.
dim_service	2	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_trainer_appointment_status	1	0	3	Table	Statuses of the employee for a specific appointment
fct_checkin	0	6	10	Table	The event of a customer visiting a gym (organization_unit) owned by you.
fct_class_appointment	0	6	14	Table	Stores all appointments for a particular class
fct_class_event	0	10	13	Table	Stores all customers events for class appointments such as booking or cancellations
fct_contract	4	16	24	Table	The contract between a customer and a gym (organization_unit). Includes different contract_types (CONTRACT, MODULE, FLAT_FEE see `dim_rate.contract_type`) and reversed contracts (see `is_reversed`).
fct_contract_cancellation	0	10	13	Table	DEPRECATED. Use `fct_contract` instead. The table tracks canceled contracts and specific reasons for its cancelation
fct_contract_price_history	0	5	14	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 `valid_from_date >= CURRENT_DATE < valid_to_date`
fct_contract_term_dates	0	5	7	Table	Each individual contract term of a customer.
fct_customer_appointment	0	8	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 `appointment_id`.
fct_idle_period	0	8	11	Table	A timeframe in which a customer doesnt have to pay for his contract(s).
fct_lead_lifetime	0	5	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 `lead_source`).
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
fct_revenue	0	9	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.
fct_trainer_appointment	0	6	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 `appointment_id`.
fct_trainer_class_appointments	0	7	9	Table	DEPRECATED. Use fct_trainer_appointment instead. Stores all appointments for trainers

Showing 1 to 44 of 44 entries

Tables

SchemaSpy Analysis of Magicline Enterprise Data Warehouse

Database Properties

Schema magicline

Magicline Enterprise Data Warehouse

Authentication

Connection

Updates

Non-Breaking Changes

Breaking Changes

Anonymization / Access Level

Contact

Tables

DEPRECATED. Use `dim_contract_cancellation_reason` instead.
A reason for contract cancellation

DEPRECATED. Use `dim_contract_cancellation_reason` instead.
A set of basic properties of cancelation such as origin, if the cancelationwas extraordinary, etc

DEPRECATED. Use `fct_contract` instead.

DEPRECATED. Use fct_trainer_appointment instead.