Logo und Schriftzug Karsten Ritz DV-Beratungen
Karsten Ritz





Sonstiges | Veröffentlichungen

Effiziente Auswertung großer Datenmengen aus verschiedensten Quellen mit Bordmitteln von Microsoft Excel

Kundeninfo 11/14

Microsoft Excel ist das weltweite Standardwerkzeug für die individuelle Datenverarbeitung am Arbeitsplatz. Anwender benutzen es täglich für Kalkulationen, zur Speicherung von Informationen und zur Auswertung bzw. Darstellung von Daten. Seit der Version 2010 bietet Microsoft Excel auch eingebaute Möglichkeiten zur effizienten Verarbeitung von bis zu mehreren Millionen von Datensätzen aus verschiedensten Quellen: Die PowerPivot-Technologie.

Microsoft Excel ist für viele Anwender aus ihrem Arbeitsalltag nicht mehr wegzudenken - und trotzdem gibt es Situationen, in denen Excel an seine Grenzen stößt. Arbeitsmappen lassen sich nur noch mit langer Wartezeit öffnen und bei komplexen Berechnungsmodellen verliert der Anwender schnell die Übersicht. Die hier vorgestellte Technologie kann dabei helfen, Excel effizienter einzusetzen. Die Voraussetzungen dazu gibt es seit der Version 2010 (Plug-In kann kostenlos von Microsoft heruntergeladen werden). Ab Excel 2013 ist die Technologie standardmäßig an Bord und muß nur aktiviert werden.


Abb.: PowerPivot-Ribbon

Abb.: PowerPivot-Ribbon

Problem: Excel "pur" hat Begrenzungen und die Verknüpfung mehrerer Tabellen ist ineffizient

Excel kann in normalen Arbeitsblättern maximal 1.048.576 Zeilen speichern. Das hört sich erst einmal nach einer sehr komfortablen Menge an. Allerdings ist durch die Zeilen/Spalten-Struktur von Excel jede einzelne Zelle ein mehr oder weniger eigenständiges Objekt, das separat verwaltet wird. Bei mehreren Spalten können sehr große Datenmengen zusammenkommen, auch weit unterhalb der maximal zulässigen Zeilenzahl.

Darüber hinaus werden Rechenoperationen für jede Zelle separat definiert und normalerweise "nach unten kopiert". Dadurch entstehen extrem hohe Antwortzeiten bei Änderungen. Zudem ist die Wartbarkeit eher gering. Es ist extreme Disziplin des Anwenders gefragt, damit Tabellenmodelle übersichtlich bleiben.

Durch die Matrixorganisation der Tabellen und fehlende Kompression im Hauptspeicher explodiert auch die Größe der Dateien und damit die Lade- und Speicherzeit. Es ist nicht ungewöhnlich, daß schnell Volumen >200MB Dateigröße entstehen, die dann auch auf gut ausgestatten Rechnern Ladezeiten von 30 Sekunden haben können.

Schließlich ist die Verknüpfung von mehreren Tabellen (z.B. Nachschlagen von Werten über die SVERWEIS()-Funktion oder die Nutzung von Schleifen in VBA-Programmcode) aufwendig in der Formulierung, unübersichtlich und sehr rechenintensiv.

Ein Datenmodell im Speicher (PowerPivot-Technologie)

Zur Lösung der Probleme von Excel "pur" bietet Microsoft seit der Version 2010 die PowerPivot-Technologie an.

Dabei wird innerhalb der Excel-Datei ein separater Speicherbereich genutzt, der als Datenmodell bezeichnet wird. Hier können Daten gespeichert, miteinander verknüpft und ausgewertet werden. Im Gegensatz zu Excel "pur" werden Daten spaltenweise komprimiert. Man gelangt in diesen Speicherbereich z.B. über das PowerPivot-Ribbon (siehe Abbildung oben).


Abb.: PowerPivot-Datenmodell

Abb.: PowerPivot-Datenmodell

Berechnungen werden nicht für einzelne Zellen ausgeführt, sondern einmal für eine gesamte Spalte definiert, was die Wartbarkeit verbessert und die Effizienz erhöht. Darüber hinaus können KPIs (Key Performance Indicators) und berechnete Felder erstellt werden. Die Ausdrücke sehen ähnlich wie in Excel "pur" aus und werden mit Hilfe der PowerPivot-Sprache DAX (Data Analysis eXpressions) formuliert.

Daten aus verschiedenen Quellen kombinieren

Die Daten für die PowerPivot-Datenmodelle können aus vielfältigen Quellen stammen:

  • Relationale Datenbanken (u.a. Microsoft SQL Server, Oracle, Microsoft Access oder beliebige Datenquellen mit ODBC-Schnittstelle)
  • Mehrdimensionale Quellen (Microsoft Analysis Services)
  • Datenfeeds (u.a. Berichte aus Microsoft Reporting Services oder Datenfeeds aus dem Internet)
  • Textdateien (Excel-Dateien, CSV-Dateien)

Abb.: Tabellenimport-Assistent

Abb.: Tabellenimport-Assistent

Die Tabellen im Datenmodell können mehrere Millionen Datensätze umfassen. Durch die Spaltenkompression sind sie wesentlich speichereffizienter als vergleichbare Tabellen in Excel "pur".

Die importierten Tabellen lassen sich alle insgesamt oder einzeln aktualisieren. Dabei bleiben berechnete Spalten, Felder und KPIs über mehrere Importvorgänge hinweg stabil, weil sie nicht für einzelne Zellen, sondern insgesamt definiert sind.

Für komplexere Importanforderungen steht Microsoft PowerQuery zur Verfügung, mit dessen Hilfe auch z.B. Tabellen aus WWW-Seiten oder XML-Dateien importiert werden können. Darüber hinaus sind mit PowerQuery mehrstufige automatisierbare Datentransformationen möglich, so daß Rohdaten vor dem Import in das PowerPivot-Datenmodell strukturiert aufbereitet werden können.

Auswertung per "Slice and dice"

Wenn die Daten im Datenmodell logisch angeordnet, miteinander verknüpft sind und die Berechnungen definiert sind, dann werden Auswertungen per Pivot-Tabelle erzeugt. Die Ergebnisse können wiederum in weiteren Tabellenblättern weiterverwendet werden und ggf. in das PowerPivot-Datenmodell zurückfließen.

Die Auswertung wird auch als "Slice and dice" bezeichnet ("to slice" wörtlich "aufschneiden"; "to dice" wörtlich "würfeln"). Gemeint ist damit, daß der Anwender die ihn interessierenden Aspekte aus dem Datenmodell herausschneidet und so anordnet, daß er eine optimale Sicht auf die Daten hat.

Durch die effiziente Speicherung der Daten im Hauptspeicher des Systems und die Optimierung der Datenstrukturen des Datenmodells sind die Antwortzeiten extrem kurz. Der Anwender kann praktisch in Echtzeit durch seine Daten "surfen".

Als Hilfsmittel in den Pivottabellen stehen u.a. folgende Elemente zur Verfügung:

  • Measures (die berechneten Werte des Datenmodells)
  • Dimensionen (die Kriterien, nach denen die Daten "aufgeschnitten" werden)
  • Hierarchien (hierarchisch angeordnete Dimensionen, die vom Allgemeinen zum Speziellen führen)
  • Filter (übergeordnete Kriterien, nach denen Daten selektiert werden)
  • Slicer (grafische Elemente, mit dessen Hilfe der Anwender Dimensionen interaktiv selektieren kann)

Im folgenden Beispiel sind aus dem Open Source Projekt "Open Geo Coordinates Database" Datensätze in das PowerPivot-Datenmodell importiert worden, die als Textdatei mit Tabulatortrennzeichen vorgelegen haben. Diese Datensätze wurden ergänzt durch das Verzeichnis der Gemeinden in Deutschland des statistischen Bundesamtes, das als Excel-Datei vorlag. Beide Tabellen wurden über den AGS (Amtlicher Gemeindesschlüssel) miteinander verknüpft.

Zu sehen ist die Auswertung mit

  • drei Metriken (Measures): Einwohner pro qkm, Anzahl der Einwohner (in 1000), Fläche in qkm
  • eine Dimensionshierarchie: Bundesland/Kreis/Ort (zu öffnen über das "+"-Symbol)
  • zwei Slicer: Vorwahl von PLZ-Bereich und Typ des Ortes
  • ein Filter: Level selektiert die Ebene der Geo-Koordinaten; "6" steht für Orte
  • ein Diagramm, das sich in Echtzeit an den Inhalt der Tabelle anpaßt (und umgekehrt)

Abb.: Beispielauswertung

Abb.: Beispielauswertung

Das Beispiel läßt sich einfach mit weiteren Daten anreichern, z.B.

  • Measures für Absatz, Umsatz und Deckungsbeiträgen aus der Warenwirtschaft
  • Dimensionen bzw. Hierarchien für Vertriebsgebiete und Vertriebsmitarbeiter aus dem CRM-System
  • Zeitdimensionen bzw. Hierarchien (Jahr/Quartal/Monat/Tag) zum Aufbau von Zeitreihen
  • Produktdimensionen bzw. Hierarchien (Segement/Produktgruppe/Produktlinie/Produkt)

Dimensionen können flexibel in Zeilen und/oder Spalten miteinander kombiniert werden. Es ist jederzeit möglich, über die "Zurück"-Funktion von Excel Arbeitsschritte zurückzunehmen und sich so komfortabel durch den Datenbestand zu arbeiten. Wie in Excel "pur" ist auch die Automatisierung per VBA (Visual Basic for Applications) möglich, um Routineabläufe zu vereinfachen.

Ergebnisse präsentieren

Wenn der Anwender die Daten zusammengestellt und ausgewertet hat, können die Ergebnisse mit Anderen geteilt werden. Der einfachste Weg dazu ist, die Excel-Datei auf einer Netzwerkfreigabe im lokalen Netzwerk abzulegen.

Darüber hinaus gibt es weitere Möglichkeiten, die zusätzlichen Nutzen bieten: Die Excel Dateien können z.B. auf einem SharePoint-Server abgelegt werden (im lokalen Netzwerk oder über ein externes Rechenzentrum, z.B. Microsoft Office 365). Darüber können gezielt einzelne Tabellen oder Diagramme präsentiert werden. Andere Anwender können automatisch per E-Mail über neue bzw. geänderte Daten informiert werden und die Excel-Dateien können in Workflows integriert werden.

Auch ohne Schreibrechte auf die Excel-Dateien können Anwender individuelle Auswertungen auf den PowerPivot-Datenmodellen vornehmen. Voraussetzung ist nur ein aktueller Internet-Browser. Es ist keine lokale Excel-Installation notwendig.

Beispiel von oben im Browser (gefiltert auf PLZ-Bereich 2 und nur Typ Stadt, Hierarchie Bundesland/Kreis/Ort geöffnet; Sie können über http://bit.ly/geotestpivot selber damit experimentieren):


Abb.: Präsentation PowerPivot auf SharePoint

Abb.: Präsentation PowerPivot auf SharePoint

Darüber hinaus bieten Microsoft PowerView und Microsoft PowerMap weitere Möglichkeiten der Präsentation (2D, 3D, Animationssequenzen, tabellarisch, grafisch).

In der folgenden Abbildung sehen Sie das Beispiel von oben als "Heatmap" (Einwohnerzahl, gefiltert im PLZ-Bereich 24xxx, nur größere Orte):


Abb.: Beispiel Heatmap

Abb.: Beispiel Heatmap

Die entsprechende Darstellung auf einer 3D-Weltkugel, die interaktiv gedreht oder in andere Programme übernommen werden kann:


Abb.: Beispiel Weltkugel

Abb.: Beispiel Weltkugel

Ausblick: Wenn PowerPivot doch an seine Grenzen stößt

Die Möglichkeiten von PowerPivot in Microsoft Excel zur Auswertung von Daten sind sehr umfangreich, besonders in Kombination mit PowerQuery, PowerView und PowerMap, die ebenfalls "Bordmittel" sind.

Microsoft bezeichnet diese Möglichkeiten als "Self-Service BI". BI ist die Abkürzung für Business Intelligence, d.h. Verfahren und Prozesse zur systematischen Analyse von Daten (siehe auch Wikipedia). Self-Service, also wörtlich Selbstbedienung, bedeutet, daß der Anwender diese Verfahren ohne Hilfe von zusätzlichen IT-Systemen oder von Personal der IT-Abteilungen in Eigenregie benutzen kann.

Trotz der Möglichkeiten von "Self-Service BI", die oben beschrieben worden sind, kann dieser Ansatz auch an Grenzen stoßen. Es gibt u.a. folgende Probleme beim Einsatz von Excel als Basis für BI:

  • Sicherheit
    Excel ist nicht dafür gemacht, Sicherheitsaspekte zu regeln. Wenn Daten aus einer Datenbank oder anderen Systemen in eine Excel-Datei importiert worden sind, dann liegen die Daten für jeden, der Leserechte auf die Datei hat, offen und können auch relativ einfach entwendet werden. Die Freigabe mit Leserechten über SharePoint (wie oben beschrieben), mildert das Problem zwar ab, löst es aber nicht grundsätzlich. Darüber hinaus können keine unterschiedlichen Benutzersichten auf die Daten erzeugt werden (Beispiel: Ein Vertriebsmitarbeiter soll nur die Vertriebsdaten sehen, die für sein Gebiet relevant sind). Die Lösung bestünde darin, mehrere Dateiversionen für unterschiedliche Benutzer vorzuhalten, was aufwendig und fehleranfällig ist.
  • Partitionierung
    Innerhalb von PowerPivot für Excel können mehrere sehr große Tabellen, die miteinander verknüpft sind, vorgehalten werden. Bei Aktualisierung der Daten kann jedoch nur die gesamte Tabelle auf einen Schlag aktualisiert werden. Das kann bei mehreren Millionen Datensätzen extrem lange dauern. Da Excel ein Werkzeug für individuelle Datenverarbeitung am Arbeitsplatz ist, ist die automatisierte Aktualisierung in Zeiten mit wenig Auslastung, also z.B. in der Nacht oder am Wochenende, auch eher schwer zu realisieren.
  • Datenbankgröße
    Theoretisch kann Excel sehr große Dateien verwalten. Bei der 32Bit-Version sind es 2 GB (die sich der Dateiinhalt mit dem Excel-Programmsystem selber teilt), die 64Bit-Version kann den gesamten zur Verfügung stehenden Hauptspeicher nutzen. In der Praxis werden diese theoretischen Möglichkeiten aber nicht ausgenutzt. Das Öffnen und Speichern von Dateien >500 MB dauert so lange, daß kaum ein Anwender die damit verbundenen Wartezeiten akzeptiert.

Die Lösung für diese Probleme besteht darin, ein serverbasiertes Datenbankmanagementsystem einzusetzen, das die genannten Probleme überwindet und noch weitere Leistungsmerkmale besitzt.

Die Lösung von Microsoft in diesem Bereich ist der SQL Server mit

  • SSIS: SQL Server Integration Services (Laden von Daten)
  • SSAS: SQL Server Analysis Services (Analyse der Daten)
  • SSRS: SQL Server Reporting Services (Erstellung von Berichten)

Mit Hilfe von SSAS kann Excel weiterhin die Benutzerschnittstelle für den Anwender bleiben und wird so bedient wie oben beschrieben. Unterschied: Die aufwendigen Prozesse werden von einem zentralen Server für mehrere Anwender gleichzeitig zur Verfügung gestellt.

Die mit PowerPivot für Excel erstellten Datenmodelle können mit einfachen Mitteln in SSAS-Modelle überführt werden, so daß bei der Entscheidung zum Wechsel zu einer serverbasierten BI-Lösung nicht wieder bei Null angefangen werden muß. PowerPivot für Excel ist somit auch als Prototyping-Lösung geeignet!

Anforderung von weiteren Informationen zu Microsoft Excel und Business Intelligence

Hier haben Sie die Möglchkeit, weitere Informationen zu den oben behandelten Themen anzufordern:

Geben Sie mir Infos zu...  
Microsoft Excel "pur"
PowerPivot-Technologie
Automatisierung mit VBA
Datenmodellierung und Integration verschiedener Datenquellen
Microsoft SharePoint / Office 365
Microsoft SQL Server (SSIS/SSAS/SSRS)
Andere Technologien
   
Ich bin interessiert an...  
Informationen
Schulung
Workshop
BI-Lösung am Arbeitsplatz
BI-Lösung unternehmensweit
   
Ich bin...  
Name und Firma:
E-Mail:
Telefon:
   
 
 

Startseite    Inhaltsverzeichnis    Referenzprojekte    Kontakt    Impressum

Systemanalyse    DV-Konzeption    Controlling    Systementwicklung    Internet/Intranet    Schulungen