Office: Erstellen eines Speicher effizienten Datenmodells mithilfe von Excel und dem Power Pivot-Add-in

Erstellen eines Speicher effizienten Datenmodells mithilfe von Excel und dem Power Pivot-Add-in
Excel für Microsoft 365 Excel 2019 Excel 2016 Excel 2013 Mehr... Weniger In Excel 2013 oder höher können Sie Datenmodelle erstellen, die Millionen von Zeilen enthalten, und dann eine leistungsstarke Datenanalyse für diese Modelle durchführen. Datenmodelle können mit dem Power Pivot-Add-In oder ohne es erstellt werden, um eine beliebige Anzahl von PivotTables, Diagrammen und Power View-Visualisierungen in derselben Arbeitsmappe zu unterstützen.

Hinweis: In diesem Artikel werden Datenmodelle in Excel 2013 beschrieben. Die gleichen Daten Modellierungs-und Power Pivot-Features, die in Excel 2013 eingeführt wurden, gelten aber auch für Excel 2016. Zwischen diesen Versionen von Excel gibt es praktisch keinen Unterschied.

Obwohl Sie in Excel ganz einfach sehr umfangreiche Datenmodelle erstellen können, gibt es einige Gründe dafür, dies nicht zu tun. Erstens sind große Modelle mit Unmengen von Tabellen und Spalten in der Regel der Tod der meisten Analysen und liefern äußerst unhandliche Feldlisten. Zweitens belegen große Modell wertvollen Speicherplatz, was sich negativ auf andere Anwendungen und Berichte auswirkt, die dieselben Systemressourcen nutzen. Schließlich begrenzen in Microsoft 365 sowohl SharePoint Online als auch Excel Web App die Größe einer Excel-Datei auf 10 MB. Bei Arbeitsmappen-Datenmodellen mit Millionen von Zeilen wird der Grenzwert von 10 MB schnell zum Problem. Weitere Informationen finden Sie unter Spezifikation und Beschränkungen von Datenmodellen.

In diesem Artikel erfahren Sie, wie Sie ein straff konstruiertes Modell erstellen, mit dem sich einfacher arbeiten lässt und das weniger Speicherplatz beansprucht. Wenn Sie sich die Zeit für das Erlernen bewährter Methoden bei einem effizienten Modellentwurf nehmen, müssen Sie sich für jedes Modell, das Sie erstellen und verwenden, in Excel 2013, Microsoft 365 SharePoint Online, auf einem Office Web Apps-Server oder in SharePoint 2013 auf den Weg machen.

Überlegen Sie auch, den Workbook Size Optimizer auszuführen. Damit wird die Excel-Arbeitsmappe analysiert und nach Möglichkeit weiter komprimiert. Laden Sie den Arbeitsmappengröße-Optimiererherunter.

Inhalt dieses Artikels
Komprimierungsverhältnisse und das Modul für die Datenanalyse im Arbeitsspeicher

Eine Spalte, die nicht vorhanden ist, verbraucht immer noch den wenigsten Speicherplatz

Zwei Beispiele für Spalten, die immer ausgeschlossen werden sollten

Ausschließen überflüssiger Spalten

Können nur die notwendigen Zeilen gefiltert werden?

Wenn die Spalte nun aber doch benötigt wird – lässt sich ihr Platzbedarf dennoch verringern?

Ändern von Datetime-Spalten

Ändern der SQL-Abfrage

Verwenden von mittels DAX berechneten Measures anstelle von Spalten

Welche 2 Spalten sollten Sie behalten?

Fazit

Links zu verwandten Themen

Komprimierungsverhältnisse und das Modul für die Datenanalyse im Arbeitsspeicher
Datenmodelle in Excel verwenden zum Speichern von Daten das Modul für die Datenanalyse im Arbeitsspeicher. Das Modul implementiert leistungsstarke Komprimierungsmethoden, um den Speicherplatzbedarf zu verringern, wodurch eine Ergebnismenge so stark verkleinert wird, dass sie nur noch ein Bruchteil der Originalgröße belegt.

Im Durchschnitt können Sie davon ausgehen, dass ein Datenmodell 7- bis 10-mal kleiner ist als dieselben Daten an ihrem Ursprungsort. Wenn Sie beispielsweise 7 MB Daten aus einer SQL Server-Datenbank importieren, kann das Datenmodell in Excel leicht nur 1 MB oder weniger groß sein. Der Grad der tatsächlich erzielten Komprimierung hängt primär von der Anzahl eindeutiger Werte in jeder Spalte ab. Je mehr eindeutige Werte vorhanden sind, desto mehr Speicherplatz wird zu deren Speicherung benötigt.

Nun, warum behandeln wir Komprimierung und eindeutige Werte? Der Grund ist, dass es beim Erstellen eines effizienten Modells, das den Speicherplatzbedarf minimiert, um nichts anderes als Komprimierungsmaximierung geht – und die einfachste Methode, dies zu erreichen, besteht darin, alle Spalten zu entfernen, die nicht wirklich benötigt werden, insbesondere die Spalten, die große Mengen eindeutiger Werte enthalten.

Hinweis:  Die Unterschiede beim Speicherplatzbedarf für einzelne Spalten können immens sein. In manchen Fällen ist es sogar besser, mehrere Spalten mit wenigen eindeutigen Werten zu haben, statt einer Spalte mit vielen eindeutigen Werten. Der Abschnitt zur Datetime-Optimierung behandelt diese Methode im Detail.

Eine Spalte, die nicht vorhanden ist, verbraucht immer noch den wenigsten Speicherplatz
Die speichereffizienteste Spalte ist in erster Linie die, die nie importiert wurde. Wenn Sie ein effizientes Modell erstellen möchten, sehen Sie sich jede Spalte an, und fragen Sie sich dabei, ob Sie wirklich zu der Analyse beiträgt, die Sie durchführen möchten. Ist dies nicht der Fall, oder sind Sie sich nicht sicher, lassen Sie die Spalte weg. Sie können jederzeit später neue Spalten hinzufügen, wenn Sie sie benötigen.

Zwei Beispiele für Spalten, die immer ausgeschlossen werden sollten
Das erste Beispiel verwendet Daten, die aus einem Data Warehouse stammen. In einem Data Warehouse findet man häufig Artefakte von ETL-Prozessen, die Daten im Warehouse laden und aktualisieren. Spalten wie "Erstellungsdatum", "Aktualisierungsdatum" und "ETL-Ausführung" werden beim Laden der Daten erstellt. Keine dieser Spalten wird im Modell benötigt, weshalb alle diese Spalten beim Importieren von Daten deaktiviert werden sollten.

Das zweite Beispiel beinhaltet das Auslassen der primären Schlüsselspalte beim Importieren einer Faktentabelle.

Viele Tabellen, einschließlich Faktentabellen, besitzen primäre Schlüssel. Bei den meisten Tabellen wie solchen, die Kunden-, Mitarbeiter- oder Verkaufsdaten enthalten, möchten Sie den primären Schlüssel haben, damit Sie mit dessen Hilfe Beziehungen im Modell herstellen können.

Hier unterscheiden sich Faktentabellen. Bei einer Faktentabelle wird der primäre Schlüssel verwendet, um jede Zeile eindeutig zu identifizieren. Während dies für Normalisierungszwecke erforderlich ist, ist dies in einem Datenmodell weniger hilfreich, wo Sie nur die Spalten haben möchten, die für die Analyse oder zum Herstellen von Tabellenbeziehungen verwendet werden. Aus diesem Grund sollten Sie beim Importieren aus einer Faktentabelle den primären Schlüssel nicht übernehmen. Primäre Schlüssel in einer Faktentabelle verbrauchen große Mengen von Platz im Modell, ohne dabei von Vorteile zu sein, da sie zum Herstellen von Beziehungen nutzlos sind.

Hinweis:  In Datawarehouses und mehrdimensionalen Datenbankenwerden große Tabellen, die aus überwiegend numerischen Daten bestehen, häufig als "Faktentabellen" bezeichnet. Faktentabellen enthalten in der Regel Geschäftsleistung oder Transaktionsdaten wie Umsatz-und Kostendaten Punkte, die aggregiert und an Organisationseinheiten, Produkte, Marktsegmente, geographische Regionen usw. ausgerichtet sind. Alle Spalten in einer Faktentabelle, die Geschäftsdaten enthalten oder für den Querverweis auf in anderen Tabellen gespeicherte Daten verwendet werden können, sollten im Modell enthalten sein, um die Datenanalyse zu unterstützen. Die Spalte, die Sie ausschließen möchten, ist die Primärschlüsselspalte der Faktentabelle, die aus eindeutigen Werten besteht, die nur in der Faktentabelle und nirgendwo sonst vorhanden sind. Da Faktentabellen so riesig sind, werden einige der größten Vorteile der Modell Effizienz dadurch abgeleitet, dass Zeilen oder Spalten aus Faktentabellen ausgeschlossen werden.

Ausschließen überflüssiger Spalten
Effiziente Modelle enthalten nur die Spalten, die Sie tatsächlich in Ihrer Arbeitsmappe benötigen. Wenn Sie kontrollieren möchten, welche Spalten in das Modell aufgenommen werden, müssen Sie statt des Dialogfelds "Daten importieren" in Excel den Tabellenimport-Assistenten im Power Pivot-Add-In zum Importieren der Daten verwenden.

Wenn Sie den Tabellenimport-Assistenten starten, wählen Sie die zu importierenden Tabellen aus.





Für jede Tabelle können Sie auf die Schaltfläche "Vorschau & Filter" klicken und die Teile der Tabelle auswählen, die Sie wirklich benötigen. Wir empfehlen Ihnen, zuerst die Auswahl aller Spalten auszuheben und dann die benötigten Spalten zu markieren, nachdem Sie über ihre Erforderlichkeit für die Analyse entschieden haben.





Können nur die notwendigen Zeilen gefiltert werden?
Viele Tabellen in Unternehmensdatenbanken und Data Warehouses enthalten historische Daten, die über lange Zeiträume angesammelt wurden. Darüber hinaus ist es möglich, dass die Tabellen, denen Ihr Interesse gilt, Informationen über Geschäftsbereiche enthalten, die für Ihre spezifische Analyse bedeutungslos sind.

Mit dem Tabellen Import-Assistenten können Sie historische oder nicht verknüpfte Daten filtern und so viel Platz im Modell sparen. In der folgenden Abbildung wird ein Datumsfilter verwendet, um nur Zeilen abzurufen, die Daten für das aktuelle Jahr enthalten, ohne historische Daten, die nicht benötigt werden.





Wenn die Spalte nun aber doch benötigt wird – lässt sich ihr Platzbedarf dennoch verringern?
Es gibt ein paar zusätzliche Methoden, die Sie anwenden können, um eine Spalte für die Komprimierung zu optimieren. Denken Sie daran, dass die einzige Eigenschaft der Spalte, die sich auf die Komprimierung auswirkt, die Anzahl eindeutiger Werte ist. In diesem Abschnitt erfahren Sie, wie einige Spalten verändert werden können, um die Anzahl eindeutiger Werte zu verringern.

Ändern von Datetime-Spalten
In vielen Fällen verbrauchen Datetime-Spalten viel Platz. Glücklicherweise gibt es eine Reihe von Methoden zum Verringern des Speicherplatzbedarfs dieses Datentyps. Die Methoden variieren in Abhängigkeit davon, wie Sie die Spalte verwenden und wie vertraut Sie mit dem Erstellen von SQL-Abfragen sind.

Datetime-Spalten enthalten einen Datumsteil und eine Uhrzeit. Wenn Sie sich selbst fragen, ob Sie eine Spalte benötigen, stellen Sie sich diese Frage bei einer Datetime-Spalte mehrmals:

• Benötige ich den Uhrzeitteil?
  
  
• Benötige ich vom Uhrzeitteil die Stunden? ... Minuten? ... Sekunden? ... Millisekunden?
  
  
• Habe ich mehrere Datetime-Spalten, weil ich Differenzen zwischen diesen Spalten berechnen möchte, oder lediglich um die Daten nach Jahr, Monat, Quartal usw. zu aggregieren?
  
  

Ihre Antwort auf jede dieser Fragen bestimmt Ihre Möglichkeiten für den Umgang mit der Datetime-Spalte.

Alle dieser Lösungen machen die Änderung einer SQL-Abfrage erforderlich. Um die Änderung der Abfrage zu vereinfachen, sollten Sie in jeder Tabelle mindestens eine Spalte ausfiltern. Durch das Ausfiltern einer Spalte ändern Sie den Aufbau der Abfrage von einem verkürzten Format (SELECT *) in eine SELECT-Anweisung, die vollqualifizierte Spaltennamen enthält, die sich wesentlich einfacher ändern lassen.

Lassen Sie uns nun einen Blick auf die Abfragen werfen, die für Sie erstellt werden. Im Dialogfeld "Tabelleneigenschaften" können Sie zum Abfrage-Editor wechseln und die aktuelle SQL-Abfrage für jede Tabelle anzeigen.





Wählen Sie in "Tabelleneigenschaften" den Abfrage-Editor aus.





Im Abfrage-Editor wird die SQL-Abfrage angezeigt, mit der die Tabelle aufgefüllt wird. Wenn Sie während des Imports Spalten ausgefiltert hatten, enthält Ihre Abfrage vollqualifizierte Spaltennamen:

Im Gegensatz dazu wird, wenn Sie eine Tabelle vollständig, also ohne Deaktivierung von Spalten oder Anwendung eines Filters importiert haben, die Abfrage als "Select * from" angezeigt, was schwieriger zu ändern ist:

Ändern der SQL-Abfrage
Jetzt, wo Sie wissen, wie Sie die Abfrage finden, können Sie sie ändern, um die Größe Ihres Modells weiter zu verringern.

1. Verwenden Sie bei Spalten, die Währungs- oder Dezimaldaten enthalten, die folgende Syntax, um Dezimalstellen zu entfernen, wenn Sie diese nicht benötigen:
   
   "SELECT ROUND([Decimal_column_name],0)… .”
   
   Wenn Sie die Cents, aber nicht die Brüche von Cents benötigen, ersetzen Sie die 0 durch 2. Wenn Sie negative Zahlen verwenden, können Sie auf Einheiten, Zehner, Hunderte usw. Runden.
   
   
2. Wenn Sie eine Datetime-Spalte namens "dbo.Bigtable.[Date Time]" besitzen und den Zeitteil (Time) nicht benötigen, verwenden Sie folgende Syntax, um den Zeitteil zu entfernen:
   
   "SELECT CAST (dbo.Bigtable.[Date time] as date) AS [Date time]) "
   
   
3. Wenn Sie eine Datetime-Spalte namens "dbo.Bigtable.[Date Time]" besitzen und sowohl den Datumsteil (Date) als auch den Zeitteil (Time) benötigen, verwenden Sie in der SQL-Abfrage mehrere Spalten anstelle der einzelnen Datetime-Spalte:
   
   "SELECT CAST (dbo.Bigtable.[Date Time] as date ) AS [Date Time],
   
   datepart(hh, dbo.Bigtable.[Date Time]) as [Date Time Hours],
   
   datepart(mi, dbo.Bigtable.[Date Time]) as [Date Time Minutes],
   
   datepart(ss, dbo.Bigtable.[Date Time]) as [Date Time Seconds],
   
   datepart(ms, dbo.Bigtable.[Date Time]) as [Date Time Milliseconds]"
   
   Verwenden Sie so viele Spalten, wie Sie benötigen, um jeden Teil in einer getrennten Spalte zu speichern.
   
   
4. Wenn Sie Stunden- und Minutenangaben benötigen, diese aber lieber zusammen in einer Zeitspalte haben möchten, können Sie folgend Syntax verwenden:
   
   Timefromparts(datepart(hh, dbo.Bigtable.[Date Time]), datepart(mm, dbo.Bigtable.[Date Time])) as [Date Time HourMinute]
   
   
5. Wenn Sie zwei Datetime-Spalten haben, z. B. [Start Time] und [End Time], Sie aber eigentlich die Zeitdifferenz zwischen beiden Spalten in Sekunden als Spalte namens [Duration] benötigen, entfernen Sie beide Spalten aus der Liste, und fügen Sie Folgendes hinzu:
   
   "datediff(ss,[Start Date],[End Date] as [Duration]"
   
   Wenn Sie anstelle von "ss" das Schlüsselwort "ms" verwenden, erhalten Sie die Dauer (Duration) in Millisekunden.
   
   

Verwenden von mittels DAX berechneten Measures anstelle von Spalten
Wenn Sie bereits mit der DAX-Ausdruckssprache gearbeitet haben, wissen Sie eventuell schon, dass berechnete Spalten verwendet werden, um neue Spalten auf Grundlage anderer Spalten im Modell abzuleiten, während berechnete Measures einmalig im Modell definiert, und nur ausgewertet werden, wenn sie in einer PivotTable oder einem anderen Bericht verwendet werden.

Eine Speicherplatz sparende Methode besteht darin, normale oder berechnete Spalten durch berechnete Measures zu ersetzen. Das klassische Beispiel hierfür sind "Unit Price" (Einzelpreis), "Quantity" (Menge) und "Total" (Summe). Wenn Sie alle drei haben, können Sie Platz sparen, indem Sie nur zwei beibehalten und die dritte mit DAX berechnen.

Welche 2 Spalten sollten Sie behalten?
Behalten Sie im vorangehenden Beispiel "Quantity" (Menge) und "Unit Price" (Einzelpreis). Diese beiden haben weniger Werte als "Total" (Summe). Fügen Sie zum Berechnen von "Total" ein berechnetes Measure ähnlich dem folgenden hinzu:

"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"

Berechnete Spalten sind in der Hinsicht wie normale Spalten, dass sie ebenfalls Platz im Modell verbrauchen. Im Gegensatz dazu werden berechnete Measures bei Bedarf berechnet und belegen keinen Speicherplatz.

Fazit
In diesem Artikel haben wir mehrere Ansätze besprochen, die Ihnen beim Erstellen eines speichereffizienteren Modells hilfreich sein können. Das Reduzieren der Dateigröße und des Speicherplatzbedarfs eines Datenmodells erfolgt durch Verringerung der Gesamtzahl von Spalten und Zeilen sowie der Anzahl eindeutiger Werte, die in den jeweiligen Spalten vorkommen. Nachfolgend einige Methoden, die behandelt wurden:

• Das Entfernen von Spalten ist natürlich die beste Methode zum Einsparen von Platz. Entscheiden Sie, welche Spalten Sie wirklich benötigen.
  
  
• Manchmal können Sie eine Spalte entfernen und durch ein berechnetes Measure in der Tabelle ersetzen.
  
  
• Möglicherweise benötigen Sie nicht alle Zeilen in einer Tabelle. Sie können Zeilen im Tabellenimport-Assistenten ausfiltern.
  
  
• Im Allgemeinen stellt das Aufteilen einer einzelnen Spalte in mehrere getrennte Teile eine gute Möglichkeit dar, um die Anzahl eindeutiger Werte in einer Spalte zu verringern. Jeder dieser Teile enthält eine kleinere Anzahl eindeutiger Werte und die Gesamtsumme wird geringer sein als die Anzahl in der ursprünglichen Einzelspalte.
  
  
• In vielen Fällen benötigen Sie die getrennten Teile auch, um sie in Ihren Berichten als Datenschnitte verwenden zu können. Wo angebracht, können Sie Hierarchien aus Teilen wie Stunden, Minuten und Sekunden erzeugen.
  
  
• Häufig enthalten Spalten mehr Informationen als sie wirklich müssten. Angenommen eine Spalte speichert beispielsweise Dezimalwerte, aber Sie haben eine Formatierung angewendet, die alle Dezimalstellen ausblendet. Runden kann eine sehr effektive Methode sein, um die Größe einer numerischen Spalte zu verringern.
  
  

Nachdem Sie nun alle möglichen Schritte unternommen haben, um die Größe der Arbeitsmappe zu verringern, können Sie auch noch den Workbook Size Optimizer ausführen. Damit wird die Excel-Arbeitsmappe analysiert und nach Möglichkeit weiter komprimiert. Laden Sie den Arbeitsmappengröße-Optimiererherunter.

Links zu verwandten Themen
Spezifikation und Beschränkungen von Datenmodellen

Arbeitsmappen-Größen Optimierer herunterladen

Power Pivot: Leistungsstarke Datenanalyse und Datenmodellierung in Excel

:)