Einleitung

Mit Microsoft Excel können in der Registerkarte Daten, Daten von zahlreichen Quellen geladen werden. Zu diesen Quellen gehören:

• Excel-Dateien
• Text-Dateien
• CSV-Dateien
• Online-Datenbanken
• und vieles mehr

Sharepoint ist ein Beispiel für eine externe Onlinequelle, aus der Excel Daten abrufen kann. Diese Daten können mittels PowerQuery geladen werden.

Vorab was ist überhaupt PowerQuery? PowerQuery ist ein Feature in Excel, mit dem ein Datensatz abgerufen und umgewandelt werden kann und zwar genau so, wie der Benutzer die Daten benötigt. Aus diesem Grund verwenden wir auch Power Query, um Daten aus Sharepoint zu laden.

PowerQuery gehört zu den leistungsstärksten Methoden, um Daten schnell und effizient zu bearbeiten. In diesem Artikel möchten wir PowerQuery so ausreizen, damit wir sehen, wo die Limiten sind und auf was geachtet werden muss, damit PowerQuery trotz grosser Datenmenge performant funktioniert.

In den folgenden Abschnitten werden wir die Leistung von Sharepoint überprüfen, wenn wir Daten von dort ins Excel laden. Zudem zeigen wir einen alternativen Ansatz zum Abrufen von Daten aus einer Online Quelle auf. Zuvor müssen wir jedoch die Art der durchgeführten Tests und auch die Spezifikationen des Geräts definieren:

Methode

1. Abrufen der Daten direkt aus Sharepoint – In diesem Test verwenden wir nur Power Query (MS Excel) und haben Sharepoint als Datenquelle.
2. Abrufen der Daten aus einer externen Excel-Datei – In diesem Test wird ein zusätzlicher Schritt zum Platzieren der SharePoint-Daten in einer Excel-Datei durchgeführt. Anschließend verweist die Power-Abfrage auf diese externe Datei.

Beide Tests werden wir in leicht unterschiedlicher Art und Weisen durchführen, um weitere Erkenntnisse zur Performance zu gewinnen.

Folgende Varianten führen wir durch:

1. Abrufen aller Zeilen und zwar ungefiltert und unsortiert – Wie bereits erwähnt, werden alle Daten aus der Datenquelle abgerufen
2. Abrufen des Datensatzes zuerst gefiltert und dann sortiert – Anstelle der 1 Million Zeilen ruft dieser Test nur einen Filterdatensatz ab. Im Allgemeinen bedeutet weniger abzurufende Zeilen eine schnellere Ladezeit.
3. Abrufen des Datensatzes zuerst sortiert und dann gefiltert – Ähnlich wie beim zweiten Test, jedoch sortieren wir den Datensatz zuerst, anstatt ihn zuerst zu filtern.

Die letzten beiden Tests sind in der Analyse enthalten, da wir überprüfen möchten, wie sich filtern und sortieren auf die Performance der Daten auswirkt.

In der Regel dauert die erste Ausführung eines Queries länger als die wiederholte Ausführung. Aus diesem Grund werden wir alle Tests zweifach ausführen, um zu überprüfen, welchen Einfluss dies auf die Performance hat.

Die folgende Hard- und Software wird für den Test verwendet:

• Prozessor: Intel i5 6. Generation
• Prozessorgeschwindigkeit: 2,50 GHz
• Arbeitsspeicher: 16 GB
• Excel: Microsoft Excel LTSC 64-Bit

Sharepoint und Power Query

Vor den Tests sollten wir erklären, wie die Beziehung von Sharepoint zu Power Query und Excel ist.

Das obige Bild zeigt, wie die Daten von Sharepoint nach Excel geladen werden. Um die Daten aus Sharepoint abzurufen, verwendet Excel Power Query. Die Daten in Sharepoint werden anschließend über Power Query abgerufen.

Die folgende Abbildung zeigt, wenn eine externe Excel-Datei als Zwischenspeicher für die Daten verwendet wird. Genau wie im ersten Bild werden die Daten mithilfe von Power Query in die externe Excel-Datei abgerufen. Der Grund für diesen Zwischenschritt ist, weil Excel die Daten schneller aus einer lokalen Excel-Datei laden kann, als aus einer Online-Quelle. Wenn Sie mehrere Abfragen durchführen müssen, dann warten Sie ohne den Zwischenschritt jedes Mal bis die Daten runtergeladen sind. Wenn Sie die Daten in einem Zwischenspeicher, speichern dann müssen Sie die Daten nur einmal abfragen und verweisen dann auf die lokale Datei.

Grundsätzlich ist es auch möglich, dass die Daten in der eigentlichen Excel-Datei gespeichert wird d.h. in der Datei, wo anschliessend die Daten auch analysiert werden. Nur müssen Sie beachten, dass dies einen Einfluss auf die Performance der Datei hat. Jedes Mal wenn Sie die Analyse Datei öffnen, dauert es dann eine gewisse Zeit bis alles geladen wurde. Mit der externen Excel-Datei umgehen Sie diese Problematik.

Eine weitere Sache, die Sie beachten müssen ist, dass Excel über gewisse Limiten verfügt.

Excel-Limiten

In Excel können pro Registerkarte maximal 1.048.576 Zeilen verwendet werden. Wenn Sie mehr Daten abfragen müssen, dann müssen Sie die Daten auf unterschiedliche Registerblätter aufteilen. Wobei bei einer so grossen Excel-Datei, dies auch auf die Performance einen Einfluss hat.

TEST I – Direkt aus Sharepoint

Der Test hier dient als Ausgangsbasis für den nächsten Test, damit wir wissen wie lange eine „normale“ Abfrage dauert. In diesem Test können wir sehen, wie lange es gedauert hat, die gesamten 1 Million Zeilen zum ersten Mal zu aktualisieren, und wie lange eine zweite Aktualisierung dauert.

Wie bereits weiter oben in diesem Artikel erwähnt, dauert die erste Aktualisierung länger als die nachfolgenden Aktualisierungen. Dies liegt daran, dass Power Query einen Routineprozess erstellt, um Daten zu speichern und/oder für spätere Verwendungen zu transformieren. Wir können sehen, dass die Aktualisierung in der Kategorie mit ungefilterten Daten 13 % schneller ist als bei der ersten Ausführung. Bei den anderen Tests können wir sehen, dass die Aktualisierung um 4 % und 3 % schneller ist.

Die zweite Gruppe von Tests zeigt, wenn die Daten zuerst gefiltert werden, bevor sie in Excel geladen werden. Im Allgemeinen ist es schneller, weniger Daten zu laden, selbst wenn zuerst einige Arbeitsschritte ausgeführt werden müssen, als alle Daten unbearbeitet zu laden. Und in diesem Test können wir sehen, dass der Datensatz 14 % schneller ist als der erste, wenn wir ihn zuerst filtern, bevor er geladen wird.

Bei der dritten Reihe von Tests, die unten gezeigt werden, werden die Daten zuerst sortiert und dann gefiltert. Das heißt, bevor die Daten in Excel geladen werden, sortiert diese Methode zuerst die Millionen Datensätze und filtert dann unnötige Daten heraus, sodass sie weniger Zeilen haben.

Im Gegensatz zur zweiten Methode ist diese langsamer als der zweite Test, aber immer noch schnell. Im Vergleich zum ersten Test ist die Aktualisierung 12 % schneller.

Erstes Laden der Daten
1Million – ungefiltert/unsortiert	08:05:00
1Million – zuerst gefiltert, dann sortiert	07:04:00
1Million – zuerst sortiert, dann gefiltert	07:12:00

Zweites Laden der Daten
1Million – ungefiltert/unsortiert	07:08:00
1Million – zuerst gefiltert, dann sortiert	06:51:00
1Million – zuerst sortiert, dann gefiltert	06:56:00

Mit den in diesem Test gezeigten Ergebnissen können wir schlussfolgern, dass der zweite Test mit dem Filtern und Sortieren der Daten viel schneller ist, als nur alle Daten abzurufen.

In den nächsten Tests verwenden wir eine externe Datei, an der die Powerabfrage arbeiten soll.

TEST II – Laden aus einer externen Excel-Datei mit Sharepoint-Anbindung

Der Zweck dieses Tests besteht darin, zu überprüfen, ob die Verwendung einer externen Datei schneller ist als das direkte Laden aus Sharepoint,  wobei die zuvor festgelegte Excel-Limite zu beachten ist.

In diesem Test laden wir zunächst die SharePoint-Daten in eine externe Excel-Datei. Das bedeutet, dass die obige Zeit auf jeden Fall auch hier einmal ausgeführt werden muss. Im obigen Ansatz, werden die Daten aber bei jeder Abfrage erneut geladen, in diesem Ansatz aber nur einmal.  Der Grund dafür ist, dass die externen Daten ihre Daten immer noch aus Sharepoint abrufen müssen. Im früheren Test dauerte es etwa 8 Minuten, um die 1 Million Zeilen beim ersten Durchlauf zu erhalten. Wir können sehen, dass die Dauer zum Aktualisieren der externen Datei nur etwa eine Minute beträgt, so dass die gesamte Dauer etwa 9 Minuten dauert, bis das Laden abgeschlossen ist.

Wenn wir die 8 Minuten zuvor ignorieren, können wir sehen, dass die Verwendung einer externen Datei beim Laden der Daten viel schneller sein kann. Das Gleiche gilt für die nächsten beiden Tests.

Erstes Laden der Daten
1Million – ungefiltert/unsortiert	00:01:08
1Million – zuerst gefiltert, dann sortiert	00:01:02
1Million – zuerst sortiert, dann gefiltert	00:01:04

Zweites Laden der Daten
1Million – ungefiltert/unsortiert	00:00:49
1Million – zuerst gefiltert, dann sortiert	00:00:45
1Million – zuerst sortiert, dann gefiltert	00:00:47

Mit den angezeigten Ergebnissen und der früheren Bedingung ist diese Methode schneller, vorausgesetzt, sie wird für mehrere Abfragen verwendet, die auf dieselbe Datenquelle verweisen. Wir können sehen, dass die Aktualisierung beim Abrufen von Daten in einem Setup mit mehreren Abfragen um mindestens 86 % schneller ist.

Schlussfolgerung

Zusammenfassend lässt sich sagen, dass Power Query bei einer so grossen Datenmenge eine gewisse Dauer benötigt, im Vergleich zu anderen Tools aber sehr gut mithalten kann.

Im Allgemeinen sollten nur die Daten abgerufen werden, die erforderlich sind. Weniger abgerufene Daten bedeuten eine schnellere Aktualisierungs-/Downloaddauer. Wenn die Daten nur eine Million oder weniger Zeilen enthalten, kann ein externes Excel verwendet werden. Andernfalls kann Sharepoint für diejenigen mit Datensätzen mit mehr als einer Million Zeilen die Quelle sein.

Benötigen Sie einen VBA Programmierer?

Wir als exact construct programmieren mit einem Team von rd. 20 Mitarbeitern seit über 10 Jahren Excel-Tools. Wir sind ein Nischenanbieter der spezialisiert auf Makros/VBA-Codes ist. Daneben unterstützen wir auch als 3rd Level Support die IT-Abteilungen rund um Probleme bei MS Office (Excel, Word, PowerPoint, etc.).

Haben Sie ein Excel-Problem? Benötigen Sie einen Makro-Programmierer? Rufen Sie uns unverbindlich an +41 52 511 05 25 oder kontaktieren Sie uns via Kontaktformular.