Database Properties
Database Type: Redshift - 8.0.2
Schema erp_v2
Real-Time Magicline Enterprise Data Warehouse
These are the docs for the new
erp_v2Data Warehouse schema that is served in near real time. To see the docs for the old schema, please refer to the legacy schema documentation.
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 synced incrementally (inserts/updates/deletes) every few minutes
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.
Best Practices
Using Views
To ensure our ETL process can properly refresh the underlying tables, it is critical that you create views using the WITH NO SCHEMA BINDING option.
This type of view, also known as a late-binding view, does not create a dependency between the view and the underlying tables. This allows us to drop and recreate tables without affecting your views.
To create a late-binding view in Redshift, use the following syntax:
CREATE VIEW public.your_view_name
AS
SELECT
column1,
column2,
...
FROM
magicline.dim_or_fact_table_name
WITH NO SCHEMA BINDING;
By adhering to this practice, you will prevent issues with our data refresh process and ensure the continuous availability of your data.
Contact
- For feedback and issues, please contact your account manager