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

Generierung von Testdaten auf dem SQL-Server

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:

<span style="color: #3366ff;">
DECLARE</span> @Numbers <span style="color: #3366ff;">TABLE</span> (
    Nr <span style="color: #3366ff;">INT</span>
)

;<span style="color: #3366ff;">WITH</span> x <span style="color: #3366ff;">AS</span> (
       <span style="color: #3366ff;">SELECT</span>
             n
       <span style="color: #3366ff;">FROM</span>
             (
                    <span style="color: #3366ff;">VALUES</span> (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)
             ) v (n)
)
<span style="color: #3366ff;">INSERT INTO</span> @Numbers
<span style="color: #3366ff;">SELECT</span>
              ones.n +
       10*    tens.n +
       100*   hundreds.n  AS Nr
<span style="color: #3366ff;">FROM</span>
       x ones,    
       x tens,
    x hundreds    
<span style="color: #3366ff;">WHERE</span>
              ones.n +
       10*    tens.n +
       100*   hundreds.n &lt;= 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.

<span style="color: #3366ff;">
CASE</span> 
  <span style="color: #3366ff;">WHEN</span> MonthModulo &lt; 8 <span style="color: #3366ff;">THEN</span> 1 
  <span style="color: #3366ff;">WHEN</span> MonthModulo &gt;= 8 AND MonthModulo &lt; 14 <span style="color: #3366ff;">THEN</span> 2 
  <span style="color: #3366ff;">WHEN</span> MonthModulo &gt;= 14 AND MonthModulo &lt; 19 <span style="color: #3366ff;">THEN</span> 3 
  <span style="color: #3366ff;">WHEN</span> MonthModulo &gt;= 19 AND MonthModulo &lt; 31 <span style="color: #3366ff;">THEN</span> 4 
  <span style="color: #3366ff;">WHEN</span> MonthModulo &gt;= 31 AND MonthModulo &lt; 36 <span style="color: #3366ff;">THEN</span> 5 
  <span style="color: #3366ff;">WHEN</span> MonthModulo &gt;= 36 AND MonthModulo &lt; 39 <span style="color: #3366ff;">THEN</span> 6 
  <span style="color: #3366ff;">WHEN</span> MonthModulo &gt;= 39 AND MonthModulo &lt; 42 <span style="color: #3366ff;">THEN</span> 7 
  <span style="color: #3366ff;">WHEN</span> MonthModulo &gt;= 42 AND MonthModulo &lt; 50 <span style="color: #3366ff;">THEN</span> 8 
  <span style="color: #3366ff;">WHEN</span> MonthModulo &gt;= 50 AND MonthModulo &lt; 65 <span style="color: #3366ff;">THEN</span> 9 
  <span style="color: #3366ff;">WHEN</span> MonthModulo &gt;= 65 AND MonthModulo &lt; 76 <span style="color: #3366ff;">THEN</span> 10 
  <span style="color: #3366ff;">WHEN</span> MonthModulo &gt;= 76 AND MonthModulo &lt; 87 <span style="color: #3366ff;">THEN</span> 11 
  <span style="color: #3366ff;">WHEN</span> MonthModulo &gt;= 87 THEN 12
<span style="color: #3366ff;">END</span>    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.

<span style="color: #3366ff;">
SELECT TOP</span> 1
    @TimeID = TimeID
<span style="color: #3366ff;">FROM
    Time
WHERE</span>
        MonthNumber = @MonthNumber
    AND [Year] = @Year
       AND WeekdayName != 'Sonntag'
<span style="color: #3366ff;">ORDER BY</span> 
    <span style="color: #ff00ff;">NEWID</span>()

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.

Rastislav Paluv

Rastislav Paluv ist Senior BI Consultant und berät Kunden seit über acht Jahren im Bereich Datenstruktur und -analyse. Er ist zertifiziert im Bereich Microsoft SQL Server und hat ein breites Know-How im Software Engineering.

Ähnliche Blogartikel

Die folgenden Beiträge zum Thema könnten Sie auch interessieren:

Kommentar verfassen

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