Archiv der Kategorie: PowerQuery

Access als Datensammler – die Single Source of Truth

Viel Jahre sah der übliche Daten-Aufbereitungsprozess für Controller immer gleich aus:

Vorsysteme wie SAP liefern Daten als Export in Form von Text-, CSV- oder Excel-Dateien. Diese wurden dann in mehr oder weniger aufwändigen Verfahren aufbereitet, um saubere Listen zu bekommen, die dann in Excel gespeichert wurden. Dies Daten konnten dann mit den verschiedensten Techniken, wie etwa SUMMEWENN, TEILERGEBNIS, PivotTabellen oder Konsolidieren verdichtet und mittels Diagrammen visualisiert werden.

Weiterlesen

„Wofür benötigen wir eigentlich noch ACCESS?“

Manchmal führe ich ja Selbstgespräche…

„Wozu brauchen wir eigentlich ACCESS noch?“ werde ich oft gefragt und frage mich das auch immer wieder selbst.

Na, zum strukturierten Speichern von Daten natürlich! Datenbank eben. Das haben wir hier im BLOG ja schon diskutiert…

Ja, aber EXCEL kann doch auch speichern und ist viel leichter zu bedienen.  

Das stimmt, aber man kann nicht so viele Zeilen speichern, es ist weniger performant, nicht organisiert und stabil, weder relational noch redundanz-arm, von Positionsunabhängigkeit und struktureller Flexibilität ganz zu schweigen! Und die Performance ist in jedem Fall noch schlechter, wenn durch das Herunterkopierern vieler SVERWEISE ineffizient gearbeitet wird!

Weiterlesen

Performance von Power Query-Modellen verbessern, Probleme vermeiden

Wer seine ersten Versuche mit Power Query unternimmt, kommt oft an den Punkt, an dem Power Query mit unerklärlichen Meldungen aufwartet, für die es noch dazu in den einschlägigen Foren keine Lösungen gibt. Das geht meist einher mit einer ungewöhnlichen Dauer bei den Aktualisierungen einher. Und dass, obwohl die 4GB bei der Dateigröße noch nicht erreicht wurden!

Die Ursachen dafür sind nicht immer eindeutig auszumachen, liegen unter Umständen in einer Kombination verschiedener Ursachen begründet:

Problem 1:

Nach dem Einstieg in die Nutzung von Power Query neigen die meisten Anwender dazu, mit zu vielen Einzelschritte Daten aus den Datenquellen zu verbinden und zu transformieren. So gibt es häufig redundante Schritte (z.B. Ändern des Datentyps für eine Spalte) oder zu viele einzelne Datenselektionsschritte. Mit der Kaffeetasse in der einen und der Maustaste in der anderen Hand, geht das ganz schnell! 😊
Allerdings werden diese Schritte bei einer Aktualisierung alle erneut durchgeführt. Das kann dann in der Summe dazu führen, dass Aktualisierungen außergewöhnlich lang dauern oder sich das Modell dabei „aufhängt“. Hier hilft nur „Ausmisten“ und optimieren
von Transformationsschritten.

Problem 2:

Power Query bietet die fantastische Möglichkeit, verschiedene Datenquellen in einem Datenmodell zu verbinden und die Daten aus diesen Quellen zu transformieren. Dabei kann ein ähnliches Problem entstehen, wie in unter 1 geschildert, auch wenn man alle Schritte optimiert. Hier hilft das sogenannte Staging-Kozept .

Abbildung: Staging-Konzeption in Anlehnung an Ken Pulse

Eine mögliche Methode, Abfragen aufzuteilen und zu ordnen, ist die Aufteilung in zwei Phasen bzw. Gruppen. Dafür sind die Begriffe Staging Queries und Loading Queries gebräuchlich.

Bei Staging Queries wird für jede Datenquelle eine Abfrage erstellt, die nur diese eine Datenquelle einliest. In dieser Abfrage finden alle Transformationen statt, es werden aber keine weiteren Datenquellen oder Abfrageergebnisse eingebunden.
Bei Loading Queries werden dagegen Ergebnisse von einer oder mehreren Staging Queries weiterverarbeitet. Sie greifen nie direkt auf eine Datenquelle zu, sondern beziehen sich stattdessen auf die jeweilige Staging Query.

Problem 3:

Sind viele „Filter“-Befehle notwendig, um im Power Query-Modell Datenselektionen vornehmen zu können, leidet bei großen Datenmengen die Performance. In diesem Fall (und nicht nur in diesem) ist es vorteilhaft, als Datenquelle ein Datenbank zu verwenden. Denn technisch gesehen ist es ein großer Unterschied, ob Sie als Datenquelle eine Datei (Excel, TXT, CSV) oder eine Datenbank nutzen. Während die Dateien beispielsweise keine Möglichkeit zur Filterung von Daten bieten, gibt es in einer Datenbank viele Möglichkeiten, Daten zu filtern, bzw. zu selektieren. Power Query setzt beim Zugriff auf Datenbanken eine Technik namens Query Folding ein. Query Folding ermöglicht es Power Query, möglichst viel Verarbeitungsschritte auf die Datenquelle auszulagern. Dazu werden Transformationsschritte in die Sprache der Datenbank übersetzt (z.B. SQL-Select, Distinct) und gesendet. Enthält eine Datenbank bspw. 25 Millionen Datensätze und sollen davon nur 2.000 verwendet werden, so geschieht die Selektion in der Datenbank. Danach werden nur diese 2.000 Datensätze in das Power-Query-Modell geladen und bearbeitet.

Fazit:

Power Query ist fantastisch im spontanen, flexiblen Zugriff auf Daten und in deren Verarbeitung (Transformation). Möchten Sie Power Query für ein permanentes (Reporting-)Modell einsetzen, so sollten Sie auf jeden Fall eine Datenbank als Datenquelle verwenden. Dies ist besonders dann notwendig, wenn Sie die Historie Ihrer Daten sichern und verwenden wollen. Haben Sie Office Pro im Einsatz, dann verfügen Sie auch über eine Access-Lizenz, die Open-Source Datenbank MySQL böte sich ebenfalls an, bzw. die bei Ihnen bereits im Zusammenhang mit Ihrem ERP-System (z.B. SAP) genutzte Datenbank (SQL Server, Oracle, DB2 usw.) . Einen möglichen Workaround dazu finden Sie in diesem BLOG-Beitrag.

Ein Laufwerk mit Power Query aufräumen

Normalerweise setzten wir mit großer Begeisterung Power Query für den Zugriff auf verschiedene Datenquelle und die Verdichtung von Daten zu einer Tabelle ein. (Mehr dazu in diesem Beitrag).

Allerdings kann man Power Query auch für „Aufräumarbeiten“ einsetzen. Haben Sie auch verschiedene Dateien doppelt und x-fach auf Ihrem Laufwerk gespeichert? Verschiedene Versionen, Stände, Varianten? Excel kann beim Aufräumen helfen! Mit Power Query können Sie ganz leicht ein Inhaltverzeichnis eines Orders oder eines ganzen Laufwerks erstellen. Mit Filtern nach Dateitypen, Häufigkeitszählung, Sortieren und Gruppieren auch nach Teilen des Dateinamens etc.

Und das geht so: Weiterlesen

Schriftart für Zeilen- und Spaltenköpfe ändern

Kürzlich in erhielt ich in einem Strategie Workshop eine ältere Excel-Datei mit einer Balanced Scorecard. Als ich damit begann Optimierungen in diesem Modell vorzunehmen, bemerkte ich die „seltsame“ Schriftart in den Spalten- und Zeilenköpfen. Es handelt sich um eine Serifenschriftart! Kurzer Blick in eine andere Datei: Eindeutig keine Serifenschriftart in den Zeilen und Spaltenköpfen!

Aus bestimmten Gründen wollte und konnte ich diese Serifenschriftart nicht beibehalten. Doch wie ändern?

Vorweg, es handelt sich hier nicht um ein „kriegsentscheidendes“ Excel-Wissen. Manchmal ärgert man sich aber über solche Kleinigkeiten! 😉

Weiterlesen

Daten mit Power Query zusammenführen – SVERWEIS 3.0

Mit Hilfe von Power Query lassen sich Stammdaten und Bewegungsdaten zu einer Datenliste vereinigen. Eine Aufgabenstellung, bei der viele USER die Funktionen SVERWEIS und / oder INDEX & VERGLEICH einsetzen.

Im aktuellen Beispiel gibt es vier Tabellenblätter in der Beispieldatei. Um eine gemeinsame Datenbasis zu erzeugen, müssen Bewegungsdaten (TB_Absatz) mit den Stammdaten verbunden werden. Dies geschieht normalerweise mit SVERWEIS & Co, hier im Beispiel mit Power Query: Weiterlesen

Big Data, Digitale Transformation, Controlling und Excel?????

Wie sieht die Rolle des Controllers in der digitalen Welt aus? Digitale Transformation, Internet 4.0, Internet der Dinge, BIG Data: Alles nur Buzz Words?

Wie verändern diese Themen die Rolle und die Aufgaben des Controllers? Der Internationale Controllerverein macht sich im Rahmen seiner Ideenwerkstatt dazu intensiv Gedanken,  während ein Fachkreis des ICV für dieses Thema Methoden und Werkzeuge entwickelt. Was wird sich aber konkret für Controller ändern, über welche Fähigkeiten müssen Sie verfügen? Hier ein paar Überlegungen zum Thema…… Weiterlesen