Data Warehouse Systems: Tutorial 2

Michael Dejori (1018844)
19.10.2011
Data Warehouse Systems: Tutorial 2
Exercise 1: Physical Modeling
a) Star Schema
ProdukttDimension
- ProduktKey (PK)
- Artikel
- Author
- ISBN
- Produktgruppe
- Produktfamilie
- Produktkategorie
ZeitDimension
- ZeitKey (PK)
- Tag
- Monat
- Woche
- Quartal
- Jahr
FACTTABLE
- Umsatz
- ZeitKey (FK)
- ProduktKey(FK)
- FilialeKey(FK)
FilialeDimension
- FilialeKey (PK)
- Filiale
- Stadt
- Provinz
- Region
- Land
b) Snowflake Schema
-
ProduktKey
Artikel
Author
ISBN
PgruppeKey(FK)
- PgruppeKey
- Produktgruppe
- Pfamilie-Key(FK)
- PfamilieKey
- Produktfamilie
- Pkategorie-Key(FK)
- PkategorieKey
- Produktkategorie
FACTTABLE
- Umsatz
- ZeitKey (FK)
- ProduktKey(FK)
- FilialeKey(FK)
- WocheKey
- Wochebez
- MonatKey
- Monatbez
- QuartalkKey(FK)
- FilialeKey
- Filiale
- StadtKey(FK)
- StadtKey
- Stadtbez
- ProvinzKey(FK)
ZeitKey
Tagbez
WocheKey(FK)
MonatKey(FK)
- QuartalKey
- Quartalbez
- JahrKey(FK)
- RegionKey
- Regionbez
- LandKey(FK)
- ProvinzKey
- Provinzbez
- RegionKey(FK)
- JahrKey
- Jahrbez
- LandKey
- Land
Michael Dejori (1018844)
19.10.2011
c) To store a Data Warehouse Model physically, we strongly need relations. So I would not use a flatfile structure, since it is hardly possible to build data by joining the keys. In order to do that a
relational storage is considered. For instance implementing the snow-flake schema there are
many relations (foreign key) due to the normalization. Moreover flat-file storage basically keeps
one tuple per row and so more fields are in a row. The descriptor works more or less row
oriented and therefore field values need to be parsed. Relations are built like a tables and fields
can be accessed easier.
Exercise 2: SQL
a) + b) Sql-Statements: Tables and constraints(Foreign Key) are created both in the
following code:
CREATE TABLE timedimension (
ZeitKey INTEGER NOT NULL,
Tag SMALLINT,
Monat SMALLINT,
Woche SMALLINT,
Quartal SMALLINT,
Jahr SMALLINT,
PRIMARY KEY(ZeitKey)
)
CREATE TABLE filialedimension (
FilialeKey INTEGER NOT NULL,
Filiale VARCHAR(30),
Stadt VARCHAR(30),
Provinz VARCHAR(30),
Region VARCHAR(30),
Land VARCHAR(30),
PRIMARY KEY(FilialeKey)
)
CREATE TABLE produktdimension (
ProduktKey INTEGER NOT NULL,
Artikel VARCHAR(30),
Author VARCHAR(30),
ISBN VARCHAR(20),
Produktgruppe VARCHAR(30),
Produktfamilie VARCHAR(30),
Produktkategorie VARCHAR(30),
PRIMARY KEY(ProduktKey)
)
CREATE TABLE fact (
ZeitKey INTEGER NOT NULL,
FilialeKey INTEGER NOT NULL,
ProduktKey INTEGER NOT NULL,
Umsatz FLOAT,
PRIMARY KEY (ProduktKey, ZeitKey, FilialeKey),
FOREIGN KEY (ZeitKey) REFERENCES timedimension(ZeitKey) ON DELETE
RESTRICT ON UPDATE RESTRICT ,
FOREIGN KEY (FilialeKey) REFERENCES filialedimension(FilialeKey) ON
DELETE RESTRICT ON UPDATE RESTRICT ,
FOREIGN KEY (ProduktKey) REFERENCES produktdimension(ProduktKey) ON
DELETE RESTRICT ON UPDATE RESTRICT
)