Was ist eigentlich der Unterschied zwischen Duplizieren und Verweis? Und wofür verwendet man einen Verweis? Und warum sollte man Abfragen deaktivieren?
Excel Power Pivot oder Power BI
Sowohl Power BI als auch Excel Power Pivot nutzen Power Query. Die folgenden Tipps und Tricks gelten für beide Werkzeuge. Unterschiede erläutern wir an der jeweiligen Stelle.
Verweise & Duplikate
Legen Sie in Power Query eine neue Abfrage an. Im Editor sehen Ihre Daten in einer Vorschau. Dort in der linken Hälfte finden sind Ihre neue und gegebenenfalls andere Abfragen in einer Liste dargestellt.
Sie können jede Abfrage duplizieren oder einen Verweis darauf erstellen.
Die Funktion Duplizieren kopiert die Abfrage. Die kopierte Abfrage ist damit unabhängig vom Original. Für einen Test sehr nützlich.
Erstellen Sie hingegen einen Verweis, wird eine zweite Abfrage angelegt. Diese neue Abfrage greift auf das Ergebnis der ersten zu. Die M-Syntax der zweiten Abfrage ist denkbar einfach.
Wozu benutzt man Verweise?
Daten liegt häufig in einer großen zweidimensionalen Form vor. Beispielsweise bei einer Tabelle Auftragspositionen.
Dabei wären uns eine eigene Materialtabelle, eine Auftragspositionstabelle und eine Kundentabelle lieber. Mit diesen Tabellen (und sicherlich noch einer Kalender-Tabelle) können wir ein Star-Schema aufbauen. In diesen separierten Tabellen dienen die Felder Materialnr und Kundennr als Verknüpfung.
Leider kann man oftmals an der Datenquelle nichts ändern. Trotzdem können wir uns mit Power Query behelfen. Als erstes nehmen wir uns die die Materialtabelle vor. Folgende Schritte führen wir nacheinander aus:
- Verweise auf die Auftragspositionen-Tabelle
- umbenennen der neuen Abfrage in DimMaterial
- Funktion Spalten auswählen und nur die Spalten Materialnr und Materialtext behalten.
- Spalte Materialnr markieren und Funktion Zeilen entfernen – Duplikate entfernen auswählen
Fertig ist die Dimensionstabelle DimMaterial.
Und Für die Kundendaten gehen Sie gleich vor. Damit bleibt am Ende folgende Tabelle übrig:
Im letzten Schritt bearbeiten wir die Faktentabelle. Die Felder Materialtext oder Kundenort benötigen wir dort nicht mehr. Diese Spalten dürfen wir in der Ausgangstabelle aber nicht löschen! Die Abfragen DimMaterial und DimKunden verweisen auf diese Spalten. Daher erstellen wir einen weiteren Verweis auf die Tabelle Auftragspositionen und nennen diese FaktAuftragspositionen. Über die Funktion Spalten wählen aktivieren wir noch folgende Spalten:
Diese drei Tabellen können wir später im Datenmodell über die Felder Kundennr und Materialnr wieder in Beziehung setzen.
Abfragen nicht laden
Vielleicht haben Sie im Kontextmenü einer Abfrage in Power BI die Option Laden aktivieren gefunden. Deaktivieren Sie diese Option, wird die Abfrage kursiv angezeigt. Damit wird die Abfrage nicht mehr geladen und die Ergebnisse der Abfrage stehen im Bericht nicht mehr zur Verfügung.
In Excel Power Pivot ist das ein kleines bisschen umständlicher. Im Dialog Laden In definieren Sie, ob das Ergebnis der Abfrage in ein Tabellenblatt, in das Datenmodell oder in beide geladen werden soll. Hier wählen Sie Nur Verbindung erstellen.
Anders als in Power BI sehen Sie in Power Query die Abfrage nicht kursiv dargestellt. In Excel sehen Sie das im Fenster Abfragen und Verbindungen.
Wozu benötigen wir deaktivierte Abfragen? Im obigen Beispiel Fall haben wir vier Tabellen:
- Auftragspositionen
- DimMaterial
- DimKunden
- FaktAuftragspositionen
Die Tabelle Auftragspositionen dient uns nur als Quelle für die anderen Abfragen. Das Ergebnis dieser Abfrage benötigen wir nicht im Datenmodell. Durch Nur Verbindung erstellen erreichen wir genau das: Die Tabelle wird nicht geladen.
Organisation mit Gruppen
Mit den gezeigten Methoden erstellen Sie in Ihren Projekten mehr Abfragen. Einige davon werden nicht geladen. Andere sind Verweise. Damit Sie die Übersicht nicht verlieren, können Sie Abfragen entsprechend benennen. Ich benenne Abfragen meist mit einem Unterstrich als Präfix, wenn sie nicht geladen werden. Also beispielsweise \Auftragspositionen_.
Darüber hinaus können Sie Abfragen zusätzlich in Ordnern organisieren. In Power Query Gruppen genannt. Im der Liste der Abfragen finden Sie die Funktion Neue Gruppe. Legen Sie eine Gruppe an wird automatisch eine weitere mit Namen Andere Abfragen angelegt. In dieser anderen Gruppe legt Power Query neue, nicht zugeordnete Abfragen an. Via Drag and Drop können Sie die Abfragen organisieren:
In Excel in Abfragen und Verbindungen wird diese Struktur entsprechend dargestellt:
Kommentar hinterlassen
Ob Sie eine Abfrage haben oder eine Abfrage mit Verweis auf eine andere, macht technisch keinerlei Unterschied. Intern wird ein sog. "Abstrakter Syntax Baum" aufgebaut, der dann an den Treiber der Datenverbindung weiter gegeben wird - um daraus beispielsweise SQL zu erzeugen. Nur ist das ein wenig schwierig verständlich. Daher bedient man sich dem Bild, dass die zweite Abfrage auf die erste zugreift.
Was Microsoft schreibt und Sie zitiert haben, ist leider etwas missverständlich und aus dem Kontext gerissen: Wenn Sie aus Ihrer Datenquelle Daten abfragen, beispielsweise in vier verschiedenen Abfragen, dann belasten Sie die Datenquelle vier mal. Wenn Sie eine Abfrage als Grundlage nehmen von der Sie dann weitere vier Abfragen ableiten, dann belasten Sie Ihre Datenquelle ebenfalls vier mal. Aber: die gemeinsame Logik der vier Abfragen haben Sie im zweiten Fall in einer Abfrage statt verstreut in vier Abfragen. Ob Sie vier Abfragen parallel oder vier Abfragen, die auf eine gemeinsame Abfrage greifen, ausführen, macht aus Sicht der Performance keinen Unterschied. Die Datenquelle wird in beiden Fällen vier mal belastet. Dessen muss man sich aber natürlich bewusst sein.
Wichtig: Die Zweite Abfrage greift nur logisch auf das Ergbnis der ersten Abfrage zu. Die Daten der ersten Abfrage werden NICHT gepuffert und man spart sich keine Performance/Datenbank-Zugriffe dadurch.
Möchte man die Datenquelle entlasten, muss man die Daten in eine Zwischendatenbank überführen. Diese wird dann (egal ob Verweis oder nicht) auch vier mal belastet, aber eben nicht die Quelldatenbank, die meistens noch für den operativen Betrieb genutzt wird. Und das ist dann auch der Hinweis in dem Artikel von Microsoft: Zugriff auf Dataflows. Hier werden die Daten tatsächlich geladen und zwischen gespeichert. Allerdings raten wir von Dataflows eher ab, sofern man keinen bezahlten DataLake nutzt - das führt jetzt hier aber zu weit.