Einführung in PowerQuery, Teil 1 von ?

thIn diesem Blog Eintrag möchte ich eine kurze Einführung/Tutorial in das Thema PowerQuery geben. Das ganze wird eine Serie, Teil 1 von ? – ganz in der Manier des Report Viewers. Schauen wir mal wieviele es werden.

In diesem Beitrag schauen wir uns den Import von Daten aus dem Web an und wie die Daten verarbeitet, transformiert und in das Modell geladen werden können. Weiterhin werden 2 Tabellen mit einander verbunden und als eine Liste später in Excel dargestellt. Soviel zum Ziel…

2014-06-28_21-19-21Starten wir auf einer Webseite. Dort liegt eine fiktive Preisliste die außerhalb der eigenen Umgebung liegt. D.h. diese Liste steht weder auf internen SQL Servern o.ä. zur Verfügung und liegt auch nicht unter der eigenen Kontrolle. Dennoch soll diese Preisliste in Excel bzw. PowerPivot verarbeitet werden. Um an solche Daten zu kommen, bietet sich das kostenlose Excel 2013 Addin „PowerQuery“ an.

2014-06-28_21-20-19In Excel wechseln wir auf den Ribbon Power Query und laden Daten aus dem Web. Dort wird die URL der Webseite eingegeben.

2014-06-28_21-21-12Nach dem Bestätigen werden uns 2 Tabellen angeboten. Auf der Webseite war schon zu sehen das die erste Tabelle die Preisliste darstellt. Wenn man sich nicht sicher ist, fährt man mit der Maus über die Einträge und bekommt dort schon eine Vorschau.

Mit einem Klick auf Bearbeiten wechselt man in ein eigenes PowerQuery Fenster um die Verarbeitungsoptionen festzulegen.

2014-06-28_21-24-03In dem Bereich „Angewendete Schritte“ wird eine Historie geführt. Das erste nach dem Laden ist immer das setzen, dass die erste Zeile die Überschriften der Zellen beinhaltet. Jegliche Schritte können hier manuell gelöscht werden. Da die Spalte Rabatt leider die Daten mit Punkten anstatt einem Komma in der Fließkomma Zahl trennt, muss dies beim Import geändert werden, da ansonsten dies in Excel als Tausender Trennzeichen interpretiert wird. Weiterhin muss das Prozentzeichen entfernt werden. Beim Preis wurde schon automatisch das Euro Zeichen entfernt. Über den Bereich Werte Ersetzen werden diese Änderungen vorgenommen.

2014-06-28_21-24-572014-06-28_21-25-29Weiterhin wird die Spalte noch als Datentyp Zahl definiert. Der ETL Prozess ist somit eigentlich abgeschlossen. Über den Bereich „Einstellungen laden“ können nun die Daten nach Excel und/oder PowerPivot (via in Datenmodell laden) übergeben werden.

2014-06-28_21-32-59Nun haben wir aber noch eine zweite Preisliste in einem anderen Format die hier dieser Liste angefügt werden soll. Ein Feature was in PowerPivot schonmal nicht möglich wäre, da wir so nur zwei Tabellen bilden könnten. Diese Preisliste unterscheidet sich in den Spalten. Die Rabatt Spalten fehlen völlig und die Beschreibung wie auch die Preisspalte sind auch noch anders benannt. Dennoch importieren wir einfach über PowerQuery die Daten:

2014-06-28_21-34-212014-06-28_21-35-14

 

 

 

 

 

 

 

Hier benennen wir erstmal die Spalten so wie in der vorherigen Tabelle indem man mit einem Doppelklick auf die Überschriften der Spalten klickt und die vorherigen Werte überschreibt.

2014-06-28_21-37-30Nun fügen wir einfach die beiden fehlenden Spalten noch der Tabelle hinzu damit die beiden Tabellen das gleiche Format/Modell haben. Als Standardwert tragen wir hier einfach eine 0 ein.

2014-06-28_21-40-37Nun nutzen wir das Feature „Abfrage anfügen“. Hier wählen wir die vorherige in PowerQuery verarbeitete Tabelle aus und bestätigen das Fenster mit OK.

Nun sind beide Tabellen in einer zusammengeführt. Über den Aktualisieren Knopf werden auch beide Tabellen aktualisiert, d.h. aus zwei Schritten wird einer.

2014-06-28_21-41-41Wenn nun die Daten nach PowerPivot oder Excel übergeben werden, liegen die Daten in einer Tabelle.

Fazit:
Mächtiges Tool was teils komplexe ETL Prozesse in den Self Service BI Bereich bringt und Anwendern die Möglichkeit bietet, Internet Daten schnell und einfach ohne IT zu verarbeiten.

 

 

 

 

 

PowerBI: Automatisches Aktualisieren von PowerPivot Mappen aus onPremise Umgebungen

PowerBIErgänzend zu den Unterlagen von Microsoft zum Thema Power BI wie man einen Gateway und eine Datenquelle in einer on Premise Umgebung einrichtet, will ich das hier nochmal etwas deutlicher und bebildert beschreiben, da es hier doch einige Stolpersteine gibt …

Kurz vor ab  – wofür ist das ganze Thema gut? Mit Power BI können PowerPivot Arbeitsmappen mit oder ohne PowerView auf einen Office 365 SharePoint Online Server gelegt werden. Bei dem Thema bekommen immer die meisten Angst („oh je, da sind ja alle meine Daten in der Cloud und was sagt der Datenschützer dazu?“) – ich persönlich halte i.d.R. nur die Daten in den Arbeitsmappen die ich für die Ermittlung der Kennzahlen und dessen Visualisierung benötige – mehr nicht! D.h. darin befindet sich nicht der Name meines Kunden oder dessen Detail Daten, denn sowas brauche ich eher selten in einem Balkendiagramm bei der Visualisierung einer Kennzahl auf einem Dashboard. Sollte ich dennoch solche Daten brauchen, bspw. bei einem Kundenblatt, dann verlasse ich mich auf die geschlossene Auftragsdatenverarbeitungserklärung (kurz ADV) mit Microsoft und das die Daten in Europa liegen und ich dies mit meinem Kunden auch so vereinbart und besprochen haben, aber das soll hier kein Vortrag zum Thema Datenschutz und Datensicherheit in der Cloud werden.

Nun denn, wenn meine Excel Sheets nun in der Cloud liegen, müssen ja auch irgendwie die Daten via der Datenquellen aktualisiert werden. D.h. die Datenquellen liegen in einer lokalen Installation innerhalb meiner Infrastruktur und die Aktualisierung soll möglichst automatisch und außerhalb der Lastzeiten liegen.

Um dies zu realisieren, installiere ich einen Gateway Dienst von Office 365 der auf einem Server innerhalb meiner Infrastruktur ausgeführt wird. Dieser nimmt die Aktualisierung vor indem dieser die Daten aus meiner SQL Server Datenquelle extrahiert und zum PowerPivot Excel Sheet in Office 365 transportiert. Dieser Weg ist m.E. auch recht sympathisch, da die Verbindung aus meiner Umgebung ausgehend und besser kontrollierbar ist. Wir der Gateway Dienst installiert wird, wird recht einfach und gut in diesem Dokument beschrieben. Sobald das Gateway online ist, sieht man dies auf der Power BI Admin Center Webseite:
28-06-_2014_15-21-30Auf dieser Übersichtswebseite sieht man jegliches eingerichtete Gateway. D.h. es können auch mehrere Gateways installiert und betrieben werden um so die Arbeitsmappen mit verschiedenen Quellen zu versorgen. Stand jetzt, funktioniert die automatische Aktualisierung von PowerPivot Mappen nur mit SQL Server Datenquellen. Über den lokalen Gateway Manager („Microsoft-Datenverbindungsgateway-Konfigurations-Manager“) erhält man auch die nötigen Informationen zum verwendeten Endpunkt in Bezug auf das Protokoll und den Port der selbstverständlich in der Firewall (sofern ausgehende Verbindungen geblockt werden) eingetragen werden muss.

Sobald das Gateway läuft, sollte man zuerst die PowerPivot Mappe sich anschauen und die darin enthaltene Verbindung überprüfen:

28-06-_2014_15-29-34Innerhalb der PowerPivot Mappe wechselt man auf die vorhandenen Verbindungen, dort auf bearbeiten und auf Erweitert. Hier erhält man alle Informationen die in der Datenquelle in Office 365 eingetragen werden müssen (Hinweis, auch wenn fast selbstverständlich, aus aktuellem Anlass: Auf die Servernamen . oder localhost sollte verzichtet werden, wenn der Gateway Server nicht gleich der SQL Server ist …).

Nun trägt man genau diese Informationen in der neuen Datenquelle im PowerBI Admin Center ein:

28-06-_2014_15-35-08Hier sollte man unbedingt darauf achten, dass der Benutzer die entsprechende Berechtigungen erhalten hat der konfiguriert wurde um die Daten zu lesen aber auch das der gleiche Verbindungsanbieter gewählt wurde, der auch in der PowerPivot Mappe angegeben wurde. Sollten irgendwelche Daten abweichen, war meine Erfahrung das die Aktualisierung einfach nicht funktioniert hat – irgendwie auch logisch :-)

Nun kann man die PowerPivot Mappe in die PowerBI Gallerie legen und auch hier die automatische Aktualisierung konfigurieren. Durch die Warn-Mechanismen wird man auch auf fehlerhafte Aktualisierungen aufmerksam gemacht. Sollte eine Aktualisierung nach ca. 10 mal nicht funktionieren, wird automatisch die Aktualisierung deaktiviert, bzw. der Zeitplan.

Klasse finde ich bei Power BI dann auch die Unterstützung der Power BI QA, also das man dem Server fragen stellt und dieser die anhand des Datenmodells beantwortet. Wenn man dies das erstemal verwendet, merkt man wie schlecht häufig die Felder und Tabellen wie auch Measures benannt sind, so das eine Frage dann lautet Anzahl der Anzahl nach Beschreibung … Aber eben auch die Features der Power BI App im Windows Store die kostenlos dazu zählt ist klasse, so bekommen die Adressaten die Power View Berichte der Excel Arbeitsmappen als Touch App auf die Endgeräte. Lediglich der Preis von ca. 32 € pro Nutzer und Monat ist etwas abschreckend, aber schauen wir mal wie sich das entwickelt. Am besten einfach mal selber mit einer Evaluation ausprobieren.

 

Automatisiert Adressen in Geo-Daten umwandeln und die Entfernung zu einem Standort berechnen

WeltkugelImmer wieder habe ich das Problem, dass ich haufenweise Adressen habe und diese bspw. in einem Report (Reporting Services) verarbeiten muss. D.h. ich brauche Geo-Daten und will diese natürlich nicht Datensatz für Datensatz in Längen- und Breitengrad übersetzen. In diesem Artikel will ich kurz beschreiben, wie eine CSV Datei mit Adressen automatisch mit den Google Webdiensten in Längen- und Breitengrad übersetzt wird und diese Daten dann im SQL Server wiederverwendet werden können.

Beispiel: Eine CSV Datei mit verschiedenen Imbiss Buden aus Koblenz (via den Gelben Seiten abgefragt):

2014-06-21_15-17-21

Via PHP wurde nun die Datei zeilenweise ausgelesen und die Adresse wurde an Google gesendet:2014-06-21_15-20-19

Die Ausgabe des Scripts ergibt folgendes:2014-06-21_15-22-20

Diese Daten können nun super per SSIS in die Datenbank eingelesen werden – natürlich kann man auch gleich die Daten in den SQL Server, bzw. die Datenbank schreiben. Die Tabelle dazu:
2014-06-21_15-24-48

Und nun brauchen wir noch eine Tabelle wo wir unseren eigenen Standort reinschreiben:
2014-06-21_15-25-29

Nun haben wir eine Tabelle in der die Adressen sind und auch eine Tabelle wo unser Standort enthalten ist. Über je ein Update Statement lassen wir gleich das GEO-Feld in den beiden Tabellen füllen (aus Längen- und Breitengrad) und über die STDistance Funktion können wir dann die jeweilige Entfernung zwischen dem Standort und der jeweiligen Adresse finden – so finden wir den nächsten Imbiss :-)

UPDATE dbo.Orte SET geo = GEOGRAPHY::STPointFromText(‚POINT(‚ + CONVERT(VARCHAR, laengengrad) + ‚ ‚ + CONVERT(VARCHAR, breitengrad) + ‚)‘,4326)

UPDATE meinStandort SET geo = GEOGRAPHY::STPointFromText(‚POINT(‚ + CONVERT(VARCHAR, laengengrad) + ‚ ‚ + CONVERT(VARCHAR, breitengrad) + ‚)‘,4326)

SELECT name, orte.adresse, orte.geo.STDistance(ms.geo) As [Entfernung In Meter] FROM dbo.Orte CROSS JOIN meinStandort ms