Beziehungsparameter richtig festlegen

Diese Seite richtet sich an alle Nutzer, die versuchen, mit LookML ein Explore in Looker zu erstellen. Die Seite ist leichter verständlich, wenn Sie mit SQL vertraut sind, insbesondere wenn Sie den Unterschied zwischen inneren und äußeren Joins kennen. Eine kurze Erklärung dazu, wie sich innere und äußere Joins unterscheiden, finden Sie in diesem W3Schools-Artikel zu SQL-Joins.

Looker kann eine leistungsstarke SQL-Engine für Ihr Unternehmen sein. Mit der abstrakten Modellierung in LookML können Daten- und IT-Teams allgemeine Regeln erstellen, die immer wahr sind. So können Business Analysten Abfragen erstellen, die immer korrekt sind, auch wenn das Datenteam nie einen Bedarf dafür vorhergesehen hat. Der Hauptgrund für diese Funktion ist der Algorithmus für symmetrische Aggregate, der ein branchenweites Problem mit SQL-Joins löst. Damit Sie den Algorithmus jedoch optimal nutzen können, müssen zwei Dinge richtig gemacht werden: Die Primärschlüssel müssen in jeder Ansicht, die einen Messwert enthält (in der Regel in allen), korrekt sein. Außerdem müssen die relationship-Parameter in jedem Join korrekt sein.

Primärschlüssel

In vielerlei Hinsicht ist das Verständnis des Primärschlüssels einer Tabelle im Wesentlichen dasselbe wie das Verständnis der Tabelle und ihrer Verwendungsmöglichkeiten. Die einzige Voraussetzung ist, dass die Spalte (oder die zusammengesetzten Spalten), die Sie als Primärschlüssel auswählen, keine wiederholten Werte enthalten darf.

Der Parameter relationship

Nachdem Sie Ihre Primärschlüssel überprüft haben, können Sie den richtigen Wert für den relationship-Parameter des Joins ermitteln. Mit dem Parameter relationship wird Looker mitgeteilt, ob beim Schreiben des Joins in eine SQL-Abfrage symmetrische Aggregate aufgerufen werden sollen. Eine mögliche Lösung wäre, Looker anzuweisen, sie immer aufzurufen. Dadurch würden immer korrekte Ergebnisse erzielt. Dies hat jedoch Leistungskosten zur Folge. Daher sollten Sie symmetrische Aggregate mit Bedacht verwenden.

Die Ermittlung des korrekten Werts unterscheidet sich bei inneren und äußeren Zusammenführungen geringfügig.

Inner Joins

Angenommen, Sie haben eine Tabelle mit Bestellungen mit dem Primärschlüssel order_id:

order_id Menge customer_id
1 25,00 $ 1
2 50,00 $ 1
3 75,00 $ 2
4 35,00 $ 3

Angenommen, Sie haben auch eine Kundentabelle mit dem Primärschlüssel customer_id:

customer_id first_name last_name Besuche
1 Amelia Earhart 2
2 Bessie Coleman 2
3 Wilbur Wright 4

Sie können diese Tabellen über das Feld customer_id zusammenführen, das in beiden Tabellen vorhanden ist. Dieser Join würde in LookML so dargestellt:

explore: orders {
  join: customers {
    type: inner
    sql_on: ${orders.customer_id} = ${customers.customer_id} ;;
    relationship: many_to_one
  }
}

Das Ergebnis dieses LookML-Joins kann als einzelne zusammengeführte Tabelle dargestellt werden:

order_id Menge customer_id customer_id first_name last_name Besuche
1 25,00 $ 1 1 Amelia Earhart 2
2 50,00 $ 1 1 Amelia Earhart 2
3 75,00 $ 2 2 Bessie Coleman 2
4 35,00 $ 3 3 Wilbur Wright 4

Die many_to_one-Beziehung bezieht sich hier darauf, wie oft ein Wert des Zusammenführungsfelds (customer_id) in jeder Tabelle vertreten ist. In der orders-Tabelle (linke Tabelle) ist eine einzelne Kundennummer mehrmals vertreten. In diesem Fall ist es der Kunde mit der ID 1, der in mehreren Zeilen vorhanden ist.

In der Tabelle customers (rechte Tabelle) ist jede Kundennummer nur einmal vertreten, da customer_id der Primärschlüssel dieser Tabelle ist. Daher können Datensätze in der Tabelle orders viele Übereinstimmungen für einen einzelnen Wert in der Tabelle customers haben. Wenn customer_id in jeder Zeile der Tabelle customers nicht eindeutig ist, ist die Beziehung many_to_many.

So können Sie den richtigen Beziehungswert programmatisch ermitteln, indem Sie Primärschlüssel prüfen:

  1. Geben Sie als Beziehung zuerst many_to_many ein. Solange Ihre Primärschlüssel korrekt sind, werden immer genaue Ergebnisse erzielt, da Looker immer den Algorithmus für die symmetrische Aggregation auslöst und für Genauigkeit sorgt. Da der Algorithmus jedoch Abfragen verkompliziert und die Ausführungszeit verlängert, ist es sinnvoll, eine oder beide Seiten in one anstelle von many zu ändern.
  2. Sehen Sie sich die Felder in der sql_on-Klausel in der linken Tabelle an. Wenn das Feld oder die Felder den Primärschlüssel der linken Tabelle bilden, können Sie die linke Seite des relationship-Parameters in one ändern. Andernfalls muss es in der Regel many bleiben. Informationen zu einem Sonderfall finden Sie weiter unten im Abschnitt Wichtige Hinweise.
  3. Sehen Sie sich als Nächstes die Felder an, die in der sql_on-Klausel für die rechte Tabelle stehen. Wenn das Feld oder die Felder den Primärschlüssel der rechten Tabelle bilden, können Sie die rechte Seite in one ändern.

Es empfiehlt sich, den sql_on-Ausdruck mit der linken Tabelle zu beginnen, die sich links vom Gleichheitszeichen befindet, und der rechten Tabelle, die sich auf der rechten Seite befindet. Die Reihenfolge der Bedingungen im Parameter sql_on spielt keine Rolle, es sei denn, die Reihenfolge ist für den SQL-Dialekt Ihrer Datenbank relevant. Für den Parameter sql_on ist es nicht erforderlich, die Felder so anzuordnen. Wenn Sie die sql_on-Bedingungen jedoch so anordnen, dass die linke und rechte Seite des Gleichheitszeichens mit der Lesrichtung des relationship-Parameters von links nach rechts übereinstimmen, können Sie die Beziehung leichter bestimmen. Wenn Sie die Felder so anordnen, können Sie auf einen Blick erkennen, mit welcher vorhandenen Tabelle im Explore Sie die neue Tabelle zusammenführen.

Outer Joins

Bei äußeren Zusammenführungen müssen Sie außerdem berücksichtigen, dass es zu einer Verzweigung kommen kann, wenn während der Zusammenführung Null-Eintragssätze hinzugefügt werden. Das ist besonders wichtig, da in Looker standardmäßig linke äußere Joins verwendet werden. Nulle Datensätze haben zwar keine Auswirkungen auf Summen oder Durchschnittswerte, aber auf die Art und Weise, wie Looker ein Maß für type: count berechnet. Andernfalls werden die Nulleinträge gezählt, was unerwünscht ist.

Bei einem Full Outer Join können beiden Tabellen Nulldatensätze hinzugefügt werden, wenn für den Join-Schlüssel Werte fehlen, die in der anderen Tabelle vorhanden sind. Das folgende Beispiel mit einer orders-Tabelle veranschaulicht dies:

order_id Menge customer_id
1 25,00 $ 1
2 50,00 $ 1
3 75,00 $ 2
4 35,00 $ 3

Angenommen, Sie haben auch die folgende Tabelle customers:

customer_id first_name last_name Besuche
1 Amelia Earhart 2
2 Bessie Coleman 2
3 Wilbur Wright 4
4 Karl Yeager 3

Nach der Zusammenführung kann die zusammengeführte Tabelle so dargestellt werden:

order_id Menge customer_id customer_id first_name last_name Besuche
1 25,00 $ 1 1 Amelia Earhart 2
2 50,00 $ 1 1 Amelia Earhart 2
3 75,00 $ 2 2 Bessie Coleman 2
4 35,00 $ 3 3 Wilbur Wright 4
null null null 4 Karl Yeager 3

Genau wie bei einem inneren Join ist die Beziehung zwischen den Primärschlüsseln der Tabellen many_to_one. Der hinzugefügte Nulleintrag erfordert jedoch auch symmetrische Aggregate in der linken Tabelle. Daher müssen Sie den Parameter relationship in many_to_many ändern, da durch die Ausführung dieses Joins die Zählungen in der linken Tabelle gestört werden.

Wenn es sich bei diesem Beispiel um einen linken äußeren Join gehandelt hätte, wäre die Nullzeile nicht hinzugefügt und der zusätzliche Kundendatensatz gelöscht worden. In diesem Fall würde die Beziehung weiterhin many_to_one sein. Das ist die Standardeinstellung in Looker, da davon ausgegangen wird, dass die Analyse durch die Basistabelle definiert wird. In diesem Fall analysieren Sie Bestellungen, nicht Kunden. Wenn die Kundentabelle links platziert wäre, wäre die Situation anders.

Mehrere Joins

In einigen Explores wird die Basistabelle mit einer oder mehreren Ansichten verknüpft, die wiederum mit einer oder mehreren zusätzlichen Ansichten verknüpft werden müssen. Im Beispiel hier würde das bedeuten, dass eine Tabelle mit der Kundentabelle zusammengeführt wird. In diesen Fällen sollten Sie bei der Auswertung des relationship-Parameters nur den einzelnen Join betrachten, der geschrieben wird. Looker erkennt, wenn sich eine abgeleitete Verzweigung auf eine Abfrage auswirkt, auch wenn sich die betroffene Ansicht nicht in der Verbindung befindet, durch die die Verzweigung erstellt wurde.

Wie kann mir Looker helfen?

In Looker gibt es Mechanismen, die dafür sorgen, dass der Beziehungswert korrekt ist. Eine davon ist die Prüfung auf Eindeutigkeit des Primärschlüssels. Wenn es eine Verzweigung gibt und symmetrische Aggregate zur Berechnung eines Messwerts erforderlich sind, prüft Looker den verwendeten Primärschlüssel auf Eindeutigkeit. Wenn sie nicht eindeutig ist, wird bei der Abfrageausführung ein Fehler angezeigt. Es wird jedoch kein Fehler vom LookML-Validator ausgegeben.

Wenn Looker eine Verzweigung nicht verarbeiten kann (normalerweise, weil kein Primärschlüssel angegeben ist), werden in der explorativen Datenanalyse aus dieser Ansicht keine Messwerte angezeigt. Sie können das Problem beheben, indem Sie ein Feld als Primärschlüssel festlegen, damit Ihre Messwerte in die explorative Datenanalyse aufgenommen werden.

Wichtige Punkte

Unterstützung von symmetrischen Summen durch Datenbankdialekt

Looker kann eine Verbindung zu einigen Dialekten herstellen, die symmetrische Summen nicht unterstützen. Eine Liste der Dialekte und ihrer Unterstützung für symmetrische Summen finden Sie auf der Dokumentationsseite zu symmetric_aggregates.

Sonderfall

Im Abschnitt Inner Join weiter oben auf dieser Seite wird erläutert, dass Sie zum Ermitteln des richtigen Beziehungswerts das Feld oder die Felder in der sql_on-Klausel der linken Tabelle prüfen sollten: „Wenn das Feld oder die Felder den Primärschlüssel der linken Tabelle bilden, können Sie die linke Seite des Parameters relationship in one ändern. Andernfalls muss es in der Regel als many bleiben. Dies gilt, es sei denn, Ihre Tabelle enthält mehrere Spalten ohne wiederholte Einträge. In diesem Fall können Sie bei der Formulierung der Beziehung jede solche Spalte so behandeln, als wäre sie ein Primärschlüssel, auch wenn es sich nicht um die Spalte handelt, die als primary_key: yes gekennzeichnet ist.

Es kann hilfreich sein, eine Softwareregel zu definieren, die dafür sorgt, dass die Aussage im vorherigen Abschnitt für die von Ihnen angegebene Spalte immer wahr bleibt. Wenn ja, behandeln Sie sie so und notieren Sie sich ihre spezielle Eigenschaft in der Ansichtsdatei, damit andere sie später verwenden können (mit einem Link zu SQL Runner als Nachweis). Beachten Sie jedoch, dass Looker die implizite Eindeutigkeit bestätigt, wenn ein Feld als Primärschlüssel festgelegt ist, dies aber nicht für andere Felder tut. Der Algorithmus für symmetrische Summen wird einfach nicht aufgerufen.