Organizations structure operational data by departments and processes, but analytical questions require cross-departmental views.
The hardware load differs in operational application systems and data warehouse systems over time
=> A data warehouse relieves the operational systems
The Star schema has a central fact table and exactly one dimension table for each dimension.
The cardinality between the fact table and a dimension table is n∶1, i.e. a fact (e.g. sales: 67,000 euros) is described by one dimension expression for each dimension (e.g. time: 1st quarter, region: Hesse, product group: books). A dimension characteristic (e.g. product group: books) is associated with 0, 1 or n facts.
In the Snowflake schema, the redundancies in the Star schema are resolved in the dimension tables and the hierarchy levels are each modeled with their own tables:
The Galaxy schema contains more than one fact table. The fact tables share some but not all dimensions. The Galaxy schema thus represents more than one data cube (multi-cubes).
erDiagram
DELIVERY {
int id_product PK
int id_supplier PK
int id_date PK
float amount
float volume
}
SALES {
int id_product PK
int id_client PK
int id_shop PK
int id_date PK
float amount
float volume
}
PRODUCT {
int id_product PK
int id_brand
string name
string description
}
BRAND {
int id_brand PK
int id_supplier
string name
}
SUPPLIER {
int id_supplier PK
int id_country
string name
string address
}
COUNTRY {
int id_country PK
string name
string code_name
}
TIME {
int id_date PK
int week_no
}
CLIENT {
int id_client PK
int id_client_group
string name
string last_name
}
CLIENT_GROUP {
int id_client_group PK
string group_name
string segment_name
}
SHOP {
int id_shop PK
int id_city
string name
string business_type
}
CITY {
int id_city PK
int id_region
string name
}
REGION {
int id_region PK
string name
string country
}
DELIVERY }o--|| PRODUCT : product
DELIVERY }o--|| SUPPLIER : supplier
DELIVERY }o--|| TIME : date
PRODUCT }o--|| BRAND : brand
BRAND }o--|| SUPPLIER : supplier
SUPPLIER }o--|| COUNTRY : country
SALES }o--|| PRODUCT : product
SALES }o--|| CLIENT : client
SALES }o--|| SHOP : shop
SALES }o--|| TIME : date
CLIENT }o--|| CLIENT_GROUP : group
SHOP }o--|| CITY : city
CITY }o--|| REGION : region
The result of a nest operation physically represents a two-dimensional matrix. It is extended by displaying different hierarchy levels of one or more dimensions on one axis (column or row) in nested form. In the following example, three dimensions are displayed:
