SSIS 2 Power Query > Integration Service als ODATA Feed [2]

Im zweiten Teil des Blogs geht es nun darum den Datafeed in meinem Power BI Portal zur Verfügung zu stellen und ihn über Power Query abzufragen.
Leider steht Power BI Portal zur Zeit nur als Cloud Lösung (O365) zur Verfügung, ich hoffe das Microsoft hier nicht zu sehr von eigenen Interessen und self-focused Möglichkeiten der Cloud getrieben wird und der Kunde dabei auf der Strecke bleibt, der seine Daten nicht in externen Rechenzentren auslagern und seine eigene Roadmap für Software im Unternehmen verfolgen möchte.

Power BI ist eine Lösung die auf Office 365 und dort im Speziellen auf SharePoint aufsetzt. Zu Power BI gehören aber auch Excel Add Ins wie Power Pivot, Power Query, Power View und Power Map (Nein, PowerPoint wird zusammen geschrieben und gehört nicht dazu 😉 ) Auf die einzelnen Tools werde ich hier nicht direkt eingehen, vielleicht mal in einem anderen Beitrag. Was uns hier interessiert, wie kommen die Daten denn in unsere Cloud Lösung und wie frage ich diese mit Power Query ab? Bis dato haben wir bei unseren Cloud Projekten mit MS CRM Online oder SalesForce.com unser geliebtes SSIS genutzt. Diesmal nicht!
Microsoft hat für Power BI seine eigene technische Lösung entwickelt, die es ermöglicht Daten aus on premise Systemen als “Cloud Zugriff” oder ODATA Feed zu veröffentlichen. Wie geht das?

Installation Gateway

Für die Kommunikation von O365 und on premise Systemen benötigt Microsoft ein sogenanntes Data Management Gateway. Das Gateway erhält man im Power BI Portal:
Im O365 Portal findet man unter “Administrator” den Menüeintrag “Power BI”:

PowerBI_1

Dort befindet sich der Power BI Admin Center. In diesem Bereich kann der Benutzer unter
”Gateways” Verbindungen zu den on premise Systemen definieren.

PowerBI_2

Im ersten Schritt wird der Name des Gateways definiert:

PowerBI_3

Im nächsten Schritt kann die on premise Gateway Komponente heruntergeladen
werden:

PowerBI_4

Die gleiche Komponente findet sich im MS Download Bereich:
http://www.microsoft.com/de-de/download/details.aspx?id=39717

Das Gateway muss on premise installiert werden. Für die Installation auf der entsprechenden Maschine benötigt man .net 4.5 und einen Gatewayschlüssel, welcher auf dieser Seite angezeigt und kopiert werden muss, da er in der Installation on premise benötigt wird.
Die eigentliche Installation on premise ist simples click next > finish.

PowerBI_5 PowerBI_7

Jetzt muss das Gateway registriert werden, dazu benötigen wir den Schlüssel den
wir kopiert hatten:

PowerBI_8

Im nächsten Schritt wird der HTTP Endpoint gesetzt. Hier können HTTP oder HTTPS,
sowie die Portnummer konfiguriert werden. Die Ports sollten durch den FW des Unternehmens
und falls am Start, der Maschine freigegeben sein.

PowerBI_9

Fertig! Jetzt können wir das Gateway in Power BI betrachten:

PowerBI_10

Wir können in Power BI zentral unsere verschiedenen Gateways monitoren:

PowerBI_11

Wir erkennen Lastspitzen, sowie auch Ausfallzeiten/Maintenance der Gateways.
Noch einmal ergänzend die schematische Darstellung des Gateways:

PowerBI_Structure

Erstellen einer ODATA Datenquelle in Power BI

Um mit Power Query über die Gateways zu zugreifen zu können, benötigen wir nun eine Datenquelle, die im Admin Center von Power BI definiert wird:

PowerBI_12

Bei der Erstellung einer Datenquelle kann zwischen Datenquellen die für SharePoint Online aktualisiert werden (Excel Workbooks und Power View Reports) und Datenquellen (OData Feeds) die für Power Query nutzbar sind, unterschieden werden. In unserem Beispiel nutzen wir den ODATA Feed.
Im nächsten Schritt geben wir die Verbindungsinformationen ein:

PowerBI_13

Als erstes muss dabei das Gateway ausgewählt werden welches wir nutzen wollen. Im zweiten Teil der Maske können wir zwischen der Eingabe eines Connectionsstrings oder der Definition der Verbindungseigenschaften wählen. Im Beispiel nutzen ich die Eigenschaften… kann mir Connectionstrings nie merken..

PowerBI_14

Hier muss man etwas aufpassen: a.) der Button für Anmeldeinformationen taucht erst nach etwas Zeit auf, dann lädt sich eine ClickOnce App runter. b.) Bei anderen Browsern wie Firefox und Chrome, hier wird man auf ein Plug In Download geroutet.

PowerBI_15

Im Dialog gibt man entweder die entsprechenden Windows oder SQL User Credentials ein, entsprechend dem Dropdown im vorherigen Dialog.

PowerBI_16

Nun erscheint ein Dialog für weitere Einstellungen. Hier können Tabellen und Views für den ODATA Feed ausgewählt werden, auch die View die das SSIS Package aus Teil 1 des Blogs triggert.

PowerBI_17

Der letzte Schritt ist die Zuordnung von Power BI Benutzern und Gruppen:

PowerBI_18

Power Query Abfrage:

Um nun Daten in Power Query zu nutzen, können wir uns aus dem Admin Center die entsprechende URL laden:

PowerBI_20

Per Klick auf die … erscheint das Detail, wo die OData URL zu kopieren ist:

PowerBI_19

Den kopierten ODATA URL nehmen wir jetzt und nutzen ihn in Power Query. Dort im Tab “Power Query” unter Other Sources finden wir die ODATA Datenquelle:

ODATA1  ODATA2

Im nächsten Dialog muss man darauf aufpassen, das der Link zum ODATA Feed mit der richtigen Authentifizierung durchgeführt wird. Hier wird OAuth von Office 365 genutzt und keine Windows oder andere Authentifizierung.

ODATA3

Unter Machine Settings > Data Source Settings können diese Einstellungen in Power Query immer wieder verändert werden. Danach öffnet sich der Dialog, welcher die verschiedenen Sets der ODATA Quelle darstellt. In meinem Falle nur der Currency Feed. Einmal ausgewählt, wird via des Gateways das SSIS Package aus dem Teil 1 aufgerufen und liefert die Daten in mein Power Query zur weiteren Verwendung.

ODATA4

Ein aktuelles Problem habe ich jedoch bei dieser Art von Gateway: Es funktioniert bei mir nur, wenn ich Zugang zur entsprechenden Domain des Gatways von meinem aufrufenden Power Query Client habe. Sonst erscheint folgende Meldung:

ODATA5

Wenn ich Zugriff auf den entsprechenden Server habe funktioniert es ohne Problem. Vielleicht ist das by design, eine email an Dany und die MS folks ist bis dato noch unbeantwortet.

ODATA6

ODATA7

Für die Zukunft würde ich mir wünschen, das diese Datenquellen direkt über das Portal aufgerufen werden können.