Beziehungsparameter richtig interpretieren

Diese Seite richtet sich an alle, die LookML verwenden möchten, um ein Explore in Looker zu erstellen. Die Seite ist einfacher zu verstehen, wenn Sie sich mit SQL auskennen, insbesondere wenn Sie den Unterschied zwischen inneren und äußeren Joins kennen. Eine kurze Erklärung der Unterschiede zwischen inneren und äußeren Joins finden Sie im w3schools-Artikel SQL Joins.

Looker kann eine leistungsstarke SQL-Engine für Ihr Unternehmen sein. Die abstrakte Modellierung in LookML ermöglicht es Daten- und IT-Teams, allgemeine Regeln zu erstellen, die immer wahr sind. Business-Analysten können so Abfragen im Übrigen erstellen, die immer korrekt sind, selbst wenn das Datenteam nie einen Bedarf für sie erwartet hat. Der Hauptgrund für diese Funktion ist der Algorithmus für symmetrische Aggregate, der ein branchenweites Problem mit SQL-Joins löst. Zwei Dinge müssen jedoch korrekt ausgeführt werden, um den Algorithmus nutzen zu können: Primärschlüssel müssen in jeder Ansicht, die einen Messwert enthält (normalerweise alle) genau sein, und relationship-Parameter müssen 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 was damit geschehen könnte. Das Einzige, was zutrifft, ist, dass die Spalte (oder der Satz verketteter 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. Über den Parameter relationship wird Looker mitgeteilt, ob symmetrische Summen aufgerufen werden sollen, wenn der Join in eine SQL-Abfrage geschrieben wird. 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.

Der Prozess zum Ermitteln des richtigen Werts unterscheidet sich leicht zwischen inneren und äußeren Joins.

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 $ 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-Join kann wie folgt als einzelne verknüpfte 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 $ 3 3 Wilbur Wright 4

Die many_to_one-Beziehung bezieht sich hier darauf, wie oft ein Wert des Join-Felds (customer_id) in jeder Tabelle dargestellt wird. 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 die Primärschlüssel prüfen:

  1. Schreiben Sie zuerst many_to_many als Beziehung. Solange Ihre Primärschlüssel korrekt sind, führt dies immer zu genauen Ergebnissen, da Looker immer den symmetrischen Aggregationsalgorithmus auslöst und die Genauigkeit erzwingt. Da der Algorithmus jedoch Abfragen verkompliziert und die Ausführungszeit verlängert, sollten Sie versuchen, 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 Sonderfällen finden Sie weiter unten auf dieser Seite im Abschnitt Was Sie beachten sollten.
  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 darstellen, können Sie die rechte Seite in one ändern.

Es empfiehlt sich, die sql_on-Wortgruppe beginnend mit der linken Tabelle, die sich auf der linken Seite des Gleichheitszeichens befindet, und der rechten Tabelle, die sich auf der rechten Seite befindet, zu schreiben. 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. Auch wenn für den sql_on-Parameter keine solche Reihenfolge erforderlich ist, kannst du die Beziehung besser bestimmen, indem du die sql_on-Bedingungen so anordnest, dass die linke und rechte Seite des Gleichheitszeichens mit dem von links nach rechts gelesenen relationship-Parameter übereinstimmen. Wenn Sie die Felder auf diese Weise anordnen, können Sie auf einen Blick leichter erkennen, mit welcher vorhandenen Tabelle im Explore Sie die neue Tabelle verknüpfen.

Outer Joins

Bei Outer Joins müssen Sie außerdem berücksichtigen, dass ein Fanout auftreten kann, wenn während des Joins Null-Einträge hinzugefügt werden. Das ist besonders wichtig, da in Looker standardmäßig linke äußere Joins verwendet werden. Nulleinträge 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 $ 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

Nachdem diese Tabellen verknüpft wurden, kann die verknüpfte Tabelle wie folgt 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 $ 3 3 Wilbur Wright 4
null null null 4 Karl Yeager 3

Wie bei einem Inner Join kann auch die Beziehung zwischen den Primärschlüssel ist many_to_one. Der hinzugefügte Nulleintrag erzwingt jedoch auch die Notwendigkeit symmetrischer Summen in der linken Tabelle. Sie müssen daher den Parameter relationship in many_to_many ändern, weil durch diesen Join die Anzahl in der linken Tabelle unterbrochen wird.

Wenn dieses Beispiel ein Left Outer Join gewesen wäre, wäre die Zeile mit dem Wert null nicht hinzugefügt worden und der zusätzliche Kundendatensatz wäre gelöscht worden. In diesem Fall würde die Beziehung weiterhin many_to_one sein. Dies ist die Standardeinstellung von Looker, da angenommen wird, dass die Basistabelle die Analyse definiert. In diesem Fall analysieren Sie Bestellungen, nicht die Kundschaft. Wenn die Kundentabelle links platziert wäre, wäre die Situation anders.

Joins auf mehreren Ebenen

In einigen Explores ist die Basistabelle mit einer oder mehreren Ansichten verknüpft, die wiederum mit einer oder mehreren zusätzlichen Ansichten verknüpft werden müssen. In diesem Beispiel würde dies bedeuten, dass eine Tabelle mit der Kundentabelle verknüpft wird. In diesen Situationen ist es am besten, bei der Auswertung des relationship-Parameters nur den einzelnen Join zu berücksichtigen, der geschrieben wird. Looker erkennt, wenn sich ein nachgelagerter Fanout auf eine Abfrage auswirkt, obwohl die betroffene Ansicht nicht in dem Join enthalten ist, der den Fanout tatsächlich erstellt hat.

Wie hilft mir Looker?

In Looker gibt es Mechanismen, die dafür sorgen, dass der Beziehungswert korrekt ist. Erstens: Die Eindeutigkeit des Primärschlüssels wird geprüft. Immer wenn ein Fanout auftritt und zur Berechnung einer Messung symmetrische Summen erforderlich sind, überprüft Looker den verwendeten Primärschlüssel auf Eindeutigkeit. Wenn er nicht eindeutig ist, wird bei der Ausführung der Abfrage ein Fehler angezeigt. Für diesen Fall gibt es jedoch keinen LookML-Validator-Fehler.

Wenn es für Looker keine Möglichkeit gibt, ein Fanout zu verarbeiten (normalerweise, weil kein Primärschlüssel angegeben ist), werden aus dieser Ansicht im Explore keine Messwerte angezeigt. Um dies zu korrigieren, legen Sie einfach ein Feld als Primärschlüssel fest, damit Ihre Messwerte in das Explore 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 heißt es, dass Sie sich zum Bestimmen des richtigen Beziehungswerts das Feld oder die Felder in der sql_on-Klausel aus der linken Tabelle ansehen 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 jede Spalte beim Formulieren der Beziehung so behandeln, als wäre sie ein Primärschlüssel, auch wenn es sich nicht um die Spalte mit der Bezeichnung primary_key: yes handelt.

Es kann hilfreich sein, sicherzustellen, dass eine Art Softwareregel vorhanden ist, die sicherstellt, dass die Anweisung im vorherigen Absatz immer für die von Ihnen angegebene Spalte wahr bleibt. Ist dies der Fall, behandeln Sie sie als solche und notieren Sie sich die spezielle Eigenschaft in der Ansichtsdatei, damit andere in Zukunft darauf verweisen können (vollständig mit SQL Runner-Link zur Bestätigung). Beachten Sie jedoch, dass Looker die Wahrheit der impliziten Eindeutigkeit bestätigt, wenn ein Feld als Primärschlüssel festgelegt wird. Dies gilt jedoch nicht für andere Felder. Der Algorithmus für symmetrische Summen wird einfach nicht aufgerufen.