Fallstudie 1:
Mehrdimensionale Analyse
mit Microsoft Excel
Themen
1. Mehrdimensionale Analyse in OLTP-Umgebungen (Online
Transaction Processing)
2. Grundlegende OLAP-Navigation mit Excel-Pivot-Tabellen
Aufgabe: Erstellen Sie eine PivotTable in Microsoft Excel, um
die Daten mit dem mehrdimensionalen Reporting zu
analysieren.
- mehrdimensionale Navigationstechniken
- spezielle Techniken zur Darstellung von Kennzahlen
EXCEL Datei: [Link]
Business Case
Robert Jones ist Manager mehrerer Vertriebsorganisationen bei
Global Bike Inc. und seine Verantwortung liegt in der Überwachung
und Steuerung der Vertriebsaktivitäten.
Er verfügt über mehrere OLTP-Systeme, die ihn bei der Aufzeichnung
der täglichen Transaktionen unterstützen. Am Ende eines jeden
Monats erhält er einen Bericht, der jeden Verkauf anzeigt.
Die Verkaufsdaten werden von der IT-
Abteilung in folgendem Format geliefert:
Obwohl dieser Bericht viele Informationen enthält, liegen die Informationen nicht in
einem Format vor, das Sie bei den Entscheidungen, die Sie treffen müssen, leicht
unterstützen kann.
Robert hat beschlossen, die PivotTables zu untersuchen, um nützlichere Berichte zu
erstellen.
1. Pivot-Tabelle erstellen
• Beginnen Sie mit einer allgemeinen Übersicht und erstellen
Sie eine Pivot-Tabelle, die den Umsatz (in $) in Deutschland
und den USA über die Jahre hinweg anzeigt. Stellen Sie die
Anzahl der Stellen ein.
Pivot-Tabellen sind einfach zu erstellen und
erfordern keine Kenntnisse von Excel-
Funktionen oder Formeln.
• Power Pivot ist ein Excel-Add-In, mit dem
riesige Datenmengen (Millionen Zeilen)
aus mehreren Quellen analysiert werden
können.
• Power Pivot ist eine Excel-Funktion, die das
Pivot-Tabellen Importieren, Bearbeiten und Analysieren
von Big Data ohne Geschwindigkeits-
mit Power /Funktionsverlust ermöglicht.
Pivot Power Pivot-Tabellen sind Pivot-Tabellen, die
es ermöglichen, große Datensätze sogar aus
verschiedenen Tabellen zu mischen, was
Extraktion, Kombination, Analyse und
leistungsstarke Filter und Diagramme
ermöglicht.
Power Pivot hat eine andere Berechnungs-
Engine als Excel und dies macht die
Datenanalyse schneller.
Erstellen einer Pivot-Tabelle mit Power Pivot
• Um die obige Anforderung zu lösen, wählen Sie
eine Zelle Ihrer Excel-Datenbank aus - Salesdata-
Arbeitsblatt (z. B. A1).
• Verwenden Power Pivot Tab -> Add to Data Model
(! Dieses neue Fenster nicht schließen!)
• Gehen Sie im Power Pivot-Fenster zu Home Tab ->
Pivot Table -> Pivot Table
• Wählen Sie “New Worksheet”
Ab sofort arbeiten wir am ersten Arbeitsblatt!
• Excel fügt eine leere PivotTable hinzu und wir befolgen die Anforderungen
• Wählen Sie alle numerischen Daten der Pivot-Tabelle (mit Ausnahme
der Jahre/years aus Spalten/Columns) aus, gehen Sie zur Home Tab ->
Number options -> Accounting USD und verwenden Sie Decrease
Format und decimals Option.
Or
Stellenzahl anpassen • Wählen Sie alle numerischen Daten der Pivot-Tabelle (mit Ausnahme
der Jahre/years aus Spalten/Columns) aus und verwenden Sie das
contextual Menü (Rechtsklick-Mausbedienung) -> Format Cells->
Number ->Accounting -> USD + Decimal places 0
Or
ROTATE
• Dabei ist ein unterschiedliches
Verhalten zweier Länder zu
beobachten: Während
Deutschland einen nahezu
kontinuierlichen
Umsatzanstieg verzeichnet,
sind die Umsätze in den USA
deutlich volatiler: Zwischen
leichten Rückgängen gibt es
2009 einen starken
Umsatzrückgang.
• Versuchen Sie jetzt, Gründe
für dieses Verhalten in den
Daten zu identifizieren! Tun
Sie dies, indem Sie die Daten
in Scheiben schneiden
(slicing).
• Um diese und alle anderen Anforderungen zu lösen, müssen Sie
zum Power Pivot-Fenster für Excel zurückkehren und von dort
aus eine neue Pivot-Tabelle erstellen
-> Home Tab -> Pivot Table -> Pivot Table -> New Worksheet
• ! Wenn das Power Pivot-Fenster geschlossen ist, gehen Sie
einfach zu Power Pivot Tab im ersten Arbeitsblatt (Salesdata)->
Manage!
• Erstellen Sie eine neue Pivot-Tabelle gemäß den Anforderungen:
SLICE
• Wir analysieren zuerst die
Daten aus Deutschland
und machen daher einen
Schnitt nach Ländern.
Stellen Sie die Kennzahl
auf Umsatz in lokaler
Währung um.
• Auf dem deutschen
Markt scheint alles in
Ordnung – 2008 gab es
nur einen kleinen
Rückgang.
Das Ergebnis der Anwendung des Slicing-Vorgangs auf Datenquellenebene.
Beachten Sie die Einführung des Währungsfilters/Currency (nur EUR wird
für deutsche Revenue ausgewählt)
Drill down zu Verkaufsorganisation (Sales
Organization) und Kunde (Customer)
• Drill down zu Sales
Organization
• Es besteht keine sichtbare
Abhängigkeit: Beide
Vertriebsorganisationen
verhalten sich insgesamt
ähnlich.
• 2008 gab es in
Norddeutschland
offensichtlich einen
Umsatzrückgang.
Pivot table und Field List
Drill down zu Verkaufsorganisation (Sales
Organization) und Kunde (Customer)
• Führen Sie in der
Verkaufsorganisation/sale
s organization einen
Drilldown zu CustDescr
durch, während Sie die
Ansicht erneut drehen.
• Wir sehen, dass unsere
größten Kunden in
Norddeutschland (Alster
& Rotlicht) 2008 nicht wie
gewohnt bestellt haben.
• Andererseits haben sich
die Verkäufe an Bavaria
Bikes in den letzten 10
Jahren fast verdoppelt –
das ist wirklich toll!
ROTATION
• Überprüfen Sie die Abhängigkeit von der Produktkategorie (ProdCat) und die Produktbeschreibung (ProdDescr) durch Drehen des Würfels.
• We can notice, the red variants of our Touring Bikes didn´t sell at all since 2014. We should consider taking them out of the German
catalogue. We also observe the introduction of a new product category (E-Bike) with 2 new products: the new E-bike Tailwind in 2010 and
the futuristic Hoverboard in 2015.
Analysieren Sie die neuen Märkte
genauer. PROZENTWERTE ANZEIGEN
• Vergleichen Sie die neuen Produkte mit anderen Fahrrädern/Bike (nicht mit Accesories). Filtern Sie die Jahre 2011 bis 2019 und
alle Bikes (Filter auf Division). Anstatt den Umsatz in absoluten Zahlen anzuzeigen, zeigen Sie die Werte als % der Grand Total
an. Sortieren Sie die Daten nach Revenue.
• Uns ist bewusst, dass das neue Tailwind-Bike bereits mehr als 5 % zum total Revenue beiträgt. Das Hoverboard wurde
innerhalb kürzester Zeit verkauft. Außerdem können wir sehen, dass die Red Varianten unserer Tourenräder Anfang 2010
langsam untergegangen sind.
Analysieren Sie die neuen Märkte genauer
DRILL-THROUGH
• Wir wollen
herausfinden, wann
das Hoverboard in
Deutschland verkauft
wurde. Schauen Sie
sich dazu alle
Bestellpositionen
doppelt sortiert nach
Datum an, nämlich
auf- und absteigend
• Wir erfahren, dass das
Hoverboard erstmals
am 01.08.2015 und
letztmalig am
24.01.2016 verkauft
wurde.
2. Analyse der US-Daten
• Filtern Sie das Land nach USA und entfernen Sie alle anderen Filter. Da der Umsatz in lokaler Währung angegeben ist, zeigen
Sie den Umsatz in $ an.
• Um den Kunden mit seiner Stadt in zwei getrennten Spalten anzuzeigen, verwenden Sie Report layout -> Show in tabular form
um die Zwischensummen/subtotals zur besseren Übersicht auszublenden.
• Wir beobachten, dass wir 2013 / 2014 einen sehr wichtigen Kunden verloren haben. Wir wollen dies genauer untersuchen und
verwenden ein Pivot-Diagramm, um die Daten zu visualisieren
Analyse der US-Daten.
Ein Diagramm/ Chart einfügen
• Filtern Sie Silicon Valley Bikes und Beantown Bikes und die Jahre 2012 bis 2014 (dice operator).
Führen Sie einen Drilldown zum Kalendermonat aus und fügen Sie ein Column chart ein.
• Wir stellen fest, dass die Einnahmen im Allgemeinen in der Frühjahrs-/Sommersaison immer hoch
sind. Der Umsatz von Silicon Valley Bikes ist ab September 2013 gleich Null. Wir müssen den
zuständigen Verkäufer fragen, was hier los ist.
Analyse der US-Daten.
Conditional Formatting/Bedingte Formatierung
• Um andere Effekte zu
erkennen, entfernen
wir den Filter auf
Kalenderjahr und
Kunde und schauen
uns die Monatswerte
im Vergleich zum
Vorjahr an.
• Dies zeigt einen
Rückgang im
September 2008
(Lehman-Krise), der
sich in den folgenden
Jahren teilweise
wieder erholt:
3. Berechnete Kennzahlen
• Bisher beschränkte Robert seine Untersuchung auf die
Kennzahl Umsatz und fand einige Vorfälle, die unseren
Umsatz beeinflussten, darunter neue Innovationen in
Deutschland (E-Bike / Hoverboard), Lehman-Krise,
Farbpopularität der Fahrräder und Verlust des größten
Kunden in den USA.
• Die Originaldaten beinhalten jedoch auch Rabatt und
Herstellkosten. Also erweitert Robert seine Analyse zu net
sales und contribution margin.
Hinweis: Die Kennzahlen können wie folgt berechnet werden
• Net sales = Revenue - Discount
• Contribution Margin = Net Sales - CostsUSD
• Contribution Margin Ratio = Contribution Margin / Revenue
3.1. Berechne Contribution Margin
Setzen Sie einen Filter auf US 2019 und entdecken Sie, welches
Produkt am besten abgeschnitten hat Contribution Margin in USD
3.2 Berechne Contribution Margin Ratio
• ... und welche Produktkategorie am meisten
Contribution Margin Ratio
Einzelarbeit / Hausaufgaben
• What product sold the least number of units?/Welches Produkt wurde am wenigsten
verkauft?
• What product category provided the most revenue in 2011?/Welche Produktkategorie
erzielte 2011 den höchsten Umsatz?
• What percentage did the off-road bikes contribute to the overall bicycle sales quantity? / Wie
viel Prozent trugen die Offroad-Bikes zur gesamten Fahrradabsatzmenge bei?
• In which three cities was this percentage significantly above the average?/ In welchen drei
Städten lag dieser Anteil deutlich über dem Durchschnitt?
• Bicycles and accessories are more likely to be bought in spring and summer as in fall and
wintertime. Find which both products are an exception to this rule./Fahrräder und Zubehör
werden eher im Frühjahr und Sommer gekauft als im Herbst und Winter. Finden Sie heraus,
welche beiden Produkte eine Ausnahme von dieser Regel darstellen.
• What is the yearly average Exchange from EUR to USD rate of the currency throughout the
years?/Wie hoch ist der jährliche durchschnittliche Wechselkurs der Währung von EUR zu
USD im Laufe der Jahre?