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:


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.

Analysis Services auf dem SQL-Server: Full Process vs Process Update

Heute möchte ich mal wieder aus dem Nähkästchen plaudern und eine aktuelle Herausforderung aus dem Bereich Analysis Services auf dem SQL-Server (SSAS) erläutern. Es wird also wieder technisch.

Das Thema multidimensionale Datenbanken haben wir ja bereits im Detail behandelt. Dabei gibt es stets die Aufteilung in einen Cube, der die eigentlichen Daten beinhaltet und die beschreibenden Dimensionen darum herum.
Wenn eine Dimension überdimensional groß ist, dauert ein Update oder eine Veränderung entsprechend lange. Dabei gibt es verschiedene Verarbeitungsmethoden. Beim Full Process wird immer alles verarbeitet, egal was mit den Daten passiert ist und was aktualisiert wurde. Bei einem Process Update wird hingegen geprüft, was geändert oder zugefügt wurde und nur das wird aktualisiert.

In einem aktuellen Kundenprojekt haben wir den Fall, dass wir einen sehr großen Cube mit zum Teil auch überdurchschnittlich großen Dimensionen haben. Bisher haben wir zur Aktualisierung der Dimensionen die Methode Process Update genutzt, in der Erwartung, dass es die schnellere Methode ist.
Für den Cube ist das Processing so gestaltet, dass immer nur die Partitionen verarbeitet werden, welche vorab über eine Anwendung definiert wurden (auf Monatsebene). Somit werden nur die Daten aktualisiert, welche geändert oder neu hinzugekommen sind. Alle bestehenden Daten außerhalb dieser Partition bleiben unberührt.

Aktuell kam jedoch die Diskussion auf, ob die Performance für das Verarbeiten der Dimensionen so tatsächlich besser ist, oder ein Full Process wider Erwarten schneller sein kann. Daher haben wir die Methode umgestellt und Full Process eingeführt. Die Umstellung in der Dimension brachte jedoch den unschönen Nebeneffekt mit sich, dass im Cube nur noch die aktuell zu verarbeitende Partition verfügbar war. Alle anderen (historischen Partitionen) waren nicht mehr verfügbar bzw. unprocessed. Wenn man das ganze einmal genauer betrachtet, ist dieser Fall auch durchaus logisch. Dadurch dass sich die Dimension insgesamt verändert hat, muss theoretisch auch jede (historische) Partition neu verarbeitet werden, um die Veränderungen der Dimension mitzubekommen.

Unsere „Lesson learned“ ist also, dass man sich vorab sehr genau überlegen sollte, welche Änderungen man an einem Cube vornimmt. Vor der Umsetzung der vermeintlichen Verbesserung sollte immer geprüft werden, in wie fern hier Auswirkungen auf den Cube bzw. dessen Verfügbarkeit entstehen.
Nachdem die Process-Methode der Dimension wieder zurück auf Process Update geändert wurde, funktionierte wieder alles problemlos.

Datenzugang beschränken bei Analysis Services auf dem SQL-Server

Heute möchte ich ein aktuelles Beispiel aus einem Kundenprojekt vorstellen, da sicher einige Analysten vor einer ähnlichen Herausforderung stehen. Dazu entführe ich Sie kurz in die technischen Abgründe der Analysis Services auf dem Microsoft SQL-Server, genauer gesagt in die Row-Level-Security.

Im laufenden Projekt ergab sich die Anforderung, dass Daten für bestimmte User erst ab einem bestimmten Zeitpunkt im Cube sichtbar sein dürfen, nämlich wenn sie explizit freigegeben sind. Eine definierte Gruppe (z.B. beteiligte Analysten und Datenlieferenten) soll die Daten zuerst sehen und prüfen können, bevor diese z.B. auch von der Geschäftsführung und dem Management einsehbar sind. So wird verhindert, dass Verwirrungen entstehen oder falsche Entscheidungen getroffen werden.

So weit so gut, ich habe – wie schon in anderen Projekten auch – alles vorbereitet, um die Row-Level-Security für den Cube zu implementieren.
Nachdem die Vorbereitungen auf der relationalen Ebene abgeschlossen waren, bin ich im Cube hergegangen und habe die entsprechenden Rollen angelegt. Eine Superuser-Rolle, deren Mitglieder alles sehen darf und die User Rolle, welche nur die speziellen Daten sehen darf.
Die Superuser-Rolle war schnell erledigt, da diese gänzlich ohne Row-Level-Securtiy auskommt.

Als ich dann die User-Rolle angelegt hatte und die entsprechenden Attribute mit den MDX-Statements zur Securtiy versehen hatte, bekam ich beim Browsen im Cube die Fehlermeldung: „Das Attribut ‘XXX’ in der Dimension ‘YYY’ hat einen generierten Dimensionssicherheitsausdruck, der nicht gültig ist.“ (The ‘XXX’ attribute in the ‘YYY’ dimension has a generated dimension security expression that is not valid).

Nach einiger Recherche und Analyse stellte ich fest, dass die Security nicht in der Cube-Dimension, sondern auf der Datenbankdimension hinterlegt war. Die Datenbankdimensionen werden in der Darstellung zuerst angezeigt, anschließend erst die Cube Dimensionen, was mir hier zum „Verhängnis“ wurde.
Die Cube Dimension ist eine Instanz der Datenbank Dimension in einem speziellen Cube. Die Datenbank Dimension kann in mehreren Cubes oder in einem Cube mehrfach (Role-Playing Dimension) verwendet werden.

Die Darstellung in Visual Studio ist also etwas ungünstig. Teilweise hat man so viele Dimensionen in Einsatz, dass die Darstellung unübersichtlich wird und die zuunterst dargestellten leicht übersehen werden können.
Es gilt unbedingt zu beachten, die Row-Level-Security immer auf Cube-Ebene und nicht auf Datenbankebene zu setzen.

Es handelt sich hierbei zwar um ein recht triviales Problem, aber wie sooft ist die Fehlermeldung nicht sonderlich hilfreich, sodass man nicht sofort darauf kommt, dass die Sicherheit auf der falschen Ebene gesetzt wurde.