Um diese Beispiele zu nutzen, können Sie gerne unsere Testdaten importieren und die Scripte anpassen um die Testdaten zu erzeugen. Alle hier gezeigten Scripte basieren auf den SQL Server von Microsoft.

Aufbau eines Data Warehouse – Von der Planung bis zur Realisierung

Die Welt der Daten ist faszinierend und komplex, und in der Ära der Digitalisierung ist das Management dieser Daten entscheidend geworden. Unternehmen aller Größenordnungen setzen auf Data Warehouses (DWH), um ihre Geschäftsprozesse zu optimieren und fundierte Entscheidungen zu treffen. In dieser Artikelserie werden wir gemeinsam ein Data Warehouse von Grund auf aufbauen – von der ersten Planung bis zur Realisierung.

In dieser ersten Ausgabe beginnen wir mit der Entwicklung einer klassischen Stage-to-Core-Architektur mit einem täglichen ETL-Job. In späteren Ausgaben werden wir das Konzept erweitern, um untertägige Datenextraktionen und letztlich ein Real-Time DWH zu realisieren.

Phase 1: Planung und Konzeption

1.1 Verstehen Sie Ihr Geschäft

Bevor Sie mit dem Bau Ihres DWH beginnen, ist es entscheidend, die Geschäftsprozesse, Anforderungen und Ziele Ihres Unternehmens zu verstehen. In unserem Labor Unternehmen geht es um ein Internet Unternehmen mit einen eigenen Online Shop. Dieser Online Shop hat in den letzten 24 Monaten rund 485 Kunden registriert und zu etwa 14.581  Bestellungen geführt. Wir werden als erstes einmal eine Datenbank mit den Testdaten unseres ERP erstellen.
Grundlage ist unsere aktuelle DWH Datenbank [DEV_ERP] auf Microsoft SQL Server 20222 Basis.

1.2 Datenquellen identifizieren

Die Erkundung und Bewertung von Datenquellen ist ein kritischer Schritt in der Planung eines Data Warehouse-Projekts. Hier sind einige generelle Schritte und Überlegungen, um dieses Ziel zu erreichen:

1. Identifikation von Datenquellen

  • Interne Datenquellen: ERP-Systeme, CRM-Software, Finanzsysteme, Betriebssysteme, etc.
  • Externe Datenquellen: Daten von Partnern, Kundendaten, Marktdaten, etc.

2. Struktur der Datenquellen

  • Datentypen: Text, Zahlen, Daten, BLOBs etc.
  • Schema: Wie sind die Daten organisiert? Welche Beziehungen existieren zwischen verschiedenen Datenentitäten?
  • Format: Daten können in verschiedenen Formaten vorliegen, wie z.B. CSV, XML, JSON, Excel, etc.

3. Datenqualität

  • Vollständigkeit: Fehlen Daten oder sind sie unvollständig?
  • Konsistenz: Sind die Daten über verschiedene Systeme hinweg konsistent?
  • Genauigkeit: Wie genau sind die Daten? Gibt es mögliche Fehlerquellen?

4. Zugänglichkeit der Daten

  • Schnittstellen: Wie können die Daten abgerufen werden? Gibt es APIs, Datenexporte oder andere Schnittstellen?
  • Berechtigungen: Wer hat Zugang zu den Daten? Wer sollte Zugang haben und wer nicht?
  • Aktualität: Wie oft werden die Daten aktualisiert? Wie oft müssen sie abgerufen werden?

5. Compliance und Sicherheit

  • Datenschutz: Welche Daten dürfen gespeichert und genutzt werden, insbesondere im Hinblick auf persönliche Informationen?
  • Compliance: Welche regulatorischen Anforderungen müssen beachtet werden (z.B. GDPR, HIPAA)?

6. Datenintegration

  • Integrationstechnologie: Welche Technologie wird zum Abrufen und Integrieren der Daten genutzt?
  • Transformationsbedarf: Wie müssen die Daten transformiert werden, um in das Data Warehouse integriert zu werden?

7. Datenexploration

  • Datenvisualisierung: Erstellen Sie Dashboards oder Berichte, um einen visuellen Überblick über die Daten zu bekommen.
  • Statistische Analyse: Erkunden Sie die Daten mithilfe statistischer Methoden, um Trends, Anomalien oder Muster zu erkennen.

8. Dokumentation

  • Metadaten: Dokumentieren Sie Metadaten, Beziehungen, Herkunft und jegliche Transformationen der Daten.
  • Datenkatalog: Erstellen Sie einen Katalog, der es den Benutzern ermöglicht, die verfügbaren Daten zu entdecken und zu verstehen.

9. Datenqualitätsmanagement

  • Datenbereinigung: Prozesse und Regeln zur Identifikation und Korrektur (oder Löschung) fehlerhafter Datensätze.
  • Datenanreicherung: Methoden zur Verbesserung der Datenqualität durch Anreicherung mit zusätzlichen Quellen.

10. Datenarchitektur

  • Datenmodellierung: Entwerfen Sie ein Datenmodell, das die Struktur der Datenquellen im Data Warehouse widerspiegelt.
  • Datenarchivierung: Entwickeln Sie Strategien zur Archivierung älterer Daten.

Die sorgfältige Erkundung und Bewertung von Datenquellen ist entscheidend für den Erfolg eines Data Warehouse-Projekts. Dies erfordert eine enge Zusammenarbeit zwischen den Fachbereichen, die die Daten nutzen, und den IT-Experten, die sie bereitstellen und verwalten. Ein strukturierter Ansatz, wie oben skizziert, hilft dabei, den Prozess systematisch und vollständig zu gestalten.

Basierend auf unser Test ERP Datenmodell ([DEV_ERP]) können wir verschiedene Kennzahlen (KPIs) und Dimensionen für ein Data Warehouse identifizieren. Hier sind einige Beispiele, wie Sie die Daten aus Ihrem operativen System für analytische Zwecke im Data Warehouse nutzen können:

Mögliche Kennzahlen (KPIs)

  1. Verkaufszahlen
    • Totaler Umsatz
    • Durchschnittlicher Verkaufswert
    • Verkaufte Menge pro Produkt/Kategorie
  2. Kundenaktivität
    • Anzahl neuer Kunden (in einem bestimmten Zeitraum)
    • Durchschnittliche Anzahl an Bestellungen pro Kunde
    • Durchschnittlicher Umsatz pro Kunde
  3. Produktleistung
    • Bestverkaufte Produkte
    • Umsatz pro Produkt/Kategorie
    • Lagerbestand pro Produkt/Kategorie
  4. Zahlungsperformance
    • Einnahmen pro Zahlungsart
    • Durchschnittliche Zahlungsdauer
  5. Kundenservice
    • Anzahl an Serviceanfragen
    • Durchschnittliche Lösungsdauer für Serviceanfragen
    • Anzahl offener/unbearbeiteter Serviceanfragen

Mögliche Dimensionen

  1. Zeitdimension
    • Jahr, Quartal, Monat, Woche, Tag, Stunde für zeitliche Analysen der KPIs (z. B. Trendanalysen)
  2. Kundendimension
    • Informationen über Kunden wie Ort, E-Mail-Adresse etc. für segmentierte Analysen
  3. Produktdimension
    • Informationen über Produkte wie Produktname, Beschreibung, Kategorie für Produktanalysen
  4. Kategoriedimension
    • Informationen über Produktkategorien zur Analyse von Kategorieleistungen
  5. Zahlungsdimension
    • Details über Zahlungsmethoden und Zahlungszeitpunkte für Cash Flow-Analysen und Zahlungstrendanalysen

Analysebeispiele

  1. Umsatzanalyse
    • Analysieren Sie den Umsatz auf täglicher, monatlicher und jährlicher Basis.
    • Vergleichen Sie den Umsatz zwischen verschiedenen Produktkategorien und einzelnen Produkten.
  2. Kundenanalyse
    • Ermitteln Sie die „Top-Kunden“ basierend auf dem Umsatz.
    • Analysieren Sie das Kaufverhalten von Kunden (welche Produkte kaufen sie, zu welchem Zeitpunkt etc.).
  3. Produktanalyse
    • Bestimmen Sie „Top-Seller“- und „Low-Seller“-Produkte.
    • Analysieren Sie den Lagerbestand und identifizieren Sie Produkte, die bald nachbestellt werden müssen.
  4. Serviceanalyse
    • Analysieren Sie die Häufigkeit und Art von Kundenanfragen.
    • Bestimmen Sie die durchschnittliche „Time-to-Solve“ für Supporttickets.

Zusammenfassung

Die Erstellung eines Data Warehouse basiert auf den identifizierten KPIs und Dimensionen und ermöglicht es Ihrem Unternehmen, aus den operativen Daten Einblicke zu gewinnen und fundierte Entscheidungen zu treffen.

Es ist wichtig, die Geschäftsziele und Anforderungen klar zu definieren, um sicherzustellen, dass das Data Warehouse die benötigten Daten in der erforderlichen Form bereitstellt. Dies kann auch die Erstellung von Dashboards und Reports einschließen, um verschiedene Stakeholder mit den relevanten Informationen zu versorgen.

1.3 Modellierung des Data Warehouse

Entscheiden Sie, welches Datenmodell am besten zu Ihren Anforderungen passt: Star Schema, Snowflake Schema oder eine Variation davon.

Das Datenmodell, das wir im jetzigen Beispiel für die Core-Schicht besprechen werden, ähnelt am ehesten einer Star Schema-Architektur. Im Star Schema zentriert eine Faktentabelle, die messbare, quantitative Daten enthält, umgeben von Dimensionstabellen, die deskriptive, qualitative Daten enthalten. Dieses Schema ist als „Stern“ bekannt, weil die Entity-Relationship-Diagramme dieser Schemata wie ein Stern aussehen, wobei die Faktentabelle in der Mitte ist, umgeben von den Dimensionstabellen.

Phase 2: Implementierung der Stage-Schicht

Die Staging-Schicht (oder Stage Area) ist ein kritischer Bestandteil einer Data-Warehouse-Architektur. Hier werden Daten aus unterschiedlichen Quellsystemen temporär gespeichert, bevor sie weiterverarbeitet und in die nachfolgenden Schichten (z.B. Core-Layer) des Data Warehouses geladen werden. Nachfolgend sind einige wichtige Aspekte und Best Practices für die Gestaltung und Verwendung der Staging-Schicht aufgeführt:

2.1 Datenimport:

  • Rohdatenimport: Daten werden in einem „wie es ist“-Zustand importiert, ohne Anwendung von Geschäftslogik oder Transformationen.
  • Automatisierung: Der Datenimport sollte automatisiert und nach einem definierten Zeitplan erfolgen.

2.2 Temporäre Speicherung:

  • Kurzfristige Lagerung: Daten werden nur für eine kurze Zeitspanne gelagert, oft nur für die Dauer des ETL-Prozesses.
  • Struktur: Die Struktur der Staging-Tabellen ähnelt oft stark den Quellsystemen.

2.3 Datenqualität:

  • Validierung: Daten können auf Konsistenz und Vollständigkeit geprüft werden.
  • Fehlerhandling: Fehlende oder fehlerhafte Daten sollten erfasst und behandelt werden, um die Datenqualität zu gewährleisten.

2.4 Performance:

  • Indizierung: Während in der Staging-Schicht in der Regel keine Indizes benötigt werden, kann es in manchen Fällen (z.B. bei der Delta-Bestimmung) sinnvoll sein.
  • Partitionierung: Für größere Datenmengen kann eine Partitionierung der Daten hilfreich sein, um die Performance zu optimieren.

2.5 Datenmanagement:

  • Archivierung: Alte Daten sollten regelmäßig archiviert oder gelöscht werden, um die Performance nicht zu beeinträchtigen.
  • Backup: Obwohl Daten in der Staging-Schicht temporär sind, könnte ein Backup-Strategie notwendig sein, abhängig von den Anforderungen und der Dauer der Datenhaltung.

2.6 Sicherheit:

  • Datenschutz: Sensible Daten sollten geschützt werden, möglicherweise durch Anonymisierung oder Verschlüsselung.
  • Zugriffssteuerung: Der Zugriff auf die Staging-Schicht sollte kontrolliert und beschränkt sein, um die Sicherheit und Integrität der Daten zu gewährleisten.

2.7 Konformität:

  • Gesetzliche Vorgaben: Es ist wichtig, alle relevanten Datenschutzgesetze und -regelungen, wie z.B. die DSGVO, zu beachten und einzuhalten.

2.8 Monitoring:

  • Überwachung: Überwachung der Datenladeprozesse ist entscheidend, um Probleme oder Fehler schnell zu identifizieren und zu beheben.
  • Protokollierung: Alle Aktivitäten, insbesondere Fehler oder Abweichungen, sollten protokolliert werden.

Die Staging-Schicht spielt eine entscheidende Rolle bei der Vorbereitung der Daten für weitere Verarbeitungsstufen im Data Warehouse und ermöglicht eine effiziente und qualitativ hochwertige Datenverarbeitung. Beachten Sie diese Prinzipien, um eine robuste und effektive Staging-Schicht zu gewährleisten.

Basierend auf unsere vorherige Analyse der möglichen Auswertungen im DWH benötigen wir bis auf Warenkorb und WarenkorbArtikel alle Tabellen aus unserem ERP Datenmodell für die Stage Area (In einer späteren Ausbaustufe unseres DWH`s werden wir bei jeden Abzug die Stage Area dynamisch neu aufbauen, dazu später mehr) :

CREATE DATABASE DWH_Stage
GO
USE DWH_Stage
GO
-- Staging_Benutzer Tabelle
CREATE TABLE ERP_Benutzer (
BenutzerID INT,
Benutzername NVARCHAR(255),
Passwort NVARCHAR(255),
Email NVARCHAR(255),
Adresse NVARCHAR(1024),
Telefonnummer NVARCHAR(20),
ExtractDate DATETIME DEFAULT GETDATE(),
LLDateID INT,
SourceID INT
);

-- Staging_Kategorie Tabelle
CREATE TABLE ERP_Kategorie (
KategorieID INT,
KategorieName NVARCHAR(255),
Beschreibung NVARCHAR(1024),
ExtractDate DATETIME DEFAULT GETDATE(),
LLDateID INT,
SourceID INT
);

-- Staging_Produkt Tabelle
CREATE TABLE ERP_Produkt (
ProduktID INT,
ProduktName NVARCHAR(255),
Beschreibung NVARCHAR(1024),
Preis DECIMAL(10, 2),
MinMenge INT,
KategorieID INT,
ExtractDate DATETIME DEFAULT GETDATE(),
LLDateID INT,
SourceID INT
);

-- Staging_Bestellung Tabelle
CREATE TABLE ERP_Bestellung (
BestellungID INT,
BenutzerID INT,
Bestelldatum DATETIME,
BeginnBearbeitungDatum DATETIME,
Versanddatum DATETIME,
Lieferdatum DATETIME,
VersenderID INT,
Status NVARCHAR(100),
ExtractDate DATETIME DEFAULT GETDATE(),
LLDateID INT,
SourceID INT
);

CREATE TABLE ERP_Versender (
    VersenderID INT,
    VersenderName NVARCHAR(255),
    Beschreibung NVARCHAR(1024),
	ExtractDate DATETIME DEFAULT GETDATE(),
	LLDateID INT,
	SourceID INT
);

-- Staging_BestellDetail Tabelle
CREATE TABLE ERP_BestellDetail (
BestellDetailID INT,
BestellungID INT,
ProduktID INT,
Menge INT,
Preis DECIMAL(10, 2),
ExtractDate DATETIME DEFAULT GETDATE(),
LLDateID INT,
SourceID INT
);


-- Staging_Zahlung Tabelle
CREATE TABLE ERP_Zahlung (
ZahlungID INT,
BestellungID INT,
Zahlungsart NVARCHAR(100),
Zahlungsbetrag DECIMAL(10, 2),
Zahlungsdatum DATETIME,
ExtractDate DATETIME DEFAULT GETDATE(),
LLDateID INT,
SourceID INT
);

-- Staging_Lager Tabelle
CREATE TABLE ERP_Lager (
LagerID INT,
LagerName NVARCHAR(100),
Adresse NVARCHAR(255),
Kapazität INT,
ExtractDate DATETIME DEFAULT GETDATE(),
LLDateID INT,
SourceID INT
);

-- Staging_Lagerbestand Tabelle
CREATE TABLE ERP_Lagerbestand (
LagerbestandID INT,
LagerID INT,
ProduktID INT,
Lagerbestand INT,
ExtractDate DATETIME DEFAULT GETDATE(),
LLDateID INT,
SourceID INT
);

-- Staging_ServiceAnfragen Tabelle
CREATE TABLE ERP_ServiceAnfragen (
AnfrageID INT,
BenutzerID INT,
Erstellungsdatum DATE,
Status NVARCHAR(50),
Beschreibung NVARCHAR(1000),
Lösung NVARCHAR(1000),
ExtractDate DATETIME DEFAULT GETDATE(),
LLDateID INT,
SourceID INT
);

-- Staging_SupportTickets Tabelle
CREATE TABLE ERP_SupportTickets (
TicketID INT,
AnfrageID INT,
ZuweisenAn NVARCHAR(50),
Priorität NVARCHAR(50),
ExtractDate DATETIME DEFAULT GETDATE(),
LLDateID INT,
SourceID INT
);

In den Staging-Tabellen habe ich zusätzliche Spalten wie ExtractDate und SourceID sowie LLDateID hinzugefügt. Diese Spalten sind nützlich, um zu wissen, wann die Daten extrahiert wurden und aus welcher Quelle sie stammen. Sie können bei der Fehlersuche und der Datenvalidierung nützlich sein.

2.9 Einrichten der Datenextraktion

Entwickeln Sie ETL-Prozesse, um Daten aus Ihrem ERP-System zu extrahieren und in die Staging-Area Ihres DWH zu laden.
An dieser Stelle implementieren wir auch gleich unser Meta Datenmanagement.
Dieses erweitern wir um eine neue Tabelle [META].[dbo].[DWH_ETL_Source] mit den ersten Datensatz.

USE META
GO

CREATE TABLE dbo.DWH_ETL_Source(
	ID int IDENTITY(1,1) NOT NULL,
	SourceName nvarchar(100) NOT NULL,
	Aktiv int NULL,
	LastImportDateID int NULL
) ON [PRIMARY]
GO

ALTER TABLE dbo.DWH_ETL_Source ADD  CONSTRAINT DF_DWH_ETL_Source_Aktiv  DEFAULT ((0)) FOR Aktiv
GO

--- für die Initialbefüllung nehmen wir alles was älter als 01.01.2020 ist.
INSERT INTO dbo.DWH_ETL_Source (SourceName,Aktiv,LastImportDateID) VALUES ('DEV-ERP',1,20200101);

Jetzt erstellen wir einen neuen SQL Agent Server Job. Der Job soll jede Nacht um 00:00 Uhr laufen und die Daten von unserem ERP System in die Stage Area unseres DWH laden.

USE msdb
GO


/****** Object:  Job [ETL ERP to Stage]    Script Date: 09.10.2023 17:10:14 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 09.10.2023 17:10:14 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


END


DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'ETL ERP to Stage', 
@enabled=1, 
@notify_level_eventlog=0, 
@notify_level_email=0, 
@notify_level_netsend=0, 
@notify_level_page=0, 
@delete_level=0, 
@description=N'mit diesen Job laden wir unsere Daten in die Stage Area. Teils werden die Daten komplett geladen, bei großen Tabellen wird geprüft ob wir Anhand einer Fachlichen Abgrenzung an ein Datum einschränken können.', 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'FRANK-MASTER\asus', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [ELT  - Daten holen aus dem Operativen System]    Script Date: 09.10.2023 17:10:14 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'ELT  - Daten holen aus dem Operativen System', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=3, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'USE DWH_Stage
GO
Declare @Source int, @LLDateID int, @LastImportDateID int;

--- wir suchen uns die ID für unsere Source aus der Meta Tabelle
Set @Source = (SELECT ID FROM META.dbo.DWH_ETL_Source WHERE SourceName = ''DEV-ERP'' AND Aktiv =1);

--- Damit wir unsere neugeladenen Daten schneller finden können, benötigen wir hier noch eine ID
SET @LLDateID = convert(int,convert(nvarchar(8),getdate(),112)); 

--- hier holen wir uns den letzten Ladetag und nehmen diesen - 10 Tage zurück
SET @LastImportDateID = (SELECT  convert(int,convert(nvarchar(8),DateADD(DAY,-10,CONVERT(date, CONVERT(varchar(8), LastImportDateID), 112)),112)) FROM META.dbo.DWH_ETL_Source WHERE SourceName = ''DEV-ERP'' AND Aktiv =1);

-- Laden von Benutzer Tabelle, diese wird bei jeden Abzug einfach immer komplett geladen
Truncate Table ERP_Benutzer; 
INSERT INTO ERP_Benutzer (BenutzerID, Benutzername, Passwort, Email, Adresse, Telefonnummer, ExtractDate,LLDateID, SourceID)
SELECT BenutzerID, Benutzername, Passwort, Email, Adresse, Telefonnummer, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.Benutzer;

-- Laden von Kategorie, auch komplett 
Truncate Table ERP_Kategorie; 
INSERT INTO ERP_Kategorie (KategorieID, KategorieName, Beschreibung, ExtractDate,LLDateID, SourceID)
SELECT KategorieID, KategorieName, Beschreibung, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.Kategorie;

-- Laden von Produkt, auch komplett 
Truncate Table ERP_Produkt; 
INSERT INTO ERP_Produkt (ProduktID, ProduktName, Beschreibung, Preis, MinMenge, KategorieID, ExtractDate, LLDateID, SourceID)
SELECT ProduktID, ProduktName, Beschreibung, Preis, MinMenge, KategorieID, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.Produkt;

-- Laden von Bestellung, hier grenzen wir ein, nur die veränderten Daten, wir wissen Bestelldatum oder Versanddatum werden aktualisiert hier nehmen das Datum vom letzten Lauf - 10 Tage. 
Truncate Table ERP_Bestellung; 
INSERT INTO ERP_Bestellung (BestellungID, BenutzerID, Bestelldatum, BeginnBearbeitungDatum, Versanddatum, Lieferdatum, VersenderID, Status, ExtractDate, LLDateID, SourceID)
SELECT BestellungID, BenutzerID, Bestelldatum, BeginnBearbeitungDatum, Versanddatum, Lieferdatum, VersenderID, Status, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.Bestellung WHERE convert(int,convert(nvarchar(8),Bestelldatum,112)) >= @LastImportDateID  OR convert(int,convert(nvarchar(8),Versanddatum,112)) >=@LastImportDateID OR convert(int,convert(nvarchar(8),BeginnBearbeitungDatum,112)) >=@LastImportDateID OR convert(int,convert(nvarchar(8),Lieferdatum,112)) >=@LastImportDateID;

-- Laden von Versender auch komplett,
Truncate Table ERP_Versender; 
INSERT INTO ERP_Versender (VersenderID, VersenderName, Beschreibung, ExtractDate, LLDateID, SourceID)
SELECT VersenderID, VersenderName, Beschreibung, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.Versender;

-- Laden von BestellDetail abgrenzen nur die Bestellungen die wir zuvor geladen haben. 
Truncate Table ERP_BestellDetail; 
INSERT INTO ERP_BestellDetail (BestellDetailID, BestellungID, ProduktID, Menge, Preis, ExtractDate, LLDateID, SourceID)
SELECT BestellDetailID, BestellungID, ProduktID, Menge, Preis, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.BestellDetail WHERE BestellungID IN ( Select BestellungID FROM ERP_Bestellung);

-- Laden von Lager, auch komplett 
Truncate Table ERP_Lager; 
INSERT INTO ERP_Lager (LagerID, LagerName, Adresse, Kapazität, ExtractDate, LLDateID, SourceID)
SELECT LagerID, LagerName, Adresse, Kapazität, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.Lager;

-- Laden von Lagerbestand, auch komplett 
Truncate Table ERP_Lagerbestand; 
INSERT INTO ERP_Lagerbestand (LagerbestandID, LagerID, ProduktID, Lagerbestand, ExtractDate, LLDateID, SourceID)
SELECT LagerbestandID, LagerID, ProduktID, Lagerbestand, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.Lagerbestand;

-- Laden von ServiceAnfragen, hier wird das Erstellungsdatum immer gesetzt
Truncate Table ERP_ServiceAnfragen; 
INSERT INTO ERP_ServiceAnfragen (AnfrageID, BenutzerID, Erstellungsdatum, Status, Beschreibung, Lösung, ExtractDate, LLDateID, SourceID)
SELECT AnfrageID, BenutzerID, Erstellungsdatum, Status, Beschreibung, Lösung, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.ServiceAnfragen WHERE convert(int,convert(nvarchar(8),Erstellungsdatum,112)) >=@LastImportDateID;

-- Laden von Bestellung, hier grenzen wir ein, nur die veränderten Daten, wir wissen Bestelldatum oder Versanddatum werden aktualisiert. 
Truncate Table ERP_Zahlung; 
INSERT INTO ERP_Zahlung (ZahlungID, BestellungID, Zahlungsart, Zahlungsbetrag, Zahlungsdatum, ExtractDate, LLDateID, SourceID)
SELECT ZahlungID, BestellungID, Zahlungsart, Zahlungsbetrag, Zahlungsdatum, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.Zahlung WHERE convert(int,convert(nvarchar(8),Zahlungsdatum,112)) >=@LastImportDateID;

-- Laden von ServiceAnfragen, auch komplett 
Truncate Table ERP_SupportTickets; 
INSERT INTO ERP_SupportTickets (TicketID, AnfrageID, ZuweisenAn, Priorität, ExtractDate, LLDateID, SourceID)
SELECT TicketID, AnfrageID, ZuweisenAn, Priorität, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.SupportTickets;

', 
@database_name=N'master', 
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Ladedatum hochsetzen]    Script Date: 09.10.2023 17:10:14 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Ladedatum hochsetzen', 
@step_id=2, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'Update META.dbo.DWH_ETL_Source SET LastImportDateID = convert(int,convert(nvarchar(8),getdate(),112))  WHERE SourceName  = ''DEV-ERP'' AND Aktiv =1 ;', 
@database_name=N'master', 
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'täglicher ETL Schritt to Stage', 
@enabled=1, 
@freq_type=4, 
@freq_interval=1, 
@freq_subday_type=1, 
@freq_subday_interval=0, 
@freq_relative_interval=0, 
@freq_recurrence_factor=0, 
@active_start_date=20231009, 
@active_end_date=99991231, 
@active_start_time=0, 
@active_end_time=235959, 
@schedule_uid=N'9b687e39-9bd1-4d3c-95f8-06ec2448480f'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Phase 3: Implementierung der Core-Schicht

CREATE DATABASE DWH_Core
GO
USE DWH_Core
GO

---Die DimDate wird einmal aufgebaut, danach kommen erst wieder nach 2099 Daten hinzu :) 
CREATE TABLE dbo.DimDate(
	DateID int,
	[Date] Date, 
	Date_DE char(10),
	Date_US char(10),
	Date_ANSI char(10),
	Date_ISO8601 char(10),
	YearID int,
	MonthID nvarchar(10),
	QuarterID char(6),
	KwID char(6),
	DayofYear int,
	CountofDayMonth int,
	restofDayYear int,
	DayofWeek int,
	CalendarWeek int,
	Month int,
	Day int,
	Quarter int,
	beginLastWeek date,
	endLastWeek date,
	beginCurrentWeek date,
	endCurrentWeek date,
	beginNextWeek date,
	endNextWeek date,
	beginLastMonth date,
	endLastMonth date,
	beginCurrentMonth date,
	endCurrentMonth date,
	beginNextMonth date,
	endNextMonth date,
	beginLastYear date,
	endLastYear date,
	beginCurrentYear date,
	endCurrentYear date,
	beginNextYear date,
	endNextYear date,
	weekday_DE nvarchar(30),
	MonthName_DE nvarchar(30)
) ;

---Die Benutzertabelle, hier haben wir das Passwort nicht mit in unser DWH überführt, dafür haben wir hier die Adresse richtig aufgelöst. Bundesland und Land fügen wir dann noch von einer anderen Externen Quelle ein.
CREATE TABLE DimBenutzer (
    BenutzerKey INT PRIMARY KEY IDENTITY(1,1),
    BenutzerID INT,
    Benutzername NVARCHAR(255),
    Email NVARCHAR(255),
    Strasse NVARCHAR(255),
    Telefonnummer NVARCHAR(10),
    PLZ NVARCHAR(10),
    Ort NVARCHAR(100),
    Bundesland NVARCHAR(100),
    Land NVARCHAR(100),
    GueltigVon DATETIME,
    GueltigBis DATETIME
);


-- DimProdukt
CREATE TABLE DimProdukt (
    ProduktKey INT PRIMARY KEY IDENTITY(1,1),
    ProduktID INT,
    ProduktName NVARCHAR(255),
    Beschreibung NVARCHAR(1024),
	MinMenge INT,
    KategorieKey INT,
    GueltigVon DATETIME,
    GueltigBis DATETIME
);


-- DimKategorie
CREATE TABLE DimKategorie (
    KategorieKey INT PRIMARY KEY IDENTITY(1,1),
    KategorieID INT,
    KategorieName NVARCHAR(255),
    Beschreibung NVARCHAR(1024),
    GueltigVon DATETIME,
    GueltigBis DATETIME
);


-- DimZahlungsart
CREATE TABLE DimZahlungsart (
    ZahlungsartKey INT PRIMARY KEY IDENTITY(1,1),
    Zahlungsart NVARCHAR(100),
    GueltigVon DATETIME,
    GueltigBis DATETIME
);

CREATE TABLE DimVersender (
    VersenderKey INT PRIMARY KEY IDENTITY(1,1),
	VersenderID int,
    VersenderName NVARCHAR(255),
    Beschreibung NVARCHAR(1024),
    GueltigVon DATETIME,
    GueltigBis DATETIME
);

CREATE TABLE DimStatus (
    StatusKey INT PRIMARY KEY IDENTITY(1,1),
    Status NVARCHAR(255),
    Beschreibung NVARCHAR(1024),
    GueltigVon DATETIME,
    GueltigBis DATETIME
);

--- DimLager 
CREATE TABLE DimLager(
	LagerKey INT PRIMARY KEY IDENTITY(1,1),
	LagerID INT,
	LagerName NVARCHAR(100),
	Adresse NVARCHAR(255),
	Kapazität INT,
    GueltigVon DATETIME,
    GueltigBis DATETIME
);

--- DimServiceAnfrage
CREATE TABLE DimServiceAnfrage (
    ServiceAnfrageKey INT PRIMARY KEY IDENTITY(1,1),
    ServiceAnfrageID INT,
    BenutzerKey INT,
    Erstellungsdatum DATETIME,
    Status NVARCHAR(50),
    Beschreibung NVARCHAR(1000),
    Lösung NVARCHAR(1000),
    GueltigVon DATETIME,
    GueltigBis DATETIME,
   
);

--- DimSupportTicket
CREATE TABLE DimSupportTicket (
    SupportTicketKey INT PRIMARY KEY IDENTITY(1,1),
    SupportTicketID INT,
    ServiceAnfrageKey INT,
    ZuweisenAn NVARCHAR(50),
    Priorität NVARCHAR(50),
    GueltigVon DATETIME,
    GueltigBis DATETIME
);

-- FactBestellungAngelegt 
CREATE TABLE FactBestellungAngelegt (
    BestellungKey INT PRIMARY KEY IDENTITY(1,1),
	BestellungID int,
    AnlegeZeitDateID INT,
    BenutzerKey INT,
    ProduktKey INT,
    Bestelldatum DATETIME,
	BestellDateID INT,
    Menge INT,
    EPN_Preis DECIMAL(10, 2), --- EinzelPreis Netto
	EPB_Preis DECIMAL(10, 2), --- EinzelPreis Brutto
	GPN_Summe DECIMAL(10, 2), --- Gesamtpreis Netto
	GPB_Summe DECIMAL(10, 2), --- Gesamtpreis Brutto
	LLDateID INT,
    ImportDatum DATETIME
);

-- FactBestellungBearbeitet
CREATE TABLE FactBestellungBearbeitet (
    BearbeitungKey INT PRIMARY KEY IDENTITY(1,1),
    BestellungKey INT,
	Bestelldatum DATETIME,
	BeginnBearbeitung DATETIME,
    BeginnBearbeitungZeitDateID INT,
	StatusID INT,
	LLDateID INT,
    ImportDatum DATETIME
);

-- FactBestellungVersendet
CREATE TABLE FactBestellungVersendet (
    VersandKey INT PRIMARY KEY IDENTITY(1,1),
    BestellungKey INT,
    VersandZeitDateID INT,
    VersenderKey INT,
	BeginnBearbeitung DATETIME, 
	VersandZeit DATETIME,
	StatusID INT,
	LLDateID INT,
    ImportDatum DATETIME
);


-- FactBestellungGeliefert
CREATE TABLE FactBestellungGeliefert (
    LieferungKey INT PRIMARY KEY IDENTITY(1,1),
    BestellungKey INT,
    LieferZeitDateID INT,
	VersandZeit DATETIME,
	LieferZeit DATETIME, 
    VersenderKey INT,
	LLDateID INT,
    ImportDatum DATETIME
);


-- FactZahlung 
CREATE TABLE FactZahlung (
    ZahlungKey INT PRIMARY KEY IDENTITY(1,1),
    BestellungKey INT,
    ZahlungsartKey INT,
    Zahlungsbetrag DECIMAL(10, 2),
    Zahlungsdatum DATETIME,
	LLDateID INT,
    ImportDatum DATETIME
);


-- FactLagerbestand
CREATE TABLE FactLagerbestand (
    LagerbestandKey INT PRIMARY KEY IDENTITY(1,1),
    ProduktKey INT,
    LagerKey INT, 
    Datum DATETIME,
    DaumID INT,
    Menge INT,
    Preis DECIMAL(10, 2), -- Preis des Artikels am jeweiligen Tag
	LLDateID INT,
    ImportDatum DATETIME
);

----FactSupportInteraktion
CREATE TABLE FactSupportInteraktion (
    SupportInteraktionKey INT PRIMARY KEY IDENTITY(1,1),
    SupportTicketKey INT,
    InteraktionsDatum DATETIME,
    InteraktionsTyp NVARCHAR(50),
    InteraktionsDauer INT, -- z.B. Dauer eines Anrufs in Minuten
 	LLDateID INT,
    ImportDatum DATETIME
);

Für unsere DimDate benötigen wir nur eine Initialbefüllung da diese Daten für die nächsten 199 Jahre ausreichend sind….ok jetzt nur noch 76 Jahre 🙂

USE DWH_Core
GO
DECLARE @StartDate DATE, @EndDate DATE

---wir befüllen die DimDate mal für knapp 200 Jahre
SET @StartDate = '1900-01-01'
SET @EndDate = '2099-12-31';


WITH DateRange(DateData) AS 
(
  SELECT @StartDate as Date
  UNION ALL
  SELECT DATEADD(d,1,DateData)
  FROM DateRange 
  WHERE DateData < @EndDate
)
Insert into dbo.DimDate
SELECT 
      CONVERT(INT,CONVERT(VARCHAR(8),DateData,112)) AS DateID
,DateData
,CONVERT(char(10), DateData, 104) AS Date_DE
,CONVERT(char(10), DateData, 101) AS Date_US 
,CONVERT(char(10), DateData, 102) AS Date_ANSI 
,CONVERT(char(10), DateData, 23) AS Date_ISO8601 
,DATEPART(year, DateData) AS YearID
,FORMAT(DateData,'yyyyMM') AS MonthID
,CAST(year(DateData) AS char(4)) + CAST(DATEPART( QUARTER, DateData ) AS char(2)) AS QuarterID 
,CAST(year(DateData) AS char(4)) + CAST(DATEPART( wk, DateData ) AS char(2)) AS KwID
,DATEPART(dayofyear, DateData) AS DayofYear
,DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,DateData),0))) AS CountofDayMonth
,DATEDIFF(day, DateData, DATEADD(DAY, -1, DATEADD(YEAR, 1, DATEFROMPARTS(YEAR(DateData), 1, 1)))) restofDayYear
,DATEPART(WEEKDAY, DateData) AS DayofWeek
,DATEPART( wk, DateData ) AS CalendarWeek
,DATEPART(MONTH, DateData) AS Month
,DATEPART(DAY, DateData) AS Day
,DATEPART( QUARTER, DateData ) AS Quarter
,CAST(DATEADD(WEEK, -1 ,DATEADD(DAY, -DATEPART(WEEKDAY, DateData) + 1, DateData)) AS DATE) AS beginLastWeek
,CAST(DATEADD(WEEK, -1, DATEADD(DAY, -DATEPART(WEEKDAY, DateData) + 7, DateData)) AS DATE) AS endLastWeek
,CAST(DATEADD(DAY, -DATEPART(WEEKDAY, DateData) + 1, DateData) AS DATE) AS beginCurrentWeek
,CAST(DATEADD(DAY, -DATEPART(WEEKDAY, DateData) + 7, DateData) AS DATE) AS endCurrentWeek
,CAST(DATEADD(WEEK, 1 ,DATEADD(DAY, -DATEPART(WEEKDAY, DateData) + 1, DateData)) AS DATE) AS beginNextWeek
,CAST(DATEADD(WEEK, 1, DATEADD(DAY, -DATEPART(WEEKDAY, DateData) + 7, DateData)) AS DATE) AS endNextWeek
,CAST(DATEADD(MONTH, -1 , DATEFROMPARTS(YEAR(DateData),MONTH(DateData),1)) AS DATE) AS beginLastMonth
,CAST(DATEADD(DAY, -1 , DATEFROMPARTS(YEAR(DateData),MONTH(DateData),1)) AS DATE) AS endLastMonth
,DATEFROMPARTS(YEAR(DateData),MONTH(DateData),1) AS beginCurrentMonth
,DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(DateData),MONTH(DateData),1))) AS endCurrentMonth
,DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(DateData),MONTH(DateData), 1)) AS beginNextMonth
,DATEADD(DAY, -1, DATEADD(MONTH, 2, DATEFROMPARTS(YEAR(DateData),MONTH(DateData), 1))) AS endNextMonth
,DATEADD(YEAR, -1, DATEFROMPARTS(YEAR(DateData), 1, 1)) AS beginLastYear
,DATEADD(DAY, -1, DATEFROMPARTS(YEAR(DateData), 1, 1)) AS endLastYear
,DATEFROMPARTS(YEAR(DateData), 1, 1) AS beginCurrentYear
,DATEADD(DAY, -1, DATEADD(YEAR, 1, DATEFROMPARTS(YEAR(DateData), 1, 1))) AS endCurrentYear
,DATEADD(YEAR, 1, DATEFROMPARTS(YEAR(DateData), 1, 1)) AS beginNextYear
,DATEADD(DAY, -1, DATEADD(YEAR, 2, DATEFROMPARTS(YEAR(DateData), 1, 1))) AS endNextYear
,DATENAME(dw,DateData) AS weekday_DE
,DATENAME(MONTH,DateData) AS MonthName_DE
FROM DateRange
OPTION (MAXRECURSION 0);

 

3.1 Einführung in Slowly Changing Dimensions (SCD)

Wir implementieren SCD-Strategien, um Änderungen im Laufe der Zeit effektiv nachzuvollziehen.

Fangen wir mit unser Benutzertabelle an, hier werden wir nicht nur die Daten von der Stage in die Core Schicht laden, sondern auch die Adresse transformieren und mit zusätzlichen Daten anreichern:

USE DWH_Core
GO

DECLARE @CurrentDate AS DATETIME = GETDATE();
DECLARE @IsUpdated AS BIT = 0;

----Benutzer in die Core laden und Historisieren

-- Cursor-Variablen-Deklaration
DECLARE @BenutzerID INT, @Benutzername NVARCHAR(255), @Adresse NVARCHAR(1024), @Email NVARCHAR(255), @Telefonnummer NVARCHAR(20);

-- Markieren Sie alle Datensätze als gelöscht, die nicht mehr in der DWH_Stage.dbo.ERP_Benutzer-Tabelle vorhanden sind
UPDATE DimBenutzer
SET GueltigBis = @CurrentDate
WHERE GueltigBis IS NULL AND BenutzerID NOT IN (SELECT BenutzerID FROM DWH_Stage.dbo.ERP_Benutzer);

-- Cursor-Definition
DECLARE BenutzerCursor CURSOR FOR
SELECT
BenutzerID,
Benutzername,
Adresse,
Email,
Telefonnummer
FROM
DWH_Stage.dbo.ERP_Benutzer

OPEN BenutzerCursor;
FETCH NEXT FROM BenutzerCursor INTO @BenutzerID, @Benutzername, @Adresse, @Email, @Telefonnummer;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Aufteilen der Adresse in ihre Komponenten
-- (Hier können Sie weitere Verarbeitungslogik hinzufügen, um die Adresse in ihre Komponenten aufzuteilen)
DECLARE @Strasse NVARCHAR(255), @PLZ NVARCHAR(10), @Ort NVARCHAR(255);
SET @Strasse = LEFT(@Adresse, CHARINDEX(',', @Adresse) - 1);
SET @PLZ = SUBSTRING(@Adresse, CHARINDEX(',', @Adresse) + 2, CHARINDEX(' ', @Adresse, CHARINDEX(',', @Adresse) + 2) - CHARINDEX(',', @Adresse) - 2);
SET @Ort = RIGHT(@Adresse, LEN(@Adresse) - CHARINDEX(' ', @Adresse, CHARINDEX(',', @Adresse) + 2));
-- Prüfen Sie, ob es Änderungen für diesen Benutzer gibt
IF NOT EXISTS (
SELECT 1
FROM DimBenutzer
WHERE
BenutzerID = @BenutzerID
AND Benutzername = @Benutzername
AND Strasse = @Strasse
AND PLZ = @PLZ
AND Ort = @Ort
AND Email = @Email
AND Telefonnummer = @Telefonnummer
AND GueltigBis IS NULL -- Nur aktuelle Datensätze prüfen
)
BEGIN
-- Setzen Sie die Flag, um anzuzeigen, dass Updates vorhanden sind
SET @IsUpdated = 1;
-- Setzen Sie das GültigBis-Datum für den alten Datensatz auf das aktuelle Datum
UPDATE DimBenutzer
SET GueltigBis = @CurrentDate
WHERE BenutzerID = @BenutzerID AND GueltigBis IS NULL;
END
-- Wenn es ein Update gibt oder der Benutzer noch nicht in DimBenutzer existiert, fügen Sie den neuen Datensatz ein
IF @IsUpdated = 1 OR NOT EXISTS (
SELECT 16
FROM DimBenutzer
WHERE BenutzerID = @BenutzerID
)
BEGIN
INSERT INTO DimBenutzer (BenutzerID, Benutzername, Strasse, PLZ, Ort, Email, Telefonnummer, GueltigVon, GueltigBis)
VALUES (@BenutzerID, @Benutzername, @Strasse, @PLZ, @Ort, @Email, @Telefonnummer, @CurrentDate, NULL);
END
SET @IsUpdated = 0;
FETCH NEXT FROM BenutzerCursor INTO @BenutzerID, @Benutzername, @Adresse, @Email, @Telefonnummer;
END

CLOSE BenutzerCursor;
DEALLOCATE BenutzerCursor;

--hier ergänzen wir die Daten noch mit dem Bundeslad und dem Lankreis
UPDATE DWH_Core.dbo.DimBenutzer
SET Bundesland = PLZ.Bundesland, Land = PLZ.Landkreis
FROM DWH_Core.dbo.DimBenutzer Benutzer
INNER JOIN TestDaten.dbo.Adressen PLZ
ON Benutzer.PLZ = PLZ.Postleitzahl AND Benutzer.Ort = PLZ.Ort
WHERE Benutzer.Bundesland IS NULL
AND Benutzer.Land IS NULL ;

Jetzt folgen 2 Dimensionen die wir nicht Historisieren können, da wir nie sicherstellen können, jeden zustand täglich in unsere Stage Laden zu können.

USE DWH_Core
GO
---Diese Dimensionen werden nie einen Status wie geschlossen herstellen können, von daher ist hier eine Versionierung nicht möglich, und auch nicht Sinnvoll.
DECLARE @CurrentDate AS DATETIME = GETDATE();

-- MERGE-Befehl zum Einfügen/Aktualisieren von Daten in DimStatus
MERGE INTO DimStatus AS Target
USING (
SELECT DISTINCT Status
FROM DWH_Stage.dbo.ERP_Bestellung
) AS Source
ON Target.Status = Source.Status AND Target.GueltigBis IS NULL
-- Wenn der Datensatz in der Ziel-Tabelle nicht gefunden wird, wird eingefügt
WHEN NOT MATCHED BY TARGET THEN
INSERT (Status, GueltigVon, GueltigBis)
VALUES (Source.Status, @CurrentDate, NULL);


-- MERGE-Befehl zum Einfügen/Aktualisieren von Daten in DimZahlungsart
MERGE INTO DimZahlungsart AS Target
USING (
SELECT DISTINCT Zahlungsart
FROM DWH_Stage.dbo.ERP_Zahlung
) AS Source
ON Target.Zahlungsart = Source.Zahlungsart AND Target.GueltigBis IS NULL
-- Wenn der Datensatz in der Ziel-Tabelle nicht gefunden wird, wird eingefügt
WHEN NOT MATCHED BY TARGET THEN
INSERT (Zahlungsart, GueltigVon, GueltigBis)
VALUES (Source.Zahlungsart, @CurrentDate, NULL);

Unsere Produkt Tabelle ist die nächste, hier können wir wieder unsere Daten Historisieren, da wir die Produkt Tabelle komplett aus dem Operativen System täglich laden.

USE DWH_Core
GO

DECLARE @CurrentDate AS DATETIME = GETDATE();
-- MERGE-Befehl zum Einfügen/ Aktualisieren von Daten in DimProdukt
MERGE INTO DimProdukt AS Target
USING DWH_Stage.dbo.ERP_Produkt AS Source
ON Target.ProduktID = Source.ProduktID AND Target.GueltigBis IS NULL -- um nur aktuelle Datensätze zu vergleichen
-- Wenn der Datensatz in der Ziel-Tabelle gefunden wird und Unterschiede aufweist, wird aktualisiert
WHEN MATCHED AND (
Target.ProduktName <> Source.ProduktName OR
Target.Beschreibung <> Source.Beschreibung OR
Target.MinMenge <> Source.MinMenge OR
Target.KategorieKey <> Source.KategorieID
) THEN
UPDATE SET
Target.GueltigBis = @CurrentDate
-- Wenn der Datensatz in der Ziel-Tabelle nicht gefunden wird, wird eingefügt
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProduktID, ProduktName, Beschreibung, MinMenge, KategorieKey, GueltigVon, GueltigBis)
VALUES (Source.ProduktID, Source.ProduktName, Source.Beschreibung, Source.MinMenge, Source.KategorieID, @CurrentDate, NULL)
-- Optional: Wenn der Datensatz in der Quell-Tabelle nicht gefunden wird, wird gelöscht/ deaktiviert
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET Target.GueltigBis = @CurrentDate;

-- Einfügen von aktualisierten Daten zurück als neue Versionen
INSERT INTO DimProdukt (ProduktID, ProduktName, Beschreibung, MinMenge, KategorieKey, GueltigVon, GueltigBis)
SELECT Source.ProduktID, Source.ProduktName, Source.Beschreibung, Source.MinMenge, Source.KategorieID, @CurrentDate, NULL
FROM DWH_Stage.dbo.ERP_Produkt AS Source
JOIN DimProdukt AS Target
ON Target.ProduktID = Source.ProduktID AND Target.GueltigBis = @CurrentDate;

DimKategorie ist genauso möglich:

USE DWH_Core
GO

DECLARE @CurrentDate AS DATETIME = GETDATE();
-- MERGE-Befehl zum Einfügen/ Aktualisieren von Daten in DimKategorie
MERGE INTO DimKategorie AS Target
USING DWH_Stage.dbo.ERP_Kategorie AS Source
ON Target.KategorieID = Source.KategorieID AND Target.GueltigBis IS NULL -- um nur aktuelle Datensätze zu vergleichen
-- Wenn der Datensatz in der Ziel-Tabelle gefunden wird und Unterschiede aufweist, wird aktualisiert
WHEN MATCHED AND (
Target.KategorieName <> Source.KategorieName OR
Target.Beschreibung <> Source.Beschreibung
) THEN
UPDATE SET
Target.GueltigBis = @CurrentDate
-- Wenn der Datensatz in der Ziel-Tabelle nicht gefunden wird, wird eingefügt
WHEN NOT MATCHED BY TARGET THEN
INSERT (KategorieID, KategorieName, Beschreibung, GueltigVon, GueltigBis)
VALUES (Source.KategorieID, Source.KategorieName, Source.Beschreibung, @CurrentDate, NULL)
-- Optional: Wenn der Datensatz in der Quell-Tabelle nicht gefunden wird, wird gelöscht/ deaktiviert
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET Target.GueltigBis = @CurrentDate;

-- Einfügen von aktualisierten Daten zurück als neue Versionen
INSERT INTO DimKategorie (KategorieID, KategorieName, Beschreibung, GueltigVon, GueltigBis)
SELECT Source.KategorieID, Source.KategorieName, Source.Beschreibung, @CurrentDate, NULL
FROM DWH_Stage.dbo.ERP_Kategorie AS Source
JOIN DimKategorie AS Target
ON Target.KategorieID = Source.KategorieID AND Target.GueltigBis = @CurrentDate;

machen wir weiter mit der DimLager:

USE DWH_Core
GO

DECLARE @CurrentDate AS DATETIME = GETDATE();

-- MERGE-Befehl zum Einfügen/ Aktualisieren von Daten in DimLager 
MERGE INTO DimLager AS Target
USING DWH_Stage.dbo.ERP_Lager AS Source
ON Target.LagerID = Source.LagerID AND Target.GueltigBis IS NULL -- um nur aktuelle Datensätze zu vergleichen
-- Wenn der Datensatz in der Ziel-Tabelle gefunden wird und Unterschiede aufweist, wird aktualisiert
WHEN MATCHED AND (
    Target.LagerName <> Source.LagerName OR
    Target.Adresse <> Source.Adresse OR
Target.Kapazität <> Source.Kapazität
) THEN
    UPDATE SET 
        Target.GueltigBis = @CurrentDate
-- Wenn der Datensatz in der Ziel-Tabelle nicht gefunden wird, wird eingefügt
WHEN NOT MATCHED BY TARGET THEN
    INSERT (LagerID, LagerName, Adresse, Kapazität, GueltigVon, GueltigBis)
    VALUES (Source.LagerID, Source.LagerName, Source.Adresse, Source.Kapazität, @CurrentDate, NULL)
-- Optional: Wenn der Datensatz in der Quell-Tabelle nicht gefunden wird, wird gelöscht/ deaktiviert
WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET Target.GueltigBis = @CurrentDate;

-- Einfügen von aktualisierten Daten zurück als neue Versionen
INSERT INTO DimLager (LagerID, LagerName, Adresse, Kapazität, GueltigVon, GueltigBis)
SELECT Source.LagerID, Source.LagerName, Source.Adresse, Source.Kapazität, @CurrentDate, NULL
FROM DWH_Stage.dbo.ERP_Lager AS Source
JOIN DimLager AS Target
ON Target.LagerID = Source.LagerID AND Target.GueltigBis = @CurrentDate;

und zum Schluss die DimLager:

USE DWH_Core
GO

DECLARE @CurrentDate AS DATETIME = GETDATE();

-- MERGE-Befehl zum Einfügen/ Aktualisieren von Daten in DimVersender
MERGE INTO DimVersender AS Target
USING DWH_Stage.dbo.ERP_Versender AS Source
ON Target.VersenderID = Source.VersenderID AND Target.GueltigBis IS NULL -- um nur aktuelle Datensätze zu vergleichen
-- Wenn der Datensatz in der Ziel-Tabelle gefunden wird und Unterschiede aufweist, wird aktualisiert
WHEN MATCHED AND (
Target.VersenderName <> Source.VersenderName OR
Target.Beschreibung <> Source.Beschreibung
) THEN
UPDATE SET
Target.GueltigBis = @CurrentDate
-- Wenn der Datensatz in der Ziel-Tabelle nicht gefunden wird, wird eingefügt
WHEN NOT MATCHED BY TARGET THEN
INSERT (VersenderID, VersenderName, Beschreibung, GueltigVon, GueltigBis)
VALUES (Source.VersenderID, Source.VersenderName, Source.Beschreibung, @CurrentDate, NULL)
-- Optional: Wenn der Datensatz in der Quell-Tabelle nicht gefunden wird, wird gelöscht/ deaktiviert
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET Target.GueltigBis = @CurrentDate;

-- Einfügen von aktualisierten Daten zurück als neue Versionen
INSERT INTO DimVersender (VersenderID, VersenderName, Beschreibung, GueltigVon, GueltigBis)
SELECT Source.VersenderID, Source.VersenderName, Source.Beschreibung, @CurrentDate, NULL
FROM DWH_Stage.dbo.ERP_Versender AS Source
JOIN DimVersender AS Target
ON Target.VersenderID = Source.VersenderID AND Target.GueltigBis = @CurrentDate;
3.2 Laden der Fakten Daten

Sichern Sie historische Daten, um Trends und Änderungen im Laufe der Zeit analysieren zu können.
Wir haben folgende Faktentabellen zu laden:

USE DWH_Core
GO

DECLARE @CurrentDate AS DATETIME = GETDATE();

-----FactBestellungAngelegt 
INSERT INTO DWH_Core.dbo.FactBestellungAngelegt (
    BestellungID,
    AnlegeZeitDateID,
    BenutzerKey,
    ProduktKey,
Bestelldatum,
BestellDateID,
    Menge,
  EPN_Preis,
EPB_Preis,
GPN_Summe,
GPB_Summe,
LLDateID,
ImportDatum
)
SELECT 
    stgBe.BestellungID,
    DimZeit.DateID,
    DimBen.BenutzerKey,
    DimProd.ProduktKey,
stgBe.Bestelldatum,
CONVERT(int, CONVERT(varchar(10),stgBe.Bestelldatum, 112)) ,
    stgBeDe.Menge,
CONVERT(decimal(10,2),(stgBeDe.Preis / 119 * 100  )) AS EPN_Preis,
stgBeDe.Preis AS EPB_Preis,
(CONVERT(decimal(10,2),(stgBeDe.Preis / 119 * 100  )) *stgBeDe.Menge) AS GPN_Summe,
(stgBeDe.Preis*stgBeDe.Menge) AS GPB_Summe,
CONVERT(int, CONVERT(varchar(10),@CurrentDate, 112)),
@CurrentDate
FROM 
    DWH_Stage.dbo.ERP_Bestellung stgBe
JOIN 
    DWH_Stage.dbo.ERP_BestellDetail stgBeDe ON stgBe.BestellungID = stgBeDe.BestellungID
JOIN 
    DWH_Core.dbo.DimDate DimZeit ON  CONVERT(int, CONVERT(varchar(10),stgBe.Bestelldatum, 112)) = DimZeit.DateID
JOIN
    DWH_Core.dbo.DimBenutzer DimBen ON stgBe.BenutzerID = DimBen.BenutzerID AND DimBen.GueltigBis IS NULL
JOIN
    DWH_Core.dbo.DimProdukt DimProd ON stgBeDe.ProduktID = DimProd.ProduktID AND DimProd.GueltigBis IS NULL
WHERE 
    NOT EXISTS (
        SELECT 1 
        FROM DWH_Core.dbo.FactBestellungAngelegt 
        WHERE BestellungID = stgBe.BestellungID
    ); 
USE DWH_Core
GO

DECLARE @CurrentDate AS DATETIME = GETDATE();

INSERT INTO DWH_Core.dbo.FactBestellungBearbeitet (
      BestellungKey
      ,Bestelldatum
      ,BeginnBearbeitung
      ,BeginnBearbeitungZeitDateID
      ,StatusID
      ,LLDateID
      ,ImportDatum
)
SELECT 
    DimBe.BestellungKey,
DimBe.Bestelldatum,
BeginnBearbeitungDatum AS BeginnBearbeitung,
    CONVERT(int, CONVERT(varchar(10),stgBe.BeginnBearbeitungDatum, 112)) AS BeginnBearbeitungZeitDateID,
Stat.StatusKey,
CONVERT(int, CONVERT(varchar(10),@CurrentDate, 112)) AS LLDateID,
@CurrentDate AS ImportDatum
FROM 
 
    DWH_Stage.dbo.ERP_Bestellung stgBe
JOIN
   DWH_Core.dbo.FactBestellungAngelegt DimBe ON stgBe.BestellungID = DimBe.BestellungID 
JOIN 
    DWH_Core.dbo.DimDate AS DimZeit ON  CONVERT(int, CONVERT(varchar(10),stgBe.BeginnBearbeitungDatum, 112))  = DimZeit.DateID
JOIN
    DWH_Core.dbo.DimBenutzer DimBen ON stgBe.BenutzerID = DimBen.BenutzerID AND DimBen.GueltigBis IS NULL
Join 
DWH_Core.dbo.DimStatus Stat ON stgBe.Status = Stat.Status AND Stat.GueltigBis IS NULL
WHERE 
    NOT EXISTS (
        SELECT 1 
        FROM DWH_Core.dbo.FactBestellungBearbeitet 
        WHERE BestellungKey = DimBe.BestellungKey
    )
    AND stgBe.BeginnBearbeitungDatum IS NOT NULL;
USE DWH_Core
GO

DECLARE @CurrentDate AS DATETIME = GETDATE();
INSERT INTO DWH_Core.dbo.FactBestellungVersendet (
BestellungKey
      ,VersandZeitDateID
      ,VersenderKey
      ,BeginnBearbeitung
      ,VersandZeit
      ,StatusID
      ,LLDateID
      ,ImportDatum
)
SELECT 
    DimBe.BestellungKey,
    DimZeit.DateID,
    DimVer.VersenderKey,
BeginnBearbeitungDatum,
Versanddatum,
Stat.StatusKey,
CONVERT(int, CONVERT(varchar(10),@CurrentDate, 112)) AS LLDateID,
@CurrentDate AS ImportDatum
FROM 
    DWH_Stage.dbo.ERP_Bestellung stgBe
JOIN
    DWH_Core.dbo.FactBestellungAngelegt DimBe ON stgBe.BestellungID = DimBe.BestellungID 
JOIN 
    DWH_Core.dbo.DimDate AS DimZeit ON CONVERT(int, CONVERT(varchar(10),stgBe.Versanddatum, 112))  = DimZeit.DateID
JOIN
    DWH_Core.dbo.DimVersender DimVer ON stgBe.VersenderID = DimVer.VersenderID AND DimVer.GueltigBis IS NULL 
JOIN 
DWH_Core.dbo.DimStatus Stat ON stgBe.Status = Stat.Status AND Stat.GueltigBis IS NULL


WHERE 
    NOT EXISTS (
        SELECT 1 
        FROM DWH_Core.dbo.FactBestellungVersendet 
        WHERE BestellungKey = DimBe.BestellungKey
    )
    AND stgBe.Versanddatum IS NOT NULL;
USE DWH_Core
GO


DECLARE @CurrentDate AS DATETIME = GETDATE();


INSERT INTO DWH_Core.dbo.FactBestellungGeliefert (
BestellungKey
      ,LieferZeitDateID
      ,VersandZeit
      ,LieferZeit
      ,VersenderKey
      ,LLDateID
      ,ImportDatum
)
SELECT 
    DimBe.BestellungKey,
    DimZeit.DateID,
    Versanddatum,
Lieferdatum,
DimVer.VersenderKey,
CONVERT(int, CONVERT(varchar(10),@CurrentDate, 112)) AS LLDateID,
@CurrentDate AS ImportDatum
FROM 
    DWH_Stage.dbo.ERP_Bestellung stgBe
JOIN
    DWH_Core.dbo.FactBestellungAngelegt DimBe ON stgBe.BestellungID = DimBe.BestellungID 
JOIN 
    DWH_Core.dbo.DimDate AS DimZeit ON CONVERT(int, CONVERT(varchar(10),stgBe.Lieferdatum, 112))  = DimZeit.DateID
JOIN
    DWH_Core.dbo.DimVersender DimVer ON stgBe.VersenderID = DimVer.VersenderID AND DimVer.GueltigBis IS NULL 
JOIN 
DWH_Core.dbo.DimStatus Stat ON stgBe.Status = Stat.Status AND Stat.GueltigBis IS NULL


WHERE 
    NOT EXISTS (
        SELECT 1 
        FROM DWH_Core.dbo.FactBestellungGeliefert 
        WHERE BestellungKey = DimBe.BestellungKey
    )
    AND stgBe.Lieferdatum IS NOT NULL;

Die Bestandstabelle ist wieder etwas anders, hier laden wir pro Tag einmal die Preise und den Bestand pro Lager in die Faktentabelle

USE DWH_Core
GO


DECLARE @CurrentDate AS DATETIME = GETDATE();


--- Löschen für disen tag, falls unsere Bestände schon einmal gelaufen sind.
DELETE FROM FactLagerbestand WHERE  DaumID = convert(int,convert(nvarchar(8),getdate()-1,112));


--- Laden der Bestände aus der Stage  
INSERT INTO FactLagerbestand (ProduktKey, LagerKey, Datum, DaumID, Menge, Preis, LLDateID, ImportDatum)
SELECT 
    P.ProduktKey,
    L.LagerKey,
    getdate()-1,
convert(int,convert(nvarchar(8),getdate()-1,112)), 
    E.Lagerbestand,
    PP.Preis,
    CONVERT(int, CONVERT(varchar(10),@CurrentDate, 112)) AS LLDateID,
    @CurrentDate AS ImportDatum
FROM 
    [DWH_Stage].[dbo].[ERP_Lagerbestand] E
JOIN 
    DimProdukt P ON E.ProduktID = P.ProduktID AND P.GueltigBis IS NULL
JOIN 
    [DWH_Stage].[dbo].[ERP_Produkt] PP ON E.ProduktID = PP.ProduktID 
JOIN 
    DimLager L ON E.LagerID= L.LagerID AND L.GueltigBis IS NULL
3.3 Implementierung des Delta-Ladeprozesses

Erstellen Sie Prozesse, um nur geänderte Daten seit dem letzten ETL-Lauf in die Core-Schicht zu laden.

Hier habe ich ich einen Agent Job erstellt, der diese ganzen Dimensionen und Fakten Tabellen befüllt. Der Job läuft jede Nacht um 0:15 Uhr los.

Download des Scripts ETL-Stage-to-Core.sql

Phase 4: Datenpräsentation und -analyse

4.1 Implementierung der Mart-Schicht

Entwickeln Sie Data Marts, die spezifische Geschäftsbereiche adressieren und das Reporting erleichtern.

4.2 Entwicklung von Berichten und Dashboards

Nutzen Sie BI-Tools, um aussagekräftige Berichte und Dashboards zu erstellen, die auf den Daten in Ihren Data Marts basieren.

Schlussfolgerung

Mit dem Abschluss dieser ersten Ausgabe haben wir ein solides Fundament für unser Data Warehouse geschaffen, das uns ermöglicht, Daten effektiv von unseren Quellsystemen zu extrahieren, zu transformieren und in einer strukturierten und konsistenten Form zu speichern. In den kommenden Ausgaben werden wir diesen Ansatz weiter ausbauen und verfeinern, um zusätzliche Anforderungen und Komplexitäten zu adressieren.

Bleiben Sie dran, während wir in den nächsten Ausgaben unsere Reise fortsetzen und in die Welt der untertägigen und Real-Time Data Warehousing eintauchen.