Power BI 2.0 im Detail: R Integration

Dass das letzte Release von Power BI unter anderem neue Konnektoren bereitstellt, klingt zunächst einmal nicht sonderlich spektakulär. Michael Koch und Sebastian Müller haben Pionierarbeit geleistet und untersucht, welche Möglichkeiten die neue Anbindung an die Datenquelle „R Skript“ eröffnet – und sind zu dem Schluss gekommen, dass es sich bei dieser Option sehr wohl um eine sehr wertvolle Ergänzung des bestehenden Leistungsangebots handelt. Wie kann man von dem neuen Feature profitieren? Und was ist zu tun, um die eigenen Daten mittels R-Skript statistischen Analysen zu unterziehen? Schritt für Schritt erklären die beiden Power BI-Experten, wie man zu aussagekräftigen statistischen Ergebnisse gelangt und neue Einblicke in das eigene Geschäft gewinnt.

Das November-Update für Microsoft Power BI brachte einige Neuerungen mit sich: Neben erweiterten Darstellungsmöglichkeiten und Analyse-Funktionen wurden auch neue Konnektoren veröffentlicht. In diesem Blog-Beitrag wird die neue Datenquelle „R Skript“ vorgestellt. Dieser Konnektor ermöglicht es, statistische Auswertungen und Analysen mittels der Programmiersprache R durchzuführen und die Ergebnisse anschließend in Power BI zu integrieren. Besonderes Augenmerk liegt dabei auf der Verarbeitung der eigenen, in Power BI eingebundenen Daten mit Hilfe eines R-Skriptes. 

 

Was ist R und was bringt es in Power BI?

Die statistische Programmiersprache R bietet eine große Bandbreite an analytischen und prädiktiven Auswertungsmöglichkeiten und kombiniert die Aspekte der funktionalen und objektorientierten Programmierung. Neben den bereits (frei) mitgelieferten Funktionspaketen kann die Programmiersprache R um weitere Pakete ergänzt werden.

Die Ausgabe der statistischen Ergebnisse erfolgt in Form von Grafiken oder Tabellen – sogenannten Data Frames. Letztere können – verwendet man den neuen R-Skript Konnektor – in Power BI eingebunden und in die eigenen Datenmodelle integriert werden. Das R-Skript wird hierbei von Power BI an eine (lokale) R-Instanz gesendet. Hier werden die Berechnungen ausgeführt und das Ergebnis an Power BI zurückgegeben. Es kann dann in Power BI visualisiert oder auch mit weiteren Daten angereichert werden.

 

Wie kann ich R in Power BI nutzen?

Um R in Power BI nutzen zu können, muss eine entsprechende R-Installation auf dem lokalen System existieren. Es handelt sich um Freeware und kann zum Beispiel mit Hilfe der folgenden Anleitung installiert werden:

https://www.r-project.org/

Ist dies erfolgt, kann direkt in Power BI, unter „Daten abrufen“ -> „Sonstige“ -> R Script (beta) als Datenquelle gewählt werden:

 

 Oops, an error occurred! Code: 20180621210218e3e6cd20

Abbildung 1: R als Datenquelle

 

Anschließend erscheint eine Warnung von Microsoft, dass diese Quelle noch Beta ist und es noch Änderungen an der Logik geben kann. Anschließend kann direkt ein R-Script geschrieben werden. Hierbei ist zu beachten, dass die R-Quelle aktuell nur ganze DataFrames als Ergebnis an Power BI zurückgeben kann. Hier ein kleines Beispiel, wie ein solches R-Script aussehen kann:

x = 1

y = 2

z = x/y

df = data.frame(x,y,z)

df

 

Dieses Beispiel berechnet x (=1) / y (=2) und speichert das Ergebnis in z. Anschließend werden x, y und z in einem DataFrame ausgegeben.

Zu beachten bei der Arbeit mit der R-Quelle ist, dass die lokale Instanz hinterlegt ist. Diese wird direkt im R-Skript-Popup unter R-Installationseinstellungen eingetragen und angepasst:

 

Einstellungen für R

Abbildung 2: Einstellungen für R

 

Anschließend mit „OK“ bestätigen, und Power BI erhält eine Tabelle als Ergebnis, mit den Spalten x, y und z und den jeweiligen Werten, die im R-Skript definiert wurden. Auf diese Weise kann sehr einfach mit R in Power BI gearbeitet werden.

Eine detailliertere Analyse der R-Quelle kann vorgenommen werden, indem in der aktuellen Abfrage unter „Start“ -> „erweiterter Editor“ der eigentliche Quellcode dargestellt wird (in der Skriptsprache M):

 

Erweiterter Editor mit R Skript

Abbildung 3: Erweiterter Editor mit R Skript

 

Hier wird klar, dass die neue Quelle lediglich eine Funktion aufruft (R.Execute), die einen Text als Parameter erwartet, der das eigentliche R Skript beinhaltet. Dieser kann also zum Beispiel auch generisch erzeugt werden. Dabei ist darauf zu achten, dass der Zeilenumbruch in R immer ein Execute des aktuellen Befehls zur Folge hat und daher explizit angegeben werden muss („#(lf)“).

 

Democase: Dynamische R Integration in Power BI

Wie beschrieben kann R als Datenquelle in Power BI genutzt werden. In den bereits existierenden Demos hierzu werden als Datenbasis für die R Berechnungen immer lokal gespeicherte Daten (wie beispielsweise CSV Dateien) herangezogen. Dies macht durchaus Sinn, gerade wenn die zu analysierenden Daten sehr groß sind und nicht unnötig in Power BI importiert werden sollen. Ein Beispiel findet sich hier:

http://blogs.msdn.com/b/powerbi/archive/2015/11/24/visualizing-and-operationalizing-r-data-in-power-bi.aspx

Es stellt sich allerdings die Frage, ob es auch möglich ist, Daten direkt aus Microsoft Power BI einem R-Skript zu übergeben und so direkt Auswertungen auf Basis der bereits in Power BI importierten Daten durchzuführen. Es sollte also eine Funktion geben, die es ermöglicht, Spalten einer Tabelle zu übergeben, definierte Berechnungen in R vorzunehmen und das Ergebnis in einer Tabelle auszugeben.

In diesem Democase wird daher weniger Wert auf eine komplexe R Berechnung gelegt als vielmehr darauf, einen Weg aufzuzeigen, um Daten direkt aus Power BI zu übergeben. Daher stellt das Ergebnis der Demo-Funktion, die über R berechnet wird, beliebige Quantile über eine Datenspalte dar. Hierzu wird in den Abfragen von Power BI eine leere Abfrage als neue Quelle angelegt:

 

Anlegen einer neuen Abfrage

Abbildung 4: Anlegen einer neuen Abfrage

 

Der erste Schritt zu der oben beschriebenen Funktion ist die R-Methode, die statisch aufgerufen wird. Hierzu wird der folgende Code im erweiterten Editor („Start“ -> „erweiterter Editor“) hinzugefügt:

 

let

                  RResults = R.Execute("n = c(1.2,3.4,10)

                  #(lf)result = quantile(n, c(0.1,0.5))

                  #(lf)percentile=c(0.1,0.5)

                  #(lf)df = data.frame(percentile,result)

                  #(lf)df"),

                  ResultDataFrame = RResults{[Name="df"]}[Value]

in

             ResultDataFrame

 

 

Das Ergebnis dieser Abfrage wird direkt berechnet und ausgegeben:

 

Ergebnis der ersten R-Abfrage

Abbildung 5: Ergebnis der ersten R-Abfrage

 

 

Wie bereits beschrieben, führt die Funktion „R. Execute“ den eigentlichen R-Code aus. In dieser Demo wird ein Vektor mit Testwerten erzeugt (n = 1.2, 3.4, 10), für den im nächsten Schritt das 10%- und das 50%-Quantil berechnet werden. Anschließend werden sowohl das Ergebnis als auch ein Vektor mit den Werten der berechneten Quantile auf den Ausgabe-Dataframe gelegt und ausgegeben.

Im zweiten Schritt dieser Demo wird die Abfrage zu einer Funktion erweitert. Hierzu wird erneut der erweiterte Editor aufgerufen und der Code wie folgt angepasst:

 

let

                  RCalculation = (DataList as text, Percentiles as text) =>

                  let

                                   RResults = R.Execute("n = c("&DataList &")

                                   #(lf)result = quantile(n, c("&Percentiles&"))

                                   #(lf)percentile=c("&Percentiles&")

                                   #(lf)df = data.frame(percentile,result)

                                   #(lf)df"),

                                   ResultDataFrame = RResults{[Name="df"]}[Value]

                  in

                                   ResultDataFrame

in

                  RCalculation

 

 

Wird diese Funktion nun aufgerufen, erscheint ein Popup, das zwei Texte als Eingabe erwartet:

Parametereingabe der RCalculation

Abbildung 6: Parametereingabe der RCalculation

 

 

Nun werden die Parameter aus dem ersten Teil übergeben (DataList: 1.2 ,3.4 ,10 und Percentiles: 0.1, 0.5) und wiederum die korrekten Quantile berechnet. Somit ist der erste Schritt hin zu einer dynamischen Berechnung mittels R in Power BI geschafft. Hierfür wurden lediglich die Texte, die als Parameter übergeben wurden, in dem Code für die R-Funktion eingebettet.

Im letzten Schritt wird der erste Parameter so abgeändert, dass kein Text mehr notwendig ist, sondern direkt aus einer Spalte die darin befindlichen Daten übernommen werden können. Voraussetzung hierfür ist, dass die Spalte ein (beliebiges) Zahlenformat beinhaltet und die Sicherheitseinstellungen wie folgt angepasst werden:

Um sicherzustellen, dass die Implementierung korrekt verläuft, sollte in den Optionen von Power BI die folgende Option deaktiviert sein (in unseren Tests schlug die Funktion fehl, wenn diese Option aktiv war):

 

Anpassen der Sicherheitsoptionen

Abbildung 7: Anpassen der Sicherheitsoptionen

 

 

Nachdem diese Sicherheitseinstellung angepasst ist, kann nun wieder der erweiterte Editor für die Abfrage geöffnet und der bestehende durch folgenden Code ersetzt werden:

 

let

    RCalculation = (DataList as list, Percentiles as text) =>

                  let

                     DataTable = Table.FromList(DataList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

                     DataTableColumns = Table.ColumnNames(DataTable),

                 DataTableText = Table.TransformColumnTypes(DataTable ,{{List.Last(DataTableColumns),

                 type text}}, "en-US"),

                     DataInputList = Table.ToList(Table.Transpose(DataTableText),

                 Combiner.CombineTextByDelimiter(",")),

                     RResults = R.Execute("

                     n = c("&List.First(DataInputList)&")

                     #(lf)result = quantile(n, c("&Percentiles&"))

                     #(lf)percentile=c("&Percentiles&")

                     #(lf)df = data.frame(percentile,result)

                     #(lf)df"),

                     ResultDataFrame = RResults{[Name="df"]}[Value]

                  in

                     ResultDataFrame

in

                  RCalculation

 

Die neuen Eingabeparameter haben sich entsprechend geändert: Die DataList ist nun eine Liste. Dies entspricht einer Tabellenspalte in Power BI. Entsprechend könnten auch die Percentile aus einer Tabelle ausgelesen werden. Da dies redundant zu der DataList ist, wird hier darauf verzichtet (der Parameter wird also weiterhin per Hand als Text übergeben).

Die DataList selbst muss nun in einen Text umgewandelt werden, der Komma-separiert die einzelnen Werte der Liste enthält. Hierzu wird die Liste in eine Tabelle umgewandelt, und die neue Spalte in Text umformatiert (hierzu muss die neue Spalte in der Tabelle identifiziert werden; da diese Tabelle nur eine Spalte beinhaltet, lässt sich das relativ einfach über die Funktion Table.ColumnNames erledigen). Auch wurde hier explizit die Culture mitgegeben (en-US), um gegebenenfalls Dezimalwerte umzuwandeln (zum Beispiel 1,23 -> 1.23). Abschließend werden die Spalten und Zeilen vertauscht (Table.Transpose) und die Tabelle wieder in eine Liste zurückverwandelt. Das Komma dient dabei als Trennsymbol. Das Ergebnis stellt die ausgewählte Spalte als Komma-separierten Text zur Verfügung und kann nun in die bereits bekannte Funktion eingesetzt werden.

Wird nun die Funktion aufgerufen, kann eine beliebige Tabellenspalte als DataList gewählt werden, ergänzt um die zu berechnenden Quantile als Text:

 

Parametereingabe der finalen Funktion

Abbildung 8: Parametereingabe der finalen Funktion

 

Die Funktion wandelt nun die Spalte in den entsprechenden Text um und übergibt diesen an die R-Funktion. Das Ergebnis sieht wie folgt aus (die Testdaten kommen aus einer Spalte, welche die Werte 0, 0.1, 0.2, … ,9.9, 10 enthält):

 

Ausgabe der finalen Funktion

Abbildung 9: Ausgabe der finalen Funktion

 

 

Fazit: Treffen sich zwei Buchstaben…

 

Die Möglichkeit, die eigenen Daten mittels R-Skript statistischen Analysen zu unterziehen, ist aus unserer Sicht eine sehr wertvolle Ergänzung zum bestehenden Leistungsangebot von Power BI. Die wahre Stärke und Flexibilität liegt allerdings in der Kombination der Power Query Abfragesprache M und der statistischen Programmiersprache R. Hier gilt es auf der einen Seite eine Schnittstelle zu bilden, die es ermöglicht, die eigenen Daten in der passenden Form einzulesen. Auf der anderen Seite können eben diese Daten mit passenden Analyse-Methoden ausgewertet werden, um aussagekräftige statistische Ergebnisse zu erhalten und neue Einblicke in und Erkenntnisse über das eigene Geschäft zu gewinnen.

Durch die Kapselung verschiedener Analyse-Modelle (R-Skripte) in Power Query-Funktionen lassen sich solche Modelle auch von anderen Kollegen wiederverwenden, ohne dass sie dafür tiefe Kenntnisse über das dahinterliegende statistische Modell haben müssten. Kleiner Wermutstropfen: Einzelne Power Query-Abfragen können aktuell noch nicht über das Power BI-Portal veröffentlicht werden. Mit Blick auf die rasanten Entwicklungsfortschritte, die Microsoft beim Thema Power BI derzeit an den Tag legt, lässt dieses Feature aber vermutlich nicht mehr allzu lange auf sich warten – wir werden in diesem Blog darüber berichten.

Michael Koch

Senior Consultant

pmOne AG

Als Entwickler, Architekt und Projektleiter ist Michael Koch seit 2009 im Bereich Business Intelligence/Data Warehousing für die pmOne tätig. Dabei liegt der Fokus des Senior Consultants auf der Strategieberatung im Bereich Self-Service BI. Darüber hinaus arbeitet er bereits seit mehreren Jahren als Trainer für Power BI.

https://www.pmone.com •  Blog-Beiträge von diesem Autor

Sebastian Müller

Principal Architect / Principal Engineer

pmOne AG

Seit seinem Master-Abschluss in Informatik im Jahr 2013 berät Sebastian Müller als Teil des pmOne-Teams Unternehmen in Data Warehouse- und Business-Intelligence-Angelegenheiten. Sein Fachwissen bringt er zusätzlich ein, indem er ein pmOne-internes Forschungsteam mit dem Fokus auf Self-Service Business Intelligence leitet.

https://www.pmone.com •  Blog-Beiträge von diesem Autor