Plus IT > Ressource Center > Blog > Generierung von Testdaten auf dem SQL-Server

Generierung von Testdaten auf dem SQL-Server

Testdaten generieren

Um die Qualität einer Software zu testen und zu verbessern oder um z.B. im Bereich von Dashboards und Reports überhaupt etwas plausibles anzeigen zu können, benötigt man Testdaten.

Heute möchte ich die Funktionen und Konstrukte des SQL-Servers vorstellen, die sich hervorragend zur Generierung von Testdaten eignen.

Ein einfaches Konstrukt kann z.B. folgendermaßen aussehen:


DECLARE @Numbers TABLE (
	Nr INT
)

;WITH x AS (
       SELECT
             n
       FROM
             (
                    VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)
             ) v (n)
)
INSERT INTO @Numbers
SELECT
              ones.n +
       10*    tens.n +
       100*   hundreds.n  AS Nr
FROM
       x ones,    
       x tens,
	x hundreds    
WHERE
              ones.n +
       10*    tens.n +
       100*   hundreds.n <= 999

 

Es handelt sich dabei um eine Tabellenvariable gefüllt mit den Zahlen von 0 bis 999. Das Konstrukt beginnt mit der Deklaration der Zieltabelle in Form einer Tabellenvariable. WITH leitet eine sogenannte Common Table Expression (CTE) ein. Sie beinhaltet einen Ausdruck, der mit dem Alias x später im Statement referenzierbar ist. Der Ausdruck gibt eine Spalte mit 10 Zeilen (Werte von 0 bis 9) zurück. Im FROM-Teil der Abfrage erzeugt man sich die impliziten CROSS-JOINs (jeder Eintrag einer Tabelle ge-joint mit jedem Eintrag aller anderen Tabellen (vulgo. Kreuzprodukt)), die bei der Komma-Separierung von Tabellen oder – wie in diesem Fall – Tabellen-Ausdrücken entstehen. Für jede Spalte wird der Tabellen-Ausdruck einmal hinzugefügt. Der SELECT-Teil fasst nun alles zu einer Spalte zusammen. Damit jede Zahl an der richtigen Position landet, müssen die Werte entsprechend multipliziert werden.

Die Tabellenvariable steht nun zur Verfügung, um die Menge der Datensätze zu bestimmen, die zur Befüllung einer konkreten Tabelle herangezogen werden kann. Allerdings muss man sich für den SELECT-Teil Gedanken über die Inhalte machen.

Im Datawarehouse-Umfeld sind die Daten in den Dimensionen immer wieder sehr leicht zu bestimmen. Es kommt dann nur auf eine plausible Verteilung in den Fakten an. Hier seien z.B. Zeit, Länder, Kundenkategorien etc. genannt.

Das nächste Konstrukt besteht aus zwei Teilen, wobei die Funktionen des ersten Teils für sich schon genutzt werden können, wenn eine Gleichverteilung gewünscht ist.

CROSS JOIN (SELECT ABS(CHECKSUM(NewId())) % 100 MonthModulo) a

Die Funktion NewId() erzeugt eine zufällige GUID, die sich über CHECKSUM() zu einem Integer mit negativen Werten umwandeln lässt. Da hier nur positive Werte benötigt werden, wird mit Hilfe von ABS()  der absolute Wert erzeugt. % 100 auf das Ergebnis der drei Funktionen angewandt, liefert nun Werte zwischen 0 und 99.


CASE 
  WHEN MonthModulo < 8 THEN 1 
  WHEN MonthModulo >= 8 AND MonthModulo < 14 THEN 2 
  WHEN MonthModulo >= 14 AND MonthModulo < 19 THEN 3 
  WHEN MonthModulo >= 19 AND MonthModulo < 31 THEN 4 
  WHEN MonthModulo >= 31 AND MonthModulo < 36 THEN 5 
  WHEN MonthModulo >= 36 AND MonthModulo < 39 THEN 6 
  WHEN MonthModulo >= 39 AND MonthModulo < 42 THEN 7 
  WHEN MonthModulo >= 42 AND MonthModulo < 50 THEN 8 
  WHEN MonthModulo >= 50 AND MonthModulo < 65 THEN 9 
  WHEN MonthModulo >= 65 AND MonthModulo < 76 THEN 10 
  WHEN MonthModulo >= 76 AND MonthModulo < 87 THEN 11 
  WHEN MonthModulo >= 87 THEN 12
END	MonthNumber

 

Der zweite Teil des Konstrukts besteht aus einer CASE-Anweisung, in der der Modulo-Wert geprüft wird und daraufhin eine bestimmte Zuweisung eines Werts für die Spalte vorgenommen wird. Wie an der Wahl der Aliase leicht zu erkennen ist, geht es in diesem Fall um die Bestimmung des Monats, wobei bestimmte Monate in den Fakten stärker repräsentiert sein sollen als andere.

Das letzte Konstrukt, auf welches hier eingegangen werden soll, sei der Vollständigkeit halber erwähnt. Es funktioniert nur für kleinere Datenmengen und erfordert die Verwendung einer Schleife. Letzteres möchte man in der Regel aus Gründen der Performance lieber vermeiden. Aber angenommen man hat eine Tabelle mit Daten, die man zur Generierung der Testdaten heranziehen möchte, dann kann mit folgendem Konstrukt einen zufälligen Eintrag aus dieser Tabelle bestimmen.


SELECT TOP 1
	@TimeID = TimeID
FROM
	Time
WHERE
	    MonthNumber = @MonthNumber
	AND [Year] = @Year
       AND WeekdayName != 'Sonntag'
ORDER BY 
	NEWID()

 

Wie an dem Snippet zu erkennen ist, wird aus der Dimension Time zufällig eine TimeID und damit ein Tag in Abhängigkeit eines Monats bzw. Jahres mit dem Ausschluss von Sonntagen selektiert. Es wäre auch möglich die TimeID anders zu bestimmen, allerdings müsste dann innerhalb einer CASE-Anweisung die unterschiedliche Anzahl der Tage eines Monats bzw. die Erkennung von Sonntagen dort abgehandelt werden.

Alles in Allem ist es mit den Bordmitteln des SQL-Servers relativ leicht, Testdaten zu generieren. Es ist dabei wichtig, sich Gedanken über die plausible Verteilung bei der Ausprägung von Merkmalen zu machen, damit man einigermaßen aussagekräftige Daten bekommt.

mm

Stefan Berends

Stefan Berends ist Geschäftsführer der plus-IT und berät seit fast 20 Jahren Kunden im BI Bereich. Er ist Autor und Experte für SAP Crystal Reports und verfügt über langjährige Expertise im Bereich Dashboarding und Reporting sowie Machbarkeitsstudien und Tool-Empfehlungen.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.