Code & Queries

Code & Queries: Your Source for SQL, Python, and AI Insights

Home » Beiträge mit Stichwort Adventureworks

SQL Server und die Verwaltung von Materiallisten (Bills of Materials) mit hierarchyid

- Veröffentlicht unter SQL & Datenbanken von

Die effiziente Verwaltung von Materiallisten (englisch: Bills of Materials, BOMs) ist ein zentraler Bestandteil der Produktions- und Fertigungsprozesse in der modernen Industrie. In der Welt der Datenbanktechnologie bietet Microsoft SQL Server eine leistungsstarke Lösung für die Organisation und Abfrage von hierarchischen Strukturen, insbesondere durch den Einsatz des Datentyps hierarchyid. Dieser Artikel führt Sie durch die Grundlagen der Verwendung von hierarchyid in SQL Server, um komplexe BOM-Strukturen zu erstellen und zu verwalten. Wir werden uns dabei auf das AdventureWorks-Beispiel konzentrieren, das häufig als Referenzmodell in der Praxis genutzt wird.

Was ist eine Bill of Materials (BOM)?

Eine Bill of Materials ist eine detaillierte Liste aller Komponenten und Versammlungen, die benötigt werden, um ein Endprodukt herzustellen. Diese Liste kann mehrere Ebenen tief gehen, da einzelne Komponenten selbst wieder aus weiteren Unterkomponenten bestehen können. Die Struktur einer BOM ist daher meistens hierarchisch, was bedeutet, dass sie sich wie ein Baumstruktur darstellt.

In der Praxis wird eine BOM verwendet, um: - Die erforderlichen Materialien für die Produktion zu identifizieren. - Die Kosten eines Produkts zu berechnen. - Die Verfügbarkeit von Materialien im Lager zu überprüfen. - Die Produktionsabläufe zu planen.

Warum hierarchyid für BOMs verwenden?

Der hierarchyid-Datentyp in SQL Server wurde speziell entwickelt, um hierarchische Datenstrukturen zu modellieren und abzufragen. Er ermöglicht es, komplexe Baumstrukturen wie BOMs effizient zu speichern und zu navigieren. Im Gegensatz zu rekursiven CTEs (Common Table Expressions), die ebenfalls zur Bearbeitung hierarchischer Daten verwendet werden können, bietet hierarchyid folgende Vorteile: - Leistung: Schnellere Abfragen durch direkte Vergleiche anstatt rekursiver Aufrufe. - Speicherplatz: Effizientere Speicherung der Hierarchieinformationen. - Funktionalität: Integrierte Methoden zur Bearbeitung und Analyse der Hierarchie (z. B. GetLevel, GetAncestor, IsDescendantOf).

Beispieldatenbank: AdventureWorks

Um die Funktionalität von hierarchyid zu demonstrieren, nutzen wir die AdventureWorks-Datenbank, die von Microsoft zur Verfügung gestellt wird. Diese Datenbank enthält eine Tabelle namens Production.BillOfMaterials, die die Zusammenhänge zwischen verschiedenen Produkten und deren Komponenten beschreibt.

Schemaübersicht

  • Product: Enthält Informationen über Produkte, einschließlich ihrer Kategorie und Unterkategorie.
  • ProductSubcategory: Definiert die Unterkategorien der Produkte.
  • ProductCategory: Definiert die Hauptkategorien der Produkte.
  • BillOfMaterials: Beschreibt die Zusammenhänge zwischen Versammlungen (ProductAssemblyID) und Komponenten (ComponentID).

Die wichtigsten Spalten in der BillOfMaterials-Tabelle sind: - ProductAssemblyID: Identifiziert die Versammlung (Parent). - ComponentID: Identifiziert die Komponente (Child). - BOMLevel: Gibt die Hierarchiestufe an. - PerAssemblyQty: Gibt die Menge der Komponente pro Versammlung an.

Eltern- und Kinderknoten in einer BOM

Elternknoten (Parent)

Ein Elternknoten in einer BOM ist ein Produkt oder eine Versammlung, die aus einer oder mehreren Komponenten besteht. In der AdventureWorks-Datenbank wird dies durch die Spalte ProductAssemblyID repräsentiert. Der Elternknoten steht ganz oben in der Hierarchie und wird auch als Wurzelknoten bezeichnet.

Beispiel: - Das Fahrradmodell "Mountain-100 Black, 38" mit der ProductID = 775 ist der Elternknoten.

Kinderknoten (Child)

Ein Kinderknoten in einer BOM ist eine Komponente, die Teil eines Elternknotens ist. In der AdventureWorks-Datenbank wird dies durch die Spalte ComponentID repräsentiert. Jeder Kinderknoten kann selbst wieder ein Elternknoten sein, wenn er aus weiteren Unterkomponenten besteht.

Beispiel: - Die Komponenten Rahmen, Gabel und Lenker sind Kinderknoten des Fahrrads "Mountain-100 Black, 38".

Schritt-für-Schritt-Anleitung zur Erstellung einer BOM mit hierarchyid

1. Initialisierung der Hierarchie

Um eine BOM mit hierarchyid zu erstellen, müssen wir zunächst jedem Knoten in der Hierarchie einen eindeutigen Wert zuweisen. Dieser Wert kann entweder im Dezimalformat (z. B. /1/, /2/) oder im Hexadezimalformat (z. B. 0x68) gespeichert werden.

Beispiel-Script:

-- Deklariere die Wurzelknoten-ID
DECLARE @root_pid INT = 775; -- ProductID für "Mountain-100 Black, 38"

-- Temporäre Tabelle für Ebene 1
SELECT 
    '/' AS Ancestor_NodeString,
    '/' + CAST(ROW_NUMBER() OVER (ORDER BY ComponentID) AS NVARCHAR(20)) + '/' AS Comp_NodeString,
    [ProductCategory].Name AS cat_name,
    [ProductSubcategory].Name AS sub_cat_name,
    (SELECT Name FROM Production.Product WHERE ProductID = @root_pid) AS assembly_name,
    [BillOfMaterialsID],
    [ProductAssemblyID],
    [ComponentID],
    [UnitMeasureCode],
    [BOMLevel],
    [PerAssemblyQty]
INTO #bom_level_1_comps
FROM [AdventureWorks2014].[Production].[BillOfMaterials]
LEFT JOIN AdventureWorks2014.Production.Product
    ON [BillOfMaterials].ProductAssemblyID = Product.ProductID
LEFT JOIN AdventureWorks2014.Production.ProductSubcategory
    ON Product.ProductSubcategoryID = ProductSubcategory.ProductSubcategoryID
LEFT JOIN AdventureWorks2014.Production.ProductCategory
    ON ProductCategory.ProductCategoryID = ProductSubcategory.ProductCategoryID
WHERE ProductAssemblyID = @root_pid AND EndDate IS NULL;

-- Zeige die Ergebnisse für Ebene 1
SELECT * FROM #bom_level_1_comps ORDER BY Comp_NodeString;

2. Hinzufügen weiterer Ebenen

Nachdem wir die ersten Komponenten erfasst haben, fügen wir schrittweise weitere Ebenen hinzu, indem wir die Kinderknoten der aktuellen Ebene abrufen.

Beispiel-Script für Ebene 2:

-- Temporäre Tabelle für Ebene 2 ohne NodeString
SELECT 
    ROW_NUMBER() OVER (PARTITION BY productassemblyid ORDER BY ComponentID) AS c_w_a,
    ProductAssemblyID AS productassemblyid_pid,
    *
INTO #bom_level_2_comps_wo_cns
FROM [AdventureWorks2014].[Production].[BillOfMaterials]
WHERE ProductAssemblyID IN (
    SELECT ComponentID 
    FROM [AdventureWorks2014].[Production].[BillOfMaterials] 
    WHERE EndDate IS NULL AND ProductAssemblyID = @root_pid
);

-- Generiere NodeStrings für Ebene 2
SELECT 
    #bom_level_1_comps.Comp_NodeString AS Ancestor_NodeString,
    #bom_level_1_comps.Comp_NodeString + CAST(#bom_level_2_comps_wo_cns.c_w_a AS NVARCHAR(20)) + '/' AS Comp_NodeString,
    #bom_level_1_comps.cat_name,
    #bom_level_1_comps.sub_cat_name,
    (SELECT Name FROM Production.Product WHERE ProductID = #bom_level_2_comps_wo_cns.ProductAssemblyID) AS assembly_name,
    #bom_level_2_comps_wo_cns.BillOfMaterialsID,
    #bom_level_2_comps_wo_cns.ProductAssemblyID,
    #bom_level_2_comps_wo_cns.ComponentID,
    #bom_level_2_comps_wo_cns.UnitMeasureCode,
    #bom_level_2_comps_wo_cns.BOMLevel,
    #bom_level_2_comps_wo_cns.PerAssemblyQty
INTO #bom_level_2_comps
FROM #bom_level_2_comps_wo_cns
LEFT JOIN #bom_level_1_comps
    ON #bom_level_2_comps_wo_cns.ProductAssemblyID = #bom_level_1_comps.ComponentID
WHERE #bom_level_2_comps_wo_cns.BOMLevel = 2;

-- Zeige die Ergebnisse für Ebene 2
SELECT * FROM #bom_level_2_comps ORDER BY Comp_NodeString;

3. Konvertierung in Hexadezimalformat

SQL Server speichert hierarchyid-Werte intern im Hexadezimalformat, um Leistung und Speicherplatz zu optimieren. Um die Dezimaldarstellung in Hexadezimal umzuwandeln, verwenden wir die Parse-Methode.

Beispiel-Script:

-- Konvertiere NodeStrings in Hexadezimalformat
SELECT 
    hierarchyid::Parse(Ancestor_NodeString) AS Ancestor_Node,
    hierarchyid::Parse(Comp_NodeString) AS Comp_Node,
    hierarchyid::Parse(Comp_NodeString).GetLevel() AS Comp_Node_Level,
    *
INTO #comps_with_nodes
FROM (
    SELECT * FROM #bom_level_1_comps
    UNION
    SELECT * FROM #bom_level_2_comps
) AS CombinedLevels;

-- Zeige die Ergebnisse
SELECT 
    Ancestor_Node,
    Comp_Node,
    Comp_Node_Level,
    assembly_name,
    (SELECT Name FROM Production.Product WHERE ProductID = #comps_with_nodes.ComponentID) AS comp_name,
    BillOfMaterialsID,
    ProductAssemblyID,
    ComponentID,
    UnitMeasureCode,
    BOMLevel,
    PerAssemblyQty
FROM #comps_with_nodes;

Sortierung von BOM-Daten

Breitensuche (Breadth-First)

Bei einer Breitensuche werden alle Elemente einer bestimmten Ebene vor den Elementen der nächsten Ebene angezeigt.

Beispiel-Script:

SELECT 
    Ancestor_Node,
    Comp_Node,
    Comp_Node_Level,
    assembly_name,
    (SELECT Name FROM Production.Product WHERE ProductID = #comps_with_nodes.ComponentID) AS comp_name,
    BillOfMaterialsID,
    ProductAssemblyID,
    ComponentID,
    UnitMeasureCode,
    BOMLevel,
    PerAssemblyQty
FROM #comps_with_nodes
ORDER BY Comp_Node_Level;

Tiefensuche (Depth-First)

Bei einer Tiefensuche werden die Kinder eines Elements vollständig ausgegeben, bevor zum nächsten Geschwisterknoten gewechselt wird.

Beispiel-Script:

SELECT 
    Ancestor_Node,
    Comp_Node,
    Comp_Node_Level,
    assembly_name,
    (SELECT Name FROM Production.Product WHERE ProductID = #comps_with_nodes.ComponentID) AS comp_name,
    BillOfMaterialsID,
    ProductAssemblyID,
    ComponentID,
    UnitMeasureCode,
    BOMLevel,
    PerAssemblyQty
FROM #comps_with_nodes
ORDER BY Comp_Node;

Visualisierung der BOM

Mit den generierten hierarchyid-Werten können Sie auch visuelle Darstellungen der BOM erstellen, z. B. in Excel oder anderen Tools. Ein Beispiel dafür ist die Darstellung der Versammlungshierarchie als Baumdiagramm.

Beispiel:

HL Mountain Frame – Black, 38
├── Chain Stays
│   ├── Metal Sheet 5
│   └── Other Components
├── Fork
│   ├── HL Fork
│   │   ├── Lower Assembly
│   │   └── Crown Race
│   └── Other Components
└── Paint

Fazit

Die Verwendung des hierarchyid-Datentyps in SQL Server vereinfacht die Verwaltung und Abfrage komplexer hierarchischer Strukturen wie Bills of Materials erheblich. Durch die Integration von Methoden wie GetLevel und Parse können Sie nicht nur die Hierarchie effizient speichern, sondern auch schnell und einfach nach bestimmten Kriterien filtern und sortieren.

Um die vorgestellten Konzepte in Ihrer eigenen Organisation umzusetzen, empfehlen wir Ihnen, die Skripts anzupassen und in gespeicherten Prozeduren zu kapseln. Dadurch können Sie die Funktionalität leicht wiederverwendbar machen und Ihre Produktionsprozesse effektiver gestalten.

Nächste Schritte

  1. Laden Sie die AdventureWorks-Datenbank herunter und testen Sie die Skripts in Ihrer lokalen Umgebung.
  2. Passen Sie die Skripts an Ihre eigenen BOM-Daten an.
  3. Erstellen Sie Demonstrationsbeispiele für Ihr Produktions TEAM, um die Vorteile von hierarchyid aufzuzeigen.
  4. Implementieren Sie die Funktionalität in Ihren Produktionsprozessen durch gespeicherte Prozeduren oder andere T-SQL-Container.

Durch die Nutzung von hierarchyid können Sie die Verwaltung Ihrer Materiallisten professionalisieren und die Effizienz Ihrer Produktionsprozesse signifikant steigern.