SQL Server Graph database – analyseren van ‘many-to-many’ relaties

We leven in een ‘connected world’! Alle stukjes informatie zijn steeds meer en meer met elkaar verbonden. De berg met data blijft jaar op jaar groeien, maar de relaties tussen de data groeit nog vele malen harder. Relationele databases kunnen deze relaties prima vastleggen met de foreign keys functionaliteit. Het analyseren van deze data wordt echter steeds lastiger als de onderlinge relaties complexer worden. In relationele database zijn dan veel joins, subqueries en andere hulpmiddelen nodig om de data op de juiste manier aan elkaar te koppelen. Een graph database is speciaal gericht om de ‘many-to-many’ relaties op te slaan en deze daarmee veel eenvoudiger en sneller te analyseren. Vanaf versie 2017 bevat SQL Server ook functionaliteit om een Graph database te modelleren. Een graph database heeft dezelfde structuur als de standaard tabellen en ze kunnen ook op dezelfde manier ‘ge-queried’ worden. SQL Server heeft een nieuw statement MATCH waarmee de relaties kunnen worden geanalyseerd.

In deze blog laten we zien hoe een Graph database is opgebouwd en hoe de resultaten in Power BI gevisualiseerd kunnen worden.

Nodes en edges

Een graph database bestaat uit ‘Nodes’ en ‘Edges’. Een node representeert een entiteit bijvoorbeeld een persoon of een organisatie en een edge representeert de relatie tussen twee nodes. Als voorbeeld voor deze blog gebruiken we onderstaand schema die de relaties tussen medewerkers, klanten en steden weergeeft.

De visualisatie is uiteraard slechts een versimpelde weergave maar geeft al wel inzicht in de meervoudige relaties tussen de elementen. In deze weergave zijn Medewerker, Stad en Klant de ‘nodes’ en de lijnen tussen de nodes geven de ‘edges’ aan.

Aanmaken nodes

Het aanmaken van nodes lijkt veel op de CREATE TABLE statements voor reguliere SQL tabellen. De belangrijkste toevoeging is AS NODE aan het einde van het script. In de volgende scripts worden de nodes voor Medewerker, Stad en Klant aangemaakt.

CREATE TABLE Medewerker (
ID INTEGER PRIMARY KEY,
MedewerkerNaam VARCHAR(100)
) AS NODE;

CREATE TABLE Klant (
ID INTEGER NOT NULL,
KlantNaam VARCHAR(100)
) AS NODE;

CREATE TABLE Stad (
ID INTEGER PRIMARY KEY,
Stad VARCHAR(100)
) AS NODE;

In SQL Server Management Studio (SSMS) zijn de nodes nu zichtbaar via onder Graph Tables.

Als we naar de inhoud van de nodes kijken heeft SQL Server zelf twee nieuwe kolommen toegevoegd aan de tabel, namelijk graph_id en $node_id:

Deze kolommen worden door SQL gebruikt als unieke referenties om de relatie tussen de tabellen vast te kunnen leggen.

Met het volgende script voegen we voorbeeld data toe aan de nodes:

INSERT INTO Medewerker (Id, MedewerkerNaam)
VALUES (1, ‘Gideon’)
, (2, ‘Hans’)
, (3, ‘Jan’)
, (4, ‘Karel’)
, (5, ‘Elena’);

INSERT INTO Klant (Id, KlantNaam)
VALUES (1, ‘Klant A’)
, (2, ‘Klant B’)
, (3, ‘Klant C’);

INSERT INTO Stad (Id, Stad)
VALUES (1, ‘Ridderkerk’)
, (2, ‘Alphen aan den Rijn’)
, (3, ‘Dordrecht’);

Aanmaken edges

Het aanmaken van edges gaat op vergelijkbare wijze behalve dat nu de expressie AS EDGE aan het script wordt meegegeven. Bovendien hoeven aan deze edges geen verdere kolommen te worden teogevoegd.

In de volgende scripts worden de edges aangemaakt.

CREATE TABLE woontIn AS EDGE;
CREATE TABLE werktIn AS EDGE;
CREATE TABLE isMentorVan AS EDGE;
CREATE TABLE isRelatiemanagerVan AS EDGE;
CREATE TABLE werktAaan AS EDGE;
CREATE TABLE isGevestigdIn AS EDGE;

De edges verschijnen nu ook onder de Graph tables:

Aan het icoon voor de naam is te zien of het een Edge of een Node betreft.

SQL server heeft voor elke edge acht kolommen toegevoegd:

Deze kolommen worden gebruikt om de relaties tussen de nodes vast te kunnen leggen. De id’s verwijzen naar de identifiers van de nodes die eerder zijn aangemaakt.

Met de volgende scripts voegen we voorbeeld data toe aan de edges.

INSERT INTO isGevestigdIn
VALUES ((SELECT $node_id FROM Klant WHERE ID = 1), (SELECT $node_id FROM Stad WHERE ID = 1))
, ((SELECT $node_id FROM Klant WHERE ID = 2), (SELECT $node_id FROM Stad WHERE ID = 2))
, ((SELECT $node_id FROM Klant WHERE ID = 3), (SELECT $node_id FROM Stad WHERE ID = 3));

INSERT INTO woontIn
VALUES ((SELECT $node_id FROM Medewerker WHERE ID = 1), (SELECT $node_id FROM Stad WHERE ID = 1))
, ((SELECT $node_id FROM Medewerker WHERE ID = 2), (SELECT $node_id FROM Stad WHERE ID = 2))
, ((SELECT $node_id FROM Medewerker WHERE ID = 3), (SELECT $node_id FROM Stad WHERE ID = 3))
, ((SELECT $node_id FROM Medewerker WHERE ID = 4), (SELECT $node_id FROM Stad WHERE ID = 2))
, ((SELECT $node_id FROM Medewerker WHERE ID = 5), (SELECT $node_id FROM Stad WHERE ID = 1));

INSERT INTO werktIn
VALUES ((SELECT $node_id FROM Medewerker WHERE ID = 1), (SELECT $node_id FROM Stad WHERE ID = 3))
, ((SELECT $node_id FROM Medewerker WHERE ID = 2), (SELECT $node_id FROM Stad WHERE ID = 2))
, ((SELECT $node_id FROM Medewerker WHERE ID = 3), (SELECT $node_id FROM Stad WHERE ID = 3))
, ((SELECT $node_id FROM Medewerker WHERE ID = 4), (SELECT $node_id FROM Stad WHERE ID = 1))
, ((SELECT $node_id FROM Medewerker WHERE ID = 5), (SELECT $node_id FROM Stad WHERE ID = 1));

INSERT INTO isRelatiemanagerVan
VALUES ((SELECT $node_id FROM Medewerker WHERE ID = 1), (SELECT $node_id FROM Klant WHERE ID = 3))
, ((SELECT $node_id FROM Medewerker WHERE ID = 2), (SELECT $node_id FROM Klant WHERE ID = 2))
, ((SELECT $node_id FROM Medewerker WHERE ID = 5), (SELECT $node_id FROM Klant WHERE ID = 1));

INSERT INTO isMentorVan
VALUES ((SELECT $node_id FROM Medewerker WHERE ID = 1), (SELECT $node_id FROM Medewerker WHERE ID = 2))
, ((SELECT $node_id FROM Medewerker WHERE ID = 1), (SELECT $node_id FROM Medewerker WHERE ID = 3))
, ((SELECT $node_id FROM Medewerker WHERE ID = 2), (SELECT $node_id FROM Medewerker WHERE ID = 4))
, ((SELECT $node_id FROM Medewerker WHERE ID = 2), (SELECT $node_id FROM Medewerker WHERE ID = 5)) ;

INSERT INTO werktAaan
VALUES ((SELECT $node_id FROM Medewerker WHERE ID = 1), (SELECT $node_id FROM Klant WHERE ID = 3))
, ((SELECT $node_id FROM Medewerker WHERE ID = 1), (SELECT $node_id FROM Klant WHERE ID = 2))
, ((SELECT $node_id FROM Medewerker WHERE ID = 2), (SELECT $node_id FROM Klant WHERE ID = 1))
, ((SELECT $node_id FROM Medewerker WHERE ID = 2), (SELECT $node_id FROM Klant WHERE ID = 2))
, ((SELECT $node_id FROM Medewerker WHERE ID = 2), (SELECT $node_id FROM Klant WHERE ID = 3))
, ((SELECT $node_id FROM Medewerker WHERE ID = 3), (SELECT $node_id FROM Klant WHERE ID = 2))
, ((SELECT $node_id FROM Medewerker WHERE ID = 4), (SELECT $node_id FROM Klant WHERE ID = 1))
, ((SELECT $node_id FROM Medewerker WHERE ID = 5), (SELECT $node_id FROM Klant WHERE ID = 1))
, ((SELECT $node_id FROM Medewerker WHERE ID = 5), (SELECT $node_id FROM Klant WHERE ID = 2))
, ((SELECT $node_id FROM Medewerker WHERE ID = 5), (SELECT $node_id FROM Klant WHERE ID = 3));

Zoals je ziet, wordt in de definitie van de relatie verwezen naar de $node_id’s die in de nodes zijn toegewezen aan de elementen.

Als we de inhoud van een edge tabel bekijken na uitvoeren van bovenstaande scripts, is te zien hoe SQL de graph data heeft opgeslagen.

Filteren van de data

Nu alle nodes en edges zijn aangemaakt, kunnen we de data gaan filteren.
hierbij maken we gebruik van de MATCH functie van SQL Server.

Stel dat we willen weten waar de Medewerkers wonen, gebruiken we het volgende script;

SELECT Medewerker.MedewerkerNaam , Stad.Stad
FROM Medewerker, woontIn, Stad
WHERE MATCH(Medewerker-(woontIn)->Stad);

We hoeven geen JOINS te definiëren zoals in een regulier SQL script.
In de WHERE statement gebruiken we de MATCH functie om aan te geven welke relatie we willen zien.

In één query kunnen er meerdere relaties opgenomen worden om de onderlinge samenhang te bekijken. Als voorbeeld de volgende query waarin ook de relaties tussen de medewerker en de klant en de relatie tussen de klanten en de steden is opgenomen.

SELECT Medewerker.MedewerkerNaam , Stad.Stad , Klant.KlantNaam, Stad2.Stad
FROM Medewerker, woontIn, Stad , isRelatieManagerVan, Klant, isGevestigdIn, Stad Stad2
WHERE MATCH( Stad2<-(isGevestigdIn)-Klant<-(isRelatieManagerVan)-Medewerker-(woontIn)->Stad);

De sytnax biedt eindeloze mogelijkheden om de relaties tussen de gegevens in kaart te brengen.

Visualiseren in Power BI

Voor de mensen die meer visueel zijn ingesteld, kunnen de relaties ook gevisualiseerd worden in Power BI. De graph tabellen kunnen niet zonder meer benaderd worden in Power BI zoals normale SQL tabellen. Als de queries echter eerst in een view worden opgenomen kunnen de gegevens wel worden ingelezen.

Met het volgende script wordt een view aangemaakt met de relaties tussen de medewerkers, woonplaatsen, vestigingsplaatsen en klanten.

CREATE VIEW [dbo].[vwGraphData]
AS
SELECT Medewerker.MedewerkerNaam , Stad.Stad , Klant.KlantNaam, Stad2.Stad AS VestigingStad
FROM Medewerker, woontIn, Stad , [werktAaan], Klant, isGevestigdIn, Stad Stad2
WHERE MATCH(Klant<-(werktAaan)-Medewerker-(woontIn)->Stad AND Stad2<-(isGevestigdIn)-Klant);

Voor de visualisatie in Power BI kan gebruikt gemaakt worden van de Custom Visual Force-Directed Graph. Deze visual kan via de volgende link gedownload worden: https://appsource.microsoft.com/en-us/product/power-bi-visuals/wa104380764?tab=overview

Als bovenstaande view wordt ingelezen in Power BI, kan de Force-Directed Graph worden getoond door in ieder geval de Bron en Doel in te vullen.
In onderstaand voorbeeld is voor Bron de Medewerkernaam gekozen en als doel de VestigingStad.

In deze visual zijn nu goed alle onderlinge relaties te zien en nader te analyseren.

Afsluiting

Een graph database is uitstekend geschikt om complexere relaties tussen gegevens op te slaan en goed te kunnen analyseren. Met de systematiek van nodes en edges zijn de relaties flexibel uit te breiden en zijn er veel mogelijkheden om de relaties te analyseren.

Power BI biedt met de custom visual Forec-Directed graph de optie om de relaties ook visueel weer te geven.

De onderlinge relaties tussen gegevens kunnen in een relationele database zonder meer ook opgeslagen en ge-queried worden. Middels de Graph techniek zijn er echter veel minder JOINS nodig voor hetzelfde resultaat en zijn de relaties vele male makkelijker te analyseren.