Einen Olap-Cube in Excel erstellen. Herstellen einer Verbindung zu einem OLAP-Datenwürfel. Arten von Datenquellen

Mit OLAP-Datenwürfeln (Online Analytical Processing) können Sie mehrdimensionale Daten effizient extrahieren und analysieren. Im Gegensatz zu anderen Datenbanktypen sind OLAP-Datenbanken speziell für die analytische Verarbeitung und die schnelle Extraktion aller Arten von Datensätzen daraus konzipiert. Tatsächlich gibt es mehrere wesentliche Unterschiede zwischen standardmäßigen relationalen Datenbanken wie Access oder SQL Server und OLAP-Datenbanken.

Reis. 1. Um einen OLAP-Cube mit einer Excel-Arbeitsmappe zu verbinden, verwenden Sie den Befehl Von Analytics Services

Laden Sie die Notiz in oder herunter

In relationalen Datenbanken werden Informationen als Datensätze dargestellt, die nacheinander hinzugefügt, gelöscht und aktualisiert werden. OLAP-Datenbanken speichern nur eine Momentaufnahme der Daten. In einer OLAP-Datenbank werden Informationen als einzelner Datenblock archiviert und sind nur für die bedarfsgesteuerte Ausgabe vorgesehen. Obwohl einer OLAP-Datenbank neue Informationen hinzugefügt werden können, werden vorhandene Daten selten bearbeitet und schon gar nicht gelöscht.

Relationale Datenbanken und OLAP-Datenbanken unterscheiden sich strukturell. Relationale Datenbanken bestehen typischerweise aus einer Reihe von Tabellen, die miteinander in Beziehung stehen. In manchen Fällen enthält eine relationale Datenbank so viele Tabellen, dass es sehr schwierig ist, festzustellen, wie sie miteinander verbunden sind. In OLAP-Datenbanken werden die Beziehungen zwischen einzelnen Datenblöcken vorab definiert und in einer Struktur namens OLAP-Cubes gespeichert. Datenwürfel speichern vollständige Informationen über die hierarchische Struktur und die Beziehungen der Datenbank, was die Navigation durch die Datenbank erheblich vereinfacht. Darüber hinaus ist es viel einfacher, Berichte zu erstellen, wenn Sie im Voraus wissen, wo sich die Daten befinden, die Sie extrahieren, und welche anderen Daten damit verbunden sind.

Der Hauptunterschied zwischen relationalen Datenbanken und OLAP-Datenbanken besteht in der Art und Weise, wie Informationen gespeichert werden. Daten in einem OLAP-Cube werden selten allgemein dargestellt. OLAP-Datenwürfel enthalten normalerweise Informationen, die in einem vorgefertigten Format dargestellt werden. Somit werden die Vorgänge des Gruppierens, Filterns, Sortierens und Zusammenführens von Daten in Würfeln durchgeführt, bevor sie mit Informationen gefüllt werden. Dadurch wird das Abrufen und Anzeigen der angeforderten Daten so einfach wie möglich. Im Gegensatz zu relationalen Datenbanken müssen die Informationen nicht ordnungsgemäß organisiert werden, bevor sie auf dem Bildschirm angezeigt werden.

OLAP-Datenbanken werden in der Regel von IT-Administratoren erstellt und verwaltet. Wenn Ihre Organisation nicht über eine Struktur verfügt, die für die Verwaltung von OLAP-Datenbanken verantwortlich ist, können Sie sich mit der Bitte um Implementierung an den Administrator der relationalen Datenbank wenden Firmennetzwerk zumindest einige OLAP-Lösungen.

Herstellen einer Verbindung zu einem OLAP-Datenwürfel

Um auf eine OLAP-Datenbank zuzugreifen, müssen Sie zunächst eine Verbindung zum OLAP-Cube herstellen. Gehen Sie zunächst zur Registerkarte „Multifunktionsleiste“. Daten. Drück den Knopf Aus anderen Quellen und wählen Sie den Befehl aus dem Dropdown-Menü aus Von Analytics Services(Abb. 1).

Wenn Sie den angegebenen Befehl des Datenverbindungsassistenten auswählen (Abb. 2). Seine Hauptaufgabe besteht darin, Ihnen beim Aufbau einer Verbindung zum Server zu helfen, der von Excel bei der Datenverwaltung verwendet wird.

1. Zuerst müssen Sie Excel mit Registrierungsinformationen versorgen. Geben Sie den Servernamen, den Anmeldenamen und das Datenzugriffskennwort in die Felder des Dialogfelds ein, wie in Abb. 2. Klicken Sie auf die Schaltfläche Weiter. Wenn Sie eine Verbindung mit herstellen Konto Windows, dann stellen Sie den Schalter ein Verwenden Sie die Windows-Authentifizierung.

2. Wählen Sie aus der Dropdown-Liste die Datenbank aus, mit der Sie arbeiten möchten (Abb. 3). Das aktuelle Beispiel verwendet die Analysis Services Tutorial-Datenbank. Sobald Sie diese Datenbank ausgewählt haben, werden Sie in der folgenden Liste aufgefordert, alle darin verfügbaren OLAP-Cubes zu importieren. Wählen Sie den gewünschten Datenwürfel aus und klicken Sie auf die Schaltfläche Weiter.

Reis. 3. Wählen Sie Arbeitsbasis Daten und OLAP-Würfel, das Sie für die Datenanalyse verwenden möchten

3. Im nächsten Assistentendialogfeld, dargestellt in Abb. 4 müssen Sie beschreibende Informationen über die Verbindung eingeben, die Sie erstellen. Alle Felder des in Abb. 4 müssen nicht ausgefüllt werden. Sie können das aktuelle Dialogfeld jederzeit ignorieren, ohne es auszufüllen. Dies hat keinerlei Auswirkungen auf Ihre Verbindung.

Reis. 4. Ändern Sie die Verbindungsbeschreibungsinformationen

4. Klicken Sie auf die Schaltfläche Bereit um die Verbindungserstellung abzuschließen. Auf dem Bildschirm erscheint ein Dialogfeld Daten importieren(Abb. 5). Stellen Sie den Schalter ein PivotTable-Bericht und klicken Sie auf OK, um mit der Erstellung der Pivot-Tabelle zu beginnen.

OLAP-Würfelstruktur

Wenn Sie eine Pivot-Tabelle aus einer OLAP-Datenbank erstellen, werden Sie feststellen, dass das Aufgabenfenster angezeigt wird Pivot-Tabellenfelder wird sich von dem für eine normale Pivot-Tabelle unterscheiden. Der Grund liegt in der Anordnung der PivotTable, um die Struktur des damit verbundenen OLAP-Würfels genau widerzuspiegeln. Um so schnell wie möglich durch einen OLAP-Cube zu navigieren, müssen Sie sich gründlich mit seinen Komponenten und deren Interaktion vertraut machen. In Abb. Abbildung 6 zeigt die Grundstruktur eines typischen OLAP-Würfels.

Wie Sie sehen, sind die Hauptkomponenten eines OLAP-Cubes Dimensionen, Hierarchien, Ebenen, Elemente und Kennzahlen:

Maße. Die Hauptmerkmale der analysierten Datenelemente. Gängige Beispiele für Dimensionen sind „Produkte“, „Kunde“ und „Mitarbeiter“. In Abb. Abbildung 6 zeigt die Struktur der Produktdimension.
Hierarchien. Eine vordefinierte Aggregation von Ebenen in einer bestimmten Dimension. Mit der Hierarchie können Sie zusammenfassende Daten erstellen und diese auf verschiedenen Ebenen der Struktur analysieren, ohne sich mit den Beziehungen zwischen diesen Ebenen befassen zu müssen. Im Beispiel in Abb. 6: Die Produktdimension verfügt über drei Ebenen, die in einer einzigen Hierarchie von Produktkategorien zusammengefasst sind.
Ebenen. Ebenen sind Kategorien, die in einer gemeinsamen Hierarchie zusammengefasst sind. Stellen Sie sich Ebenen als Datenfelder vor, die getrennt voneinander abgefragt und analysiert werden können. In Abb. 6 gibt es nur drei Ebenen: Kategorie, Unterkategorie und Produktname.
Mitglieder. Ein einzelnes Datenelement innerhalb einer Dimension. Der Zugriff auf Elemente erfolgt normalerweise über eine OLAP-Struktur aus Dimensionen, Hierarchien und Ebenen. Im Beispiel in Abb. Für die Ebene „Produktname“ sind 6 Mitglieder definiert. Andere Ebenen haben ihre eigenen Mitglieder, die in der Struktur nicht angezeigt werden.
Maßnahmen- Das sind echte Daten in OLAP-Würfeln. Kennzahlen werden in eigenen Dimensionen gespeichert, die als Kennzahldimensionen bezeichnet werden. Sie können Kennzahlen mithilfe einer beliebigen Kombination aus Dimensionen, Hierarchien, Ebenen und Elementen abfragen. Dieses Vorgehen nennt man „Slicing“-Maßnahmen.

Nachdem Sie nun mit der Struktur von OLAP-Cubes vertraut sind, werfen wir einen neuen Blick auf die PivotTable-Feldliste. Die Organisation der verfügbaren Felder wird übersichtlich und gibt keinen Anlass zu Beanstandungen. In Abb. Abbildung 7 zeigt, wie die Feldliste die Elemente einer OLAP-Pivot-Tabelle darstellt.

In der Liste der Felder in einer OLAP-Pivot-Tabelle werden Kennzahlen zuerst angezeigt und durch ein Summensymbol (Sigma) gekennzeichnet. Dies sind die einzigen Datenelemente, die in der VALUE-Region enthalten sein können. Danach werden in der Liste die Abmessungen angezeigt, die durch ein Symbol mit einem Tabellenbild gekennzeichnet sind. In unserem Beispiel wird die Kundendimension verwendet. Diese Dimension enthält eine Reihe von Hierarchien. Sobald die Hierarchie erweitert ist, können Sie die einzelnen Datenebenen anzeigen. Um die Datenstruktur eines OLAP-Cubes anzuzeigen, navigieren Sie einfach durch die Liste der Felder in der Pivot-Tabelle.

Einschränkungen für OLAP-Pivot-Tabellen

Denken Sie beim Arbeiten mit OLAP-PivotTables daran, dass Sie mit der PivotTable-Datenquelle in der Analysis Services OLAP-Umgebung interagieren. Dies bedeutet, dass jeder Verhaltensaspekt des Datenwürfels, von den Dimensionen bis zu den im Würfel enthaltenen Kennzahlen, auch von OLAP-Analysediensten gesteuert wird. Dies wiederum führt zu Einschränkungen bei den Operationen, die auf OLAP-Pivot-Tabellen ausgeführt werden können:

Sie können im VALUES-Bereich einer Pivot-Tabelle keine anderen Felder als Kennzahlen platzieren;
es ist nicht möglich, die zum Summieren verwendete Funktion zu ändern;
Sie können kein berechnetes Feld oder berechnetes Element erstellen.
Alle Änderungen an Feldnamen werden sofort verworfen, nachdem das Feld aus der PivotTable entfernt wurde.
Das Ändern von Seitenfeldparametern ist nicht zulässig.
Befehl nicht verfügbar ZeigenSeiten;
Option deaktiviert ZeigenUnterschriftenElemente wenn im Wertebereich keine Felder vorhanden sind;
Option deaktiviert Zwischensummen nach durch den Filter ausgewählten Seitenelementen;
Parameter nicht verfügbar HintergrundAnfrage;
nach einem Doppelklick in das Feld WERTE werden nur die ersten 1000 Datensätze aus dem Pivot-Tabellen-Cache zurückgegeben;
Kontrollkästchen deaktiviert OptimierenErinnerung.

Erstellen autonomer Datenwürfel

In einer Standard-Pivot-Tabelle werden die Quelldaten auf Ihrer lokalen Festplatte gespeichert. Somit können Sie diese jederzeit verwalten und auch ohne Zugriff auf das Netzwerk in der Struktur ändern. Dies gilt jedoch in keiner Weise für OLAP-Pivottabellen. Bei OLAP-Pivottabellen befindet sich der Cache nicht auf der lokalen Festplatte. Daher sofort nach dem Trennen von lokales Netzwerk Ihre OLAP-Pivot-Tabelle funktioniert nicht mehr. Sie können in einer solchen Tabelle kein einziges Feld verschieben.

Wenn Sie dennoch OLAP-Daten analysieren müssen, wenn Sie nicht mit einem Netzwerk verbunden sind, erstellen Sie einen Offline-Datencube. Dies ist eine separate Datei, die den Pivot-Tabellen-Cache darstellt. In dieser Datei werden OLAP-Daten gespeichert, die nach der Trennung vom lokalen Netzwerk angezeigt werden. Um einen eigenständigen Datencube zu erstellen, erstellen Sie zunächst eine OLAP-Pivot-Tabelle. Platzieren Sie den Cursor in der Pivot-Tabelle und klicken Sie auf die Schaltfläche OLAP-Tools Kontextregisterkarte „Analyse“, im Satz der Kontextregisterkarten enthalten Arbeiten mit Pivot-Tabellen. Wählen Sie ein Team aus Offline-Modus OLAP(Abb. 8).

Auf dem Bildschirm erscheint ein Dialogfeld Einstellungen Batterielebensdauer OLAP(Abb. 9). Drück den Knopf Erstellen Sie eine Offline-Datendatei. Das erste Fenster des Data Cube File Creation Wizard erscheint auf dem Bildschirm. Drück den Knopf Weiter um den Vorgang fortzusetzen.

Geben Sie im zweiten Schritt (Abb. 10) die Dimensionen und Ebenen an, die in den Datenwürfel aufgenommen werden sollen. Im Dialogfeld müssen Sie die Daten auswählen, die aus der OLAP-Datenbank importiert werden sollen. Es müssen nur die Abmessungen ausgewählt werden, die nach dem Trennen des Computers vom lokalen Netzwerk benötigt werden. Je mehr Dimensionen Sie angeben, desto größer wird der autonome Datenwürfel.

Drück den Knopf Weiter um mit dem dritten Schritt fortzufahren (Abb. 11). In diesem Fenster müssen Sie Mitglieder oder Datenelemente auswählen, die nicht in den Cube aufgenommen werden sollen. Wenn das Kontrollkästchen nicht aktiviert ist, wird das angegebene Element nicht importiert und belegt unnötig Platz auf Ihrer lokalen Festplatte.

Geben Sie den Speicherort und Namen des Datenwürfels an (Abbildung 12). Daten-Cube-Dateien haben die Erweiterung .cub.

Nach einiger Zeit speichert Excel den Offline-Datenwürfel im angegebenen Ordner. Um es zu testen, doppelklicken Sie auf die Datei. Dadurch wird automatisch eine Excel-Arbeitsmappe generiert, die eine Pivot-Tabelle enthält, die dem ausgewählten Datenwürfel zugeordnet ist. Nach der Erstellung können Sie den Offline-Datenwürfel an alle interessierten Benutzer verteilen, die im Offline-LAN-Modus arbeiten.

Sobald Sie mit dem lokalen Netzwerk verbunden sind, können Sie die Offline-Daten-Cube-Datei öffnen und sie und die entsprechende Datentabelle aktualisieren. Bitte beachten Sie, dass der Offline-Datencube zwar verwendet wird, wenn kein Netzwerkzugriff besteht, er jedoch aktualisiert werden muss, wenn die Netzwerkkonnektivität wiederhergestellt ist. Der Versuch, einen Offline-Datencube zu aktualisieren, nachdem die Netzwerkverbindung unterbrochen wurde, schlägt fehl.

Verwendung von Datenwürfelfunktionen in Pivot-Tabellen

Datencube-Funktionen, die in OLAP-Datenbanken verwendet werden, können auch über eine Pivot-Tabelle ausgeführt werden. IN veraltete Versionen In Excel hatten Sie erst nach der Installation des Analysis Pack-Add-Ins Zugriff auf die Datencube-Funktionalität. In Excel 2013 sind diese Funktionen in das Programm integriert und stehen daher zur Verfügung. Um ihre Fähigkeiten vollständig zu verstehen, schauen wir uns ein konkretes Beispiel an.

Einer der meisten einfache Wege Um die Funktionen eines Datenwürfels zu erlernen, müssen Sie eine OLAP-Pivot-Tabelle in Datenwürfelformeln konvertieren. Dieses Verfahren ist sehr einfach und ermöglicht es Ihnen, schnell Datenwürfelformeln zu erhalten, ohne sie von Grund auf neu erstellen zu müssen. Das Grundprinzip besteht darin, alle Zellen in der Pivot-Tabelle durch Formeln zu ersetzen, die mit der OLAP-Datenbank verknüpft sind. In Abb. Abbildung 13 zeigt eine Pivot-Tabelle, die einer OLAP-Datenbank zugeordnet ist.

Platzieren Sie den Cursor an einer beliebigen Stelle in der Pivot-Tabelle und klicken Sie auf die Schaltfläche OLAP-Tools kontextbezogene Multifunktionsleisten-Registerkarte Analyse und wählen Sie ein Team aus In Formeln umwandeln(Abb. 14).

Wenn Ihre Pivot-Tabelle ein Berichtsfilterfeld enthält, wird das in Abbildung 1 gezeigte Dialogfeld auf Ihrem Bildschirm angezeigt. 15. In diesem Fenster können Sie festlegen, ob Sie die Dropdown-Listen der Datenfilter in Formeln umwandeln möchten. Wenn die Antwort „Ja“ lautet, werden die Dropdown-Listen entfernt und stattdessen statische Formeln angezeigt. Wenn Sie in Zukunft Dropdown-Listen verwenden möchten, um den Inhalt der Pivot-Tabelle zu ändern, deaktivieren Sie das einzige Kontrollkästchen im Dialogfeld. Wenn Sie im Kompatibilitätsmodus an einer PivotTable arbeiten, werden Datenfilter automatisch und ohne vorherige Warnung in Formeln umgewandelt.

Nach einigen Sekunden sehen Sie anstelle der PivotTable Formeln, die auf den Datenwürfeln ausgeführt werden und eine Ausgabe im Fenster bereitstellen Excel erforderlich Information. Bitte beachten Sie, dass dadurch zuvor angewendete Stile entfernt werden (Abb. 16).

Reis. 16. Schauen Sie sich die Formelleiste an: Die Zellen enthalten die Datenwürfelformeln

Da die angezeigten Werte nicht mehr Teil des PivotTable-Objekts sind, können Sie Spalten, Zeilen und berechnete Elemente hinzufügen, sie mit anderen externen Quellen kombinieren und Ihren Bericht anpassen. verschiedene Wege, einschließlich Ziehen und Ablegen von Formeln.

Berechnungen zu OLAP-Pivot-Tabellen hinzufügen

In früheren Excel-Versionen waren in OLAP-Pivot-Tabellen keine benutzerdefinierten Berechnungen möglich. Dies bedeutet, dass es nicht möglich war, eine zusätzliche Analyseebene zu OLAP-PivotTables hinzuzufügen, so wie es möglich ist, berechnete Felder und Elemente zu regulären PivotTables hinzuzufügen (für weitere Informationen stellen Sie bitte sicher, dass Sie mit diesem Material vertraut sind, bevor Sie fortfahren). Lesen).

Excel 2013 führt neue OLAP-Tools ein – berechnete Kennzahlen und berechnete Elemente von MDX-Ausdrücken. Sie sind nicht mehr auf die Verwendung der von Ihrem DBA bereitgestellten Kennzahlen und Elemente in Ihrem OLAP-Cube beschränkt. Durch die Erstellung benutzerdefinierter Berechnungen erhalten Sie zusätzliche Analysemöglichkeiten.

Einführung in MDX. Wenn Sie eine PivotTable mit einem OLAP-Cube verwenden, stellen Sie MDX-Abfragen (Multidimensional Expressions) an die Datenbank. MDX ist eine Abfragesprache, die zum Abrufen von Daten aus mehrdimensionalen Quellen (z. B. OLAP-Cubes) verwendet wird. Wenn eine OLAP-PivotTable geändert oder aktualisiert wird, werden die entsprechenden MDX-Abfragen an die OLAP-Datenbank gesendet. Die Ergebnisse der Abfrage werden an Excel zurückgegeben und im PivotTable-Bereich angezeigt. Dies ermöglicht das Arbeiten mit OLAP-Daten ohne lokale Kopie Pivot-Tabellen-Cache.

Wenn Sie berechnete Kennzahlen und MDX-Elemente erstellen, verwenden Sie die MDX-Sprachsyntax. Mithilfe dieser Syntax ermöglicht eine Pivot-Tabelle die Interaktion von Berechnungen mit dem OLAP-Datenbank-Backend. Die im Buch besprochenen Beispiele basieren auf grundlegenden MDX-Designs, die Neues veranschaulichen Excel-Funktionen 2013. Wenn Sie komplexe berechnete Kennzahlen und MDX-Mitglieder erstellen müssen, müssen Sie sich die Zeit nehmen, mehr über die Funktionen von MDX zu erfahren.

Erstellen Sie berechnete Kennzahlen. Eine berechnete Kennzahl ist die OLAP-Version eines berechneten Felds. Die Idee besteht darin, ein neues Datenfeld basierend auf einigen mathematischen Operationen zu erstellen, die an vorhandenen OLAP-Feldern ausgeführt werden. Im Beispiel in Abb. 17 wird eine OLAP-Übersichtstabelle verwendet, die die Liste und Menge der Waren sowie die Einnahmen aus dem Verkauf der einzelnen Waren enthält. Wir müssen eine neue Kennzahl hinzufügen, die den Durchschnittspreis pro Einheit eines Artikels berechnet.

Analyse Arbeiten mit Pivot-Tabellen. Im Dropdown-Menü OLAP-Tools Menüpunkt wählen (Abb. 18).

Reis. 18. Wählen Sie einen Menüpunkt aus Berechnetes MDX-Maß

Auf dem Bildschirm erscheint ein Dialogfeld Erstellen Sie eine berechnete Kennzahl(Abb. 19).

Folge diesen Schritten:

2. Wählen Sie die Kennzahlengruppe aus, in der sich die neu berechnete Kennzahl befinden soll. Wenn Sie dies nicht tun, platziert Excel die neue Kennzahl automatisch in der ersten verfügbaren Kennzahlgruppe.

3. Auf dem Feld MDX-Ausdruck(MDX) Geben Sie den Code ein, der die neue Kennzahl angibt. Um den Eingabevorgang zu beschleunigen, verwenden Sie die Liste auf der linken Seite, um vorhandene Kennzahlen auszuwählen, die in den Berechnungen verwendet werden sollen. Doppelklicken Sie auf die gewünschte Kennzahl, um sie dem MDX-Feld hinzuzufügen. Berechnen Durchschnittspreis Bei den Stückverkäufen wird folgender MDX verwendet:

4. Klicken Sie auf OK.

Achten Sie auf den Knopf Überprüfen Sie MDX, das sich im unteren rechten Teil des Fensters befindet. Klicken Sie auf diese Schaltfläche, um zu überprüfen, ob die MDX-Syntax korrekt ist. Wenn die Syntax Fehler enthält, erscheint eine Meldung.

Wenn Sie mit der Erstellung Ihrer neuen berechneten Kennzahl fertig sind, gehen Sie zur Liste Pivot-Tabellenfelder und wählen Sie es aus (Abb. 20).

Der Umfang einer berechneten Kennzahl gilt nur für die aktuelle Arbeitsmappe. Mit anderen Worten: Berechnete Kennzahlen werden nicht direkt im OLAP-Server-Cube erstellt. Dies bedeutet, dass niemand auf das berechnete Maß zugreifen kann, es sei denn, Sie öffnen es allgemeiner Zugang Zu Arbeitsmappe oder Sie veröffentlichen es nicht im Internet.

Erstellen Sie berechnete MDX-Mitglieder. Ein berechnetes MDX-Element ist die OLAP-Version eines regulären berechneten Elements. Die Idee besteht darin, basierend auf einigen ein neues Datenelement zu erstellen mathematische Operationen, durchgeführt für vorhandene OLAP-Elemente. Im Beispiel in Abb. 22 wird eine OLAP-Pivottabelle verwendet, die Umsatzinformationen für 2005–2008 enthält (mit einer vierteljährlichen Aufschlüsselung). Angenommen, Sie möchten durch Erstellen Daten für das erste und zweite Quartal aggregieren neues Element Erstes Halbjahr. Wir werden auch Daten zum dritten und vierten Quartal zusammenfassen und so ein neues Element „Second of Year“ bilden.

Reis. 22. Wir werden neue MDX-berechnete Elemente hinzufügen: Erstes Halbjahr und Zweites Halbjahr

Platzieren Sie den Cursor an einer beliebigen Stelle in der PivotTable und wählen Sie die kontextbezogene Registerkarte aus Analyse aus einer Reihe kontextbezogener Registerkarten Arbeiten mit Pivot-Tabellen. Im Dropdown-Menü OLAP-Tools Menüpunkt wählen Berechnetes MDX-Mitglied(Abb. 23).

Auf dem Bildschirm erscheint ein Dialogfeld (Abb. 24).

Reis. 24. Fenster Erstellen eines berechneten Elements

Folge diesen Schritten:

1. Geben Sie der berechneten Kennzahl einen Namen.

2. Wählen Sie die übergeordnete Hierarchie aus, für die Sie neue berechnete Elemente erstellen. Auf einer Baustelle Übergeordnetes Element einen Wert zuweisen Alle. Danke dafür Excel-Einstellungen greift beim Auswerten eines Ausdrucks auf alle Elemente der übergeordneten Hierarchie zu.

3. Im Fenster MDX-Ausdruck Geben Sie die MDX-Syntax ein. Um Zeit zu sparen, verwenden Sie die Liste auf der linken Seite, um vorhandene Mitglieder auszuwählen, die im MDX verwendet werden sollen. Doppelklicken Sie auf das ausgewählte Element und Excel fügt es dem Fenster hinzu MDX-Ausdruck. Im Beispiel in Abb. 24 wird die Summe des ersten und zweiten Quartals berechnet:

..&& +

.. && +

.. && + …

4. Klicken Sie auf OK. Excel zeigt das neu erstellte MDX-berechnete Element in der PivotTable an. Wie in Abb. 25 wird das neue berechnete Element zusammen mit den anderen berechneten Elementen in der PivotTable angezeigt.

In Abb. Abbildung 26 zeigt einen ähnlichen Prozess, der zum Erstellen des berechneten Elements „Zweites Halbjahr“ verwendet wird.

Beachten Sie, dass Excel nicht einmal versucht, die ursprünglichen MDX-Mitglieder zu entfernen (Abbildung 27). Die PivotTable zeigt weiterhin Datensätze für die Jahre 2005–2008 an, aufgeschlüsselt nach Quartalen. In diesem Fall ist das keine große Sache, aber in den meisten Fällen sollten Sie „zusätzliche“ Elemente ausblenden, um Konflikte zu vermeiden.

Reis. 27. Excel zeigt das erstellte MDX-berechnete Element als Originalelemente an. Dennoch ist es besser, die ursprünglichen Elemente zu löschen, um Konflikte zu vermeiden

Denken Sie daran: Berechnete Elemente finden Sie nur in der aktuellen Arbeitsmappe. Mit anderen Worten: Berechnete Kennzahlen werden nicht direkt im OLAP-Server-Cube erstellt. Dies bedeutet, dass niemand auf die berechnete Kennzahl oder das berechnete Element zugreifen kann, es sei denn, Sie geben die Arbeitsmappe frei oder veröffentlichen sie online.

Beachten Sie, dass das von MDX berechnete Element nicht mehr funktioniert, wenn sich die übergeordnete Hierarchie oder das übergeordnete Element in einem OLAP-Cube ändert. Sie müssen dieses Element neu erstellen.

Verwalten von OLAP-Berechnungen. Excel bietet eine Schnittstelle, mit der Sie berechnete Kennzahlen und MDX-Elemente in OLAP-Pivot-Tabellen verwalten können. Platzieren Sie den Cursor an einer beliebigen Stelle in der PivotTable und wählen Sie die kontextbezogene Registerkarte aus Analyse aus einer Reihe kontextbezogener Registerkarten Arbeiten mit Pivot-Tabellen. Im Dropdown-Menü OLAP-Tools Menüpunkt wählen Computerverwaltung. Im Fenster Computerverwaltung Es stehen drei Schaltflächen zur Verfügung (Abb. 28):

Erstellen. Erstellen Sie eine neue berechnete Kennzahl oder ein berechnetes MDX-Mitglied.
Ändern.Ändern Sie die ausgewählte Berechnung.
Löschen. Löschen Sie die ausgewählte Berechnung.

Reis. 28. Dialogfeld Computerverwaltung

Führen Sie Was-wäre-wenn-Analysen für OLAP-Daten durch. In Excel 2013 können Sie Was-wäre-wenn-Analysen für Daten in OLAP-Pivot-Tabellen durchführen. Danke dafür neue Chance Sie können Werte in einer PivotTable ändern und Kennzahlen und Elemente basierend auf Ihren Änderungen neu berechnen. Sie können Änderungen auch zurück an den OLAP-Cube übertragen. Um die Was-wäre-wenn-Analysefunktionen zu nutzen, erstellen Sie eine OLAP-PivotTable und wählen Sie die kontextbezogene Registerkarte aus Analyse Arbeiten mit Pivot-Tabellen. Im Dropdown-Menü OLAP-Tools Team auswählen Was, wenn die Analyse –> Aktivieren Sie die Was-wäre-wenn-Analyse(Abb. 29).

Ab diesem Zeitpunkt können Sie die Werte der Pivot-Tabelle ändern. Um den ausgewählten Wert in der PivotTable zu ändern, klicken Sie mit der rechten Maustaste darauf und Kontextmenü Menüpunkt wählen (Abb. 30). Excel führt alle Berechnungen in der PivotTable mit den von Ihnen vorgenommenen Änderungen erneut aus, einschließlich berechneter Kennzahlen und berechneter MDX-Elemente.

Reis. 30. Wählen Sie ein Element aus Berücksichtigen Sie die Änderung bei der Berechnung der Pivot-Tabelle um Änderungen an der Pivot-Tabelle vorzunehmen

Standardmäßig sind Änderungen, die im Was-wäre-wenn-Analysemodus an einer PivotTable vorgenommen werden, lokal. Wenn Sie Änderungen an den OLAP-Server weitergeben möchten, wählen Sie den Befehl zum Veröffentlichen von Änderungen aus. Wählen Sie eine kontextbezogene Registerkarte aus Analyse, befindet sich in einer Reihe kontextbezogener Registerkarten Arbeiten mit Pivot-Tabellen. Im Dropdown-Menü OLAP-Tools Gegenstände auswählen Was, wenn die Analyse – > Änderungen veröffentlichen(Abb. 31). Durch die Ausführung dieses Befehls wird das Zurückschreiben auf dem OLAP-Server aktiviert, was bedeutet, dass Änderungen an den Quell-OLAP-Cube weitergegeben werden können. (Um Änderungen an den OLAP-Server weiterzugeben, müssen Sie über die entsprechenden Berechtigungen für den Zugriff auf den Server verfügen. Wenden Sie sich an Ihren Datenbankadministrator, um Ihnen dabei zu helfen, Schreibzugriffsberechtigungen für die OLAP-Datenbank zu erhalten.)

Die Notiz wurde basierend auf dem Buch von Jelen, Alexander, verfasst. . Kapitel 9

Der dritte Artikel widmet sich der Verarbeitung großer Datenmengen mit Excel, beschreibt die Vorteile der Verwendung von Pivot-Tabellen. Im Allgemeinen hätte dieser Artikel der erste in der Reihe sein sollen, wenn wir über die Vorteile dieser oder jener Arbeitsweise sprechen. Tatsächlich wurde die Pivot-Table-Schnittstelle speziell für die Analyse großer Datenmengen entwickelt, die nicht nur in Tabellenkalkulationsbereichen, sondern auch in externen Datenquellen gespeichert werden können. Verständnis der Funktionsprinzipien und praktischer Nutzen Pivot-Tabellen können die tägliche Arbeit von Wirtschaftswissenschaftlern deutlich optimieren. Die Erhöhung des Datenanalyseniveaus führt wiederum zu einer verbesserten Führung des Unternehmens und zur Annahme korrekter Managemententscheidungen durch Manager auf verschiedenen Ebenen.

Allgemeine theoretische Fragen zum Arbeiten mit Pivot-Tabellen und zur mehrdimensionalen Datenanalyse werden in einem anderen Artikel auf unserer Website beschrieben.

Hier werden wir näher auf bestimmte Methoden der Datenverarbeitung mithilfe der Pivot-Tabellenschnittstelle eingehen. Verwenden Sie die Datei als Beispiel nwdata_pivot.xls.

Verwendung von Pivot-Tabellen

Einzigartige Werte erfassen

Eine der beliebtesten Aufgaben, die mit einer Pivot-Tabelle gelöst werden können, ist die Auswahl eindeutiger Werte aus einer Liste oder einem Array von Daten. Mithilfe der PivotTable-Schnittstelle können Sie dieses Problem auf die „eleganteste“ Art und Weise lösen – ohne die Verwendung von Formeln.

Im Beispiel auf dem Blatt Probe zeigt eine Liste der Länder und die Anzahl der Erwähnungen im Datenarray.

Das Datenfeld muss die Art der Operation enthalten – „Menge“. Mit dieser Option können Sie nicht numerische Quelldatenfelder im PivotTable-Datenbereich verarbeiten. Eine Alternative zur Zähloperation ist die Standard-ZÄHLENWENN-Funktion. Grundsätzlich ist es auch möglich, allein über Formeln einen Satz eindeutiger Werte zu generieren (siehe Teil 1), allerdings sind dafür sehr komplexe Formeln mit berechneter Adressierung erforderlich. Das heißt, die Verwendung einer Pivot-Tabelle für diese Aufgabe ist die optimale Lösung.

Summenwerte

Eine weitere beliebte Aufgabe bei der Verwendung der Pivot-Table-Schnittstelle besteht darin, Gesamtsummen für eindeutige Datensätze in einem Datensatz zu ermitteln.

Im Beispiel auf dem Blatt Summe Es wurden zusammenfassende Daten zu Bestellungen für jedes Land erstellt:

Der Operationstyp „Summe“ im Datenfeld lässt nur numerische Felder zu. Andere Arten der Aggregation von Quelldaten werden in der Praxis fast nie verwendet.

Um ein Problem mit Standardformeln zu lösen, können Sie die SUMIF-Funktion verwenden. Offensichtlich liegt die Schwierigkeit nicht in der Konsolidierung von Werten, sondern wie im vorherigen Beispiel in der Auswahl einer eindeutigen Liste (im Beispiel der Ländernamen).

2D-Analyse

Die zuvor beschriebenen Beispiele veranschaulichen die Datenanalyse basierend auf einem Kriterium. Mit Tabellenkalkulationen können Sie Daten in zwei Dimensionen visualisieren: Spalten und Zeilen. Auch Pivot-Tabellen verfügen über diese Datenanzeigebereiche.

Im Beispiel auf dem Blatt Tisch Es wurde ein Bericht nach Land und Datum erstellt, der die Veränderungen in der Anzahl der Bestellungen im Laufe der Zeit zeigt. Bitte beachten Sie, dass für das Datumstypfeld eine zusätzliche Gruppierung angewendet wird: nach Monat und nach Jahr.

Die Summierung nach mehreren Kriterien ist auch über die Standard-Excel-Funktionen SUMIFS, SUMPRODUCT sowie Array-Verarbeitungsfunktionen (siehe Teil 1) möglich. Diese Option erfordert jedoch zuvor bekannte Werte der Parameter – der Auswahltasten. Darüber hinaus benötigen Berechnungen mithilfe von Formeln deutlich mehr Zeit, was bei großen Datenmengen zu großen Produktivitätseinbußen führen kann.

Multivariate Analyse

Zusätzlich zur visuellen Analyse im Bereich nach Zeilen und Spalten können Sie in Pivot-Tabellen einen globalen Filter nach einem oder mehreren Feldern der Quelldaten verwenden. Zu diesem Zweck befindet sich oberhalb der Pivot-Tabelle ein spezieller Zellenbereich.

Beispiel auf einem Blatt Filter demonstriert die Möglichkeit, Daten für Unternehmen in einem Land mithilfe des Filterbereichs einer Pivot-Tabelle anzuzeigen:

Das Filterfeld kann in den Zeilen- oder Spaltenbereich verschoben werden, sodass Sie eine größere Auswahl an Informationen anzeigen können. Zusätzlich zum beschriebenen Filterbereich kann eine zusätzliche Datenfilterung durch die Einrichtung von Listen von Schlüsselfeldern in den Zeilen- oder Spaltenbereichen erfolgen.

Das Analogon zur Verwendung von Pivot-Tabellenfiltern mithilfe von Arbeitsblattformeln sind in den meisten Fällen Array-Verarbeitungsformeln.

Beispiele auf Blättern Pivot1 Und Pivot2 Zeigen Sie Optionen zum Anzeigen derselben Informationen mit unterschiedlichen PivotTable-Dimensionseinstellungen an.

Arbeiten mit Daten

Datenaktualisierung

Eine Pivot-Tabelle kann sowohl auf Daten basieren, die sich in einem beliebigen Zellbereich befinden, als auch auf externen Datenquellen. Schauen wir uns zunächst die erste Option an. Diese. Daten zur Analyse werden in einer Reihe von Arbeitszellen gespeichert Excel-Tabelle.

Ein Bericht in Form einer Pivot-Tabelle kann sowohl für den einmaligen Gebrauch als auch für den laufenden Gebrauch mit einem variablen Satz an Quelldaten erstellt werden. Die letzte Option bietet der Benutzer tolle Möglichkeiten Von interaktive Arbeit: Sie müssen das Berichtsformular einmal konfigurieren und formatieren. Wenn Sie dann die Quelldaten bearbeiten, werden Änderungen im endgültigen Formular automatisch vorgenommen. In diesem Fall ändert der Report nicht nur die Daten, sondern kann auch Zeilen und Spalten hinzufügen und löschen, was mit Arbeitsblattformeln praktisch nicht umsetzbar ist.

Mit dem PivotTable-Assistenten können Sie einen Zellbereich angeben, der als Datenquelle verwendet werden soll. Wenn beim Aktualisieren von Informationen neue Zeilen hinzugefügt wurden, werden diese möglicherweise nicht in die Datenquelle der Pivot-Tabelle aufgenommen und dementsprechend nicht korrekt analysiert. Diese Funktion ist bei der Verarbeitung großer Datenmengen recht schwer zu verfolgen.

Sie können den Datenquellenbereich für eine vorhandene Pivot-Tabelle über einen speziellen Dialog in Excel 2007–2010 ändern. In früheren Excel-Versionen wurde diese Schnittstellenfunktion im Pivot-Tabellen-Assistenten implementiert, wenn dieser aus einer aktiven Pivot-Tabelle gestartet wurde. Nachdem Sie den Assistenten geöffnet haben, müssen Sie einen Schritt zurückgehen:

Korrekturen an Datenquellen können auch programmgesteuert vorgenommen werden. Zum Beispiel über das Berechnungsfenster des VBA-Editors (Sofort):

Um nicht über die Richtigkeit der Größe des Quellbereichs der Pivot-Tabellendaten nachzudenken, können Sie beim Aufbau zunächst einen Zeilenbereich mit großem Rand angeben. Wenn Sie beispielsweise wissen, dass das erwartete Zeilenvolumen 10.000 nicht überschreitet, können Sie diesen Wert sofort als Bereichsgröße festlegen. In der Praxis führt eine solche Redundanz nicht zu sichtbaren Verlangsamungen beim Betrieb der PivotTable-Schnittstelle. Sie können leere Werte in Berichtsdimensionen ausblenden. Der Nachteil dieser Methode zeigt sich vor allem bei der Arbeit mit Feldern vom Typ „Datum“. Mit der Standard-Pivot-Tabellenschnittstelle können Sie verschiedene Gruppierungen implementieren, wenn Sie mit dem Typ „Datum“ arbeiten (nach Monat, nach Quartal). Wenn jedoch leere Werte vorhanden sind, sind diese Optionen nicht verfügbar, da Excel eine solche Spalte als Text definiert.

Zusätzlich zu den betrachteten Methoden zur Steuerung der Datenquelle schlagen wir vor, den Zeilenbereich der Pivot-Tabelle des aktiven Arbeitsblatts mithilfe programmgesteuerter Methoden zu konfigurieren. Wenn die Datenquelle den gesamten Arbeitsbereich des Blattes einnimmt, können Sie den folgenden Befehl verwenden:

ActiveSheet.PivotTables(1).SourceData = _ Left(ActiveSheet.PivotTables(1).SourceData, _ InStr(ActiveSheet.PivotTables(1).SourceData, "!")) & _ Range(Application.ConvertFormula(_ ActiveSheet.PivotTables (1).SourceData, xlR1C1, xlA1) _).Worksheet.UsedRange.Address(ReferenceStyle:=xlR1C1)

Der zuverlässigste, aber langsamste Weg besteht darin, die Zeilen des Quellblatts nacheinander zu überprüfen und dann die Eigenschaft zu füllen Quelldaten aktive Pivot-Tabelle. Beachten Sie, dass diese Eigenschaft nur bei der R1C1-Adressierung gespeichert wird.

Makros können ereignisgesteuert aufgerufen werden Arbeitsblatt_Aktivieren, oder konfigurieren Sie einen Hotkey.

Arbeiten mit Analyseergebnissen

Eine PivotTable befindet sich in einer Reihe von Zellen in einem Excel-Arbeitsblatt. Das Schreiben von Arbeitsblattformeln innerhalb der Grenzen einer Pivot-Tabelle ist weder manuell noch mit programmgesteuerten Methoden zulässig. Theoretisch ist es möglich, über Links für externe Formeln mit Zellen zu arbeiten, die innerhalb der Grenzen einer Pivot-Tabelle liegen. In der Praxis wird die VLOOKUP-Funktion häufig auch zum Durchsuchen einer Pivot-Tabellenspalte verwendet. Diese Methode muss mit größter Vorsicht angewendet werden – die Schnittstelle für zusammenfassende Berichte beinhaltet die Änderung der Position der angezeigten Daten relativ zu den rechtwinkligen Koordinaten des Arbeitsblatts, ohne dass dies Auswirkungen auf die Quelle dieser Daten hat. Das heißt, es gibt keine Garantie dafür, dass der in der Formel in der Pivot-Tabelle angegebene Link bei der weiteren Arbeit mit der Datei den richtigen Wert anzeigt. Die Datenquelle darf sich jedoch nicht ändern.

Verfügbar alternative Wege Verarbeitung der Ergebnisse der Pivot-Tabelle:

Kopieren und Einfügen der Werte der Pivot-Tabelle in ein anderes Blatt (mit der Funktion „Inhalte einfügen“) mit einer weiteren Suche nach Daten in diesem gebildeten Zellbereich. Es ist viel schwieriger, die Datenintegrität in einer einfachen Tabelle zu verletzen als in einer Übersichtstabelle. Der Hauptnachteil dieser Arbeitsweise besteht offensichtlich darin, dass nach jeder Aktualisierung der Datenquelle manuelle Vorgänge erforderlich sind.
Verwenden Sie die Funktion GETPIVOTDATA (Excel 2002 und höher). Diese Funktion beinhaltet den Zugriff auf Daten nicht über Arbeitsblattkoordinaten, sondern über Pivot-Tabellenabmessungen. Für Datenquellen vom Typ OLAP-Cube stehen spezielle Funktionen für den Zugriff auf Daten und Dimensionen zur Verfügung: CUBEVALUE, CUBEMEMBER und andere (Excel 2007-2010). Diese Methode unbequem und verlangsamt auch die Arbeit erheblich, wenn Sie viel bekommen müssen unterschiedliche Bedeutungen Pivot-Tisch.
Deaktivieren Sie eine Pivot-Tabelle, um Ergebnisse zu erhalten. Verwenden Sie stattdessen die Arbeitsblattformeln (siehe Teil 1). Obwohl diese Methode schwierig zu implementieren ist, kann sie am praktischsten sein, wenn andere Berechnungen auf den Ergebnissen basieren und die Datenquelle häufig aktualisiert wird.

Pivot-Table-Schnittstellenversionen

Im neuen Format xlsx-Datei(Excel 2007–2010) Die Funktionen der Pivot-Tabellen-Schnittstelle wurden erheblich geändert. IN vorherige Versionen Schnittstelle (97-2003) wurden nur „kosmetische“ Änderungen vorgenommen:

Excel 2000 (9.0) ist die Basisversion der Pivot-Table-Schnittstelle.
Excel XP (10.0) – neue GETPIVOTDATE-Funktion
Excel 2003 (11.0) – Es sieht so aus, als ob überhaupt keine Änderungen vorgenommen wurden
Excel 2007 (12.0) – eine neue Version Pivot-Table-Schnittstelle mit Unterstützung für erweiterte Bereiche. Verbesserte Leistung, geändert Aussehen Schnittstelle. Die Kompatibilität zum alten Format bleibt erhalten.
Excel 2010 (14.0) – PowerPivot-Add-In-Unterstützung. Arbeiten mit aktualisierten OLAP-Cubes.

Wesentliche Änderungen im neuen Dateiformat (2007–2010):

Eine Spalte kann mehrere Felder einer Pivot-Tabelle enthalten, hervorgehoben durch Einrückungen (komprimierte Form).
Scheiben Mit Pivot-Tabellen können Sie den aktuellen Filterwertsatz visuell anzeigen.
Dimensionen im Filterbereich unterstützen die Mehrfachauswahl.
Dimensionselemente können über Schaltflächen, die sich in derselben Zelle wie die Kopfzeile selbst befinden, ausgeblendet/angezeigt werden.
Es gibt mehrere neue Parameter in den Feld- und Tabelleneigenschaften.
Es stehen Pivot-Tabellenstile zur Verfügung, mit denen Sie das Erscheinungsbild Ihrer Berichte jederzeit ändern können.

Um die Unterschiede besser zu verstehen, laden Sie die Beispieldateien herunter und öffnen Sie sie nwdata_pivot1.xlsx Und nwdata_pivot2.xlsx(im Archiv nwdata_pivot.zip). Die erste Datei präsentiert den Bericht im alten Format, die zweite – im neuen Format sind die Quelldaten dieselben.

Interne Organisation der Pivot-Tabellenschnittstelle

Um die Funktionsprinzipien einer Pivot-Tabelle besser zu verstehen, werfen wir einen Blick auf die interne Organisation der Schnittstelle.

Pivot-Tabellen-Cache

Wenn Sie eine PivotTable erstellen oder aktualisieren, überträgt Excel die Daten unabhängig vom ausgewählten Quelltyp an einen Zwischenspeicher namens Pivot-Tabellen-Cache. Durch die Organisationsstruktur der Daten im Cache können Sie die Datenaggregation und Berechnungen in der Pivot-Tabelle erheblich optimieren. Durch das Speichern von Daten in einem eigenen Cache können Sie verschiedene Datenquellen verwenden und gleichzeitig ähnliche Funktionen beibehalten.

Die Daten im Cache werden aktualisiert, wenn Sie auf die Schaltfläche „Aktualisieren“ der Pivot-Tabellenoberfläche klicken (eine Schaltfläche im Menüband oder im Kontextmenü) oder in einem bestimmten Zeitintervall, wenn eine solche Einstellung in den Parametern angegeben ist. Der Excel-Berechnungsmodus (automatisch oder manuell) hat keinerlei Auswirkungen auf die Pivot-Tabelle.

Mehrere Pivot-Tabellen (oder Diagramme) können Daten aus demselben Cache anzeigen. Diese Arbeitsoption wird verwendet, um mehrere Berichtsformulare derselben Daten anzuzeigen, ohne die Schnittstelle zur Messeinrichtung zu verwenden. In diesem Fall wird bei der Aktualisierung einer der Tabellen automatisch die auf demselben Cache basierende Tabelle neu erstellt.

VBA-Objekte

Der Zugriff auf Daten mit programmgesteuerten Methoden ist auf der Ebene von Pivot-Tabellenobjekten möglich – Objekt PivotTable. Andere PivotTable-Objekte sind für die Anordnung und visuelle Darstellung von Elementen und Daten verantwortlich. Dazu gehören Sammlungen von Feldern: PivotFields, ColumnFields, RowFields, Seitenfelder, Datenfelder. Feldwertoptionen sind über Objektsammlungen verfügbar PivotItems.

Universelle Möglichkeit, direkt auf Daten im Cache zuzugreifen (Objekt PivotCache) wird aus irgendeinem Grund von Excel-Entwicklern nicht bereitgestellt. Die Logik ist nicht ganz klar. Wie bereits erwähnt, werden die Cache-Daten separat gespeichert und sind sogar in einer XLSX-Datei sichtbar, wenn Sie diese Datei als Zip-Archiv öffnen. Abhängig von der Art der Datenquelle können Sie versuchen, die Eigenschaft zu verwenden Quelldaten(für bereichsbasierte Pivot-Tabellen) oder Datensatzsatz(für Quellen vom Typ „Datenbankabfrage“).

Berechnete Felder und PivotTable-Objekte ( BerechneteFelder, Berechnete Elemente) verfügen über einen eigenen Berechnungsmechanismus und Formelabhängigkeitsbaum, der nichts mit Excel-Arbeitsblattformeln zu tun hat. In der Praxis empfehlen wir, möglichst viele berechnete Felder in Pivot-Tabellen zu vermeiden, da dies zu einer deutlichen Verlangsamung der Berechnungen führt. Bei Datenquellen in Form von Zellbereichen können Sie häufig einfach eine Spalte mit einer regulären Formel zu den Quelldaten hinzufügen, und bei Datenbankabfragen können Sie Berechnungen direkt zum Hauptteil der SQL-Abfrage hinzufügen.

Arten von Datenquellen

Weltweit können Datenquellen in drei Typen unterteilt werden:

Zellbereiche
Datenbankabfragen
OLAP-Cubes und PowerPivot2010 als eine der Optionen zur Implementierung des OLAP-Mechanismus.

Bereiche

Die erste Option ist in der Praxis am häufigsten; Die vorherigen Beschreibungen der Beispiele gelten speziell für Daten, die in einem Zellbereich gespeichert sind.

Mit der Standard-Excel-Schnittstelle können Sie keinen zusammenfassenden Bericht erstellen, der auf mehreren Zellbereichen basiert. Der Grund für diese Einschränkung ist nicht ganz klar. Es besteht der Verdacht, dass die Entwickler schlicht keine intuitive Benutzeroberfläche anbieten können, um dieses Problem zu lösen. Die technische Umsetzung der Aufgabe sieht nicht allzu kompliziert aus – Sie müssen lediglich den Datencache füllen. Im Kapitel Add-ons Auf unserer Website stellen wir unsere eigene Lösung für die Erstellung komplexer zusammenfassender Berichte vor.

Datenbankabfragen

Datenbankabfragen können über verschiedene technische Mechanismen realisiert werden: Microsoft Query, ADO, ODBC. Unabhängig von der Datenzugriffsschnittstelle besteht der verbindende Faktor dieser Problemumgehung darin, den Pivot-Tabellen-Cache direkt von einer externen Quelle zu füllen. Beim weiteren Arbeiten mit der Pivot-Tabelle kann die Abfrage erneut ausgeführt werden, woraufhin die Daten erneut in den Cache übertragen werden. Mit dieser Methode können Sie Daten aus externen Quellen (Buchhaltungssystemen) in Echtzeit analysieren. Wenn die Verbindung zur Datenquelle verloren geht, kann die Analyse mit den neuesten Daten im Cache durchgeführt werden.

OLAP-Würfel

Der OLAP-Cube bietet eine mittlere Ebene der Informationsaufbereitung für die mehrdimensionale Analyse in Pivot-Tabellen. Der Cube speichert Informationen über verfügbare Feldtypen (Dimension oder Daten), hierarchische Feldabhängigkeiten, aggregierte Werte (Zwischensummen) und andere berechnete Elemente. Der Hauptvorteil der Verwendung von Cubes gegenüber direkten Datenbankabfragen besteht darin Hochleistung, da die Daten im Zwischenspeicher verschoben und aggregiert werden. Der Nachteil dieser Methode liegt ebenfalls auf der Hand: Die OLAP-Cube-Daten können irrelevante Informationen enthalten, was von den Speichereinstellungen abhängt.

Vor Office 2007 konnte ein einfacher OLAP-Cube mit erstellt werden Microsoft-Hilfe Abfrage, aber in letzte Version Diese Funktion wurde aus unbekannten Gründen deaktiviert. Entwickler empfehlen dringend die Verwendung des SQL Server Analysis Service zum Erstellen und Konfigurieren von OLAP-Cubes. Die Empfehlung ist nützlich, aber erstens ist dieser Dienst nur in kostenpflichtigen Versionen von SQL Server enthalten und zweitens erfordert er ein gründliches Studium sowohl der Schnittstelle als auch der Sprache zur Verarbeitung von MDX-Abfragen.

Das Beispiel für diesen Artikel zeigt ein Archiv nwdata_cube.zip mit zwei Dateien nwdata_cube.cub, nwdata_cube.xls. Bitte beachten Sie die Änderungen in der PivotTable-Schnittstelle bei Verwendung eines OLAP-Cubes als Datenquelle:

Da es hierarchische Dimensionen gibt, gibt es keine Möglichkeit, die übergeordneten und untergeordneten Elemente auszutauschen.
Es ist nicht erlaubt, Dimensionen in den Datenbereich zu verschieben und umgekehrt.
Zwischensummen werden für alle Artikel angezeigt, nicht für den aktuellen Gruppenfilter.

PowerPivot

Für Excel 2010 ist ein spezielles PowerPivot-Add-In verfügbar, das im Großen und Ganzen einen alternativen Mechanismus zur Implementierung von OLAP-Cubes darstellt. Mit PowerPivot können Sie Millionen unterschiedlicher Datensätze verarbeiten Informationsdateien und Datenbanken mit enormer Leistung. Gleichzeitig wird die Benutzeroberfläche für die abschließende Datenanalyse in Excel 2010 implementiert.

Es ist sehr wahrscheinlich, dass dieses Add-on im nächsten enthalten sein wird Excel-Versionen als Grundfunktionalität. Wir hoffen wirklich, der Beschreibung der Funktionsweise von PowerPivot einen separaten Artikel oder sogar eine Reihe von Artikeln widmen zu können. Heute sind PowerPivot + Excel vielleicht die meisten leistungsfähiges Werkzeug zur Analyse großer Datenmengen.

Offizielle PowerPivot-Website.

Im Rahmen dieser Arbeit werden folgende Fragestellungen berücksichtigt:

Was sind OLAP-Würfel?
Was sind Kennzahlen, Dimensionen, Hierarchien?
Welche Arten von Operationen können auf OLAP-Cubes ausgeführt werden?

Das Konzept eines OLAP-Würfels

Das Hauptpostulat von OLAP ist die Mehrdimensionalität in der Datenpräsentation. In der OLAP-Terminologie wird das Konzept eines Würfels oder Hyperwürfels verwendet, um einen mehrdimensionalen diskreten Datenraum zu beschreiben.

Würfel ist eine mehrdimensionale Datenstruktur, aus der ein Benutzer-Analyst Informationen abfragen kann. Aus Fakten und Dimensionen entstehen Würfel.

Daten- Hierbei handelt es sich um Daten über Objekte und Ereignisse im Unternehmen, die analysiert werden sollen. Sachverhalte gleicher Art bilden Maßnahmen. Eine Kennzahl ist der Werttyp in einer Würfelzelle.

Messungen- Dies sind die Datenelemente, anhand derer die Fakten analysiert werden. Eine Sammlung solcher Elemente bildet ein Dimensionsattribut (z. B. können Wochentage ein Zeitdimensionsattribut bilden). Bei betriebswirtschaftlichen Analyseaufgaben für Wirtschaftsunternehmen umfassen die Dimensionen häufig Kategorien wie „Zeit“, „Umsatz“, „Produkte“, „Kunden“, „Mitarbeiter“, „geografischer Standort“. Die Messungen sind am häufigsten hierarchische Strukturen Dabei handelt es sich um logische Kategorien, anhand derer der Benutzer tatsächliche Daten analysieren kann. Jede Hierarchie kann eine oder mehrere Ebenen haben. Somit kann die Hierarchie der Dimension „geografischer Standort“ die Ebenen „Land – Region – Stadt“ umfassen. In der Zeithierarchie kann man beispielsweise folgende Abfolge von Ebenen unterscheiden: Eine Dimension kann mehrere Hierarchien haben (jede Hierarchie einer Dimension muss das gleiche Schlüsselattribut der Dimensionstabelle haben).

Ein Cube kann tatsächliche Daten aus einer oder mehreren Faktentabellen enthalten und enthält meist mehrere Dimensionen. Jeder Würfel hat normalerweise einen bestimmten Fokus für die Analyse.

Abbildung 1 zeigt ein Beispiel für einen Würfel, der dazu dient, den Verkauf von Erdölprodukten eines bestimmten Unternehmens nach Region zu analysieren. Dieser Würfel hat drei Dimensionen (Zeit, Produkt und Region) und eine Kennzahl (Umsatzvolumen ausgedrückt in Geld). Messwerte werden in den entsprechenden Zellen des Würfels gespeichert. Jede Zelle wird durch eine Reihe von Elementen jeder Dimension, ein sogenanntes Tupel, eindeutig identifiziert. Beispielsweise wird die Zelle in der unteren linken Ecke des Würfels (enthält den Wert 98399 $) durch das Tupel [Juli 2005, Fernost, Diesel] angegeben. Hier zeigt der Wert von 98.399 US-Dollar das Verkaufsvolumen (in Geld ausgedrückt) von Diesel in Fernost für Juli 2005.

Beachten Sie auch, dass einige Zellen keine Werte enthalten: Diese Zellen sind leer, da die Faktentabelle keine Daten für sie enthält.

Reis. 1. Würfel mit Informationen zum Verkauf von Erdölprodukten in verschiedenen Regionen

Das ultimative Ziel bei der Erstellung solcher Cubes besteht darin, die Verarbeitungszeit von Abfragen zu minimieren, die die erforderlichen Informationen aus den tatsächlichen Daten extrahieren. Um diese Aufgabe zu erfüllen, enthalten Würfel normalerweise vorberechnete Gesamtsummen namens Aggregationen(Aggregationen). Diese. Der Würfel deckt einen Datenraum ab, der größer ist als der tatsächliche – es befinden sich darin logische, berechnete Punkte. Mit Aggregationsfunktionen können Sie die Werte von Punkten im logischen Raum basierend auf tatsächlichen Werten berechnen. Die einfachsten Aggregationsfunktionen sind SUM, MAX, MIN, COUNT. So können Sie beispielsweise mithilfe der MAX-Funktion für den im Beispiel angegebenen Würfel ermitteln, wann der Dieselabsatz seinen Höhepunkt in Fernost usw. erreichte.

Eine weitere Besonderheit mehrdimensionaler Würfel ist die Schwierigkeit, den Ursprung zu bestimmen. Wie legen Sie beispielsweise den Punkt 0 für die Dimension „Produkt“ oder „Regionen“ fest? Die Lösung dieses Problems besteht darin, ein spezielles Attribut einzuführen, das alle Elemente der Dimension kombiniert. Dieses Attribut (automatisch erstellt) enthält nur ein Element – Alle. Für einfache Funktionen Bei einer Aggregation wie einer Summe entspricht das All-Element der Summe der Werte aller Elemente des tatsächlichen Raums einer bestimmten Dimension.

Ein wichtiges Konzept in einem mehrdimensionalen Datenmodell ist der Unterraum oder Unterwürfel. Ein Unterwürfel ist ein Teil des Gesamtraums eines Würfels in Form einer mehrdimensionalen Figur innerhalb des Würfels. Da der mehrdimensionale Raum eines Würfels diskret und begrenzt ist, ist auch der Unterwürfel diskret und begrenzt.

Operationen an OLAP-Cubes

Die folgenden Vorgänge können an einem OLAP-Cube ausgeführt werden:

Scheibe;
Drehung;
Konsolidierung;
Detaillierung.

Scheibe(Abbildung 2) ist ein Sonderfall eines Unterwürfels. Dies ist ein Verfahren zum Bilden einer Teilmenge eines mehrdimensionalen Datenarrays, die einem einzelnen Wert eines oder mehrerer Dimensionselemente entspricht, die nicht in dieser Teilmenge enthalten sind. Um beispielsweise herauszufinden, wie sich der Verkauf von Erdölprodukten im Laufe der Zeit nur in einer bestimmten Region, nämlich im Ural, entwickelt hat, müssen Sie die Dimension „Produkte“ auf dem Element „Ural“ festlegen und die entsprechende Teilmenge (Subcube) daraus extrahieren Würfel.

Reis. 2. OLAP-Würfelstück

Drehung(Abbildung 3) – der Vorgang des Änderns der Position der in einem Bericht oder auf der angezeigten Seite dargestellten Messungen. Beispielsweise kann eine Rotationsoperation das Neuanordnen der Zeilen und Spalten einer Tabelle umfassen. Darüber hinaus werden durch Drehen eines Datenwürfels Dimensionen außerhalb der Tabelle an die auf der angezeigten Seite vorhandenen Dimensionen verschoben und umgekehrt.

Anmerkung: In dieser Vorlesung werden die Grundlagen des Entwurfs von Datenwürfeln für OLAP-Data-Warehouses behandelt. Das Beispiel zeigt die Methode zum Erstellen eines Datenwürfels mit dem CASE-Tool.

Zweck der Vorlesung

Nachdem Sie den Stoff dieser Vorlesung studiert haben, wissen Sie:

Was ist ein Datenwürfel? OLAP-Data-Warehouse ;
So entwerfen Sie einen Datenwürfel für OLAP-Data-Warehouses ;
Was ist eine Datenwürfeldimension?
wie ein Fakt mit einem Datenwürfel zusammenhängt;
Was sind Dimensionsattribute?
Was ist Hierarchie?
Was ist eine Datenwürfelmetrik?

und lernen:

bauen mehrdimensionale Diagramme ;
Design einfach mehrdimensionale Diagramme.

Einführung

Die OLAP-Technologie ist keine Single Software, Nicht Programmiersprache. Wenn wir versuchen, OLAP in all seinen Erscheinungsformen abzudecken, dann handelt es sich um eine Reihe von Konzepten, Prinzipien und Anforderungen, die zugrunde liegen Softwareprodukte Dies erleichtert Analysten den Zugriff auf Daten.

Analysten sind die Hauptkonsumenten von Unternehmensinformationen. Die Aufgabe des Analysten besteht darin, Muster in großen Datenmengen zu finden. Daher wird der Analyst nicht auf die individuelle Tatsache achten, dass an einem bestimmten Tag eine Charge Kugelschreiber an den Käufer Ivanov verkauft wurde – er benötigt Informationen über Hunderte und Tausende ähnlicher Ereignisse. Einzelne Fakten im Data Warehouse können beispielsweise für einen Buchhalter oder den Leiter der Vertriebsabteilung von Interesse sein, deren Kompetenz in der Betreuung eines bestimmten Vertrags liegt. Für einen Analysten reicht ein Datensatz nicht aus – er benötigt beispielsweise Informationen über alle Verträge einer Verkaufsstelle für einen Monat, ein Quartal oder ein Jahr. Der Analyst ist möglicherweise nicht an der TIN oder Telefonnummer des Käufers interessiert – er arbeitet mit spezifischen numerischen Daten, die den Kern seiner beruflichen Tätigkeit ausmachen.

Zentralisierung und praktische Strukturierung sind nicht alles, was ein Analyst braucht. Er benötigt ein Werkzeug zum Anzeigen und Visualisieren von Informationen. Herkömmlichen Berichten, selbst solchen, die auf der Grundlage eines einzigen Data Warehouse erstellt wurden, mangelt es jedoch an einer gewissen Flexibilität. Sie können nicht „verdreht“, „erweitert“ oder „reduziert“ werden, um die gewünschte Ansicht der Daten zu erhalten. Je mehr „Slices“ und „Abschnitte“ von Daten ein Analyst untersuchen kann, desto mehr Ideen hat er, die wiederum immer mehr „Slices“ zur Überprüfung erfordern. OLAP dient als solches Werkzeug zur Datenanalyse durch einen Analysten.

Obwohl OLAP kein notwendiges Attribut eines Data Warehouse ist, wird es zunehmend zur Analyse der in diesem Data Warehouse gesammelten Informationen verwendet.

Betriebsdaten werden aus verschiedenen Quellen gesammelt, bereinigt, integriert und in einem Data Warehouse gespeichert. Darüber hinaus stehen sie bereits für die Analyse mit verschiedenen Reporting-Tools zur Verfügung. Anschließend werden die Daten (ganz oder teilweise) für die OLAP-Analyse vorbereitet. Sie können in eine spezielle OLAP-Datenbank geladen oder in einer relationalen Datenbank belassen werden. Das wichtigste Element bei der Verwendung von OLAP sind Metadaten, also Informationen über die Struktur, Platzierung und Datentransformation. Dank ihnen ist ein effektives Zusammenspiel verschiedener Speicherkomponenten gewährleistet.

Auf diese Weise, OLAP kann als eine Reihe von Tools für die mehrdimensionale Datenanalyse definiert werden, die in einem Data Warehouse gesammelt werden. Theoretisch können OLAP-Tools direkt auf Betriebsdaten oder deren exakte Kopien angewendet werden. Es besteht jedoch die Gefahr, dass Daten einer Analyse unterzogen werden, die für diese Analyse nicht geeignet sind.

OLAP auf Client und Server

OLAP basiert auf einer mehrdimensionalen Datenanalyse. Die Erstellung kann mit verschiedenen Tools erfolgen, die sich in Client- und Server-OLAP-Tools unterteilen lassen.

OLAP-Client-Tools sind Anwendungen, die aggregierte Daten (Summen, Durchschnittswerte, Maxima usw.) berechnen Mindestwerte) und deren Anzeige, während die aggregierten Daten selbst in einem Cache im Adressraum eines solchen OLAP-Tools enthalten sind.

Wenn die Quelldaten in einem Desktop-DBMS enthalten sind, wird die Berechnung der aggregierten Daten vom OLAP-Tool selbst durchgeführt. Wenn die Quelle der Ausgangsdaten ein Server-DBMS ist, senden viele der Client-OLAP-Tools SQL-Abfragen mit dem GROUP BY-Operator an den Server und empfangen als Ergebnis aggregierte Daten, die auf dem Server berechnet wurden.

In der Regel wird die OLAP-Funktionalität in Tools implementiert statistische Verarbeitung Daten (von den Produkten dieser Klasse sind Produkte von Stat Soft und SPSS auf dem russischen Markt weit verbreitet) und in einigen Tabellenkalkulationen. Insbesondere verfügt es über gute multidimensionale Analysewerkzeuge Microsoft Excel 2000. Mit diesem Produkt können Sie einen kleinen lokalen mehrdimensionalen OLAP-Würfel erstellen und als Datei speichern und seine zwei- oder dreidimensionalen Abschnitte anzeigen.

Viele Entwicklungswerkzeuge enthalten Bibliotheken von Klassen oder Komponenten, mit denen Sie Anwendungen erstellen können, die einfache OLAP-Funktionalität implementieren (wie beispielsweise Decision Cube-Komponenten in Borland Delphi und Borland C++Builder). Darüber hinaus bieten viele Unternehmen an Kontrollen ActiveX und andere Bibliotheken, die ähnliche Funktionen implementieren.

Beachten Sie, dass Client-OLAP-Tools in der Regel mit einer kleinen Anzahl von Dimensionen (normalerweise werden nicht mehr als sechs empfohlen) und einer kleinen Vielfalt an Werten für diese Parameter verwendet werden – schließlich müssen die resultierenden aggregierten Daten in die passen Adressraum eines solchen Tools, und ihre Anzahl wächst exponentiell mit der Anzahl der Messungen Daher ermöglichen Ihnen selbst die primitivsten Client-OLAP-Tools in der Regel eine vorläufige Berechnung des benötigten Volumens Arbeitsspeicher um darin einen mehrdimensionalen Würfel zu erzeugen.

Viele (aber nicht alle) OLAP-Client-Tools ermöglichen es Ihnen, den Inhalt des Caches mit aggregierten Daten als Datei zu speichern, wodurch Sie eine Neuberechnung vermeiden können. Beachten Sie, dass diese Möglichkeit häufig genutzt wird, um aggregierte Daten zu verfremden, um sie an andere Organisationen zu übertragen oder zur Veröffentlichung zu veröffentlichen. Ein typisches Beispiel für solche veräußerlichen aggregierten Daten sind Morbiditätsstatistiken in verschiedenen Regionen und in verschiedenen Altersgruppen offene Informationen herausgegeben von den Gesundheitsministerien verschiedene Länder und die Weltgesundheitsorganisation. Gleichzeitig handelt es sich bei den Originaldaten selbst, die Informationen über konkrete Krankheitsfälle darstellen, um vertrauliche Daten medizinischer Einrichtungen und sollten auf keinen Fall in die Hände von Versicherungsgesellschaften gelangen, geschweige denn öffentlich werden.

Die Idee, einen Cache mit aggregierten Daten in einer Datei zu speichern, wurde in Server-OLAP-Tools weiterentwickelt, in denen aggregierte Daten gespeichert und geändert werden, sowie Unterstützung für den Speicher, der sie enthält. separater Antrag oder ein Prozess namens OLAP-Server. Clientanwendungen können eine solche mehrdimensionale Speicherung anfordern und als Antwort bestimmte Daten erhalten. Einige Clientanwendungen erstellen möglicherweise auch solche Speicher oder aktualisieren sie basierend auf geänderten Quelldaten.

Die Vorteile der Verwendung von Server-OLAP-Tools im Vergleich zu Client-OLAP-Tools ähneln den Vorteilen der Verwendung von Server-DBMS im Vergleich zu Desktop-DBMS: Bei der Verwendung von Server-Tools erfolgt die Berechnung und Speicherung aggregierter Daten auf dem Server und der Client-Anwendung erhält nur die Ergebnisse von Abfragen gegen sie, wodurch V Allgemeiner Fall reduzieren Netzwerktraffic, Vorlaufzeit Anforderungen und Ressourcenanforderungen, die von der Clientanwendung verbraucht werden. Beachten Sie, dass Tools zur Datenanalyse und -verarbeitung im Unternehmensmaßstab in der Regel auf Server-OLAP-Tools basieren, beispielsweise Oracle Express Server, Microsoft SQL Server 2000 Analysis Services, Hyperion Essbase, Produkte von Crystal Decisions, Business Objects, Cognos, SAS Institut. Da alle führenden Hersteller von Server-DBMS das eine oder andere Server-OLAP-Tool herstellen (oder von anderen Unternehmen lizenziert haben), ist die Auswahl recht groß und in fast allen Fällen können Sie einen OLAP-Server vom selben Hersteller wie den Datenbankserver selbst erwerben .

Beachten Sie, dass viele Client-OLAP-Tools (insbesondere Microsoft Excel 2003, Seagate Analysis usw.) den Zugriff auf Server-OLAP-Speicher ermöglichen und in diesem Fall als Clientanwendungen fungieren, die solche Abfragen durchführen. Darüber hinaus gibt es viele Produkte, die Client-Anwendungen für OLAP-Tools verschiedener Hersteller sind.

Technische Aspekte der mehrdimensionalen Datenspeicherung

Mehrdimensionale Data Warehouses enthalten aggregierte Daten mit unterschiedlichem Detaillierungsgrad, beispielsweise Verkaufsmengen nach Tag, Monat, Jahr, nach Produktkategorie usw. Der Zweck der Speicherung aggregierter Daten besteht in der Reduzierung Vorlaufzeit Anfragen, da für Analysen und Prognosen in den meisten Fällen nicht detaillierte, sondern zusammenfassende Daten von Interesse sind. Daher werden beim Erstellen einer mehrdimensionalen Datenbank immer einige aggregierte Daten berechnet und gespeichert.

Beachten Sie, dass das Speichern aller aggregierten Daten nicht immer gerechtfertigt ist. Tatsache ist, dass beim Hinzufügen neuer Dimensionen das Datenvolumen, aus dem der Würfel besteht, exponentiell wächst (manchmal spricht man von einem „explosiven Wachstum“ des Datenvolumens). Genauer gesagt hängt der Grad des Wachstums des Volumens aggregierter Daten von der Anzahl der Dimensionen des Würfels und den Elementen der Dimensionen auf verschiedenen Ebenen der Hierarchien dieser Dimensionen ab. Um das Problem des „explosiven Wachstums“ zu lösen, werden verschiedene Schemata verwendet, die es ermöglichen, bei der Berechnung nicht aller möglichen aggregierten Daten eine akzeptable Geschwindigkeit der Abfrageausführung zu erreichen.

Sowohl Roh- als auch aggregierte Daten können entweder in relationalen oder mehrdimensionalen Strukturen gespeichert werden. Daher werden derzeit drei Methoden der Datenspeicherung verwendet.

MOLAP(Multidimensionales OLAP) – Quell- und Aggregatdaten werden in einer mehrdimensionalen Datenbank gespeichert. Durch das Speichern von Daten in mehrdimensionalen Strukturen können Sie Daten bearbeiten mehrdimensionales Array, wodurch die Geschwindigkeit der Berechnung der Aggregatwerte für alle Dimensionen gleich ist. In diesem Fall ist die mehrdimensionale Datenbank jedoch redundant, da die mehrdimensionalen Daten vollständig die ursprünglichen relationalen Daten enthalten.
ROLAP(Relationales OLAP) – die Originaldaten verbleiben in derselben relationalen Datenbank, in der sie ursprünglich gespeichert waren. Aggregierte Daten werden in Servicetabellen abgelegt, die speziell für die Speicherung in derselben Datenbank erstellt wurden.
HOLAP(Hybrid OLAP) – die Originaldaten verbleiben in derselben relationalen Datenbank, in der sie sich ursprünglich befanden, und die aggregierten Daten werden in einer mehrdimensionalen Datenbank gespeichert.

Einige OLAP-Tools unterstützen die Speicherung von Daten nur in relationalen Strukturen, andere nur in mehrdimensionalen. Die meisten modernen Server-OLAP-Tools unterstützen jedoch alle drei Methoden der Datenspeicherung. Die Wahl der Speichermethode hängt vom Umfang und der Struktur der Quelldaten, den Anforderungen an die Geschwindigkeit der Abfrageausführung und der Häufigkeit der Aktualisierung der OLAP-Cubes ab.

Beachten Sie auch, dass die überwiegende Mehrheit der modernen OLAP-Tools keine „leeren“ Werte speichert (ein Beispiel für einen „leeren“ Wert wäre das Fehlen von Verkäufen eines saisonalen Produkts außerhalb der Saison).

Grundlegende OLAP-Konzepte

FAMSI-Test

Die Technologie zur komplexen mehrdimensionalen Datenanalyse heißt OLAP (On-Line Analytical Processing). OLAP ist eine Schlüsselkomponente einer Data Warehouse-Organisation. Das Konzept von OLAP wurde 1993 von Edgar Codd, einem berühmten Datenbankforscher und Autor des relationalen Datenmodells, beschrieben. Basierend auf den Anforderungen von Codd wurde 1995 das sogenannte FASMI-Test(Fast Analysis of Shared Multidimensional Information) – schnelle Analyse gemeinsamer mehrdimensionaler Informationen, einschließlich der folgenden Anforderungen für Anwendungen zur mehrdimensionalen Analyse:

Schnell(Schnell) – Bereitstellung der Analyseergebnisse für den Benutzer in akzeptabler Zeit (normalerweise nicht mehr als 5 s), auch auf Kosten einer weniger detaillierten Analyse;
Analyse(Analyse) – die Fähigkeit, jede logische und logische Umsetzung umzusetzen statistische Analyse, charakteristisch für Diese Anwendung und Speichern in einer für den Endbenutzer zugänglichen Form;
Geteilt(Geteilt) – Mehrbenutzerzugriff auf Daten mit Unterstützung geeigneter Sperrmechanismen und autorisierter Zugriffsmittel;
Mehrdimensional(Multidimensional) – mehrdimensionale konzeptionelle Darstellung von Daten, einschließlich vollständiger Unterstützung für Hierarchien und mehrere Hierarchien (dies ist eine Schlüsselanforderung von OLAP);
Information(Informationen) – die Anwendung muss auf alle zugreifen können notwendige Informationen, unabhängig von Volumen und Speicherort.

Es ist zu beachten, dass die OLAP-Funktionalität implementiert werden kann verschiedene Wege, angefangen bei einfachsten Datenanalysetools in Office-Anwendungen bis hin zu verteilten Analysesystemen auf Basis von Serverprodukten.

Mehrdimensionale Darstellung von Informationen

Würfel

OLAP bietet bequeme und schnelle Möglichkeiten zum Zugriff, zur Anzeige und zur Analyse von Geschäftsinformationen. Der Benutzer erhält ein natürliches, intuitives Gefühl Datenmodell und organisiert sie in Form von mehrdimensionalen Würfeln (Cubes). Die Achsen des mehrdimensionalen Koordinatensystems sind die Hauptattribute des analysierten Geschäftsprozesses. Bei Verkäufen könnte es sich beispielsweise um Produkt, Region oder Käufertyp handeln. Als eine der Dimensionen wird die Zeit verwendet. An den Schnittpunkten der Messachsen (Dimensionen) liegen Daten, die den Prozess quantitativ charakterisieren – Maßnahmen (Maßnahmen). Dies können Verkaufsmengen in Stücken oder in Geld, Lagerbestände, Kosten usw. sein. Ein Benutzer, der Informationen analysiert, kann den Würfel in verschiedene Richtungen „schneiden“, eine Zusammenfassung (z. B. nach Jahr) oder umgekehrt eine detaillierte (nach Woche) erhalten ) Informationen und führt andere Manipulationen durch, die ihm während des Analyseprozesses in den Sinn kommen.

Als Maße im dreidimensionalen Würfel in Abb. 26.1 werden Umsatzbeträge und als Dimensionen Zeit, Produkt und Lager verwendet. Die Messungen werden auf bestimmten Gruppierungsebenen dargestellt: Produkte werden nach Kategorie, Filialen nach Land und Transaktionszeitdaten nach Monat gruppiert. Etwas später werden wir uns die Gruppierungsebenen (Hierarchie) genauer ansehen.

Reis. 26.1.

Einen Würfel „schneiden“.

Selbst ein dreidimensionaler Würfel lässt sich auf einem Computerbildschirm nur schwer so darstellen, dass die Werte der interessierenden Maße sichtbar sind. Was können wir über Würfel mit mehr als drei Dimensionen sagen? Zur Visualisierung der in einem Cube gespeicherten Daten werden in der Regel bekannte zweidimensionale, also Tabellenansichten mit komplexen hierarchischen Zeilen- und Spaltenüberschriften verwendet.

Eine zweidimensionale Darstellung eines Würfels kann erhalten werden, indem man ihn entlang einer oder mehrerer Achsen (Dimensionen) kreuzweise „schneidet“: Wir legen die Werte aller Dimensionen außer zwei fest und erhalten eine reguläre zweidimensionale Tabelle. Die horizontale Achse der Tabelle (Spaltenköpfe) stellt eine Dimension dar, die vertikale Achse (Zeilenköpfe) stellt eine andere dar und die Tabellenzellen stellen die Werte der Kennzahlen dar. In diesem Fall wird eine Reihe von Kennzahlen tatsächlich als eine der Dimensionen betrachtet: Wir wählen entweder eine Kennzahl zur Anzeige aus (und können dann zwei Dimensionen in den Zeilen- und Spaltenüberschriften platzieren) oder zeigen mehrere Kennzahlen an (und dann eine davon). Tabellenachsen werden mit den Namen der Kennzahlen belegt, die andere mit Werten der einzigen „ungeschnittenen“ Dimension).

(Ebenen). Beispielsweise werden die in dargestellten Beschriftungen nicht von allen OLAP-Tools unterstützt. Beispielsweise unterstützt Microsoft Analysis Services 2000 beide Arten von Hierarchien, Microsoft OLAP Services 7.0 unterstützt jedoch nur ausgewogene Hierarchien. Die Anzahl der Hierarchieebenen, die maximal zulässige Anzahl der Mitglieder einer Ebene und die maximal mögliche Anzahl der Dimensionen selbst können in verschiedenen OLAP-Tools unterschiedlich sein.

Architektur von OLAP-Anwendungen

Alles, was oben über OLAP gesagt wurde, bezog sich im Wesentlichen auf die mehrdimensionale Darstellung von Daten. Wie die Daten gespeichert werden, betrifft grob gesagt weder den Endbenutzer noch die Entwickler des Tools, das der Kunde verwendet.

Die Multidimensionalität in OLAP-Anwendungen lässt sich in drei Ebenen einteilen.

Mehrdimensionale Datendarstellung – Endbenutzer-Tools, die eine mehrdimensionale Visualisierung und Bearbeitung von Daten ermöglichen; Die mehrdimensionale Darstellungsschicht abstrahiert von der physischen Struktur der Daten und behandelt die Daten als mehrdimensional.
Mehrdimensionale Verarbeitung – ein Werkzeug (Sprache) zum Formulieren mehrdimensionaler Abfragen (traditionelle relationale). SQL-Sprache sich hier als ungeeignet herausstellt) und ein Auftragsverarbeiter, der in der Lage ist, eine solche Anfrage zu bearbeiten und auszuführen.
Bei der mehrdimensionalen Speicherung handelt es sich um ein Mittel zur physischen Organisation von Daten, das die effiziente Ausführung mehrdimensionaler Abfragen gewährleistet.

Die ersten beiden Ebenen sind in allen OLAP-Tools obligatorisch. Die dritte Ebene ist zwar weit verbreitet, aber nicht notwendig, da Daten für eine mehrdimensionale Darstellung aus gewöhnlichen relationalen Strukturen extrahiert werden können; Der mehrdimensionale Abfrageprozessor übersetzt in diesem Fall mehrdimensionale Abfragen in SQL-Abfragen, die vom relationalen DBMS ausgeführt werden.

Spezifische OLAP-Produkte sind in der Regel entweder ein mehrdimensionales Datenpräsentationstool (OLAP-Client – zum Beispiel Pivot-Tabellen in Excel 2000). Microsoft oder ProClarity von Knosys) oder ein mehrdimensionales Server-DBMS (OLAP-Server – zum Beispiel Oracle Express Server oder Microsoft OLAP Services).

Die mehrdimensionale Verarbeitungsschicht ist normalerweise in den OLAP-Client und/oder OLAP-Server integriert, kann jedoch in reiner Form isoliert werden, beispielsweise in der Pivot Table Service-Komponente von Microsoft.

Eine eigenständige Cube-Datei (.cub) speichert Daten in einem Formular in einem OLAP-Cube (Online Analytical Processing). Diese Daten können Teil einer OLAP-Datenbank eines OLAP-Servers sein oder unabhängig von einer OLAP-Datenbank erstellt worden sein. Um weiterhin mit PivotTable- und PivotChart-Berichten zu arbeiten, wenn der Server nicht verfügbar oder offline ist, verwenden Sie eine Offline-Cube-Datei.

Weitere Informationenüber autonome Würfel

Wenn Sie mit einem PivotTable- oder PivotChart-Bericht arbeiten, der auf einer Datenquelle von einem OLAP-Server basiert, verwenden Sie den Offline-Cube-Assistenten, um die Quelldaten in eine separate Offline-Cube-Datei auf Ihrem Computer zu kopieren. Um diese Offlinedateien zu erstellen, muss auf Ihrem Computer ein OLAP-Datenanbieter installiert sein, der diese Funktionen unterstützt, z. B. MSOLAP von Microsoft SQL Server Analysis Services.

Notiz: Erstellen und Verwenden von Offline-Cube-Dateien von Microsoft SQL Server Analysis Services, vorbehaltlich der Bedingungen und Lizenzen Microsoft-Installationen SQL Server. Überprüfen Sie die entsprechenden Lizenzinformationen für Ihre Version von SQL Server.

Verwenden des Offline-Cube-Assistenten

Um eine Offline-Cube-Datei zu erstellen, wählen Sie mit dem Offline-Cube-Assistenten eine Teilmenge der Daten in der OLAP-Datenbank aus und speichern Sie diese dann. Der Bericht muss nicht alle in der Datei enthaltenen Felder enthalten und Sie können aus allen in der OLAP-Datenbank verfügbaren Dimensionen und Datenfeldern auswählen. Um die Dateigröße zu minimieren, können Sie nur die Daten einbeziehen, die Sie im Bericht anzeigen möchten. Sie können alle Maße überspringen und bei den meisten Maßarten auch mehr als weglassen niedriges Niveau Details und Elemente Höchststufe, die nicht angezeigt werden müssen. Bei einer Offlinedatei werden auch alle Elemente gespeichert, die in die in der Datenbank für diese Elemente verfügbaren Eigenschaftsfelder aufgenommen werden können.

Daten offline schalten und dann wieder online schalten

Dazu müssen Sie zunächst einen PivotTable-Bericht oder einen PivotChart-Bericht erstellen, der auf der Serverdatenbank basiert, und dann eine eigenständige Cube-Datei aus dem Bericht erstellen. Anschließend können Sie beim Arbeiten mit einem Bericht jederzeit zwischen der Serverdatenbank und einer Offline-Datei wechseln (z. B. beim Bearbeiten von Laptop-Computer zu Hause oder unterwegs und anschließendes Wiederherstellen der Verbindung des Computers mit dem Netzwerk).

Im Folgenden werden die grundlegenden Schritte beschrieben, um Daten offline zu schalten und wieder online zu schalten.

Notiz:

Klicken Sie auf den PivotTable-Bericht. Wenn es sich um einen PivotChart-Bericht handelt, wählen Sie den zugehörigen PivotTable-Bericht aus.

Auf der „Registerkarte“ Analyse" in einer Gruppe Berechnungen drück den Knopf OLAP-Dienst und drücken Sie die Taste Offline-OLAP.

Wählen Sie einen Artikel aus OLAP mit Konnektivität und klicken Sie dann auf die Schaltfläche OK.

Wenn Sie aufgefordert werden, eine Datenquelle zu finden, klicken Sie auf Quelle finden und suchen Sie einen OLAP-Server im Netzwerk.

Klicken Sie auf den PivotTable-Bericht, der auf der Offline-Cube-Datei basiert.

In Excel 2016: Auf der Registerkarte „ Daten" in einer Gruppe Anfragen und Verbindungen Alle aktualisieren und drücken Sie die Taste Aktualisieren.

In Excel 2013: Auf der Registerkarte „ Daten" in einer Gruppe Verbindungen Klicken Sie auf den Pfeil neben der Schaltfläche Alle aktualisieren und drücken Sie die Taste Aktualisieren.

Auf der „Registerkarte“ Analyse" in einer Gruppe Berechnungen drück den Knopf OLAP-Dienst und drücken Sie die Taste Offline-OLAP.

Drück den Knopf Offline-OLAP-Modus, Und danach - .

Notiz: Stoppen im Dialogfenster.

Warnung:

Erstellen einer Offline-Cube-Datei aus einer OLAP-Serverdatenbank

Notiz: Wenn die OLAP-Datenbank groß ist und die Cube-Datei benötigt wird, um Zugriff auf eine große Teilmenge der Daten zu ermöglichen, wird viel Speicherplatz benötigt und das Speichern der Datei kann lange dauern. Um die Leistung zu verbessern, wird empfohlen, eigenständige Cube-Dateien mithilfe eines MDX-Skripts zu erstellen.

Problem: Mein Computer verfügt beim Speichern eines Cubes nicht über genügend Speicherplatz.

OLAP-Datenbanken sind für die Verwaltung großer Mengen detaillierter Daten konzipiert, sodass eine auf einem Server gehostete Datenbank deutlich mehr Speicherplatz beanspruchen kann, als auf Ihrer lokalen Festplatte verfügbar ist. Wenn Sie eine große Datenmenge für einen Offline-Datencube auswählen, verfügen Sie möglicherweise nicht über genügend freien Speicherplatz. Der folgende Ansatz hilft dabei, die Größe der Offline-Cube-Datei zu reduzieren.

Geben Sie Speicherplatz frei oder wählen Sie eine andere Festplatte aus Entfernen Sie die Cube-Datei vor dem Speichern von der Festplatte. unnötige Dateien oder speichern Sie die Datei auf einem Netzlaufwerk.

Weniger Daten in eine Offline-Cube-Datei aufnehmenÜberlegen Sie, wie Sie die in der Datei enthaltene Datenmenge minimieren können, sodass die Datei alle für einen PivotTable-Bericht oder ein PivotChart erforderlichen Daten enthält. Probieren Sie die folgenden Schritte aus.

Verbinden einer Offline-Cube-Datei mit einer OLAP-Serverdatenbank

Aktualisieren und Neuerstellen einer Offline-Cube-Datei

Das Aktualisieren einer Offline-Cube-Datei, die aus den neuesten von einem Server-Cube erhaltenen Daten oder aus einer neuen Offline-Cube-Datei erstellt wurde, kann viel Zeit in Anspruch nehmen und viel temporären Speicherplatz erfordern. Führen Sie diesen Vorgang aus, wenn Sie keinen sofortigen Zugriff auf andere Dateien benötigen, nachdem Sie sichergestellt haben, dass auf Ihrer Festplatte genügend Speicherplatz vorhanden ist.

Problem: Neue Daten werden beim Aktualisieren nicht im Bericht angezeigt.

Überprüfen der Verfügbarkeit der Quelldatenbank Die Offline-Cube-Datei kann möglicherweise keine Verbindung zur Quellserverdatenbank herstellen, um neue Daten abzurufen. Stellen Sie sicher, dass die Originaldatenbank auf dem Server, der als Datenquelle für den Cube dient, nicht umbenannt oder an einen anderen Speicherort verschoben wurde. Stellen Sie sicher, dass der Server zugänglich ist und eine Verbindung hergestellt werden kann.

Suche nach neuen Daten Erkundigen Sie sich bei Ihrem Datenbankadministrator, ob die Daten, die in den Bericht aufgenommen werden sollen, aktualisiert wurden.

Überprüfung der Unveränderlichkeit der Datenbankorganisation Wenn der OLAP-Server-Cube geändert wurde, müssen Sie möglicherweise den Bericht neu organisieren, eine Offline-Cube-Datei erstellen oder den Assistenten zum Erstellen von OLAP-Cubes ausführen, um auf die geänderten Daten zuzugreifen. Um mehr über Datenbankänderungen zu erfahren, wenden Sie sich an Ihren Datenbankadministrator.

Einschließen anderer Daten in die Offline-Cube-Datei

Das Speichern einer geänderten Offline-Cube-Datei kann zeitaufwändig sein und Sie können nicht in Microsoft Excel arbeiten, während die Datei gespeichert wird. Führen Sie diesen Vorgang aus, wenn Sie keinen sofortigen Zugriff auf andere Dateien benötigen, nachdem Sie sichergestellt haben, dass auf Ihrer Festplatte genügend Speicherplatz vorhanden ist.

Stellen Sie sicher, dass eine Netzwerkverbindung besteht und dass auf die Quell-OLAP-Serverdatenbank zugegriffen werden kann, aus der die Offline-Cube-Datei Daten abgerufen hat.

Klicken Sie auf einen PivotTable-Bericht, der aus einer eigenständigen Cube-Datei erstellt wurde, oder auf einen zugehörigen PivotTable-Bericht für einen PivotChart-Bericht.

Auf der Registerkarte Optionen in einer Gruppe Service drück den Knopf OLAP-Dienst und drücken Sie die Taste Offline-OLAP-Modus.

Drück den Knopf Offline-OLAP-Modus, Und danach - Bearbeiten Sie die Offline-Datendatei.

Folgen Sie dem Offline-Cube-Assistenten, um weitere Daten auszuwählen, die in diese Datei aufgenommen werden sollen. Geben Sie im letzten Schritt den Namen und den Pfad der zu ändernden Datei an.

Notiz: Um das Speichern der Datei abzubrechen, klicken Sie auf die Schaltfläche Stoppen im Dialogfenster Erstellen einer Cube-Datei – Fortschritt.

Löschen einer Offline-Cube-Datei

Warnung: Wenn Sie eine Offline-Cube-Datei für einen Bericht löschen, können Sie diesen Bericht nicht mehr offline verwenden und keine Offline-Cube-Datei mehr für diesen Bericht erstellen.

Schließen Sie alle Arbeitsmappen, die Berichte enthalten, die die Offline-Cube-Datei verwenden, oder stellen Sie sicher, dass alle derartigen Berichte gelöscht werden.

IN Microsoft Windows Suchen und löschen Sie die Offline-Cube-Datei (CUB-Datei).

Weitere Informationen

Sie können jederzeit eine Frage an einen Excel Tech Community-Spezialisten stellen, in der Answers-Community um Hilfe bitten und auch Vorschläge machen neue Funktion oder Verbesserung der Website