Ein Wasserfall-Diagramm per R erzeugen und in Reporting Services 2016 einbetten

Leider bieten die Reporting Services 2016 zwar zwei neue Diagramme (siehe den Artikel „Was gibt’s Neues bei den Reporting Services„), das bei vielen Kunden so beliebte Wasserfall-Diagramm ist allerdings leider nicht dabei. Es gibt natürlich schon einige Methoden ein Wasserfall-Diagramm mit Hilfe der Reporting Services darzustellen. Eine dieser Methoden hat mein geschätzter Microsoft-Kollege / P-TSP Manager Volker Hinz hier demonstriert. Volker hat sich für einen Weg über Expressions und Calculated Fields entschieden. Es gibt andere Lösungen, bei denen das Wasserfall Diagramm über VB-Code erzeugt wurde oder sogar Implementierungen die Wasserfall-Diagramme als eigene Diagrammtypen in Reporting Services implementiert haben.

In diesem Artikel möchte ich eine weitere Möglichkeit demonstrieren, die uns jetzt mit dem SQL Server 2016 zur Verfügung steht. Im SQL Server 2016 kann man ja innerhalb von T-SQL R-Code ausführen lassen. Mit diesem R-Code kann man natürlich auch über die Bibliothek ggplot2 Diagramme erstellen die dann wiederum in Reporting Services 2016 Berichte eingebettet werden können. In diesem Blog-Artikel möchte ich demonstrieren wie man da vorgehen kann. Zunächst einmal benötigen wir R Code, der ein Wasserfall-Diagramm baut. Ich habe mir den Code nicht selbst ausgedacht sondern hier einen spannenden Artikel im Blog Learning R gefunden, in dem genau beschrieben steht wie ein Wasserfall-Diagramm in R erzeugt wird. Den Code erkläre ich noch einmal recht kurz. Zunächst erzeugen wir einfach ein Dataset das wir balance nennen und dem wir ein paar Werte zuweisen die dann im Wasserfall Diagramm angezeigt werden sollen.

balance <- data.frame(desc = c("Starting Cash",
    "Sales", "Refunds", "Payouts", "Court Losses",
    "Court Wins", "Contracts", "End Cash"), amount = c(2000,
     3400, -1100, -100, -6600, 3800, 1400, 2800))

Wenn wir einfach mal das Dataset balance über

balance

ausgeben lassen können wir sehen wie das Dataset momentan aussieht (um den R-Code zu schreiben und zu debuggen nutze ich die R-Tools für Visual Studio)

R-Waterfall-1

damit wir die Reihenfolge der einzelnen Balken im Dataset bestimmen können wird die Spalte desc in einen Faktor umgewandelt außerdem werden noch die beiden Spalten id und type hinzugefügt:

balance$desc <- factor(balance$desc, levels = balance$desc)
balance$id <- seq_along(balance$amount)
balance$type <- ifelse(balance$amount > 0, "in", "out")
balance[balance$desc %in% c("Starting Cash", "End Cash"), "type"] <- "net"

Auch hier können wir uns die Datenmenge wieder über

balance

ausgeben lassen. Im Screenshot sieht man schön die Änderungen an der Datenmenge:

R-Waterfall-2

Als letzten Schritt müssen wir auf Basis des Feldes Amount berechnen welchen Start- und Endpunkt die Daten der jeweiligen Wasserfall-Balken haben. Dazu dient der folgende Code:

balance$end <- cumsum(balance$amount)
balance$end <- c(head(balance$end, -1), 0)
balance$start <- c(0, head(balance$end, -1))
balance <- balance[, c(3, 1, 4, 6, 5, 2)]

Wieder geben wir die Datenmenge über

balance

aus. Nun sind die Feldr start und end hinzugekommen, die wir dann gleich in unserem Plot verwenden werden.

R-Waterfall-3

Die Datenmenge ist nun soweit vorbereitet, dass wir über die ggplot2 Bibliothek das Wasserfall-Diagramm plotten können. Hierzu müssen wir die Bibliothek zunächst über

library(ggplot2)

laden. Voraussetzung hierfür ist, dass die Bibliothek bereits auf dem Rechner installiert wurde. Ist das bisher nicht der Fall, so müssen wir die Bibliothek über

install.packages("ggplot2")

Installieren. Danach können wir sie einfach mit dem Befehl oben laden. Zum Schluss müssen wir unsere vorbereitete Datenmenge nur noch an ggplot übergeben und spezifizieren wie die Daten dargestellt werden sollen. Das machen wir mit dem folgenden Befehl:

ggplot(balance, aes(desc, fill = type)) + geom_rect(aes(x = desc,
xmin = id - 0.45, xmax = id + 0.45, ymin = end,
ymax = start))

Auf Basis der oben angegebenen Daten wird nun das folgende Wasserfall-Diagramm erzeugt:

R-Waterfall-4

Damit sind wir mit dem R-Teil durch. Nun müssen wir das Diagramm in die SQL Server 2016 Reporting Services einbinden. Wichtig damit man R im SQL Server benutzen kann ist, dass man die R-Services installiert hat, die innerhalb des SQL-Servers laufen. Diese kann man direkt bei der Installation des SQL Servers auswählen oder im Nachhinein nachinstallieren. Nachdem die R-Services installiert wurden muss man sie noch innerhalb des SQL Servers aktivieren. Dazu dienen die folgenden beiden Zeilen T-SQL Code:

EXEC sp_configure  'external scripts enabled', 1  
Reconfigure  with  override

Die Eingabe des Befehls wird mit einer entsprechenden Meldung vom SQL Server quittiert

R-Waterfall-5

Wichtig ist, dass man nach der Ausführung des Befehls den SQL Server einmal neu startet, da Einstellung ansonsten keinen Effekt hat:

R-Waterfall-6

Nachdem der SQL Server wieder da ist kann man über

Exec sp_configure  'external scripts enabled'

Prüfen ob die Änderung erfolgreich übernommen wurde.

R-Waterfall-7

Wird wie auf meinem Beispiel-Server bei run_value der Wert 1 angezeigt, so ist sichergestellt, dass R jetzt innerhalb vom SQL Server verwendet werden kann. Natürlich können wir über ein kleines R-Script prüfen ob man wirklich R innerhalb von T-SQL ausführen kann:

exec sp_execute_external_script  @language =N'R',  
@script=N'OutputDataSet<-InputDataSet',    
@input_data_1 =N'select 1 as hello'  
with result sets (([hello] int not null));  
go

Im Beispiel klappt das soweit und die folgende Ausgabe wird angezeigt

R-Waterfall-8

Damit ist sichergestellt, dass wir nun auch wirklich R-Scripte ausführen können. Eine genauere Anleitung wie man R innerhalb vom SQL Server 2016 konfiguriert kann man im MSDN hier finden.

Bevor wir unseren Plot mit ggplot2 zeichnen können müssen wir zunächst diese Biblothek auf dem R-Server installieren. Nur dann steht sie auch zur Verfügung (siehe oben). Auf einem SQL-Server mit installierten R-Services (das ist der Teil der über T-SQL angesprochen wird) muss man dazu in einer Kommandozeile das Verzeichnis „C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\bin\x64“ gehen wo die R-Services standardmäßig installiert werden und dann dort den Befehl „R.exe“ ausführen. Hier kann man dann wie gewohnt Bibliotheken installieren die dann aus dem Internet heruntergeladen werden. In unserem Beispiel nutzen wir dafür den Befehl

install.packages("ggplot2")

R-Waterfall-10

Hier gibt es im MSDN einen guten Artikel der die Installation von R-Packages auf dem SQL Server 2016 noch mal in aller Tiefe erklärt. Was man vielleicht noch für Systeme erwähnen sollte auf denen sowohl die R-Services als auch der R-Server installiert ist, ist dass die Pakete exklusiv für den Server oder die Services installiert werden. Möchte man ein Paket sowohl unter dem R-Server als auch unter den R-Services nutzen muss es unter beiden installiert werden.

Nun müssen wir unser R-Script ein wenig modifizieren, damit es auch in T-SQL genutzt werden kann. Damit wir die Daten nicht fest in unser Script einbauen müssen habe ich eine T-SQL Abfrage geschrieben, die die Daten entsprechend zurückliefert. Um die Dinge einfach zu halten ist auch diese Datenmenge statisch (und entspricht der Datenmenge oben aus dem Beispiel), sie ist aber nicht in R sondern in SQL geschrieben:

SELECT
    'Starting Cash' AS [desc]
  , 2000 AS amount
UNION ALL
SELECT
    'Sales'
  , 3400
UNION ALL
SELECT
    'Refunds'
  , -1100
UNION ALL
SELECT
    'Payouts'
  , -100
UNION ALL
SELECT
    'Court Losses'
  , -6600
UNION ALL
SELECT
   'Court Wins'
  , 3800
UNION ALL
SELECT
   'Contracts'
  , 1400
UNION ALL
SELECT
   'End Cash'
  , 2800;

Hier noch mal die Datenmenge als SQL-Dataset:

R-Waterfall-9

Um das oben beschriebene Wasserfall-Diagramm über R in Reporting Services zeichnen zu lassen habe ich eine Stored Procedure geschrieben die wiederum die Stored Procedure sp_execute_script aufruft mit der man R-Scripte innerhalb vom SQL-Server ablaufen lassen kann. Hier ist der Quelltext der Stored Procedure.

CREATE PROCEDURE [dbo].[plotWaterfall]
AS
BEGIN
    EXEC sp_execute_external_script
        @language = N'R'
      , @script = N'balance<-InputDataSet;
                    balance$desc <- factor(balance$desc, levels = balance$desc);
                    balance$id <- seq_along(balance$amount);
                    balance$type <- ifelse(balance$amount > 0, "in", "out");
                    balance[balance$desc %in% c("Starting Cash", "End Cash"), "type"] <- "net";
                    balance$end <- cumsum(balance$amount);
                    balance$end <- c(head(balance$end, -1), 0);
                    balance$start <- c(0, head(balance$end, -1));
                    balance <- balance[, c(3, 1, 4, 6, 5, 2)];
                    image_file = tempfile();
                    dev.new();
                    jpeg(filename = image_file, width=500, height=500);
                    library(ggplot2);
                    print(ggplot(balance, aes(desc, fill = type)) + geom_rect(aes(x = desc,xmin = id - 0.45, xmax = id + 0.45, ymin = end,ymax = start)));
                    dev.off();
                    OutputDataSet <- data.frame(data=readBin(file(image_file,"rb"),what=raw(),n=1e6));'
      , @input_data_1 = N'SELECT
                            ''Starting Cash'' AS [desc]
                          , 2000 AS amount
                        UNION ALL
                        SELECT
                            ''Sales''
                          , 3400
                        UNION ALL
                        SELECT
                            ''Refunds''
                          , -1100
                        UNION ALL
                        SELECT
                            ''Payouts''
                          , -100
                        UNION ALL
                        SELECT
                            ''Court Losses''
                          , -6600
                        UNION ALL
                        SELECT
                            ''Court Wins''
                          , 3800
                        UNION ALL
                        SELECT
                            ''Contracts''
                          , 1400
                        UNION ALL
                        SELECT
                            ''End Cash''
                          , 2800;'   
END
GO

Die Stored Procedure sp_execute_external_script bekommt die Parameter @language (in unserem Fall R), @script (das eigentliche Script) und @input_data_1 (die Datenmenge die als Eingabe an das R-Script übergeben wird) übergeben. Das R-Script selbst funktioniert wie oben beschrieben, der einzige Unterschied zum Ausführen des Scriptes unter Visual Studio besteht darin, dass wir über

image_file = tempfile();
dev.new();
jpeg(filename = image_file, width=500, height=500);

eine neue, temporäre JPEG-Datei anlegen. Über den Befehl

print(ggplot(balance, aes(desc, fill = type)) + geom_rect(aes(x = desc,xmin = id - 0.45, xmax = id + 0.45, ymin = end,ymax = start)));

Wird die ggplot-Grafik dann in diie Datei geschrieben und über

dev.off();

Wird die Datei dann wieder geschlossen. Interessant ist auch der Befehl

OutputDataSet <- data.frame(data=readBin(file(image_file,"rb"),what=raw(),n=1e6));'

Dieser liest die temporäre JPEG-Datei wieder ein und schreibt sie in das Output Dataset, d.h. die JPEG Datei wird als Binärstrom von der Stored Procedure zurückgeliefert.

R-waterfall-20

Diesen Binärstrom bzw. das darin enthaltene Bild werden wir dann im nächsten Schritt an ein Image in Reporting Services binden. Dazu erstelle ich einen neuen Reporting Services Bericht und füge in diesen eine neues freigegebenes Dataset ein. Im Dataset rufe ich dann die Prozedur plotWaterfall auf.

R-waterfall-21

Nun füge ich im Bericht ein Bild ein und parametriere es wie folgt:

R-waterfall-22

Wichtig ist, dass wir als Quelle des Bildes „Database“ angeben. Als Feld dient uns das Ergebnis der Stored Procedure. Diese kann nur eine einen einzigen Datensatz zurückliefern. Da wir an dieser Stelle aber garantieren müssen dass nur ein Datensatz kommt, müssen wir mit der Aggregat-Funktion First() arbeiten. Wichtig ist auch, dass wir den MIME-Typ des Bildes richtig angeben. Da wir in unserem R-Script ein JPEG-Bild erstellen müssen wir hier als MIME-Type „image/jpeg“ angeben. Grundsätzlich können wir das temporäre Bild in R natürlich auch in anderen Formaten ausgeben, wie beispielsweise PNG, dann muss aber der MIME-Type hier entsprechend eingestellt werden. Nachdem wir das Bild konfiguriert haben können wir den Bericht nun ausführen lassen. Und tatsächlich wird uns nun unter Reporting Services ein Wasserfall-Diagramm angezeigt:

R-waterfall-23

Damit sind die technischen Hürden ein R-Wasserfall Diagramm (bzw. irgendein R-Diagramm) unter Reporting Services anzeigen zu lassen soweit behoben. Natürlich ist das ganze noch nicht wirklich für den Praxiseinsatz geeignet, da die Daten die im Wasserfall-Diagramm angezeigt werden recht statisch sind und ggf. auch im Diagramm selbst noch die ein oder andere Feinheit gerändert werden müsste. Da aber sowohl die Datenquelle für das R-Script wie auch das R.Script selbst nichts anderes als Zeichenketten im SQL Server sind, ist es einfach möglich die Stored Procedure mit Parametern zu erweitern und dann anhand dieser Parameter die Datenquelle und auch das R-Script selbst zusammenzubauen (Dynamic R in SQL sozusagen).

Schreibe einen Kommentar

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

%d Bloggern gefällt das: