Power BI Connector
Der CoPlanner Power BI Connector ist für CoPlanner PowerUser mit Power BI Wissen das perfekte Werkzeug um innovative Dashboards mit aktuellen CoPlanner Daten im Unternehmen zu verteilen. Der CoPlanner Power BI Connector wurde entwickelt, um Daten aus dem CoPlanner beziehen zu können ohne sich diese direkt aus der SQL Datenbank holen zu müssen. Mit dem Konnektor ist es möglich Tabellen, Dimensionen, Subsets und Funktionen aus CoPlanner Modellen in Power BI zu importieren (keine Live-Connection) und das aus beliebig vielen CoPlanner Serverinstanzen.
Dieser Konnektor wurde nach den Richtlinien von Microsoft zum Erstellen eines Custom Connectors für Power BI entwickelt.
Voraussetzungen
- Power BI Desktop ist installiert
- CoPlanner Server läuft unter https
- CoPlanner Lizenz muss Power BI freigeschalten haben
Installation
Die Installation für die Desktop Variante kann über das CoPlanner Power BI connector.msi installiert werden.
Damit wird der Connector "CoPlanner.pqx" in das Verzeichnis C:\Users\<username>\Documents\Power BI Desktop\Custom Connectors gespeichert und es werden RegistryKeys gesetzt, damit der Konnektor als vertrauenswürdig gilt.
Für die Verwendung beim Gateway wird das CoPlanner.pqx auch ohne den Installer zur Verfügung gestellt.
Verwendung
Bei dem Menüpunkt "Daten abrufen" befindet sich unter "Weitere..." ein Konnektor mit dem Namen CoPlanner, sofern dieser korrekt installiert wurde.
Wählt man diesen aus, muss man die URL angeben unter welcher der Server erreichbar ist (z.B. https://meinservername:4443/coplanner) und kann optional die Sprache angeben.
Verbindet man sich erstmalig auf diese URL, so bekommt man dann einen Dialog, wo man sich entscheiden kann, ob man sich über Windowsauthentifizierung oder über den CoPlanner Benutzer anmelden will. Dieser Benutzer muss entsprechend im CoPlanner vorhanden und berechtigt sein. Will man den Zugriff auf die Daten entsprechend des CoPlanners berechtigen, wenn man den Bericht veröffentlicht hat, so sollte ein Benutzer gewählt werden, der alle Rechte besitzt, da Daten später nur wegkommen und nicht hinzugefügt werden können.
Bei weiteren Zugriffen auf diese URL bekommt man diese Abfrage nicht mehr, will man den Benutzer ändern, dann muss man das über die Datenquelleneinstellungen anpassen.
Nach dem Zugriff bekommt man ein Fenster, den Navigator, in welchem Datumstabellen, Funktionen und Tabellen, Dimensionen und Subsets, sofern diese "Für Analysen verfügbar (Power BI)" angehakt haben, aufgelistet werden. Hier wählt man die entsprechenden Daten aus, die man in diesem Bericht anzeigen will und lädt diese.
Hat man etwas vergessen oder möchte den Bericht einfach erweitern, kann man das einfach über die zuletzt verwendeten Quellen tun.
Generell ist es zu empfehlen, nicht nur die Objekte, sondern auch die Felder der Objekte auf die benötigten Felder einzuschränken über das Flag "Für Analysen verfügbar (Power BI)" in der Objektverwaltung, da dann nur die relevanten Daten geholt werden und es deshalb performanter ist.
Beziehungen verwalten
Bei der Verwendung von mehr als einer Tabelle, welche auch die gleichen Dimensionen verwenden ist es wichtig die Beziehungen richtig zu pflegen. Wenn diese richtig gepflegt sind, kann man im Bericht nach den Dimensionen filtern.
Diese Verbindungen werden beim Erstellen angelegt, müssen dann allerdings überprüft werden, ob alle Beziehungen erstellt wurden und diese auch richtig sind.
Hierarchien erstellen
Im Gegensatz zum CoPlanner erwartet sich Power BI, dass alle Blattelemente auf der gleichen Ebene sind. Der Konnektor liefert die Dimensionen entsprechend ausgeflacht und fügt, sollte ein Blattelement nicht auf der richtigen Ebene sein, weitere Ebenen mit dem Namen des Blattelements hinzu, da Power BI hier sonst weitere Ebenen, die keinen Namen haben, hinzufügen würde.
Dimensionen werden mit den Feldern Level_0 bis Level_x geliefert. Um eine Hierarchie von der obersten Ebene weg zu erstellen, öffnen Sie mit der rechten Maustaste das Kontextmenü auf Level_0 und wählen Sie "Hierarchie erstellen". Wenn man danach z.B. auf Level_1 klickt, kann man über das Kontextmenü diesen Eintrag zur Hierarchie hinzufügen. Alle weiteren Level-Elemente kann man dann einfach aufsteigend hinzufügen.
Im Datenbereich kann man dann jedes Level nach seiner entsprechenden Order-Spalte sortieren. Also Level_0 wird nach Order_0 sortiert, Level_1 nach Order_1, usw.. Damit erhalten wir wieder eine Hierachie und Sortierung wie im CoPlanner.
Für einen leichteren Überblick kann man die Level und Order Spalten dann einfach ausblenden, da diese sonst nicht mehr benötigt werden.
Datumstabellen
Damit Quickmeasure oder Zeitintelligenz Funktionen in Power BI funktionieren muss eine Datumstabelle lt. Vorgaben von Power BI erstellt werden. Power BI Datumstabellen müssen bestimmte Eigenschaften besitzen, wie z.B. eine Spalte des Datentyps date oder date/dime aufweisen muss, eindeutige Werte enthält, keine leeren Werte hat,... Der CoPlanner Connector stellt auf Basis von CoPlanner Zeitdimensionen valide Power BI Datumstabellen zur Verfügung.
Um die vom Connector erstellt Datumstabelle verwenden zu können, muss diese in der Modellierung über das Kontextmenü als Datumstabelle gekennzeichnet werden. Zusätzlich muss dann ein Mapping über das Datumsfeld der normalen Zeitdimension stattfinden. Wenn das erfolgt ist, kann man sich über Quickmeasures z.B. den prozentualen Anteil der Veränderung zum Vorjahr mit ein paar Klicks erstellen, ohne DAX Kenntnisse zu haben.
Funktion "Daten optimieren"
Über den Navigator können Tabellen, Dimensionen und Subsets geladen werden. Allerdings können wir hier zwei Themen haben:
1.) Es werden alle Plandaten unabhängig vom Plan geladen. Dies kann zu Performanceproblemen führen und ist unnötig, wenn ich mir ohnehin nur einen Plan anschauen will und keine Planvergleiche.
2.) Ich möchte ein Subset verwenden. CoPlanner-Tabellen selbst haben aber kein Mapping zu den Subsets. Damit dieses Mapping generiert wird und die Daten richtig berechnet werden, müssen die Daten speziell geladen werden.
Diese beiden Fälle können über die Funktion "Daten optimieren" entsprechend abgebildet werden.
Einschränkung der Daten auf einen Plan
Wählen Sie dazu im Navigator die Funktion Daten optimieren aus und wählen Sie nicht "Laden" sondern "Daten transformieren". Es öffnet sich der Power Query-Editor, in welchem die Parameter definiert werden können.
Als erstes muss die WebAppUrl des CoPlanners und der Tabellenname angegeben werden.
Handelt es sich um eine Plantabelle, kann man nun über das Szenario den Plan einschränken, wenn man das machen möchte. Eine Einschränkung hat Vorteile bei der Geschwindigkeit. Will man jedoch mehrere Pläne miteinander vergleichen, empfiehlt es sich eher nicht.
| Hinweis Man kann in Power BI Parameter definieren, welche man dann bei diesen Abfragen verwenden kann. Es empfiehlt sich für die URL des Servers und in dem Fall für das Szenario zu erstellen. Damit kann man einfach mit Änderung des Parameters alle Abfragen gleichzeitig anpassen. |
Für die Einschränkung den Parameter für das Szenario oder den Szenarioname angeben.
Will man die Tabelle auch mit einem Subset verknüpfen, dann kann man hier noch sein Subsetmapping angeben (siehe Verwendung von Subsets) und eine Sprache auswählen.
Sind alle Parameter vergeben auf "Aufrufen" klicken.
Es öffnet sich eine Abfrage mit der Vorschau des Ergebnisses. Dieser kann man einen aussagekräftigen Namen verleihen und in den Visuals darauf zugreifen. Im Power BI Modell sollten die Beziehungen entsprechend automatisch gepflegt werden, man sollte diese aber immer überprüfen.
Beispiel einer Abfrage, mit einem Power BI Parameter mit dem Namen ServerUrl, bei welchem die URL des CoPlanner-Servers hinterlegt ist, auf die Tabelle Bilanz_PLAN für das Szenario Budget final:
= #"Daten optimieren"(ServerUrl, "Bilanz_PLAN", "Budget final", null, null)
Bei ServerUrl wurden hier die Anführungszeichen entfernt, da es ein Parameter ist.
Verwendung von Subsets (Variante 1)
CoPlanner Tabellen selbst haben kein Mapping zu den Subsets. Um dieses Mapping zu generieren und die Daten auch richtig zu berechnen, müssen die Daten speziell geladen werden.
Wählen Sie dazu im Navigator die Funktion "Daten optimieren" aus und wählen Sie nicht "Laden" sondern "Daten transformieren". Es öffnet sich der Power Query-Editor, in welchem die Parameter definiert werden können.
Als erstes muss die WebAppUrl des CoPlanners und der Tabellenname angegeben werden. Als nächstes kann optional das Szenario bei Plantabellen eingeschränkt werden.
Um künftige Erweiterungen umsetzen zu können, ist das Subsetmapping eine Auflistung. Das Power BI UI möchte Auflistungen immer aus einer Spalte beziehen. Befüllen Sie diesen Parameter nicht über „Spalte auswählen…“, sondern klicken Sie direkt auf "Aufrufen" und definieren Sie das Mapping über die Funktionsleiste.
Definieren Sie im Parameter Subsetmapping den Spaltennamen der Basisdimension in der Tabelle mit dem gewünschten Subset mit folgender Syntax:
{„SPALTENNAME_DER_BASISDIMENSION_IN_DER_TABELLE=SUBSET_NAME“}
Beispiel einer Abfrage auf die Tabelle Bilanz_PLAN mit einem Mapping auf das Subset Sub_Bilanz, welches über die Spalte Bilanz-Struktur gemappt wurde.
= #"Daten optimieren"("https://meinserver:4443/coplanner", "Bilanz_PLAN", null, {"Bilanz-Struktur=Sub_Bilanz"}, null)
Dieser Abfrage kann man einen aussagekräftigen Namen verleihen und in den Visuals darauf zugreifen.
| Hinweis Pro Abfrage auf die Tabelle kann nur ein Subsetmapping gemacht werden. |
| Hinweis Diese Abfrage liefert nur korrekte Daten, wenn das Subset selbst auf der Achse oder in einem Filter ist, oder in dem Subset die Elemente nicht mehrfach vorkommen. |
Funktion Subsetzuordnung
Verfügbar ab CoP 11 HF 1.0.
CoPlanner Tabellen selbst haben kein Mapping zu den Subsets. Um dieses Mapping zu generieren und die Daten auch richtig zu berechnen, müssen die Daten speziell geladen werden.
Wählen Sie dazu im Navigator die Funktion "Subsetzuordnung" aus und wählen Sie nicht "Laden" sondern "Daten transformieren". Es öffnet sich der Power Query-Editor, in welchem die Parameter definiert werden können.
Als erstes muss die WebAppUrl des CoPlanners angegeben werden, dann die Basisdimension des Subsets und der Name des Subsets.
Über "Aufrufen" wird nun die Abfrage erstellt. Geben Sie der Abfrage einen Namen. Nun müssen in der Modellierung noch die richtigen Beziehungen aktiviert und angepasst werden. Das Subset soll nur eine Verbindung zur Subsetmapping Tabelle haben (Achtung, die ist meistens zu Beginn deaktiviert), die Verbindung vom Subset direkt zur Dimension soll entfernt werden und die Beziehung zwischen Subsetmapping und Dimension muss auf die Kreuzfilterung "Beide" umgestellt werden.
Berechtigungen verwalten (Funktion "Security (dynamic RLS)")
Berechtigungen zur Datenquelle CoPlanner werden unter den Datenquelleneinstellungen verwaltet (Benutzer/Passwort). Hier kann man Einstellen ob diese lokal, oder am Bericht gespeichert werden.
Für die richtige Berechtigung der Daten wie im CoPlanner muss man noch weitere Schritte durchführen. Das kommt daher, dass die Daten im Bericht importiert sind und von einem anderen Benutzer nicht neu geladen werden müssen. Dadurch könnte der Benutzer Dinge sehen, die er nicht sehen darf. Die Berechtigungen der Daten können über die Funktion "Security (dynamic RLS)" aus dem CoPlanner Connector aufgebaut werden. Dazu sollte der Berichtsbauer, der die Daten importiert/aktualisiert, alle Rechte haben, da die RLS (row-level security) die bestehenden Daten nur weiter einschränken kann. Berechtigungen werden in CoPlanner und in Power BI auf Stammdaten vergeben. In Power BI hat man zusätzlich die Möglichkeit einer OLS (Object level security), welche es im CoPlanner nicht gibt. Daher wird diese hier auch nicht weiter beschrieben.
| Hinweis Die Berechtigung funktioniert nur, wenn man sich mit einem Power BI Konto anmeldet, welches als E-Mail beim entsprechen Benutzer in der CoPlanner Benutzerverwaltung hinterlegt ist und der Benutzer der Rolle in Power BI zugewiesen ist. |
Beispiel: Nicht alle Berichtsempfänger dürfen alle Produktdaten sehen. Für die im CoPlanner angelegten Benutzer COPLANNER\benutzer1 mit der hinterlegten E-Mailadresse benutzer1@coplanner.com und COPLANNER\benutzer2 mit der hinterlegten E-Mailadresse benutzer2@coplanner.com sollten die Rechte für die Produktedimension angewendet werden.
Öffnen des Navigators und auswählen der Funktion Security (dynamic RLS). Dann nicht auf "Laden" klicken, sondern "Daten transformieren".
Hier dann bei den Parametern die WebAppUrl des Servers angeben, die Dimension/das Subset, für das die Berechtigung ziehen soll, und beim Benutzernamen muss man auf "Spalte auswählen..." klicken. Da man nachgelagert einen Liste von Benutzernamen angegeben kann, erwartet sich Power BI hier eine Auswahl. Wählen Sie einfach irgendetwas aus, da das im nächsten Schritt ohnehin korrigiert werden muss. Dann auf "Aufrufen" klicken.
Es entsteht eine neue Abfrage. Im Formelfenster finden Sie nun etwas in die Richtung "= #"Security (dynamic RLS)"("https://myserver.coplanner.com:4443/coplanner", "Produkte", MeineAbfrage[MeinFeld])"
Diese Abfrage muss nun angepasst werden, dass als dritter Parameter die Liste der Benutzer, für welche die Berechtigungen ziehen sollten angegeben wird. Die User sind in geschwungenen Klammern in Hochkomma durch Kommas getrennt anzugeben. In unserem Fall generieren wir hier die folgende Abfrage:
= #"Security (dynamic RLS)"("https://myserver.coplanner.com:4443/coplanner", "Produkte", {"COPLANNER\benutzer1","COPLANNER\benutzer2"})
Unter den Eigenschaften können wir einen Namen für die Abfrage vergeben z.B. Produkte_RLS.
| Hinweis Die Abfrage auf den Dimensions-/Subsetnamen ist case sensitive. |
| Hinweis Wählen Sie nur die Benutzer, für die die Security wirklich erforderlich ist, da alle zusätzlichen Daten die Performance beinträchtigen können. |
| Hinweis Es gibt auch die Möglichkeit in Power BI Parameter zu verwalten. Über diese kann man z.B. die WebAppUrl des Servers speichern und dann auf diesen Parameter zugreifen.
Ist ein Parameter definiert, kann man in der Abfrage ohne Hochkomma über den Parameternamen zugreifen. |
In der Modellierung muss das Ergebnis der Abfrage mit der Dimension verknüpft werden. Dazu erstellt man eine Beziehung zwischen der ID der Dimension (Produkte_ID) und dem DimensionElement unserer Abfrage.
Wichtig dabei ist, dass die Kreuzfilterrichtung auf Beide steht und der Sicherheitsfilter in beide Richtungen angewandt wird. In unserer Abfrage kann das DimensionElement öfter vorkommen, wenn wir die Security für mehrere Benutzer holen, ist daher n zu einer (1) Produkt_ID.
Damit die RLS nun auch wirklich für jemanden zieht, muss mindestens eine Rolle in Power BI erstellt werden.
Bei dieser Rolle muss auf die Abfragen, welche über die Funktion Security (dynamic RLS) erstellt wurden (in unserem Beispiel Produkte_RLS), ein Filter auf die MailAddress gesetzt werden. Dieser sollte dann wie folgt im Tabellenfilter-DAX-Ausdruck definiert sein: [MailAddress] = userprincipalname()
Wurde der Bericht dann veröffentlicht, muss man am Dataset des Berichts über den Kontextmenüeintrag "Sicherheit" die Benutzer als Mitglieder der Gruppe hinzufügt werden, damit die Security zieht.
Testen der Row Level Security in der Desktopversion
Die Row Level Security kann auch ohne einen weiteren Benutzer getestet werden in der Desktopvariante. Wählen sie im Menü "Anzeigen als" und hinterlegen Sie den Power BI Benutzer, welchen Sie testen wollen und wählen Sie die erstellte Rolle aus. Wenn der Benutzer eingeschränkte Rechte auf Dimensionen hat und diese auch in Power BI modelliert wurden, dann müssen diese nun ziehen.
Veröffentlichen von Berichten/Dashboards
Berichte/Dashboards werden mit dem Button Veröffentlichen freigegeben.
Danach werden das Dataset und die Visualisierung in das Power BI Service (Cloud) übertragen. Über einen Link/Frame/Power BI Online, werden die Dashboard an die Benutzer verteilt. Diese benötigen dazu die Rechte. Ein Fileshare wäre möglich, Power BI Desktop sollte jedoch nur für das Erstellen der Berichte verwendet werden. Veröffentlichte CoPlanner Daten können mithilfe eines Gateways aus der Cloud heraus aktualisiert werden. Diesem Gateway muss der Custom Connector hinterlegt werden, damit die Daten aktualisiert werden können. Damit die Row Level Security richtig funktioniert, sollte der Benutzer, der bei der Datenquelle in der Cloud hinterlegt wird, alle Rechte im CoPlanner besitzen und ein CoPlanner Benutzer sein (also Authentifizierungsmethode Basic).
Tipps um Berichte/Dashboards optimieren
- Sind nicht relevante Felder/Tabellen/Dimensionen bei "Sichtbar für Analyse (Power BI)" auf nein gesetzt?
- Löschen Sie nicht verwendete Tabellen/Abfragen.
- Per Default versucht Power BI Zeitinformationen aus Spalten zu erkennen und dafür eine interne Zeitdimension anzubieten. Dies kann Performance kosten, deshalb sollte diese Funktion deaktiviert werden. Siehe unter Daten laden -> Zeitintelligenz -> Auto. Datum/Uhrzeit
- Einschränken von Plantabellen auf einzelne Szenarien, sofern diese nicht für Vergleiche benötigt werden
- Row Level Security nur für Benutzer laden, die auch wirklich zugreifen
Caches leeren
Unter den Optionen von Power BI unter Daten laden gibt es die Möglichkeit Caches zu löschen. Dies kann sinnvoll sein, wenn man Performanceprobleme oder andere unerwartete Probleme hat bei Dingen, die schon funktioniert hatten.
FAQs
Mein Knoten wird in der Hierarchie bis zur Blattelementebene dargestellt, obwohl er Childelemente hat. Warum ist das so?
Dieses Verhalten sieht man, wenn auf dem Knoten selbst ein Wert vorhanden ist. Power BI verhält sich hier nicht wie der CoPlanner, dass nur am Knotenelement der Wert einfach summiert wird, sondern es muss hier auch von der untersten Ebene weg einen Zweig für den Wert geben.
Ich bekomme am Server eine Fehlermeldung: Too many login attempts
Das ist der Fall, wenn zu viele Abfragen generiert werden, die dann gegen den CoPlanner Server abgefahren werden. Der zulässige Wert lässt sich über die svrconfig.xml mit den Parametern LogonMaximumPerTimeframe und LogonMaximumTimeframeInSeconds steuern.
Ich bekomme in Power BI eine Tabelle/Dimension/Subset nicht angezeigt, welche aber im Modell vorhanden ist.
Über die Objektverwaltung kann im CoPlanner gesteuert werden, welche Tabellen, Subsets oder Dimensionen in Power BI sichtbar sind. Zusätzlich können hier je Objekt auch die Felder, Lookup-Tabellen, DImensionen und berechneten Felder ausgeblendet werden. Gesteuert wird das über die Einstellung "Sichtbar für Analyse (Power BI)".
Wo liegen meine CoPlanner Daten, wenn ich diese in Power BI verwende
Wird ein Dashboard mit Power BI Desktop erstellt, dann befinden sich die geladenen CoPlanner Daten im .pbix File. Wird das Dashboard in der Cloud veröffentlicht wird das Dataset mit den CoPlanner Daten auch in die Cloud verschoben und liegt somit in einem Rechenzentrum von Microsoft. Dies kann man bei der Installation hinterlegen, hierzu befragen Sie bitte ihren Administrator.
Wie können meine Daten automatisiert aktualisiert werden
Wenn sich die Daten in der Cloud befinden kann man einen Aktualisierungsjob auf der Datenquelle angeben. Zum Beispiel Daten aus CoPlanner einmal in der Nacht aktualisieren.
Gibt es eine Möglichkeit meine Berichte zu verteilen, ohne das meine Daten in die Cloud kommen?
Es gibt Möglichkeiten, die aber sehr einschränkend sind.
Variante 1:
Alle Benutzer besitzen Power BI Desktop und greifen über einen Fileshare darauf zu.
Variante 2:
Es gibt Power BI auch über die SQL Server Reporting Services. Bei dieser Variante kann man die CoPlanner Daten aber nicht aktualisieren ohne den Bericht neu hochzuladen, da Custom Connectors hier nicht unterstützt werden, weshalb diese Methode eher theoretisch ist. Zusätzlich braucht man für diese Variante eine eigene Power BI Desktop Version.
