Oracle Data Warehouse Multidimensionale Modellierung und Star

Workshopbegleitmaterial „Multidimensionale Modellierung und Star Schema Design“
1 /11
Oracle Data Warehouse
Multidimensionale Modellierung und
Star Schema Design
Aufgaben und Lösungen
Stand Mai 2013 V1
E:\Kurse\DM_Kurs_Feb_2013\Multidimensionale Modellerieurng Workbook.docx
Workshopbegleitmaterial „Multidimensionale Modellierung und Star Schema Design“
2 /11
Das Tagesseminar Multidimensional Modellierung und Star Schema Design nutzt für alle praktischen
Übungen die fingierte Firma Service GmbH. Ziel ist es anhand eines durchgängigen Beispiels alle nötigen DesignSchritte exemplarisch durchzuspielen.
Zu Beginn steht die Informationsbedarfsanalyse, am Ende aller Übungen in ein multidimensionales Modell in der
Datenbank implementiert, das Daten aufnehmen kann, und über das man abfragen kann.
Die Zwischenschritte sind Analyse- , Objekt- und konzeptionelles Modell.
Inhalt
1.
Die Firmenbeschreibung SERVICE GmbH ................................................................................................... 3
2.
Informationsbedarfsanalyse: Antworten aus den befragten Abteilungen: Probleme und Erwartungen ....... 5
Informationsbedarfsanalyse: Antworten aus dem Vertrieb ............................................................................ 5
Informationsbedarfsanalyse: Antworten aus dem Marketing ......................................................................... 5
Informationsbedarfsanalyse: Antworten aus der Buchhaltung ....................................................................... 6
Informationsbedarfsanalyse: Antworten aus dem Controlling ........................................................................ 6
3.
Die Übungsaufgaben .................................................................................................................................. 8
4.
Die Musterlösungen ................................................................................................................................... 9
E:\Kurse\DM_Kurs_Feb_2013\Multidimensionale Modellerieurng Workbook.docx
Workshopbegleitmaterial „Multidimensionale Modellierung und Star Schema Design“
3 /11
1. Die Firmenbeschreibung SERVICE GmbH
Geschäftsfelder
Die Service GmbH ist historisch eine Großhandels GmbH für Baumärkte, sowie eine eigenständige BaumarktHandelskette mit 55 Baumärkten deutschlandweit. Allerdings hat die Servivce GmbH über die Jahre ihre
Geschäftsfelder durch Übernahmen erweitert, um weniger anfällig bei Marktveränderungen zu sein.
Neben der Baumarktzulieferung (Firmenkunden) und dem Baumarktgeschäft ist ein Internethandel
hinzugekommen, der sich an Privatkunden wendet. Über diesen Online-Markt werden neben Baumarktartikel
zusätzlich noch Computer-Artikel (IT-Ware) angeboten. Ergänzt wurde das Geschäft rund um die
Baumarktbelieferung durch die Servicevermittlung. So werden Handwerksdienstleistungen angeboten
(Vermittlung von Handwerkern und Handwerksfachbetriebe), aber auch Vermittlungsdienste von Kleinkrediten zur
Finanzierung von Bau- oder sonstigen Handwerksleistungen.
Vertriebsbereiche
Zusammengefasst sind es die Bereiche:
 Großhandel für Baumärkte (Firmenkunden und Wiederverkäufer) und Einzelkunden in eigenen Baumärkten
o Haushaltswaren / Heimwerker / Gartenbedarf / KFZ-Zubehoer / Elektroartikel
 Eigene Baumärkte mit den zuvor gennannten Artikelgruppen
 Internethandel für Endkunden (Privatkunden bzw. Online-Kunden)
o Computerteile und
o alle oben genannten Baumarktartikel
 Vermittlung von Handwerksdienstleistung im Bausektor (Privatkunden)
 Vermittlung von Kleinkrediten (Privatkunden)
Kundenarten
Die unterschiedlichen Geschäftsbereiche richten sich an unterschiedliche Zielgruppen. Einen Teil nur an
Firmenkunden, einen anderen Teil an Privatkunden oder an Online-Kunden.
Privat-, Online und Firmenkunden unterscheidet man durch ein Status-Feld (Wert P, O oder F).
 Firmenkunden sind i. d. R. die belieferten Baumärkte, die nicht zu der eigenen Kette gehören oder z. B.
Handwerksbetriebe, die in den Kundendaten mit „F“ markeirt sind. Baumärkte können sein:
o Ein einzelner selbständiger Markt (meist kleinere Märkte auf dem Land oder in Innenstädten)
o Teil einer Baumarktkette
o Abteilungen anderer Fachgeschäfte
o Eigene Baumarktfilialen
Diese Unterscheidung ist Voraussetzung für die Preisfindung und die Zuordnung der Vertriebsmitarbeiter
zu den Partnerbaumärkten.
 Privatkunden sind alle namentlich bekannten Baumarktkunden, die i. d. R. eine Kundenkarte besitzen oder
Kunden, denen eine Dienstleistung (Handwerk- oder Finanzdienstleistung) vermittelt wurde.
Online-Kunden verfügen über ein Online-Profil mit dem sie sich bei dem Webauftritt der Service GmbH
anmelden.
Kundenkarte und Dienstleistungsvermittlungsdaten
Zum besseren Monitoring der Kundenbeziehungen wurde für Privatkunden eine Kundenkarte eingeführt. Über
diese Kundenkarte, aber auch über die Vermittlungsverträge, hat man Daten über Wohnart, Ausbildungsstand,
Berufsstand, Einkommensgruppen, Adressen (Wohnorte), Alter, Stand, Haushalte.
Produkt-/Artikeldaten
Alle Produkte, Artikel und Dienstleistungen sind in den Produktstammdaten zusammengefasst. Hierbei hat man
die ursprünglichen historischen Schlüssel aus der Zeit der reinen Baumarktzulieferung beibehalten
(Artikelnummer). Neuere Produkte und Dienstleistungen haben eine Produktnummer. Bestimmte Artikel z. B. ITArtikel können nur über den Online-Handel vertrieben werden.
E:\Kurse\DM_Kurs_Feb_2013\Multidimensionale Modellerieurng Workbook.docx
Workshopbegleitmaterial „Multidimensionale Modellierung und Star Schema Design“
4 /11
Internetvertrieb und Spediteure
Über das Internet können Privatkunden Produkte kaufen. Diese werden mit Hilfe eines Spediteurs ausgeliefert.
Nur hier sind zusätzlich IT-Produkte bestellbar. Spediteure liefern die Ware und holen auch die Retouren wieder
ab.
Läger und Retouren
Retournierte Ware kann nicht mehr an die Lieferanten zurückgegeben werden. Daher wird diese Ware in
mehreren verteilten Lägern zum Wiederverkauf aufbewahrt.
Vertriebsmitarbeiter, Zuständigkeiten und Provisionen
Die Vertriebsmitarbeiter der Service GmbH
 verkaufen an andere Baumärkte, Baumarktketten bzw. Abteilungen anderer Handelshäuser
 vermitteln Handwerks- und Finanzdienstleitstungen
Es erhalten Provision in Prozent des Dienstleistungswertes:
 die Vertriebsmitarbeiter 5%,
 ihre direkten Vorgesetzten 2%
 die Leiter der Filiale, der die Dienstleistung zugeordnet wird, zusätzlich noch einmal 1 %
Interviews
In einer Analyse für ein neues Data Warehouse – Informationssystem werden die 4 Gruppen
1.
2.
3.
4.
Vertrieb
Controlling
Buchhaltung
Marketing
befragt.
Hinweis für die Lösung der Aufgabenstellung:
Die gewählte Reihenfolge entspricht der Wichtigkeit aus der Management Perspektive. Angesichts der wenigen
Zeit, kann man das berücksichtigen.
E:\Kurse\DM_Kurs_Feb_2013\Multidimensionale Modellerieurng Workbook.docx
Workshopbegleitmaterial „Multidimensionale Modellierung und Star Schema Design“
5 /11
2. Informationsbedarfsanalyse: Antworten aus den
befragten Abteilungen: Probleme und Erwartungen
Informationsbedarfsanalyse: Antworten aus dem Vertrieb
Bekannte Probleme
 Wir haben keine verlässlichen Zahlen über „sich lohnende Produkte“.
 Wir können den Zahlen in den Berichten nicht trauen.
o Es können keine artikelgruppenbezogenen Berichte erstellt werden, weil Teile der
Serviceleistungen und Produkte in den Stammdaten nicht korrekt einsortiert sind.
o Artikelgruppenberichte sind falsch.
o Korrekte Artikelgruppenberichte können nur mühsam mit genauester Kenntnis von
Artikel/Produktbezeichnungen und durch separates Umkopieren von Daten mit Excel erstellt
werden. Diesen Aufwand betreiben nur wenige Mitarbeiter und auch immer seltener.
o Auswertungen über Sparten sind gar nicht möglich, weil auf dem Spartenlevel nicht alle
Leistungen des Unternehmens sichtbar sind.
o Provisionsabrechnungen stimmen oft nicht
Erwartungen
Der Vertrieb will seine künftigen Aktivitäten planen und sucht Hilfestellungen für eine künftige Ausrichtung.
Gewünschte Informationen sind:
 Auf welche Produkte sollte man sich fokussieren?
 Mit welchen Spartenbereichen sollte man sich mehr beschäftigen?
 Hat sich der reine Service-Bereich gelohnt, und sollte man hier mehr Angebote schaffen?
Informationsbedarfsanalyse: Antworten aus dem
Marketing
Bekannte Probleme




Trotz Kundenkarte sind nicht genügend Informationen zu den Kunden vorhanden.
Es gibt keine Informationen darüber, ob Kunden mit Kundenkarte mehr oder weniger gekauft haben.
Sind die Verkaufszahlen der Kunden mit Kundenkarte verlässlich?
Die Effektivität der Marketingabteilung ist nicht ganz klar.
Erwartungen
Ziel des Marketings ist es, die passenden Kunden mit der passenden Botschaft zu erreichen. Ein wichtiges
Hilfsmittel hierfür ist die genaue Kenntnis über die Kunden.
Gewünschte Informationen sind:
 Wie viele Kunden hat die Firma?
 Taugt die Kundenkarte als aussagekräftiges Instrument?
 Struktur der Kunden?
 Welche Segmentierungen sind möglich?
 Mit welchen Kundengruppen wird das größte Geschäft gemacht
o Baumärkte
 Eigene?
 Fremde?
o Internethandel?
o Finanzdienstleister?
o Handwerkervermittlung?
E:\Kurse\DM_Kurs_Feb_2013\Multidimensionale Modellerieurng Workbook.docx
Workshopbegleitmaterial „Multidimensionale Modellierung und Star Schema Design“
6 /11
Informationsbedarfsanalyse: Antworten aus der
Buchhaltung
Bekannte Probleme
 Es gibt keine Transparenz bei der Gewährung von Rabatten und Discounts, weil die Trennung in der
Behandlung von Firmen- und Privatkunden nicht ganz deutlich ist. Das Statuskennzeichen für Firmen- und
Privatkunden scheint nicht durchgängig gepflegt zu sein.
 Es gibt zu viele offenen Rechnungen von Kunden. Es ist nicht klar, warum diese Rechnungen nicht gezahlt
werden.
o Vermutet wird: Es handelt sich oft um nicht registrierte Retouren wobei die Ware in einem Lager
verschwindet, ohne, dass sie als Retoure der Buchhaltung gemeldet wurde.
 Es gibt Lieferungen, die offenbar die Kunden nicht erreichen. Warum ist nicht ganz klar. Aufgrund von
Stichproben werden falsche Adressdaten vermutet. Das Ergebnis sind offene Rechnungen, von denen nicht
klar ist, ob die Ware den Kunden überhaupt erreicht hat. Die Ware selbst ist aber auch nicht in den
Stammdaten verzeichnet. Offensichtlich ist die Ware durch die Spediteure in die Lager zurückgebracht
worden, wo sie nicht mehr ordnungsgemäß weiterregistriert wurde.
 Die monatlichen Spediteur-Rechnungen scheinen zu hoch. Sie können aufgrund der pauschalen Abrechnung
den tatsächlichen Leistungen (Fahrten etc.) nicht zugeordnet werden.
 Einzelne Produktchargen scheinen einfach zu verschwinden.
 Die Zuordnung von Bestellung, Bezahlung und Stornierung ist nicht deutlich.
 Was geschieht mit den Retouren?
 Preovisionsabrecchnungen für die eigenen Mitarbeiter sind oft falsch, weil Leistungen (Anteile an Verkäufen)
nicht den konkreten Mitarbeitern zugeordnet werden können.
Erwartungen
Die Buchhaltung versucht alle Leistungen und Aufwende ordnungsgemäß zu erfassen. Es müssen Zahlungen
nach einer Prüfung freigegeben, werden z. B. die an die Spediteure. Dazu braucht die Buchhaltung Daten über:
 Welche Speditionsaktivitäten haben tatsächlich und mit welchem Aufwand stattgefunden?
 Wie viele Versandkosten wurden dem Kunden pro Bestellung in Rechnung gestellt?
 Bestellvorgänge sind auf deren korrekte Bezahlung hin zu prüfen:
o Welche Bestandteile wurden aufgrund von Stornierungen nicht gezahlt?
o Werden Rechnungen innerhalb der gesetzten Fristen gezahlt?
Informationsbedarfsanalyse: Antworten aus dem
Controlling
Bekannte Probleme
 Es gibt keine einfache Auswertung, in der Einkauf und Verkauf zusammenhängend dargestellt werden
können.
 Einzelne Produkte scheinen Unsummen an Verlusten zu liefern obwohl sie zu den Rennern im Verkauf
gehören. Entweder werden die Produkte zu billig angeboten, oder die den Kunden in Rechnung gestellten
Kosten sind falsch.
 Leichte Auswertungen über Artikelgruppen und Sparten sind nicht möglich, denn die Artikelgruppen decken
nicht alle verkauften Artikel und Produkte ab. Eine Spartenauswertung ist erst recht nicht möglich, weil es
auch keine korrekte Zuordnung zwischen Artikelgruppe und Sparte gibt.
 Die Work-around-Lösung für das Controlling: Alle verkauften Artikel und Produkte müssen von den
Verkaufsdaten her in jeder Berichtsperiode, also monatlich, neu zusammengestellt werden. Das bedeutet,
die Controller haben sich ein eigenes Produkte-Gruppierungssystem erstellt, in dem sie alle in den
Verkaufsdaten gefundenen Produkte und Leistungen neu erfassen.
 Die einzelnen Produkte können nicht immer genau einem bestimmten Lieferanten zugeordnet werden.
Deshalb ist es in manchen Produkte/Leistungen nicht möglich die Einkaufsaufwendungen zu bestimmen.
Hier fehlen genaue Daten.
Erwartungen
Das Controlling versucht die Rentabilität der einzelnen Geschäftsaktivitäten zu messen. Dabei werden auch sehr
detaillierte Daten über einzelne Produkte und Dienstleistungen benötigt.
E:\Kurse\DM_Kurs_Feb_2013\Multidimensionale Modellerieurng Workbook.docx
Workshopbegleitmaterial „Multidimensionale Modellierung und Star Schema Design“
Gewünschte Informationen sind:
 Was kosten Produkte im Einkauf, und mit welcher Rendite sind sie verkauft worden?
 Durchschnittliche Verweildauer von Artikeln,
 Umschlagszeiten von gekauften und verkauften Waren
 Bestandsmengen und Werte mit zetlichem Verlauf
o Wächst der Bestandswert oder fällt er
o Wertanteile von „Langliegern“
 Anteil an Retouren im Verhältnis zu Bestellungen
E:\Kurse\DM_Kurs_Feb_2013\Multidimensionale Modellerieurng Workbook.docx
7 /11
Workshopbegleitmaterial „Multidimensionale Modellierung und Star Schema Design“
8 /11
3. Die Übungsaufgaben
Übung 1 (Analysemodell):
In einer Analyse für ein neues Data Warehouse – Informationssystem - wurden die 4 Gruppen
1. Vertrieb
2. Controlling
3. Buchhaltung
4. Marketing
befragt.
Erstellen Sie zeichnerisch ein Analysemodell auf der Grundlage der Antworten aus den einzelnen Abteilungen.
(Hinweis:
Die gewählte Reihenfolge entspricht der Wichtigkeit aus der Management Perspektive. Angesichts der wenigen
Zeit, kann man das berücksichtigen.)
Übung 2 (Objektmodell):
Erstellen Sie zeichnerisch ein Objektmodells für die Service GmbH.
Folgende Prozesse sollten berücksichtigt werden
• Bestellungen
• Lieferungen durch Spediteure
• Retouren durch Spediteure
• Beauftragungen für
• Produktlieferungen (Orders)
• Dienstleistungen durch Partner
• Zahlungen
Die folgenden Objekte sollten mindestens vorhanden sein
• Partner
• Produkte
• Kunde
• Spediteur
Übung 3 (Konzeptionelles Modell):
Erstellen Sie zeichnerisch ein konzeptionelles Modell nach der M E/R – Notation.
Nutzen Sie dazu die Musterlösung aus der Übung 2.
Übung 4 (Implementierungsmodell - Starschema)
Erstellen Sie ein Star Schema aus dem M E/R Modell für die Service GmbH. (Siehe Musterlösung zu Übung 3).
E:\Kurse\DM_Kurs_Feb_2013\Multidimensionale Modellerieurng Workbook.docx
Workshopbegleitmaterial „Multidimensionale Modellierung und Star Schema Design“
4. Die Musterlösungen
Musterlösung zu Übung 1 (Analysemodell):
Musterlösung zu Übung 2 (Objektmodell):
E:\Kurse\DM_Kurs_Feb_2013\Multidimensionale Modellerieurng Workbook.docx
9 /11
Workshopbegleitmaterial „Multidimensionale Modellierung und Star Schema Design“
Musterlösung zu Übung 3 (Konzeptionelles Modell):
E:\Kurse\DM_Kurs_Feb_2013\Multidimensionale Modellerieurng Workbook.docx
10 /11
Workshopbegleitmaterial „Multidimensionale Modellierung und Star Schema Design“
Musterlösung zu Übung 4 (Implementierungsmodell - Starschema)
E:\Kurse\DM_Kurs_Feb_2013\Multidimensionale Modellerieurng Workbook.docx
11 /11