Business Intelligence mit Excel E-Book

Ignatz Schels

• Herausgeber: Carl Hanser Verlag GmbH & Co. KG
• Kategorie: Fachliteratur
• Sprache: Deutsch
• Veröffentlichungsjahr: 2023

Seitenzahl: 340

Beschreibung

- BI-Werkzeuge und Methoden (ETL, OLAP, ODBC u. a.)
- Externe Daten abrufen und analysieren mit Power Query
- Power Pivot im Datenmodell – Import, Verknüpfungen, Berechnungen
- Mit DAX-Funktionen kalkulieren und KPIs berechnen
- Excel-Daten mit Power BI visualisieren, Berichte und Dashboards in der Cloud veröffentlichen
- NEU in der 3. Auflage: KI-Funktionen in Power Query nutzen, Power Query und VBA, eigene Funktionen in M schreiben, Time Intelligence mit DAX, BI-Unterstützung und Dashboarding mit dynamischen Arrays

Die benutzerfreundlichen BI-Tools von Microsoft Excel sind die erste Wahl im Management und Controlling, Sie helfen, Trends und Risiken zu erkennen, Zusammenhänge zu begreifen und Entscheidungsgrundlagen zu verbessern. Bereiten Sie Unternehmensdaten auf verbunden mit intelligentem Reporting und Visualisierung. Dabei können Sie die Analysen ständig, sofort und auf allen Medien verfügbar halten.

Dieses Praxisbuch vermittelt die Grundlagen für den Einstieg in Business Intelligence. Es erklärt Begriffe und Methoden und zeigt, wie Daten aus unterschiedlichen Quellen mit Microsoft Excel importiert, transformiert und visualisiert werden. Lernen Sie, mit Power Query und der Skriptsprache M Daten aufzubereiten und üben Sie den Aufbau von Datenmodellen mit Power Pivot und DAX. Anschauliche Visualisierungen, Berichte und Dashboards erstellen Sie mit PivotTables, PivotCharts und Power BI.

Leseprobe

Ignatz Schels

Business Intelligence mit Excel

Datenanalyse und Reporting mit Power Query, Power Pivot und Power BI Desktop

Für Microsoft 365

3., aktualisierte Auflage

Ihr Plus – digitale Zusatzinhalte!

Auf unserem Download-Portal finden Sie zu diesem Titel kostenloses Zusatzmaterial.

Geben Sie auf plus.hanser-fachbuch.de einfach diesen Code ein:

plus-23si4-X9u7m

Der Autor:Ignatz Schels, Wolnzach

Alle in diesem Werk enthaltenen Informationen, Verfahren und Darstellungen wurden nach bestem Wissen zusammengestellt und mit Sorgfalt getestet. Dennoch sind Fehler nicht ganz auszuschließen. Aus diesem Grund sind die im vorliegenden Werk enthaltenen Informationen mit keiner Verpflichtung oder Garantie irgendeiner Art verbunden. Autoren und Verlag übernehmen infolgedessen keine juristische Verantwortung und werden keine daraus folgende oder sonstige Haftung übernehmen, die auf irgendeine Art aus der Benutzung dieser Informationen – oder Teilen davon – entsteht. Ebenso wenig übernehmen Autoren und Verlag die Gewähr dafür, dass die beschriebenen Verfahren usw. frei von Schutzrechten Dritter sind. Die Wiedergabe von Gebrauchsnamen, Handelsnamen, Warenbezeichnungen usw. in diesem Werk berechtigt also auch ohne besondere Kennzeichnung nicht zu der Annahme, dass solche Namen im Sinne der Warenzeichen- und Markenschutz-Gesetzgebung als frei zu betrachten wären und daher von jedermann benutzt werden dürften.Die endgültige Entscheidung über die Eignung der Informationen für die vorgesehene Verwendung in einer bestimmten Anwendung liegt in der alleinigen Verantwortung des Nutzers.

Aus Gründen der besseren Lesbarkeit wird auf die gleichzeitige Verwendung der Sprachformen männlich, weiblich und divers (m/w/d) verzichtet. Sämtliche Personenbezeichnungen gelten gleichermaßen für alle Geschlechter.

Bibliografische Information der Deutschen Nationalbibliothek:Die Deutsche Nationalbibliothek verzeichnet diese Publikation in der Deutschen Nationalbibliografie; detaillierte bibliografische Daten sind im Internet über http://dnb.d-nb.de abrufbar.

Dieses Werk ist urheberrechtlich geschützt.Alle Rechte, auch die der Übersetzung, des Nachdruckes und der Vervielfältigung des Buches, oder Teilen daraus, vorbehalten. Kein Teil des Werkes darf ohne schriftliche Genehmigung des Verlages in irgendeiner Form (Fotokopie, Mikrofilm oder ein anderes Verfahren) – auch nicht für Zwecke der Unterrichtsgestaltung – mit Ausnahme der in den §§ 53, 54 URG genannten Sonderfälle –, reproduziert oder unter Verwendung elektronischer Systeme verarbeitet, vervielfältigt oder verbreitet werden.

© 2024 Carl Hanser Verlag München, www.hanser-fachbuch.deLektorat: Sylvia HasselbachCopy editing: Walter Saumweber, Sandra GottmannUmschlagdesign: Marc Müller-Bremer, www.rebranding.de, MünchenUmschlagrealisation: Max KostopoulosSatz: Eberl & Koesel Studio, Kempten

Print-ISBN:        978-3-446-47952-4E-Book-ISBN:   978-3-446-48002-5E-Pub-ISBN:     978-3-446-48003-2

Inhalt

Titelei

Impressum

Inhalt

Vorwort

1 Einleitung

1.1 Excel im Business Intelligence – eine Chance

1.2 Werkzeuge und Methoden

1.2.1 Power Query

1.2.2 Power Pivot

1.2.3 Power BI

1.2.4 Power View

1.2.5 Power BI oder Excel?

1.2.6 Power Pivot oder Datenbank?

2 Grundlagen, Verfahren und Konzepte

2.1 Relationale und multidimensionale Datenbanken

2.2 Das Entity-Relationship-Modell (ERM)

2.3 Normalisierung und Faktentabellen

2.4 Sternschema

2.5 OLAP – die multidimensionale Datenbank

2.5.1 Von OLAP zu BI

2.5.2 Wie ein OLAP-Cube funktioniert

2.6 Datenimport und dynamische Verknüpfungen

2.6.1 ODBC

2.6.2 ODBC-Treiber verwalten

2.7 ODBC und Power Query

2.8 SQL

2.8.1 SQL-Abfragen mit Excel

3 Das Datenmodell

3.1 Regeln für Datenmodelle

3.2 Spezifikationen und Einschränkungen

3.2.1 Objekte im Datenmodell

3.3 Datenmodell anlegen

3.3.1 Über die PivotTable

3.3.2 Aus Tabellen über Power Pivot

3.3.3 Implizites und explizites Datenmodell

3.3.4 Aus externen Quellen

3.3.5 Von der Excel-Liste zum Datenmodell

3.3.6 Datenmodell entfernen

3.3.7 Datenmodelle zusammenführen

3.4 Datenmodelle komprimieren und Speicherplatz sparen

3.4.1 Nur importieren, was nötig ist

3.4.2 Weniger eindeutige Werte

3.4.3 SQL DISTINCT verwenden

3.4.4 Nur die benötigten Tabellen aus der Datenbank

3.4.5 Spaltenzahl reduzieren

3.5 Optionen für den Datenimport

4 Abrufen und Transformieren (Power Query)

4.1 Menüband und Symbole

4.2 ETL – der Prozess

4.3 Power Query installieren

4.4 Daten abrufen und transformieren

4.4.1 Aus Datei

4.4.2 Aus Datenbank

4.4.3 Aus Azure

4.4.4 Aus Power BI-Plattform

4.4.5 Aus Online-Diensten

4.4.6 Aus anderen Quellen

4.5 Datenquelleneinstellungen und Abfrageoptionen

4.5.1 Daten laden

4.5.2 Power Query-Editor

4.5.3 Sicherheit

4.5.4 Datenschutz

4.5.5 Regionale Einstellungen

4.5.6 Diagnose und Ablaufverfolgung

4.5.7 Aktuelle Arbeitsmappe

4.6 Abfrage erstellen

4.6.1 Eine neue Abfrage

4.6.2 Menü Abfrage/Abfrage bearbeiten

4.7 Der Power Query-Editor

4.7.1 Die Elemente

4.7.2 Abfragesprache einstellen

4.7.3 Eigenschaften

4.7.4 Abfrage abbrechen

4.7.5 Abfrage speichern

4.7.6 Transformationen anwenden

4.7.7 Gruppieren

4.7.8 Daten transformieren und entpivotieren

4.7.9 Abfrageschritte bearbeiten

4.7.10 Schritte benennen

4.7.11 Abfragen kombinieren

4.7.12 Abfragen kombinieren mit Fuzzy

4.7.13 Abfragen anfügen

4.8 Abfragen kopieren und transferieren

4.8.1 M-Code transferieren

4.8.2 Datenvorschau

4.8.3 Abfrage kopieren

4.8.4 Abfragen als ODC-Datei speichern

4.8.5 Abfrage an den Azure-Datenkatalog senden

4.9 Berechnete Spalten

4.9.1 IntelliSense aktivieren

4.9.2 Das Prinzip

4.10 Mit Parametern arbeiten

4.10.1 Abfrage mit Parameter auf Pfad und Dateiname

4.10.2 Abfrage verfeinern mit M-Code

4.10.3 Parameterabfrage mit M-Funktion

4.10.4 Parameterabfrage mit Funktion und WENN-Filter

4.11 Die Abfragesprache M

4.11.1 Die Bearbeitungsleiste

4.11.2 Erweiterter Editor

4.11.3 Die Syntax der M-Formel

4.11.4 Funktionsreferenzen und Hilfe

4.11.5 Abfrage auf Funktionsliste erstellen

4.11.6 Datumsberechnungen mit M

4.11.7 Kalendertabelle aus Start-/Endedatum erstellen

4.11.8 Eigene Funktionen schreiben

4.12 KI-Funktionen

4.12.1 Spalte aus Beispielen

4.12.2 KI-Insights

4.13 Power Query-Praxis

4.13.1 Fehlzeitenanalyse

4.13.2 Richtig filtern

4.13.3 Webabfrage mit Formularelement kombiniert

4.13.4 Vorsicht bei automatischer Typerkennung

4.13.5 Abfragen kombinieren mit Joins

4.13.6 Zellwerte in Abfragen verwenden

4.13.7 Daten aus Zellen im Kopfbereich übernehmen

4.13.8 Daten mit variablen Spaltenüberschriften

4.13.9 Dateien aus Ordnern

4.13.10 Zuletzt gespeicherte Datei importieren

4.13.11 Quelldaten mit unterschiedlichen Spaltenüberschriften

4.13.12 SAP-Berichte

4.14 Power Query und VBA

4.14.1 VBA lernen

4.14.2 Abfragen aktualisieren per VBA

4.14.3 Abfragen und Ereignisse

4.14.4 Abfrageskript per VBA bearbeiten

5 Power Pivot – Daten modellieren im Datenmodell

5.1 Grundlagen

5.1.1 Das kann Power Pivot

5.1.2 Technische Voraussetzungen

5.1.3 Office-Version

5.2 Power Pivot starten

5.2.1 Zurück zu Excel

5.3 Die Power Pivot-Menüs

5.3.1 Das Datei-Menü

5.3.2 Das Menü „Start“

5.3.3 Das Menü „Entwurf“

5.3.4 Das Menü „Erweitert“

5.4 Daten importieren

5.4.1 Zwischenablage

5.4.2 Aus Datenbank – Aus SQL Server

5.4.3 Aus Datenbank – Aus Access

5.4.4 Aus Datenbank – Analysis Services

5.4.5 Aus OLAP-Cubes per MDX

5.4.6 Aus Datendienst

5.4.7 Aus Azure SQL

5.4.8 Aus Excel

5.4.9 Aus Text oder CSV

5.4.10 Aus anderen Quellen

5.4.11 SQL-Abfragen

5.5 Tabelleneigenschaften

5.5.1 Datumstabelle

5.5.2 Die Tabellenregister

5.6 Arbeiten mit Spalten

5.6.1 Spalte einfügen

5.6.2 Spalten formatieren

5.6.3 Spalte löschen

5.6.4 Beschreibungen für Spalten und Measures

5.7 Hierarchien

5.7.1 Hierarchien erstellen

5.7.2 Hierarchien in PivotTables verwenden

5.8 Measures erstellen und verwalten

5.8.1 Implizite Measures

5.8.2 Diskrete Anzahl

5.8.3 Explizite Measures

5.9 Time-Intelligence und KPIs

5.10 PivotTable in Cube-Funktionen umwandeln

5.10.1 Cube-Funktionen-Übersicht

5.10.2 Beispiel: Firmenumsätze

5.11 Einführung in DAX

5.11.1 Was ist DAX?

5.11.2 DAX-Funktionen erstellen

5.11.3 DAX-Funktionen im Kontext

5.11.4 Die wichtigsten DAX-Funktionen

5.11.5 Hilfe zu DAX-Funktionen

5.12 Praxisbeispiel: Vorjahresvergleich

5.12.1 Die Datenquelle

5.12.2 Die Datumstabelle

5.12.3 Relationen

5.12.4 DAX-Measures

5.12.5 PivotTable und PivotChart

5.12.6 Visualisieren mit Power BI

6 Karten und 3D-Karten

6.1 Kartendiagramme

Beispiel: Einwohner pro Bundesland

Bundesländer mit Kategorie-Einfärbung

6.2 3D-Karten

7 BI-Analysen mit PivotTables und PivotCharts

7.1 Das Pivot-Prinzip

7.1.1 Von der Filtersumme zur PivotTable

7.1.2 Voraussetzungen

7.1.3 Auswertbare Daten

7.2 Datenquellen für PivotTables

7.2.1 Listen

7.2.2 Tabellen manuell erstellen

7.2.3 MS Query

7.2.4 Power Query

7.2.5 Power Pivot

7.3 PivotTable-Berichte erstellen

7.3.1 Berichte planen

7.3.2 PivotTables positionieren

7.4 Das Berichtslayout

7.4.1 Die Feldliste

7.4.2 Layouts und Formate

7.4.3 Layout als Standard speichern

7.4.4 Wertfeldeinstellungen

7.4.5 Zahlenformate

7.4.6 Bedingte Formatierung

7.4.7 PivotTables aktualisieren

7.4.8 Datenquelle ändern

7.4.9 PivotTable verschieben und löschen

7.4.10 Drilldown (Details anzeigen)

7.5 Datenschnitte

7.5.1 Ein neuer Datenschnitt

7.5.2 Datenschnitt formatieren

7.5.3 Datenschnittverbindungen

7.6 Die Zeitachse

7.6.1 Eine neue Zeitachse

7.6.2 Zeitachse bedienen

7.7 Berechnete Felder

7.7.1 Berechnete Felder und Elemente in der PivotTable

7.7.2 Berechnete Felder mit strukturierten Verweisen

7.7.3 Berechnete Felder mit Power Query

7.7.4 Berechnete Felder mit DAX in Power Pivot

7.8 Gruppieren

7.8.1 Textfeld gruppieren

7.8.2 Wertefeld gruppieren

7.8.3 Datumsfelder gruppieren

7.9 PivotCharts

7.9.1 Das Prinzip

7.9.2 Diagrammtypen

7.9.3 Beispiel: Altersstrukturanalyse

7.9.4 Kennzahlencockpits mit PivotCharts

8 Power BI

8.1 Das Prinzip

8.2 Power BI und Excel

8.3 Power BI Desktop

8.3.1 Komponenten und Ansichten

8.3.2 Daten importieren

8.3.3 Daten modellieren mit Power Query

8.3.4 Eine Datumstabelle mit DAX

8.3.5 Beziehungen und Measures mit Power Pivot

8.3.6 Visualisierungen

8.3.7 Interaktive Filter

8.4 Mit Power BI Services in der Cloud

8.4.1 Bericht veröffentlichen

8.5 Von Excel zu Power BI

8.5.1 Excel-Datenmodell importieren

8.5.2 Excel-Arbeitsmappe veröffentlichen

8.5.3 Excel-Daten exportieren

8.5.4 Excel-Daten mit Power BI laden

8.5.5 Power BI in Excel analysieren

9 Dynamische Arrays

9.1 Das Prinzip

9.1.1 #Überlauf!

9.1.2 Implizierte Schnittmenge

9.1.3 #-Operator

9.2 Dynamische Array-Funktionen

9.2.1 EINDEUTIG()

9.2.2 SORTIEREN()

9.2.3 FILTER()

9.2.4 SPALTENWAHL()

9.2.5 SEQUENZ()

9.2.6 TEXTVOR(), TEXTNACH(), TEXTTEILEN()

9.2.7 Alternative zur PivotTable

9.2.8 Stapeln

9.3 Diagramme und dynamische Arrays

9.3.1 Beispiel: Umsatzanalyse Sport

9.3.2 Datenquelle berechnen

9.3.3 Diagrammdaten auswählen

Vorwort

Liebe Lesende,

herzlich willkommen zur mittlerweile dritten Auflage von „Business Intelligence mit Excel“. Wir – Autor und Verlag – haben keinen Aufwand gescheut, um Ihnen die aktuellen Entwicklungen rund um das Thema „Business Intelligence mit Excel“ nahezubringen. Und das ist nicht immer einfach. Microsoft macht es uns Informationsbrokern nicht leicht, aktuell zu sein. Jährliche Software-Updates, neue Funktionen und geänderte Oberflächen sind eine Herausforderung für Autoren und Leser gedruckter Publikationen.

Sie haben sich also entschlossen, in „Business Intelligence“ einzusteigen. Vielleicht haben Sie ja schon erste Versuche mit BI-Tools gemacht und wollen Ihr Wissen jetzt vertiefen. Oder Sie gehören zu den klassischen Excel-Anwendern und suchen nach Lösungen, um Ihre Arbeit zu optimieren. Es kann auch sein, dass Sie den Auftrag bekommen haben, sich im Markt nach einer geeigneten Lösung für ein BI-basiertes Reporting umzusehen, und jetzt wollen Sie wissen, ob das auch mit Excel machbar ist und wo die Grenzen sind.

In jedem Fall sind Sie richtig beraten, sich mit den BI-Tools von Excel (und mit diesem Buch) zu beschäftigen. Die Tabellenkalkulation ist nach wie vor das wichtigste Werkzeug für Datenverarbeiter am PC und hat mit Business Intelligence massiv an Bedeutung gewonnen. Neue Tools sind an Bord – Power Query, Power Pivot, Power BI –, behalten Sie den Überblick und informieren Sie sich mit diesem Buch über Vor- und Nachteile, Einsatz und Nutzen der einzelnen Werkzeuge.

Für den praktischen Nutzen ist gesorgt. Wir sind seit Jahren zum Thema Business Intelligence mit Excel in Seminaren, Inhouse-Schulungen und Workshops unterwegs und haben bereits mehrere Projekte auf Basis der BI-Tools erfolgreich abgeschlossen. Schauen Sie auf unserer Webseite vorbei und schreiben Sie uns. Wir beraten Sie gerne:

Wir – das sind Ignatz Schels und Ignaz Schels junior. Von Ignaz Schels junior stammt das Buch zur neuen Programmiersprache für die BI-Tools,

M – Daten abfragen und verarbeiten mit Excel und Power BI,

das ebenfalls im Hanser-Verlag erschienen ist (ISBN 978-3-446-45588-7).

www.schels.de

Die Beispiele im Buch . . .

. . . sind praxisnah und mit viel Erfahrung ausgearbeitet. Zu den wichtigsten Themen finden Sie immer eine oder mehrere Übungen mit Schritt-für-Schritt-Anleitungen, und um diese nachzuvollziehen, brauchen Sie Beispieldaten. Diese stehen auf der Webseite des Verlags zum Download bereit:

Ihr Plus – digitale Zusatzinhalte!

Auf unserem Download-Portal finden Sie zu diesem Titel kostenloses Zusatzmaterial. Geben Sie auf plus.hanser-fachbuch.de einfach diesen Code ein:

plus-23si4-X9u7m

Zu jeder Übung finden Sie eine von externen Verknüpfungen freie Basisdatei sowie eine Lösungsdatei. Da diese in der Regel Verbindungen zu Datenquellen enthält, beziehen sich alle Verknüpfungen auf ein einheitliches Verzeichnis. Kopieren Sie deshalb alle Beispieldaten in diesen Ordner auf Ihrem Root-Verzeichnis (C:), dann funktionieren auch die Lösungen problemlos:

C:\HanserExcelBI\

Beispieldateien haben beliebige Bezeichnungen wie Umsatz2018.csv oder Getränkemarkt. xlsx. Lösungen beginnen immer mit dem Wort Lösungen:

Absatzanalyse Sportbekleidung.xlsx→ Lösung Absatzanalyse Sportbekleidung.xlsx

Jetzt wünsche ich Ihnen viel Spaß und viel Erfolg bei Ihrer Aufgabe, „Business Intelligence mit Excel“ kennenzulernen. Mit diesem Buch wird Ihnen das gelingen!

Ihr Autor Ignatz Schels

1Einleitung

Business Intelligence – was ist das?

Die offizielle Erklärung liefert wie immer Wikipedia, das kluge Weltlexikon:

Geschäftsanalytik, englisch Business Intelligence (Abkürzung BI) ist ein der Wirtschaftsinformatik zuzuordnender Begriff, der Verfahren und Prozesse zur systematischen Analyse des eigenen Unternehmens bezeichnet. Dies umfasst die Sammlung, Auswertung und Darstellung von Daten in elektronischer Form.

Ziel des Business Intelligence ist, Wissen über den gegenwärtigen Status und die zukünftigen Perspektiven des eigenen Unternehmens wie auch des geschäftlichen Umfeldes zu erzeugen. Die Erkenntnisse dienen der Unterstützung von Managemententscheidungen, die Methoden umfassen das Sammeln der Daten (Data Mining), das Aufbereiten und Analysieren der Daten und das Visualisieren über Dashboards.

Der Hype um das Thema ist groß und wird die nächste Zeit noch größer werden. Seit 2016 steigt die Zahl der Unternehmen, die sich die Entwicklung und Bereitstellung eines BI-Systems auf die To-do-Liste geschrieben haben, rasant an. Analysten sagen voraus, dass die Unternehmen ihre IT in den nächsten 10 Jahren vollständig von zentrierter Berichtsplattform in moderne, BI-unterstützte Analyseplattformen umrüsten werden.

Auch den Anbietern von BI-Software werden goldene Zeiten prognostiziert. Firmen wie Tableau, QlickView oder prevero haben sich auf BI spezialisiert, ERP-Anbieter wie SAP und Oracle führen BI-Lösungen im Portfolio. Microsoft rüstet Dynamics und SQL Server mit BI-Fähigkeiten aus und bietet mit Power BI eine vollständige Lösung von der Desktop-Gestaltungssoftware bis zur Cloud-Integration. Microsoft Office und speziell Excel wird im Power BI-Konzept vollständig integriert und spielt in diesem eine wichtige Rolle.

. . . und Self-Service Business Intelligence?

Das ist das Verfahren, bei dem der Anwender weitgehend eigenständig und unabhängig von der IT-Abteilung Daten importiert, aufbereitet und visualisiert. Die Voraussetzungen müssen natürlich geschaffen sein. Das Data Warehouse sollte verfügbar sein, Zugriffsrechte sollten eingeräumt und die Aktualität der Daten gesichert sein. Ist der Anwender mit den passenden Werkzeugen ausgestattet, entlastet er die IT-Abteilung von Routineaufgaben, bringt seine eigenen Daten mit ein und erhöht so die Aktualität und Qualität seiner Berichte.

Excel ist mehr dem Self Service Business Intelligence zuzuordnen. Die Einbindung der Office-App in die Cloud mit Power BI bietet größtmögliche Freiheit und Individualität in der Datenanalyse und im Reporting. Damit sind auch kleinere Lösungen realisierbar, denn nicht jede Aufgabe erfordert gleich ein umfangreiches BI-Konzept.

1.1Excel im Business Intelligence – eine Chance

Für Excel-Anwender wird es höchste Zeit, aus der Bastelecke herauszukommen, und BI ist die Lösung dafür. Obwohl das allgemeine Know-how mit der Zeit gestiegen ist, sehen viele Kalkulationen immer noch wie „Malen nach Zahlen“ aus. Häufig werden Tabellen ohne Einhaltung simpelster Regeln der Datenbankentwicklung als Datenbanken missbraucht, mit dem Effekt, dass Excel-User immer noch einen großen Anteil ihrer Zeit damit verbringen, Daten per Copy-and-paste zwischen Zellen zu verschieben.

Die Tatsache, dass in vielen Tabellenmodellen die Verweisfunktionen (SVERWEIS(), XVERWEIS()) inflationär oft zum Einsatz kommt, deutet darauf hin, dass sehr häufig relationale Verbindungen zwischen Datenmengen benötigt würden, die Anwender aber weder die Ahnung noch die technischen Möglichkeiten haben, diese über eine Datenbank vorzubereiten und die darin professionell aufbereiteten Daten nach dem Client-Server-Prinzip dynamisch in ihre Auswertungen zu holen.

Die Zukunft

Alle Daten, die in Fachbereichen wie Controlling, Finanzbereich, Personalverwaltung, Marketing u. a. benötigt werden, befinden sich an zentralen Stellen, die für jeden Anwender zugängig sind. Das kann ein gemeinsamer Ordner im Netz sein, in dem Exportdateien, Excel-Arbeitsmappen, CSV-Listen, Textdaten und alle anderen Datenbestände abgelegt werden. Idealerweise richtet man eine Plattform unter dem Dokumentensystem SharePoint ein und vergibt Berechtigungen für alle Beteiligten.

Die Daten werden zentral gepflegt und aktualisiert, es gibt keine Datasets mehr und keine Altbestände oder Kopien. Alle Datenbestände sind tagesaktuell. Für den Datenabzug aus Datenbanken erhalten die Anwender die Berechtigungen, die Sie brauchen, um ihre Daten abzuholen. SQL Server, Access, Oracle bieten zu jeder Zeit Zugriff auf Abfragen und Sichten. In OLAP-Datenbanken wie SQL Server Analysis Services (SSAS) werden passende Cubes eingerichtet, aus denen Anwender zu jeder Zeit Daten extrahieren können. Für ERP-Systeme (SAP, Microsoft Dynamics, Salesforce etc.) richtet der Admin Online-Zugriffe ein oder er exportiert die Daten in regelmäßigem Turnus.

Mitarbeiter der Fachabteilungen dürfen dabei aber nicht gezwungen sein, sich mit IT-spezifischen Strukturen vertraut zu machen, um die auf unterschiedlichste Quellen verteilten Daten nutzen zu können. Es empfiehlt sich, qualifizierte Kräfte zwischen die IT-Abteilung und die Fachabteilungen zu schalten. Diese Mitarbeiter sollten die Sprache der IT-Profis ebenso beherrschen wie die der Fachabteilungen. So würde dann beispielsweise von der IT-Abteilung eine Cube-Analyse mit Dimensionen und Measures und detaillierten Tupeln angefordert und dem Controller ein Soll/Ist-Vergleich mit Year-to-date, Vorjahreswerten, Forecast und Budgets präsentiert.

Risikovermeidung und Sicherheit

Business Intelligence eröffnet dem Excel-Anwender nicht nur neue Möglichkeiten, es zwingt ihn auch in eine Arbeitsweise, in der individuelle Tabellenkonstrukte keinen Platz mehr haben. Wirtschaftsprüfer und Revisoren können ein Lied singen von undurchschaubaren Excel-Patchworks mit Monsterformeln und wilden Bezügen. In Zukunft gibt es nur noch transparente Daten und ebensolche Visualisierungen. Das Risiko von Fehlberechnungen wird vermindert, Datenanalysen werden transparenter und sicherer.

Umdenken: Vom Einzelkämpfer zum Teamplayer

Der berüchtigte Einzelkämpfer hat ausgedient. Excel-Spezialisten, die zwar ein hohes Niveau bezüglich Funktionen, Matrixtechniken und Formelkonstruktion haben, ihr Wissen aber wie die Goldreserven von Fort Knox hüten, sind nicht mehr angesagt. Teamarbeit ist angesagt. Alle Mitarbeiter müssen sich regelmäßig gegenseitig über Datenaktualität und Datenbestände informieren, Aktionen absprechen und Ergebnisse teilen. Dass bei dieser Teamarbeit so mancher Report nicht doppelt gemacht und so manche Power Query-Abfrage oder DAX-Funktion geteilt werden kann, ist ein gewollter Nebeneffekt.

Neue Aufgaben – neue Ausbildung

Power Query, Power Pivot und Power BI sind für die meisten Anwender neu und unerforscht, und die vielen neuen Begriffe, die Aufgabe, neue Skriptsprachen (M) und neue Funktionen (DAX in Englisch!) lernen zu müssen, sind auch nicht gerade akzeptanzfördernd. Die Unternehmen sollten das durch gezielte Weiterbildung fördern, die nicht nur Excel, sondern das gesamte BI-Konzept zum Inhalt hat. Das klassische Excel-Seminar hat ausgedient, der BI-Workshop ist das neue Medium für Wissenstransfer.

1.2Werkzeuge und Methoden

Die Zeit, die der Excel-Anwender aufwendet, um seine Daten berichtsfähig zu machen, ist verlorene Zeit. Sie muss nämlich für jeden Bericht neu investiert werden. So manches lässt sich zwar schon automatisieren mit Excel-Funktionen (SVERWEIS!) und PivotTables, die nur noch kurz aktualisiert werden müssen. In der Praxis wollen die Berichte aber immer wieder auf die aktuellen Daten angepasst werden. Die eigentliche Aufgabe des Reportings, nämlich dem Berichtsempfänger die Informationen zu liefern, die er für die Entscheidungsfindung braucht, kommt in diesem Fall zu kurz. Neue Besen müssen also her, um die alten, überholten Arbeitstechniken auszukehren:

       Das Importieren der Daten und das Bereinigen, Ausputzen, Reparieren und Verschönern der Daten erledigt Power Query. Das Werkzeug ist bereits in Excel 2010 als Add-in verfügbar, ab Excel 2013 heißt es Abrufen und Transformieren (eine passende Beschreibung, aber der Begriff Power Query ist griffiger, weshalb wir in diesem Buch bei diesem bleiben).

       Mit Power Pivot werden Daten ähnlich wie in Datenbanken über Schlüssel miteinander verknüpft. Die Formelsprache DAX liefert die Funktionen für zusätzliche Berechnungen.

       Power BI ist der neue Star am Reporting-Himmel: Das Produkt gehört zwar weder zu Excel noch zum Microsoft Office- bzw. Microsoft 365-Paket, wird aber in Zukunft eine bedeutende Rolle bei Business Intelligence mit Excel spielen. Excel arbeitet eng mit Power BI zusammen. Excel-Daten werden in Dateiform als Datenquellen zur Verfügung gestellt oder dynamisch in Berichte und Dashboards eingebunden. Mit dem Add-in Power BI Publisher werden Excel-Daten eng mit Power BI verknüpft.

1.2.1Power Query

Power Query ist das Datenabfrage-Tool, das für den ETL-Prozess generiert wurde und diesen mit genialen Werkzeugen unterstützt. ETL steht für Extract, Transform und Load. Mit Power Query werden die Daten aus unterschiedlichsten Datenquellen importiert (Extract), bereinigt, umgewandelt, modelliert, neu berechnet oder kombiniert (Transfer) und in das Datenmodell für die BI-Analyse geladen (Load). Aus dem Datenmodell entstehen im letzten Schritt Berichte und Dashboards, aber dafür ist wieder ein anderes Tool (Power BI) zuständig.

Power Query-Erzeugnisse generieren dynamische Abfragen auf die Daten. Für eine einmal modellierte Datenmenge genügt ein Knopfdruck, und alle Änderungen aus den Quelldaten werden automatisch aktualisiert.

Power Query kommt mit einer eigenen Programmiersprache namens M. Alle Schritte, die im Rahmen des ETL-Prozesses nötig sind, um die Daten bereitzustellen, werden in einer Art Protokoll aufgezeichnet, und für die Digitalisierung der einzelnen Aktionen verwendet M eine spezifische Sprache mit entsprechend großem Sprachumfang. Wer sich bereits mit VBA, der Makrosprache von Excel, beschäftigt hat, kennt den Makrorecorder. Er zeichnet die Aktionen des Anwenders auf und wandelt sie in VBA-Befehle um. Das Ergebnis ist ein Makro, das alle Aktionen „auf Knopfdruck“ wieder ausführt.

Power Query-Abfragen entstehen ähnlich: Der Anwender importiert Daten, bereinigt diese, filtert und sortiert, passt Datentypen an, entfernt Spalten und berechnet zusätzliche Spalten. Alles wird Schritt für Schritt aufgezeichnet, und zum Schluss hält der Anwender eine Abfrage in den Händen, die nur noch aktualisiert werden muss, um alle diese Schritte wieder neu auszuführen. Die Programmiersprache M bietet darüber hinaus noch zusätzlich Elemente wie Variablen, Parameter, logische Strukturen und eine Art Schleifen an, mit denen die aufgezeichneten Abfragen noch verfeinert und verbessert werden können.

Bild 1.1Der Power Query-Editor

1.2.2Power Pivot

Mit Power Pivot bietet Excel ein Tool für die Integration großer Datenmengen aus unterschiedlichsten Quellen (Datenbanken, Excel-Mappen, Text/CSV, Internet u. a.). Power Pivot verwaltet die Daten in Form von Tabellen. Verknüpfungen in importierten Datenbank-Daten werden automatisch erkannt und übernommen, mit Hilfe einer Diagrammansicht können unterschiedlichste Datenquellen aber auch manuell über Schlüsselfelder verknüpft werden. Damit stellt Power Pivot eine besonders nützliche Komponente für Self Service Business Intelligence zur Verfügung.

Power Pivot ist keine Weiterentwicklung der guten alten PivotTable. Technisch gesehen ist Power Pivot die Oberfläche eines SQL-Datenbankservers, und das Datenmodell, auf dem das Tool aufbaut, ist eine OLAP-Datenbank, die unsichtbar im Hintergrund aufgebaut wird. Wer sich entscheidet, in Zukunft mit Power Pivot zu arbeiten, sollte sich deshalb gleich mit den Prinzipien des Datenbankdesigns anfreunden, denn darauf basiert das Tool. Felder, KPIs, Hierarchien, 1 : n-Beziehungen, Schlüsselfelder, linker und rechter Join, Datenbank-Speech – mit diesen Dingen sollten Sie sich vertraut machen. Dazu kommt die neue Formelsprache DAX mit englischsprachigen (!) Funktionen, die oft identisch mit vertrauten Excel-Funktionen (MAX, MIN, SUM) und doch so verschieden (RELATED, SUMX, PREVOIUSPERIOD) sind. Formeln heißen Measures, PivotTables sind die einzige Schnittstelle zu Excel. Das vertraute Tabellenblatt spielt mit Power Pivot keine Rolle mehr, es gibt nur noch das Datenmodell und die PivotTables.

Bild 1.2Power Pivot mit Daten- und Diagrammansicht

1.2.3Power BI

Power BI ist im Verbund der BI-Tools für die Visualisierung der Daten zuständig. Aber damit nicht genug – das Programm bietet sogar die Funktionalität der beiden anderen Tools Power Query und Power Pivot. In der Praxis lässt sich mit Power BI also ein komplettes BI-System ganz ohne Excel-Unterstützung aufbauen. Power BI kann Daten aus unterschiedlichsten Quellen importieren, bietet die Möglichkeit, Daten ähnlich wie Power Query zu modellieren und zu transferieren, und genauso wie in Power Pivot können Tabellen auch über Schlüsselfelder relational verknüpft werden. Die Skriptsprache M steht ebenso wie in Power Query zur Verfügung, und das Datenmodell lässt sich über DAX-Funktionen mit zusätzlichen Kalkulationen erweitern.

Für die Visualisierung stehen zahlreiche Diagrammelemente, vom Balkendiagramm bis zur Geo-Map, und unterstützende Werkzeuge (Datenschnitte, Bild- und Zeichenwerkzeuge u. a.) zur Verfügung. Das Angebot an Visualisierungswerkzeugen wächst ständig, Entwickler können diese sogar selbst entwerfen und individuellen Anforderungen anpassen. Berichte können online und mobil abgefragt, bearbeitet, zu Dashboards zusammengefasst und mit Kollegen geteilt werden.

Bild 1.3Power BI Desktop

Power BI integriert Excel in sein Cloud-Konzept. Mit der Möglichkeit, Excel-Daten in der Cloud zu veröffentlichen, an Berichte und Dashboards anzuhängen und dynamisch mit diesen auszutauschen, schafft Power BI alle Voraussetzungen, um Excel weiterhin als Client zu verwenden. Voraussetzung ist natürlich die Bereitschaft, die Cloud-Dienste von Microsoft zu nutzen (mit dem Report Server lässt sich auch eine Cloud-freie Lösung schaffen). Das Power BI-Konzept bietet sowohl für Einzelkämpfer als auch für Unternehmen jeder Größenordnung bezahlbare Pakete an.

Besonders vorteilhaft ist die Import-Funktion für Excel-Arbeitsmappen, die Power-Query-Abfragen und Power-Pivot-Datenmodelle enthalten. Excel-Anwender können so ihre BI-Anwendung in vertrauter Umgebung gestalten und anschließend an Power BI Desktop oder die Power BI-App in der Cloud übermitteln.

Bild 1.4Import von BI-Daten aus Excel-Arbeitsmappen

1.2.4Power View

Power View ist ein Add-in für Excel, das mit der Version 2013 eingeführt, in den Nachfolgeversionen aber nicht weitergepflegt wurde. Mit Power View wurden Daten in interaktiven Dashboards visualisiert. Power BI ist der designierte Nachfolger, und das hat folgende Gründe:

       Power View wurde im Original mit Silverlight erstellt, einem Browser-Plug-in für Web-Applikationen. Silverlight war als Konkurrenzprodukt zu Adobe Flash geplant. Microsoft hat nach zahlreichen Sicherheitslücken und mit der Ausrichtung auf HTML5 das Produkt 2015 eingestellt. Aus diesem Grund wird ActiveX und damit auch Silverlight in Edge, dem Windows 10-Browser, nicht mehr unterstützt. Google hat Silverlight ebenfalls aus seinem Browser Chrome verbannt.

       Power View wurde zwar noch auf HTML5 umgeschrieben, dabei gingen aber bereits einige Visualisierungen verloren. Zeitgleich startete die Entwicklung von Power BI, und auch wenn ein offizielles Statement dazu fehlt, wird Microsoft nur noch Power BI weiterentwickeln und Power View allmählich auslaufen lassen.

       In älteren Excel-Versionen (bis 2016) lässt sich Power View zwar noch in der Liste der COM-Add-ins aktivieren, das Tool funktioniert aber nicht.

1.2.5Power BI oder Excel?

Warum sollte sich ein Anwender mit Power Query und Power Pivot in Excel anfreunden, wenn Power BI Desktop diese Werkzeuge auch anbietet? Wird Power BI Desktop kurz- oder langfristig Excel ersetzen?

Excel-Anwender werden sich langsam, aber stetig mit den beiden Excel-Tools Power Query und Power Pivot anfreunden, ihre Daten aber auch weiter mit den Standardwerkzeugen (Diagramme, PivotCharts, Sparklines etc.) visualisieren. Die Visualisierungselemente von Power BI Desktop sind optisch wesentlich anspruchsvoller und leistungsfähiger als die Excel-Werkzeuge, und mit der Zeit wird Power BI mit Excel zusammenwachsen und die Rolle der Visualisierung übernehmen. Aber – Power BI wird Excel niemals ersetzen können.

Vorteile von Power BI Desktop gegenüber Excel

       In Power BI Desktop sind die beiden ETL-Werkzeuge Power Query und Power Pivot enthalten, die Handhabung ist weitgehend mit Excel identisch. BI-Anwendungen in Excel-Arbeitsmappen können importiert werden.

       Power BI Desktop hat optisch anspruchsvollere und technisch ausgereiftere Visualisierungswerkzeuge als Excel (Wasserfallcharts, Maps u. a.).

       Power BI Desktop ist die Basis für den Power BI Service. Mit diesem Werkzeug lassen sich schöne Visualisierungen erstellen und in Dateiform speichern. Um Reports im eigenen Unternehmen oder in der Cloud publizieren zu können, muss bei Microsoft ein kostenpflichtiges Konto beantragt werden. Die Möglichkeiten des Power BI Service reichen von automatischen Dashboards über Q&A (Frage und Antwort) bis zu KI (künstlicher Intelligenz).

Nachteile von Power BI gegenüber Excel

       Wichtige Werkzeuge von Excel stehen mit Power BI Desktop nicht zur Verfügung. Power BI Desktop hat keine PivotTables. Reports können nur aus Tabellen erstellt werden, und Aggregationen müssen aufwendig mit DAX konstruiert werden.

       Power BI Desktop bietet nicht den Komfort eines Tabellenkalkulationsprogramms. Es gibt nur Datenquellen und Visualisierungen. Die Oberfläche lässt sich zwar flexibel ähnlich wie eine PowerPoint-Folie mit Tabellen, Diagrammen und grafischen Objekten ausschmücken, aber Excel-Tabellenblätter sind variantenreicher mit Rahmen, Farben, bedingten Formaten, Formularelementen, Bereichsnamen, Gliederungen, grafischen Objekten, SmartArts u. a. Für Layout und Druck gibt es zahlreiche Möglichkeiten, und in jeder Zelle lässt sich eine Berechnung unterbringen. Funktionen müssen sich auch nicht an Tabellenspalten und -zeilen orientieren.

       Dashboarding und Cloud-basierende Berichte sind die Zukunft, aber dieser Komfort will bezahlt werden. Power BI Services in der Cloud kosten Geld, und zwar nicht wenig, wenn ein Unternehmen sich entschließt, die BI-Services für seine Mitarbeiter oder Kunden einzurichten.

       Nicht zu vergessen: VBA. Die Makrosprache bietet nach wie vor unerreichten Komfort für den Dialog mit dem Anwender und die Automatisierung von Prozessen. Power BI hat (noch) nichts Gleichwertiges zu bieten.

1.2.6Power Pivot oder Datenbank?

Macht es Sinn, Power Pivot an Stelle einer Datenbank einzusetzen? Wo sind die Vorteile dieses Tools, das sowohl in Excel als auch in Power BI Desktop zur Verfügung steht?

An den Datenmengen kann es nicht liegen. Power Pivot kann zwar wesentlich mehr als die 1 048 576 Zeilen von Excel einlesen, aber mehrere Millionen Datensätze sind auch für Access keine Herausforderung, und der große Bruder SQL Server hat damit noch weniger Probleme.

Mit dem DBMS Access lassen sich Daten wesentlich komfortabler aufbereiten als mit Power Pivot. Die Möglichkeit, Abfragen (Queries) zu erstellen und abzuspeichern, die Formulare als Dialogschnittstelle zum Benutzer, die Berichte und nicht zuletzt die gigantische VBA-Makrosprache bieten allen Komfort für die Entwicklung moderner Datenbanken.

Mit Access werden Daten erfasst, editiert und gelöscht (Read and Write). Power Pivot liefert nur eine Sicht auf gespeicherte oder per Verknüpfung zur Verfügung gestellte Daten (Read only). Um Daten abzuändern, muss der Benutzer Rechte und Zugriff auf externe Systeme (ERP, Datenbanken etc.) haben oder die Dateien (XLSX, CSV) öffnen und darin Änderungen vornehmen. Erst dann kann er seine Power Pivot-Auswertungen aktualisieren.

Access-Formulare können mit dem Einsatz von Formularwerkzeugen benutzerfreundlich gestaltet werden. Selbst unerfahrene Einsteiger werden mit Datenbankformularen beste Resultate erzielen. Die Power Pivot-Oberfläche lässt sich nicht gestalten, eine Automatisierung von Prozessen oder eine Oberfläche mit Formularelementen gibt es nicht.

Power Pivot ist also nur eine abgespeckte Version einer Datenbank, aber Beziehungen, Datentypen und berechnete Spalten sind wesentlich einfacher zu erlernen als Datenbankstrukturen, SQL-Abfragen und Formularelemente. Wer mit dem spärlichen Komfort leben kann und schnelle Resultate mit verknüpften Datenquellen braucht, wird Power Pivot auch den Vorzug vor Access & Co. geben. Und nicht zu vergessen seien die vielen Anwender, die von der IT ausgebremst werden, weil sie kein Access haben oder haben dürfen (Datenbanken machen Ärger und Arbeit!). Für sie ist Power Pivot nicht die beste, aber die einzige Lösung.

2Grundlagen, Verfahren und Konzepte

Business Intelligence mit Excel ist mehr als Excel. Neue Verfahren, neue Werkzeuge und neue Konzepte halten Einzug in die altehrwürdige Tabellenkalkulation. Wer sich noch auf dem Wissensstand von vor 10 Jahren ausruht und seinen Horizont auf Zellen, Tabellenblättern und Arbeitsmappen mit Formatierungen und einigen Funktionen begrenzt, sollte umdenken. Das Wissen um grundlegende Datenbankkonzepte und -formate wird für Business Intelligence ebenso gebraucht wie der sichere Umgang mit den BI-Tools.

2.1Relationale und multidimensionale Datenbanken

Die relationale Datenbank ist eine Erfindung aus den 70er-Jahren. Dr. Edgar F. Codd, ein Programmierer bei IBM, erfand das Modell der relationalen Datenbank. Er verwendete die Abfragesprache SEQUEL, aus der SQL hervorging, die Sprache, die heute noch Kern und Basis aller Datenbanksysteme ist. Edgar F. Codd entwickelte auch die Normalformen und die 12 Evaluierungsregeln als Anforderung für ein OLAP-System (Online Analytic Processing). Oracle kam als erstes Unternehmen im Jahr 1979 mit einem relationalen Datenbankmanagementsystem auf den Markt.

Mitte der 90er-Jahre nutzte jedes größere Unternehmen relationale Datenbanken. Aber mit der Zeit tauchte ein Problem auf: Die Datenbanken mit relationalen Verknüpfungen waren zwar ideal geeignet, um Daten auch in größeren Mengen zu speichern. Für die Erstellung von Berichten aus Transaktionsdaten erwiesen sich die Datenbanken jedoch als zu schwerfällig. Musste zum Beispiel ein Bericht über monatliche Umsätze erstellt werden, suchte das System in Millionen von Transaktionen, um eine einzelne Summe zu ermitteln. Nicht selten ging dabei die gesamte Serverwelt „in die Knie“, und an schnelle, Just-in-time-Informationen war gar nicht zu denken.

2.2Das Entity-Relationship-Modell (ERM)

Das Entity-Relationship-Modell (ERM) ist die Grundlage für einen Datenbankentwurf. Die Grundelemente sind Entitäten, Beziehungen und Attribute. Im ERM-Modell werden die grundlegenden Beziehungen einer relationalen Datenbank aufgestellt.

Bild 2.1Das Entity-Relationship-Modell

2.3Normalisierung und Faktentabellen

Normalisierung ist die Aufteilung der Attribute (Tabellenspalten) in mehrere Relationen unter Berücksichtigung der Normalisierungsregeln und Normalformen mit dem Ziel, eine Form zu erhalten, die keine vermeidbaren Redundanzen aufweist.

Erste Normalform

In der ersten Normalform sind alle Werte atomar, d. h. nicht mehr teilbar.

Zweite Normalform

In der 2. Normalform sind die Daten in Tabellen aufgeteilt, die Beziehungen zwischen den Daten werden über Schlüssel definiert.

Beziehungsarten

1 : 1

Jeder Datensatz in Tabelle A ist genau mit einem Datensatz in Tabelle B verbunden (nicht zu empfehlen).

1 : n

Jeder Datensatz in Tabelle A kann mit beliebig vielen Datensätzen in Tabelle B verbunden sein, einem Datensatz in Tabelle B ist aber nie mehr als ein Datensatz aus Tabelle A zugewiesen (häufigste Beziehungsart).

n : m

Jeder Datensatz in Tabelle A kann mit mehreren Datensätzen in Tabelle B und umgekehrt verbunden sein. Dazu wird eine dritte Tabelle benötigt, in der die Schlüssel aus Tabelle A und Tabelle B enthalten sind.

2.4Sternschema

Zu den wichtigsten Konzepten im Datenbankdesign gehört das Sternschema. Wer längere Erfahrung mit Excel hat, wird es gewohnt sein, die Daten in Listen- oder Tabellenform anzuordnen, um gezielt filtern, sortieren oder per PivotTable analysieren zu können. Relationen, d. h. Beziehungen zwischen zwei Tabellen lassen sich über Verweisfunktionen (SVERWEIS(), HVERWEIS() XVERWEIS()) realisieren, was aber nichts mit Datenbankmodellierung zu tun hat.

Für ein relationales Datenbankmodell, das für BI-Analysen geeignet ist, werden im ersten Schritt alle Tabellen gesammelt. Für jede Tabelle wird die erforderliche Anzahl Felder definiert. Hier gilt das Prinzip der Einfachheit: Daten, die nicht ins Reporting einfließen, haben im Datenmodell nichts verloren.

Bei der Sammlung der Daten sollte von Anfang an das Sternschema berücksichtigt werden. Dazu werden alle Daten in diese Tabellen unterteilt:

       Fakten

       Dimensionen

Fakten enthalten die „bewegten“ Daten: Verkäufe mit Mengenangaben, Umsätze mit Preisen, Bestellungen, Aufträge. Fast immer enthalten Faktentabellen Datumsfelder, die in den Auswertungen auf Monate, Jahre etc. aggregiert werden.

Dimensionen enthalten die Details zu den Daten in den Faktentabellen. Die „Stammdaten“ sind Produktpreislisten mit Beschreibungen, Kategorien, Herstellerangaben, Mitarbeiterdaten mit Eintrittsdatum, Kundenlisten mit Adressen u. a. Wichtigstes Merkmal ist die Eindeutigkeit: Jeder Datensatz wird durch die Zuweisung einer ID einmalig, es gibt das Produkt ebenso wie den Kunden oder den Mitarbeiter nur einmal.

Im Sternschema wird die Faktentabelle zentriert, und die Dimensionen ordnen sich mit den ID-Verknüpfungen rund um die Fakten an.

Bild 2.2Fakten im Zentrum, Dimensionen rundum – das Sternschema

2.5OLAP – die multidimensionale Datenbank

Die multidimensionale Datenbank ist eine Weiterentwicklung des relationalen Modells. Edgar F. Codd entwickelte sie mit dem Ziel, die Server von Berechnungen zu entlasten, die für aktuelle Berichtserstellungen benötigt wurden. Dazu werden die Aggregationen (Summen) zu Zeiten, in denen die Server nicht beschäftigt sind (nachts) in speziellen Datenbanken gespeichert, sogenannte OLAP-Cubes. Cubes müssen weniger zeitaufwendige Berechnungen durchführen, da ihre Ergebnisse vorberechnet sind.

Ein OLAP-Cube ist ein „Snapshot“ auf die Daten zu einem bestimmten Zeitpunkt, zum Beispiel am Ende des Tages, der Woche, des Monats oder des Jahres (Year-to-date).

2.5.1Von OLAP zu BI

Der Begriff Business Intelligence ist eng mit der OLAP-Technik verbunden. BI-Analysen sind in der Regel nichts anderes als Auswertungen von OLAP-Cubes, aber diese Analysen waren bisher wie die Herstellung und Wartung dieser Cubes nur von IT-Spezialisten und erfahrenen Programmierern durchführbar. Entsprechend zeitraubend und kostspielig gestalteten sich BI-Analysen.

Mit der Excel-PivotTable machte Microsoft den ersten Schritt in Richtung Self-Service-BI: Der einfache Excel-Anwender war in der Lage, Daten in unterschiedlichen Perspektiven darzustellen, zu filtern und mit arithmetischen oder statistischen Methoden (Summe, Anzahl, Mittelwert, Standardabweichung etc.) auszuwerten und diese in ein Datenmodell (OLAP-Cube) zu befördern.

Technisch gesehen entsteht nämlich bei der Anlage einer PivotTable nichts anderes als ein OLAP-Cube.

2.5.2Wie ein OLAP-Cube funktioniert

Ein Cube besteht aus Measures, Dimensionen und Dimensionsattributen.

       Measures sind numerische Datenwerte aus Formeln, die über Dimensionen hinweg Werte zählen, Summen berechnen, Durchschnitte oder Maximal-/Minimalwerte ermitteln.

       Dimensionen sind ähnlich wie Tabellen im relationalen Modell die Spalten- und Zeilenachsen des Cubes. Typische Dimensionen sind Produkte, Artikel, Kunden, Mitarbeiter, Vertriebsgebiete etc.

       Dimensionsattribute sind benannte Elemente innerhalb einer Dimension, ähnlich den Spalten einer Tabelle. Typische Dimensionsattribute für die Dimension Vertriebsgebiete wären zum Beispiel Europa, USA, EMEA (Europe and Middle East).

Im Prinzip besteht ein OLAP-Cube aus vielen einzelnen Boxen. Jede Box enthält eine Aggregation (Summe) aus den Daten einer Datenbank.

Fügen wir eine Dimension für die Daten aus dem Datenbankelement Kategorie hinzu.

Die Vorderseite des Würfels enthält die Summen der ersten Kategorie.

Die mittleren Boxen enthalten die Summen der zweiten Kategorie und die letzte Würfelreihe enthält die Summen der letzten Kategorie.

Fügen wir eine weitere Dimension Zeit mit den Monaten als Elemente hinzu.

Jetzt enthält die erste horizontale Reihe die Summen des ersten Monats.

Und die letzte horizontale Reihe speichert die Summen des letzten Monats.

Das nächste Element aus der OLAP-Technik ist das Measure. Ein Measure bezeichnet die Aggregatfunktion oder einfacher gesagt, was im Würfel berechnet wird. Fügen wir auf der Unterseite des Würfels ein erstes Measure für den Lagerbestand hinzu.

Die nächste Reihe bekommt das Measure Materialkosten.

Und schließlich werden noch die Summen für die Bestellmenge und die Verkaufsdaten eingebaut.

Füllen wir die einzelnen Boxen des Cubes anschließend mit Werten, lassen sich diese über den Schnittpunkt von Measure und Dimension eindeutig bezeichnen.

So lässt sich der Umsatz (Measure) für die Kategorie Sportartikel (Dimension 1) im Monat Januar (Dimension 2) eindeutig klassifizieren.

Noch ein Begriff aus der OLAP-Technologie: Eine Hierarchie wird eingeführt, um Dimensionen zu gruppieren und in Eltern/ Kinder (parent/child) zu unterteilen. Damit bietet der Cube die Möglichkeit, Daten per Drilldown von einer oberen Ebene in eine tiefere Ebene zu analysieren.

2.6Datenimport und dynamische Verknüpfungen

Der Umgang mit Daten hat sich in Excel grundlegend gewandelt und wird sich mit Business Intelligence weiter in die eingeschlagene Richtung entwickeln. Daten werden nicht in Ta­bellenblättern und Arbeitsmappen gespeichert und auch nicht mehr mit Formelkonstruktionen und Funktionstricks kombiniert und ausgewertet. Die Verweisfunktionen SVERWEIS, WVERWEIS und XVERWEIS als klassische Werkzeuge für einfache Beziehungen zwischen Datenbeständen haben weitgehend ausgedient, an ihre Stelle treten dynamische Verknüpfungen auf externe Daten, Datenbanken und Webseiten. Client/Server heißt die Devise, Excel wird zum Auswertungstool für externe Daten.

Dabei ist die dynamische Verknüpfung auf externe Daten ein „alter Hut“ für erfahrene Excel-Anwender. Schon in Excel 5.0 (1995) gab es die DDE-Schnittstelle für dynamische Datenverbindungen, in Office 97 war MS Query der Garant für sichere SQL-Abfragen an Datenbanken. Mit Business Intelligence kommen neue Tools ins Spiel, Power Query verbindet den Datenimport mit der Möglichkeit, die Daten gleich zu transformieren und aufzubereiten und Power Pivot stellt Beziehungen wie Datenbankprogramme zur Verfügung.

2.6.1ODBC

ODBC (Abkürzung für „Open Database Connectivity“) ist eine 1992 von Microsoft entwickelte Software-Schnittstelle, die den Zugriff aus einem Anwendungsprogramm auf unterschiedliche Datenbanken gewährleistet. Jeder Datenbankhersteller (Provider), der sich an den ODBC-Standard hält, kann Treiber zur Verfügung stellen, damit Office-Programme wie Excel auf seine Datenbanken zugreifen können.

2.6.2ODBC-Treiber verwalten

Für die Verwaltung dieser Treiber stellt Windows einen Dienst in der Systemsteuerung zur Verfügung:

       Wählen Sie in der Systemsteuerung Verwaltung/Datenquellen (ODBC) oder geben Sie bei Cortana (Windows 10) oder im Suchfeld von Windows 7 einfach „ODBC“ ein.

       Der ODBC-Datenquellen-Administrator verwaltet die installierten ODBC-Treiber. Holen Sie mit Hinzufügen weitere Treiber in die Liste.

       Benutzerdatenquellen sind spezifisch für das Microsoft Windows-Anmeldekonto, das zum Zeitpunkt des Erstellens der Datenquellen aktiv ist. Sie sind für keine anderen Anmeldekonten sichtbar. Für Anwendungen, die als Dienste auf einem Computer ausgeführt werden, sind sie nicht immer sichtbar.

       Systemdatenquellen sind für alle Anmeldekonten auf einem Client sichtbar. Für Anwendungen, die als Dienste auf einem Computer ausgeführt werden, sind sie immer sichtbar.

       Dateidatenquellen werden nicht in der Systemregistrierung gespeichert; sie werden in einer Datei auf dem Client gespeichert.

Bild 2.3ODBC-Datenquellen werden unter Windows verwaltet.

2.7ODBC und Power Query

ODBC ist eine alte Datenimport-Technik, die von Microsoft nicht mehr weiterentwickelt wird. Power Query bietet als Nachfolger von ODBC wesentlich mehr und deutlich bessere Verknüpfungen zu externen Datenquellen an. Da viele Anwender aber mit ODBC arbeiten, müssen die alten Werkzeuge auch in der neueren Version noch verfügbar sein. Excel hat ODBC in Version 2016 und den ersten Versionen von 2019 noch im Menüband in einer eigenen Gruppe neben der Power Query-Gruppe Daten abrufen und transformieren angeboten, diese dann aber mit Microsoft 365 geschlossen. ODBC-Treiber finden Sie noch unter Daten/Daten abrufen und transformieren/Daten abrufen/Aus anderen Quellen/Aus ODBC, diese Treiber funktionieren aber nicht mehr. Mit dem Legacy-Assistenten holen Sie ODBC zurück in die Menüstruktur:

       Wählen Sie Datei/Optionen/Daten.

       Schalten Sie unter Legacy-Datenimport-Assistenten anzeigen die alten ODBC-Treiber für Access, Webabfragen, Textdaten, SQL-Server, OData, XML und den alten Datenverbindungs-Assistenten ein.

Damit lassen sich die ODBC-Verbindungen über Daten/Daten abrufen und Transformieren/ Daten abrufen/Legacy-Assistenten wieder starten.

2.8SQL

SQL (Structured Query Language) ist eine Datenbanksprache zur Definition von Datenstrukturen in relationalen Datenbanken und zur Erstellung und Bearbeitung von Abfragen auf darauf basierende Datenbestände. SQL-Abfragen sind das Kernstück der relationalen Datenbank. Mit der Abfrage (View) werden Daten eingefügt, editiert, gelöscht oder einfach abgefragt.

SQL lässt sich in Excel-Abfragen auf externe Datenquellen einsetzen. Der Abfrage-Assistent bietet die Möglichkeit, SQL-Abfragen zu editieren oder neue Abfragen zu generieren. In Abfragen, die mit Power Query erstellt wurden, lässt sich der SQL-Befehl nicht editieren, SQL kann aber für gezielte Abfragen an externe Datenquellen eingesetzt werden.

Hier eine Liste mit SQL-Anweisungen, die per ODBC an eine Excel-Mappe übermittelt werden können:

Abfrage

Erklärung

SELECT * FROM [Tabelle1$]

Die Daten werden aus einem Tabellenblatt erhoben. $ und [] sind hier Pflicht.

SELECT * FROM [Tabelle1$A1:C10]

Die Daten werden aus einem Zellenbereich eines Tabellenblatts erhoben.

SELECT * FROM Name

Die Daten werden aus einem „definierten Namen“ im Gültigkeitsbereich „Arbeitsmappe“ erhoben. Der Bereich muss hier auf „Arbeitsmappe“ gestellt sein (Default-Wert), da ansonsten der Name nicht über ODBC angesprochen werden kann.

SELECT * FROM BereichA UNION SELECT * FROM BereichB

Die Daten werden per UNION SELECT aus zwei verschiedenen Bereichen eines Tabellenblatts erhoben.

SELECT * FROM Datei1.csv UNION SELECT * FROM Datei2.csv

Die Daten werden per UNION SELECT aus zwei verschiedenen CSV-Dateien erhoben. In der .ini-Datei wird der Ordner angegeben, in denen sich die Dateien befinden.

INSERT INTO [Tabelle1$] VALUES (Wert1,Wert2,Wert3,...)

Es werden Daten in ein Tabellenblatt eingefügt. INSERT INTO ist nur über ein Worksheet möglich (nicht über einen Bereich).

2.8.1SQL-Abfragen mit Excel

Die ODBC-Technik ist zwar mit der Einführung der BI-Tools etwas veraltet, SQL bietet aber die Möglichkeit, gezielte Anforderungen an externe Daten zu richten. Hier ein Beispiel aus der Praxis, wie mit ODBC und SQL-Abfragen Daten gezielt aus einer externen Quelle geholt werden.

Für die Abfrage verwenden Sie Microsoft Query. Das ist ein alter Abfrage-Assistent, der schon für Excel Version 3.0 entwickelt wurde, aber immer noch gute Dienste leistet.

Die Mappe Umsatz_Golfshop.xlsx enthält Produktumsätze in 12 Monatstabellen von Januar bis Dezember.

Bild 2.4Umsätze, aufgeteilt auf zwölf Monatsblätter

Legen Sie eine neue Arbeitsmappe an und starten Sie eine neue ODBC-Abfrage über MS Query. In Excel bis 2016 finden Sie dafür ein Symbol unter Daten/Externe Daten abrufen.

Ab Excel 2016 Pro finden Sie den Befehl in Power Query unter Daten/Daten abrufen und transformieren/Daten abrufen/Aus anderen Quellen/Aus Microsoft Query.