10 Möglichkeiten zur Verbesserung der Power Pivot-Leistung

Wenn Sie Power Pivot-Berichte im Web veröffentlichen, möchten Sie Ihrem Publikum die bestmögliche Erfahrung bieten. Ein großer Teil dieser Erfahrung stellt sicher, dass die Leistung gut ist. Das Wort Leistung (in Bezug auf Anwendungen und Berichterstellung) ist normalerweise gleichbedeutend mit Geschwindigkeit – oder wie schnell eine Anwendung bestimmte Aktionen wie das Öffnen im Browser, das Ausführen von Abfragen oder das Filtern ausführt.

1Begrenzen Sie die Anzahl der Zeilen und Spalten in Ihren Datenmodelltabellen.

Ein großer Einfluss auf die Power Pivot-Leistung ist die Anzahl der Spalten, die Sie in das Datenmodell einbringen oder importieren . Jede importierte Spalte ist eine weitere Dimension, die Power Pivot beim Laden einer Arbeitsmappe verarbeiten muss. Importieren Sie keine zusätzlichen Spalten „nur für alle Fälle“ – wenn Sie sich nicht sicher sind, ob Sie bestimmte Spalten verwenden werden, fügen Sie sie einfach nicht ein. Diese Spalten können Sie später leicht hinzufügen, wenn Sie sie benötigen.

Mehr Zeilen bedeuten mehr zu ladende Daten, mehr zu filternde Daten und mehr zu berechnende Daten. Vermeiden Sie die Auswahl einer ganzen Tabelle, wenn dies nicht erforderlich ist. Verwenden Sie eine Abfrage oder eine Ansicht in der Quelldatenbank, um nur nach den Zeilen zu filtern, die Sie importieren müssen. Warum 400.000 Datenzeilen importieren, wenn Sie eine einfache WHERE-Klausel verwenden und nur 100.000 importieren können?

2Verwenden Sie Ansichten anstelle von Tabellen.

Apropos Ansichten: Verwenden Sie als Best Practice wann immer möglich Ansichten.

Obwohl Tabellen transparenter sind als Ansichten – sodass Sie alle ungefilterten Rohdaten sehen können – werden sie mit allen verfügbaren Spalten und Zeilen geliefert, unabhängig davon, ob Sie sie benötigen oder nicht. Um Ihr Power Pivot-Datenmodell auf einer überschaubaren Größe zu halten, sind Sie oft gezwungen, den zusätzlichen Schritt zu unternehmen, die nicht benötigten Spalten explizit herauszufiltern.

Ansichten können nicht nur sauberere, benutzerfreundlichere Daten bereitstellen, sondern auch dazu beitragen, Ihr Power Pivot-Datenmodell zu optimieren, indem die importierte Datenmenge begrenzt wird.

3Vermeiden Sie Beziehungen auf mehreren Ebenen.

Sowohl die Anzahl der Beziehungen als auch die Anzahl der Beziehungsebenen wirken sich auf die Leistung Ihrer Power Pivot-Berichte aus. Befolgen Sie beim Erstellen Ihres Modells die Best Practices und verwenden Sie eine einzige Faktentabelle, die hauptsächlich quantitative numerische Daten (Fakten) und Dimensionstabellen enthält, die sich direkt auf die Fakten beziehen. In der Datenbankwelt ist diese Konfiguration , wie gezeigt, ein Sternschema .

Vermeiden Sie die Erstellung von Modellen, bei denen Dimensionstabellen sich auf andere Dimensionstabellen beziehen.

4Überlassen Sie die Verarbeitung der Back-End-Datenbankserver.

Die meisten Excel-Analysten, die neu bei Power Pivot sind, ziehen Rohdaten in der Regel direkt aus den Tabellen auf ihren externen Datenbankservern. Nachdem sich die Rohdaten in Power Pivot befinden, erstellen sie berechnete Spalten und Kennzahlen, um die Daten nach Bedarf zu transformieren und zu aggregieren. Beispielsweise rufen Benutzer häufig Umsatz- und Kostendaten ab und erstellen dann eine berechnete Spalte in Power Pivot, um den Gewinn zu berechnen.

Warum also diese Berechnung von Power Pivot durchführen lassen, wenn der Back-End-Server sie hätte verarbeiten können? Die Realität ist, dass Back-End-Datenbanksysteme wie SQL Server in der Lage sind, Daten viel effizienter zu formen, zu aggregieren, zu bereinigen und zu transformieren als Power Pivot. Warum nicht ihre leistungsstarken Funktionen nutzen, um Daten zu massieren und zu formen, bevor sie in Power Pivot importiert werden?

Anstatt Tabellenrohdaten abzurufen, sollten Sie Abfragen, Ansichten und gespeicherte Prozeduren nutzen, um so viel wie möglich von der Datenaggregation und -verarbeitung durchzuführen. Diese Nutzung reduziert den Verarbeitungsaufwand für Power Pivot und verbessert auf natürliche Weise die Leistung.

5Achten Sie auf Spalten mit nicht eindeutigen Werten.

Spalten mit einer hohen Anzahl eindeutiger Werte beeinträchtigen die Power Pivot-Leistung besonders. Spalten wie Transaktions-ID, Bestell-ID und Rechnungsnummer sind in Power Pivot-Berichten und -Dashboards auf hoher Ebene oft unnötig. Lassen Sie sie also aus Ihrem Modell weg, es sei denn, sie werden benötigt, um Beziehungen zu anderen Tabellen herzustellen.

6Begrenzen Sie die Anzahl der Slicer in einem Bericht.

Der Slicer ist eine der besten neuen Business Intelligence (BI)-Funktionen von Excel in den letzten Jahren. Mit Slicern können Sie Ihrem Publikum eine intuitive Benutzeroberfläche zur Verfügung stellen, die eine interaktive Filterung Ihrer Excel-Berichte und -Dashboards ermöglicht.

Einer der nützlicheren Vorteile des Slicers besteht darin, dass er auf andere Slicer reagiert und einen kaskadierenden Filtereffekt bietet. Die Abbildung veranschaulicht beispielsweise nicht nur, dass ein Klick auf Midwest im Region-Slicer die Pivot-Tabelle filtert, sondern dass auch der Market-Slicer reagiert, indem er die Märkte hervorhebt, die zur Region Midwest gehören. Microsoft nennt dieses Verhalten Cross-Filtering.

So nützlich der Slicer auch ist, leider ist er für die Power Pivot-Leistung extrem schlecht. Jedes Mal, wenn ein Slicer geändert wird, muss Power Pivot alle Werte und Kennzahlen in der Pivot-Tabelle neu berechnen. Dazu muss Power Pivot jede Kachel im ausgewählten Slicer auswerten und die entsprechenden Berechnungen basierend auf der Auswahl verarbeiten.

7Erstellen Sie Slicer nur für Dimensionsfelder.

Slicer, die an Spalten gebunden sind, die viele eindeutige Werte enthalten, verursachen häufig einen größeren Leistungseinbruch als Spalten, die nur eine Handvoll Werte enthalten. Wenn ein Slicer eine große Anzahl von Kacheln enthält, sollten Sie stattdessen die Dropdown-Liste Pivot-Tabellenfilter verwenden.

Stellen Sie in ähnlicher Weise sicher, dass die Spaltendatentypen die richtige Größe haben. Eine Spalte mit wenigen eindeutigen Werten ist heller als eine Spalte mit einer hohen Anzahl eindeutiger Werte. Wenn Sie die Ergebnisse einer Berechnung aus einer Quelldatenbank speichern, reduzieren Sie die Anzahl der zu importierenden Stellen (nach dem Komma). Dies reduziert die Größe des Wörterbuchs und möglicherweise die Anzahl der unterschiedlichen Werte.

8Deaktivieren Sie das Cross-Filter-Verhalten für bestimmte Slicer.

Das Deaktivieren des Cross-Filter-Verhaltens eines Slicers verhindert im Wesentlichen, dass dieser Slicer die Auswahl ändert, wenn auf andere Slicer geklickt wird. Dadurch wird verhindert, dass Power Pivot die Titel im deaktivierten Slicer auswerten muss, wodurch die Verarbeitungszyklen verkürzt werden. Um das Cross-Filter-Verhalten eines Slicers zu deaktivieren, wählen Sie Slicer-Einstellungen, um das Dialogfeld Slicer-Einstellungen zu öffnen. Deaktivieren Sie dann einfach die Option Elemente ohne Daten visuell anzeigen.

9Verwenden Sie berechnete Kennzahlen anstelle von berechneten Spalten.

Verwenden Sie nach Möglichkeit berechnete Kennzahlen anstelle von berechneten Spalten. Berechnete Spalten werden als importierte Spalten gespeichert. Da berechnete Spalten von Natur aus mit anderen Spalten im Modell interagieren, berechnen sie jedes Mal, wenn die Pivot-Tabelle aktualisiert wird, unabhängig davon, ob sie verwendet werden oder nicht. Berechnete Maße hingegen werden nur zur Abfragezeit berechnet.

Berechnete Spalten ähneln regulären Spalten insofern, als sie beide Platz im Modell beanspruchen. Im Gegensatz dazu werden berechnete Maße im laufenden Betrieb berechnet und nehmen keinen Platz ein.

10Upgrade auf 64-Bit-Excel.

Wenn bei Ihren Power Pivot-Berichten weiterhin Leistungsprobleme auftreten, können Sie jederzeit einen besseren PC kaufen – in diesem Fall durch ein Upgrade auf einen 64-Bit-PC mit installiertem 64-Bit-Excel.

Power Pivot lädt das gesamte Datenmodell in den Arbeitsspeicher, wenn Sie damit arbeiten. Je mehr RAM Ihr Computer hat, desto weniger Leistungsprobleme werden angezeigt. Die 64-Bit-Version von Excel kann auf mehr RAM Ihres PCs zugreifen und stellt so sicher, dass er über die Systemressourcen verfügt, die zum Durcharbeiten größerer Datenmodelle erforderlich sind. Tatsächlich empfiehlt Microsoft 64-Bit-Excel für alle, die mit Modellen arbeiten, die aus Millionen von Zeilen bestehen.

Bevor Sie jedoch eilig mit der Installation von 64-Bit-Excel beginnen, müssen Sie diese Fragen beantworten:

Haben Sie bereits 64-Bit-Excel installiert?

Sind Ihre Datenmodelle groß genug?

Haben Sie ein 64-Bit-Betriebssystem auf Ihrem PC installiert?

Werden Ihre anderen Add-Ins nicht mehr funktionieren?