Contenidos
Diese Woche habe ich für einen Kunden gearbeitet - er hatte ein Leistungsproblem mit einem Power BI-Bericht. Die Daten in der Arbeitsmappe waren mit rund 400.000 Zeilen nicht zu groß, aber die Dateigröße betrug 110 Megabyte und die Leistung des Modells war angesichts der Anzahl der Datensätze relativ langsam. Als ich mir den Bericht ansah, stellte ich fest, dass der Bericht GUIDs zwischen Primär- und Fremdschlüsseln in mehreren Tabellen verwendete. Im Allgemeinen ist es keine gute Praxis, eine GUID zum Verknüpfen von Tabellen zu verwenden, da GUIDs nicht gut komprimiert werden und sich negativ auf die Effizienz physischer 1-zu-viele-Beziehungen auswirken.
Was ist eine GUID?
GUID ist eine Abkürzung für Globally Unique IDentifier. Kurz gesagt, eine GUID ist eine Hexadezimalzahl, die einer Dezimalzahl mit 39 Ziffern entspricht, etwa 1.000.000.000.000.000.000.000.000.000.000.000.000.000 (eine Duodezillion). Mit anderen Worten, eine GUID ist eine sehr eindeutige ID, deren Reproduktion (basierend auf der Wahrscheinlichkeit) unwahrscheinlich ist, selbst wenn sie zufällig generiert wurde. Lesen Sie hier mehr darüber:
https://betterexplained.com/articles/the-quick-guide-to-guids/
Was ist falsch an der Verwendung von GUIDs in Beziehungen?
Eine physische 1-zu-viele-Beziehung in Power BI ist ein kritischer Bestandteil der zugrunde liegenden Datenbankstruktur. Die Power BI-Engine (Vertipaq) materialisiert und speichert diese Beziehungen in der Datenbank und verwendet sie dann, um Filter schnell von einer Tabelle zur anderen zu übertragen (über die 1-zu-viele-Beziehung). Es ist sehr üblich (sogar wünschenswert), dass die Verhältnislogik in den schnellen L1- oder L2-Cache auf Ihrem PC-Chip geladen wird, damit Sie Ihre Arbeit superschnell erledigen können. Wenn die Beziehung nicht in den Cache passt, ist der gesamte Prozess langsamer. Dinge, die die Größe der Beziehung erhöhen können, sind die Anzahl der eindeutigen Werte in den in der Beziehung verwendeten Spalten, aber auch der in der Beziehung verwendete Datentyp. Es genügt zu sagen, dass eine GUID viel weniger effizient ist als ein ganzzahliger Wert als Schlüsselspalte in Power BI.
Geben Sie ein Ersatzkennwort ein
Eine Möglichkeit, dieses Problem zu lösen, besteht darin, die GUID durch einen Ersatzschlüssel zu ersetzen. Ein Ersatzschlüssel ist, wie der Name schon sagt, eine neue "Schlüsselspalte", die ein "Ersatz" (oder Ersatz) für die ursprüngliche Schlüsselspalte ist. In einer perfekten Welt, in der Sie einen SQL-Server im Back-End und eine IT-Abteilung haben, die die Arbeit für Sie erledigen kann, schlage ich vor, dass Ihre IT-Abteilung den Ersatzschlüssel für Sie erstellt und ihn sofort verfügbar macht. Sie müssen also nicht die GUID verwenden. Die Welt, in der wir leben, ist jedoch nicht perfekt. Daher zeigt Ihnen dieser Artikel, wie Sie die GUID mithilfe von Power Query durch einen Ersatzschlüssel ersetzen können.
Schritte zum Ausführen
Angenommen, Sie können die Arbeit an der Datenquelle nicht erledigen, ist dies im Wesentlichen ein Problem, das Power Query lösen muss. Die Schritte zum Abschließen dieses Vorgangs sind wie folgt
- Erstellen Sie eine Verbindung zur Rohdimensionstabelle
- Erstellen Sie einen Zweig in der Energieabfrage
- Fügen Sie eine neue Spalte mit einer Ganzzahl-ID (Ersatzschlüssel) hinzu.
- Verbinden Sie die Ersatzschlüsseltabelle erneut mit der ursprünglichen Dimensionstabelle und ersetzen Sie die GUID mithilfe des Joins
- Wiederholen Sie den Schritt, um die GUID in der Faktentabelle zu ersetzen
Bei diesem Vorgang wird davon ausgegangen, dass Ihre Dimensionstabelle eine vollständige Liste der Schlüssel enthält, die in Ihrer Faktentabelle vorhanden sind. Ist dies nicht der Fall, haben Sie trotzdem ein Problem. Es ist möglich, dieses Muster so zu variieren, dass es auch die GUIDs enthält, die aus der Faktentabelle stammen. Es löst jedoch nicht wirklich das Grundproblem (möglicherweise fehlen IDs in der Faktentabelle in der Dimensionstabelle) nicht als Teil der Lösung bereitgestellt / vorgeschlagen. Außerdem verlangsamt dies sicherlich die Aktualisierungszeit, ohne das Root-Problem zu lösen.
Stellen Sie eine Verbindung zu Daten her
Wie Sie im Bild unten sehen können, habe ich zwei Abfragen (RawCustomer, RawSales, die unten als Nummer 1 angezeigt werden), die direkt mit meinen Beispieldaten verbunden sind (ich habe Adventure Works so geändert, dass die Kundennummer eine GUID Nr. 2 unten verwendet). Beachten Sie, dass meine beiden Rohdatenabfragen einfache Verbindungen sind. Dies ist die Technik, die Ken in der Power Query Academy Trainingund ich denke, es ist eine großartige Übung. Von dort aus habe ich zwei Testabfragen erstellt (siehe Abbildung 3 unten), die einfache Verweise auf die Rohdatenabfragen sind
An diesem Punkt sieht es einfach so aus
Erstellen Sie einen Zweig in Power Query in der Dimensionstabelle
- Der nächste Schritt bestand darin, mit der rechten Maustaste auf die Kundenbereitschaftsabfrage zu klicken und "Referenz" auszuwählen, um eine neue Filiale zu erstellen. Ich habe diese neue Abfrage CustomerGUIDs genannt. In dieser Abfrage habe ich gerade die GUID-Spalte gespeichert und alles andere entfernt. Aus Sicherheitsgründen entferne ich die Duplikate, falls sich in der GUID-Spalte ein Duplikat befindet (allerdings unwahrscheinlich).
- Dann habe ich eine Indexspalte hinzugefügt, die bei 1 beginnt, und sie CustomerID genannt.
Die neue Spalte CustomerID ist der Ersatzschlüssel. Das Schöne an diesem Ansatz ist, dass die Ersatzschlüssel mit der Zeit wachsen, wenn die ursprüngliche GUID-Liste wächst (was sehr wahrscheinlich ist). Wenn es Löschungen aus der Kundentabelle gibt, wird der Ersatzschlüssel einfach mit dem neuen Datensatz neu erstellt.
Verbinden Sie die Ersatzschlüsseltabelle erneut, um eine neue Dimensionstabelle zu erstellen
Sie können die vorherige (# 1) und nachher (# 2) Version der Kundentabelle unten sehen.
Wiederholen Sie den Vorgang, um die GUID in der Faktentabelle zu ersetzen
- Ich habe eine neue Abfrage aus der SalesStaging-Abfrage erstellt (Rechtsklick, "Referenz") und sie mit der CustomerGUIDs-Tabelle zusammengeführt.
- Dann zog ich den Ersatzschlüssel heraus
- Dann habe ich die ursprüngliche GUID vom endgültigen Verkaufstisch entfernt.
Meine endgültige Ansicht der Abfrageabhängigkeit sieht folgendermaßen aus.
Ich habe sichergestellt, dass alle Tabellen so konfiguriert sind, dass sie nicht außer den Kunden- und Verkaufstabellen geladen werden.
Ergebnisse
Die Demo, die ich Ihnen hier gezeigt habe, enthält einen sehr kleinen Datensatz (19.000 Zeilen). In meinem Fall reduzierte der Ersatzschlüsselansatz die Dateigröße um mehr als 30%, und meine Datei enthielt nur eine Ersatzschlüsseltabelle. Für meinen Kunden mit rund 400.000 Datenzeilen, 3 GUID-Schlüsselspalten und einer viel größeren Anzahl eindeutiger GUIDs war die Reduzierung der Dateigröße von 110 MB auf nur 11 MB (90%-Reduzierung mit dem Ersatzschlüssel) viel größer. Am wichtigsten ist wahrscheinlich, dass es nach der Änderung spürbare Leistungsverbesserungen gab.
Gibt es negative Auswirkungen?
Updatezeit
An diesem Punkt wundern Sie sich vielleicht: "Verlangsamt dies nicht die Aktualisierungszeit?" Wenn Sie dies gedacht haben, haben Sie absolut Recht, es wird höchstwahrscheinlich die Leistung des Updates verlangsamen. Es ist jedoch viel besser, eine langsamere Aktualisierungszeit und eine schnellere Laufzeitleistung zu haben als umgekehrt.
Was ist, wenn ich meine GUIDs für die Prüfung benötige?
Ein zweites Problem besteht darin, dass Sie möglicherweise die GUIDs benötigen, um die Daten in Ihrem Bericht bis zur Transaktion auf dem Quellsystem zurückverfolgen zu können. Wenn dies bei Ihren Daten häufig erforderlich ist, empfehlen wir Ihnen, den Ersatzschlüssel weiterhin zu erstellen und zu verwenden, aber auch die GUID als zusätzliche Spalte in die Dimensionstabelle zu laden. Auf diese Weise ist die GUID verfügbar, wird jedoch in der Beziehung nicht verwendet. Besser noch, entfernen Sie die GUID aus der Dimensionstabelle und bringen Sie sie später wieder zurück, wenn Sie ein Problem haben, bei dem Sie die Quelle verfolgen müssen.
Fazit und wo man mehr erfahren kann
Hier ist ein Kopie der Arbeitsmappen Ich habe in diesem Artikel verwendet, falls Sie daran interessiert sind, genauer hinzuschauen.
Wenn Sie lernen möchten, wie Sie mit Power Query großartig umgehen können, empfehlen wir Ihnen, einen Blick auf die zu werfen Power Query Academy Online-Training bei Skillwave.training. Ken, Miguel und ich haben uns zusammengetan, um den besten und umfassendsten verfügbaren Power Query-Schulungskurs zu erstellen.
