Von Lukas Hillesheim, 24. Juni 2024

Blog Article & Copyright by Lukas Hillesheim

Mit KQL steht eine relativ neue BI-Sprache von Microsoft zur Verfügung. Sie wird in der Azure Cloud eingesetzt (Azure Data Explorer (ADX) und MS Fabric). In diesem Artikel wird gezeigt, wie man mit KQL die integrierte Graph-Engine nutzt. Dabei kommen die Operatoren make-graph und graph-match zum Zuge. Der Artikel orientiert sich an den Modellierungs- und Abfrage-Aufgaben, die im Zusammenhang mit Stücklisten entstehen.

Beispiel-Daten und Grafiken. Die Beispiele in diesem Artikel greifen auf ein gemeinsames Set an Daten zu, die in meiner ADX Datenbank ClassroomDB gehostet werden. Sie wurden mit meiner Python App ndim-graph generiert. Für die Erstellung der Grafiken wurden die ADX-Daten - ebenfalls mit ndim-graph - unter Verwendung von KQL heruntergeladen und mit networkx/pyvis visualisiert.

Inhalt:

1. Features der KQL-Sprache
2. Graph Theorie und Neuronale Netze
3. Bereitstellen von Graph-Daten
4. Verarbeiten von Graph-Daten mit KQL
5. Beispiel 1 - KQL Abfrage
6. Hinweise zur Syntax von 'graph-match'
7. Beispiel 2 - KQL Abfrage
8. Visualisierung
9. Python: KQL Abfrage und Visualisierung

1. Features der KQL-Sprache

Feature	Beschreibung
Data Wrangling	SQL-ähnliche Konstrukte wie Selektion, Filterung, JOIN, Gruppierung, Aggregierung etc.
Flexible Datentypen	KQL-Typen können mit strukturierten, unstrukturierten und komplexen Daten umgehen. Dazu gehören u.a. verschachtelte Arrays, Hashtables und JSON-Daten
Integrierte Visualisierung	Mit dem render-Operator können die ausgegebenen Daten on the fly visualisiert werden. Das Layout entspricht den Power BI Visuals
Integrierte Graph Engine	Abfragen auf Nodes und Edges eines Graphen
KI-Bibliotheken	Neuronale Netze, Zeit-Serien, Anomalie-Detection, Root-Cause-Analyse etc.
Python, R	Pyton- und R-Scripte können integriert werden

2. Graph Theorie und Neuronale Netze

2.1. Stücklisten in einer Baum-Struktur

Eine Stückliste ist i.d.R. als Baum organisiert. Die Grafik zeigt die hierarischen Beziehungen der Entitäten product und component innerhalb des Baums: * Ein bestimmtes Produkt besteht aus verschiedenen Komponenten * Diese bestehen wiederum aus anderen Komponenten * Diese wiederum bestehen aus anderen Komponenten etc.

Leaf-Nodes können sich auf unterschiedlichen Ebenen des Baums befinden.

Beispiel für eine unterschiedliche Level-Anzahl:

Item Levels:

'PROD-A' befindet sich auf Level 0
'COMP-E' befindet sich auf Level 1
'COMP-B' befindet sich auf Level 3 etc.

Diese Ausprägung eines Baumes nennt man unbalanced.

Zusammensetzung von 'PROD-A':

Abb.1: BOM als Baum oder Graph

In einer Datenbank-Tabelle kann die hierarchische Natur der Stückliste mit einer 'Parent-Child-Relationship' abgebildet werden. Z.B. besitzt 'COMP-A' eine übergeordnete Komponente 'COMP-H':

Id	...	Name	ParentId
4	...	COMP-A	11
11	...	COMP-H	12

Ein Baum kann als Sonderfall eines Graphen angesehen werden

2.2. Mehrfache Stücklisten und zusätzliche Entitäten in einem Graphen oder Neuronalem Netzwerk

Das Baum-Modell muss erweitert werden, wenn die gleichzeitige Darstellung mehrerer Stücklisten erwünscht ist. Zusätzlich könnte auch die Anforderung bestehen, weitere Entitäten wie z.B. Kunden einzubeziehen. Das daraus resultierende Modell ist ein Netz, Graph oder auch Neuronales Netz.

Die nachfolgende Grafik stellt die Beziehungen von Knoten verschiedener Entitäts-Typen dar (customer, product und component):

Ein Kunde erhält verschiedene Produkte
Ein Produkt besteht aus verschiedenen Komponenten
Komponenten bestehen aus anderen Komponenten etc.

Abb.2: Graph oder Neuronales Netz

Die Abbildung eines Graphen hat im zu Grunde liegenden Datenmodell Konsequenzen. Z.B. ist Komponente 'COMP-A' sowohl der übergeordneten Komponente 'COMP-H' zugeordnet, als auch dem Produkt 'PROD-B'. In der Tabelle befinden sich daher zwei Instanzen von 'COMP-A' - jeweils mit unterschiedlichen Parent-Ids:

Id	...	Name	ParentId
2	...	PROD-B	NULL
4	...	COMP-A	2
4	...	COMP-A	11
11	...	COMP-H	12

3. Bereitstellen von Graph-Daten

Um Graph-Daten verarbeiten zu können, sollten zwei Grundsätze beachtet werden:

Die Daten für die Graph Engine sollten in den Entitäten node und edge bereit gestellt werden
Das Verarbeiten von Daten, die in einem Graphen oder Neuronalen Netz organisiert sind, geschieht am Besten mit einer Graph Engine

3.1. Paradigmen-Wechsel durch Cloud-basierte Datenspeicherung

Das Ablösen OnPremise-basierter Datenspeicherung durch einen grossen Cloud-basierten Datenspeicher (Data Lake) für alle Typen von Daten und für alle Zwecke bewirkt einen Paradigmen-Wechsel:

Ein klassischer BI-Stack - On-Premise - besteht aus transaktionalen Quell-Systemen (OLTP), einem Data Warehouse und diversen spezialisierten Data Marts und Cubes. Die Aufteilung der Datenverarbeitungs-Schritte auf verschiedene Systeme, die auch nach verschiedenen Konzepten modelliert werden, ergibt sich aus zwei Gründen:

Die Performance verbessert sich erheblich auf Grund dedizierter Hardware, deizierter Konfiguration und dediziertem Modell
Das jeweilige System bietet mit dem dazugehörigem Daten-Modell ein zusätzliches semantisches Layer an. Ein Data Warehouse zeigt die Historie der Datenänderung. Hierdurch können für beliebige Zeitpunkte Versionen des Datenbestandes gezeigt werden. Ein Data Mart zeigt Daten als Fakten und Dimensionen, wodurch sich das Verständnis der Daten und ihre Usability in den Report-Clients verbessert.

In Cloud-Systemen kommt es zum Paradigmen-Wechsel: Es steht die Frage im Fokus, ob es nicht sinnvoll wäre, dass Clients alle Extraktionen direkt auf den zentral gespeicherten Daten des Data Lake ausführen. Die klassischen ETL-Prozesse würden dann obsolet. Jeder Daten-Architekt muss vermutlich eine Hybrid-Strategie aus beiden Ansätzen verfolgen - spezifisch für die jeweilige Kunden-Umgebung.

3.2. KQL Walk-Through

In meinem Beispiel-Szenario zeige ich im Mini-Format, wie der zweite Ansatz - Inplace-Abfragen ohne ein dediziertes separates Repository für Graph-Daten - konkret in KQL implementiert wird: die Graph-Daten werden AdHoc per KQL aus dem ADX-Repository extrahiert und transformiert. Dabei entstehen aus Komponenten, Produkten und Kunden nodes und aus Transaktionen und Parent-Child Informationen der Stückliste edges.

3.2.1 Die Quell-Tabellen

Die verwendeten Quell-Tabellen und ihre Inhalte:

Name	Content
graph_bom	Enthält die Produkte und Komponenten der Stückliste (Parent-Child-Hierarchie)
graph_tran	Enthält Transaktionen. Dabei werden die Entitäten customer und product zueinander in Beziehung gebracht

Tabelle 'graph_bom'

Diese Tabelle enthält die Stückliste.

Abb.3: Stückliste (BOM)

Tabelle 'graph_tran'

Diese Tabelle enthält Transaktionen.

Abb.4: Transaktionen

3.3. Vollständige KQL-Abfrage

Anbei der KQL-Code für eine vollständige Abfrage.

Im Abschnitt '1. Transformation: transactional layout -> graph layout' des KQL-Codes werden die Daten bereit gestellt.

Im Abschnitt '2. Graph query' des KQL-Codes wird mit dem Einsatz der Funktionen make-graph und graph-match ein Beispiel für eine KQL-Graph-Query gezeigt: (Detaillierte Hinweise zum Code folgen später)

// 1. Transformation: transactional layout -> graph-Layout: 
let nodes_bom = 
    graph_bom 
    | where Type == 'Product' or not(ParentId == '')
    | project 
        id     = toint(Id), 
        name   = Name, 
        type   = Type, 
        cost   = Cost, 
        status = Status;
let nodes_cust = 
    graph_tran
    | distinct CustomerName 
    | project 
        name = CustomerName, 
        type = 'Customer' 
    | serialize 
    | extend 
        id   = toint(row_number(100)), 
        cost = 0; 
let nodes = 
    nodes_bom 
    | union nodes_cust; 
let edges_bom = 
    graph_bom 
    | where not(ParentId == '') 
    | project 
        id = Id, 
        source_node = Name, 
        type = Type, 
        parent_id_string = ParentId
    | extend parent_id = split(parent_id_string,',')
    | mv-apply parent_id on (
        extend destination_node_id = toint(parent_id) ) 
    | lookup nodes on $left.destination_node_id == $right.id 
    | project 
        source_node, 
        destination_node = name;
let edges_tran =  
    graph_tran 
    | project 
        source_node      = ProductName, 
        destination_node = CustomerName, 
        tran_date        = TranDate;
let edges = 
    edges_bom 
    | union edges_tran; 
// 2. Graph query: 
edges 
| make-graph source_node --> destination_node with nodes on name 
| graph-match (comp) -[edge_comp_prod*1..5]-> (prod) -[edge_prod_cust*1..5]-> (cust)
  where 
    prod.name == 'PROD-A'
    and cust.name == 'CUST-A'
  project 
    customer = cust.name, 
    product = prod.name, 
    component = comp.name,
    path = edge_comp_prod.destination_node

3.4. Details zu Abschnitt 1

3.4.1. Extraktion von Nodes

In drei Schritten bzw. mit Hilfe von drei Variablen werden Node-Daten extrahiert:

let nodes_bom ... . Hiermit werden Nodes (Entitäten product und component) aus der Stückliste gewonnen
let nodes_cust ... . Hiermit werden Nodes (Entität customer) aus den Transaktions-Daten gewonnen
let nodes = ... union ... . Hiermit werden die Teil-Ergebnisse zusammengeführt

Beachten Sie dabei folgenden technischen Aspekt: let nodes_cust ... verwendet distinct. Da ein Kunde mehrere Produkte erhalten kann, taucht er mehrfach in den Transaktions-Daten auf. Daher müssen die Vorkommen durch ein distinct reduziert werden.

3.4.2. Extraktion von Edges

Edge data is extracted in three steps as well:

let edges_bom ... . Hiermit werden Edges aus den Parent-Child-Informationen der Stückliste abgeleitet
let edges_tran ... . Hiermit werden Edges aus den Transaktionen abgeleitet
let edges_tran ... union ... . Hiermit werden die Teil-Ergebnisse zusammengeführt

Beachten Sie dabei folgenden technischen Aspekt: let edges_bom = verwendet u.a. die Funktion mv-apply. Ein Blick auf die Daten zeigt, warum:

In der Spalte ParentId befindet sich der String 1, 2, der als Komma-separierte Liste zu interpretieren ist. Dabei zeigt 11 auf die Komponente mit der Id 11 und 2 zeigt auf die Komponente mit der Id 2. Mit anderen Worten: Der Knoten 'COMP-A' ist zwei anderen Knoten untergeordnet. Aus der einer Zeile 'COMP-A' müssen daher zwei Zeilen abgeleitet werden bzw. die eine Zeile muss multipliziert werden. Diese Aufgabe erledigt mv-apply:

Id	Name	Type	ParentId	Cost	Status
4	COMP-A	Component	11, 2	10,000	SCHEDULED_FOR_MAINTENANCE

Id	Name	Type	ParentId	Cost	Status
4	COMP-A	Component	11	10,000	SCHEDULED_FOR_MAINTENANCE
4	COMP-A	Component	2	10,000	SCHEDULED_FOR_MAINTENANCE

4. Verarbeiten von Graph-Daten mit KQL

Eine Custom-Lösung auf der Basis anderer Sprachen zu entwickeln, mit der die Business Logik einer Graph-Engine implementiert würde, ist denkbar. Man könnte z.B. Stored Procedures unter Verwendung von rekursivem SQL entwickeln. Hierbei könnte jedoch zu größeren technischen Herausforderung kommen:

Durch wiederholte Aufrufe der Knoten (Round-Trips zwischen Cloud und Custom Engine) ergeben sich Latenzen
Durch Iterationen und Rekursionen entsteht logische Komplexität
Ohne ein entsprechendes Graph-Datenmodell entstehen in einem klassischen (normalisierten) Datenmodell auf Grund der verschiedenen Entitäten (customer, product und component) Schema-Grenzen, die nur schwer zu überwinden sind

Die KQL Graph-Engine löst die o.g. technischen Probleme in einer optimierten Art und Weise (z.B. unter Verwendung der Adjacency Matrix) und bietet darüber hinaus ein zusätzliches semantisches Layer an, das die Beziehungen der Knoten präsentiert und intuitiv nutzbar macht. I.d.R. sind nur wenige Zeilen Code notwendig, um die Graph Engine effektiv zu nutzen. Beispiele folgen weiter unten.

5. Beispiel 1 - KQL Abfrage

Mit Hilfe von make-graph werden nodes und edges in die Graph-Engine importiert. Konzeptionell liefert make-graph einen Graphen aus, der alle zur Verfügung gestellten Daten berücksichtigt - also alle Knoten und Edges. graph-match schränkt diese Daten ein und steuert auch bestimmte Details der Ausführung.

Das folgende Beispiel zeigt einen Teil der vollständigen KQL Abfrage, und zwar den relevanten Teil mit make-graph und graph-match. Das Ziel der Abfrage ist, die Auslieferung von Produkt 'PROD-A' für den Kunden 'CUST-A' inklusive aller Komponenten für 'PROD-A' aufzulisten. Zusätzlich soll auch der Pfad des jeweiligen Knotens bis hin zu 'CUST-A' zurückgegeben werden.

KQL 'graph-match' expression:

... 
edges 
| make-graph source_node --> destination_node with nodes on name 
| graph-match (comp) -[edge_comp_prod*1..5]-> (prod) -[edge_prod_cust*1..5]-> (cust)
  where 
    prod.name == 'PROD-A'
    and cust.name == 'CUST-A'
  project 
    customer = cust.name, 
    product = prod.name, 
    component = comp.name,
    path = edge_comp_prod.destination_node

Output:

Abb.5: Path product-component-customer

Konzeptionell wird mit 'graph-match' der use case ausgedrückt.

6. Hinweise zur Syntax von 'graph-match'

Innerhalb von make-graph wird festgelegt, welche Spalte der Input-Tabelle [edges] in der Rolle als Quell-Knoten-Spalte verwendet wird und welche Spalte als Ziel-Knoten-Spalte: Die Expression source_node --> destination_node bedeutet, dass Werte der Spalte [edges].[source_node] als Identifier von Quell-Knoten zu behandeln sind. Entsprechend sind Werte der Spalte [edges].[destination_node] Identifier von Ziel-Knoten.

Die Expression nodes on name legt fest, dass die Spalte [nodes].[name] als Lookup-Spalte dient. Die Identifier von Quell- und Ziel-Knoten ([edges].[source_node] und [edges].[destination_node]) zeigen also auf die Spalte [nodes].[name].

Im Beispiel oben sind die folgenden Variablen definiert:

Variablen für nodes: comp, prod, cust
Variables für edges: edge_comp_prod, edge_prod_cust

Die Umgebung, innerhalb derer graph-match ausgeführt wird, ist konzeptionell eine Schleife. Zeile für Zeile wird graph-match aufgerufen. Bei jedem Aufruf werden Daten der aktuellen Zeile an die Variablen übergeben.

Durch die Expression -[...]-> wird ein Edge dargestellt. Da der Edge in diesem Fall von links nach rechts zeigt, befinden sich auf der linken Seite Quell-Knoten und auf der rechten Seite Ziel-Knoten. In der Expression (comp) -[edge_comp_prod*1..5]-> (prod) befindet sich die Variable prod auf der rechten Seite und wird mit den Daten des Ziel-Knotens gefüllt. In der Expression (prod) -[edge_prod_cust*1..5]-> (cust) befindet sich die Variable prod auf der linken Seite und wird mit den Daten des Quell-Knotens gefüllt.

Sowohl node, als auch edge können über Eigenschaften verfügen. Diese stammen aus den Spalten der Tabelle [nodes] bzw. [edges]. Die Expression cust.name erhält ihre Daten aus der Spalte [nodes].[name].

7. Beispiel 2 - KQL Abfrage

In diesem Beispiel soll eine Wartungs-/Rückruf-Kampagne vorbereitet werden. Kunden, denen im Mai 2024 Produkte geliefert wurden, sollen über bevorstehende Wartungen informiert werden. Die Abfrage gibt Kunden, Produkte, Teile und Kosten zurück:

graph-match (comp) -[comp_prod*1..5]-> (prod) -[tran*1..5]-> (cust) 
  where 
    comp.status != ""
    and getmonth(tran.tran_date) == 5