# Business Intelligence - ETL
###### tags: `4. Business Intelligence - ETL - Lösungsansätze`

**Klausur WiSe 2019/2020**
## 4 Business Intelligence - ETL (10 Punkte/10 Minuten)

Das Projekt zur Erstellung des BI-Systems aus Aufgabe 3 schreitet voran. Ihr Snowflake-Modell soll mit Daten aus dem OLTP-System befüllt werden. Für Testzwecke bekommen Sie aus dem operativen Vorsystem den nachfolgenden Datenabzug (nicht normalisiert) für die Monate November und Dezember.

*Skizzieren Sie die Struktur (Spalten) der Faktentabelle und den Tabellen der Product-Hierarchy (nur diese Hierarchy!) und befüllen Sie diese Tabellen mit den Datensätzen (Zeilen) die sich aus dem Datenabzug ergeben (10 Minuten).
(Die Werte des Datenabzugs sollen als IDs verwendet werden.
Weitere Attribute (z.B. die Descriptions) müssen nicht berücksichtigt werden.)*

## Lösung
### Faktentabelle
| Date | Product_ID | SalesOrgID | SQ | Revenue |
| -------- | ---------- | ---------- | --- | ------- |
| 12.11.19 | ProdID2 | SOID2 | 500 | 1000 |
| 14.11.19 | ProdID1 | SOID1 | 200 | 800 |
| 20.11.19 | ProdID3 | SOID3 | 50 | 50 |
| 29.11.19 | ProdID1 | SOID1 | 100 | 400 |
| 30.11.19 | ProdID1 | SOID2 | 50 | 200 |
| 30.11.19 | ProdID2 | SOID2 | 250 | 500 |
| 04.12.19 | ProdID1 | SOID1 | 400 | 1600 |
| 10.12.19 | ProdID3 | SOID3 | 500 | 500 |
| 23.12.19 | ProdID3 | SOID1 | 800 | 800 |
| 27.12.19 | ProdID3 | SOID3 | 400 | 400 |

### ProduktTabelle
| Product_ID | ProductGroupID | Saleprice |
| ---------- | ----------------- | --------- |
| ProdID1 | PGrpID1 | 4 |
| ProdID2 | PGrpID2 | 2 |
| ProdID3 | PGrpID3 | 1 |

### ProduktGruppeTabelle
| ProductGroupID_ID | ProductCategoryID | LevelID |
| ----------------- | ----------------- | ------- |
| PGrpID1 | PCatID1 | LevID1 |
| PGrpID2 | PCatID2 | LevID2 |
| PGrpID3 | PCatID2 | LevID1 |

### ProduktKategorieTabelle
| ProductCategoryID |
| ----------------- |
| PCatID1 |
| PCatID2 |

### LevelTabelle
| LevelID | LevelKey |
| ------- | ----------- |
| LevID1 | operativ |
| LevID2 | strategisch |

----
**Klausur SoSe 2019**
## Business Intelligence – ETL (13 Punkte)
a. Das Projekt zur Erstellung des BI-Systems aus Aufgabe 3 schreitet voran. Ihr Snowflake-Modell soll mit Daten aus dem OLTP-System befüllt werden. Für Testzwecke bekommen Sie aus dem operativen Vorsystem den nachfolgenden Datenabzug (nicht normalisiert) für die Monate Mai und Juni.
Skizzieren Sie die Faktentabelle und die Tabellen der Sales Organisation-Dimension (nur diese Dimension!) und befüllen Sie diese mit den Datensätzen (Zeilen) die sich aus dem Datenabzug ergeben (10 Pkt.).

(Die Werte des Datenabzugs sollen als IDs verwendet werden.
Weitere Attribute (z.B. die Descriptions) müssen nicht berücksichtigt werden.)

b. Erläutern Sie, was unter Surrogat-Schlüsseln zu verstehen ist und wie diese im Rahmen des ETLProzesses verwendet werden (3 Pkt.)


## Lösung
Zu Aufgabe a)
### Faktentabelle
ADAPT vor der Erstellung prüfen -> geht es bis Monat ? Tag? -> Hier bis Monat

| Date | ProductID | SalesOrg | SQ | Rev |
| -------- | --------- | -------- | --- | ----- |
| 02.05.19 | ProdID2 | S002 | 500 | 1.000 |
| 10.05.19 | ProdID1 | S001 | 200 | 1.000 |
| 23.05.19 | ProdID3 | S003 | 50 | 50 |
| 30.05.19 | ProdID1 | S001 | 100 | 400 |
| 31.05.19 | ProdID1 | S001 | 250 | 500 |
| 02.06.19 | ProdID2 | S002 | 250 | 500 |
| 05.06.19 | ProdID1 | S001 | 400 | 1.600 |
| 14.06.19 | ProdID3 | S003 | 500 | 500 |
| 20.06.19 | ProdID3 | S001 | 800 | 800 |
| 25.06.19 | ProdID3 | S003 | 400 | 400 |

| Date | ProductID | SalesOrg | SQ | Rev |
| -------- | --------- | -------- | --- | ----- |
| 05.19 | ProdID2 | S002 | 500 | 1.000 |
| 05.19 | ProdID1 | S001 | 300 | 1.400 |
| 05.19 | ProdID3 | S003 | 50 | 50 |
| 05.19 | ProdID1 | S003 | 250 | 500 |
| 06.19 | ProdID2 | S002 | 250 | 500 |
| 06.19 | ProdID1 | S001 | 400 | 1.600 |
| 06.19 | ProdID3 | S003 | 900 | 900 |
| 06.19 | ProdID3 | S001 | 800 | 800 |

### Sales-Tabelle

| SalesOrg_ID | Region_ID | SODescription |
| ----------- | ----------- | ------------- |
| S001 | Region_ID_1 | - |
| S002 | Region_ID_2 | - |
| S003 | Region_ID_3 | - |

### Region-Tabelle

| Region_ID | CountryKey | RName |
| ----------- | ------------ | --------- |
| Region_ID_1 | Country_ID_1 | SH |
| Region_ID_2 | Country_ID_1 | HH) |
| Region_ID_3 | Country_ID_1 | BW |

### Country-Tabelle

| CountryKey | CountryName |
| ------------ | ----------- |
| Country_ID_1 | GER |

Zu Aufgabe b)
~~Unter Surrogat Schlüssel versteht man einen PrimarySchlüssel, aus dem man keine weiteren Informationen ableiten kann (Ist für keine andere Tabelle ein FK). Häufig ist es eine automatisch aufzählende Zahl als Primary Key~~

**Option 2: Was sind Surrogat-Schlüssel und wozu dienen sie?**
- Surrogat-Schlüssel sind Schlüssel, die automatisch unabhängig von den Daten der Tabelle erzeugt werden, um besseren und schnelleren Zugriff auf Daten zu gewähren.
Meistens werden sie auch als Primärschlüssel verwendet.

---
**Klausur WiSe 2018/19**
## Business Intelligence – ETL (9 Punkte)
Das Projekt zur Erstellung des BI-Systems aus Aufgabe 3 schreitet voran. Ihr Snowflake-Modell soll mit Daten aus dem OLTP-System befüllt werden. Folgendes Datenmodell des OLTP-Systems wird Ihnen zur Verfügung gestellt. Für das Befüllen soll SQL verwendet werden.
- a. Formulieren Sie das SQL-Statement, zur Extraktion/ Transformation der Daten aus dem OLTPSystem (ohne Duplikate) zur initialen Befüllung Ihrer Tabelle des Snowflake-Modells (Aufgabe 3) für die Klassifikationsstufe „Level“ der ManagementLevelHierarchy (3 Pkt.).
- b. Formulieren Sie das SQL-Statement, zur Extraktion/ Transformation der Daten aus dem OLTPSystem zur initialen Befüllung der Faktentabelle (6 Pkt.). Die Kennzahl Revenue soll gemäß folgender Formel berechnet werden: SalesQuantity * Product.SalesPrice


#### VorabInfo aus Aufgabe 3 vermutliche Faktentabelle:
*Wie man am Besten vorgeht:
1. Feststellen, welche Tabelle übertragen werden soll
hier Klassifikationsstufe „Level“ der ManagementLevelHierarchy
2. Wie ist das in unserem SnowflakeModell verbunden, und wie sieht dementsprechend die Tabelle in unserem Snowflake aus
hier: LevelTabelle

| LevelID | LevelKey |
| ------- | ----------- |
| LevID1 | operativ |
| LevID2 | strategisch |
3. Statement ableiten aus der Tabelle
Man beachte dabei ohne Duplikate. Diese würden wir dann erzeugen, wenn wir von z.b. Orderitem aus ableiten für jedesmal, wenn das Produkt vorkommen würde, daher SIMPEL DENKEN!!!

> [name=Dennis]
>
>Frage hier, wie kommen wir an LevelID -> alle WHERE-Verbindungen erzeugen das Attribut
aber hier wird doch nur Verbindung aus Mastertabelle gezogen und noch keinen Werten zugeordnet, oder?
Sollte es nicht reichen, wenn man die LevelTabelle erstellt und dort automatischen PK setzt? wie Surrogat-Key, der aber durch unsere Befüllung dann nicht mehr "Surrogat" wäre
dann würde hier folgendes reichen:
SELECT LevelKey, LevelDescription FROM ProductDetails
GROUPBY LevelKey
bin mit aber überhaupt nicht sicher, weil kein JOIN dabei wäre...

Lösungsansatz zu b)
Faktentabelle abbilden, wobei
Revenue = SalesQuantity * Product.SalesPrice

**vermutliche Faktentabelle SalesData**
TimeID
ProductID
SalesOrganisationID
Revenue
SalesQuantity

SELECT DebitorID, OrderDate, SUM(OrderItem.Salesquantity * Product.SalesPrice)
FROM Orderhead
WHERE ~~Orderhead.OrderNumber~~ OrderNumber = OrderItem.OrderNumber
AND OrderItem.ProductID = Prodcut.ProductID
GROUPBY OrderDate

### **ProbeKlausur SoSe 2018**
- a. Erläutern Sie die Aufgaben der einzelnen Phasen und des Data Warehousing-Prozesses (6 Pkt.)

Der Data-Warehouse-Prozess ist der Prozess zur Bewirtschaftung und Auswertung eines Data-Warehouses (Datenlager), der die folgenden Schritte umfasst:
1. Datenbeschaffung: das heißt die Extraktion der relevanten Daten aus den Quellsystemen, Transformation und gegebenenfalls Datenbereinigung in einem Arbeitsbereich sowie Laden in das Data-Warehouse. Dieser Schritt wird auch Extract-Transform-Load-Prozess (ETL-Prozess) genannt.
2. Datenhaltung: das heißt die langfristige Speicherung der Daten im Data-Warehouse
3. Versorgung und Datenhaltung der für die Analyse notwendigen separaten Datenbestände, den Data-Marts
4. Datenauswertung durch Analyse der Daten im jeweiligen Data Mart bzw. Versorgung nachgelagerter Anwendungssysteme
Dabei ist bei allen Schritten das Repositorium eingebunden, das insbesondere beim ETL-Prozess mit Metadaten versorgt wird und insbesondere bei der Analyse der Daten genutzt wird.

- b. Das Projekt zur Erstellung des BI-Systems aus Aufgabe 3 schreitet voran. Ihr Snowflake-Modell
soll mit Daten aus dem OLTP-System befüllt werden. Folgendes Datenmodell des OLTP-Systems
wird Ihnen zur Verfügung gestellt. Für das Befüllen soll SQL verwendet werden.
Formulieren Sie die SQL-Statements, um Ihre Tabellen aus Aufgabe 3 für die PurchaseRegionHierarchy initial zu befüllen (6 Pkt.).
Formulieren Sie das SQL-Statement, um Ihre Faktentabelle initial zu befüllen (6 Pkt.). Die Kennzahl PurchaseCosts für eine Bestellpostion soll gemäß folgender Formel berechnet werden: PurchaseQantity * Product.PurchasePrice