Wir haben in unserer Session bei den SQL Days 2021 das Thema Datenerfassung mit Power BI thematisiert. In dieser Blogserie werden nun die Inhalte der Session im Detail vorgestellt und mit zusätzlichen Informationen angereichert. Im Teil 1 dieser Blogserie wurde die Power BI Anwendung im Direct Query Modus beleuchtet, in diesem Teil 2 zeigen wir, wie Power Apps zur Datenerfassung direkt in Power BI genutzt werden kann. Das Power Apps Visual haben wir bereits hier und hier einführend vorgestellt, daher beschreiben wir in diesem aktuellen Beitrag nicht mehr alle Schritte im Detail.

Überblick

Eine Power BI Anwendung, die in Echtzeit erfasste Plan- oder IST-Daten anzeigen soll können, benötigt eine Direct Query fähige Datenquelle, ein Datenmodell im Direct Query Modus und einen Mechanismus zum Auslösen des Page Refresh bei geänderten Daten. Als Planungsanwendung verwenden wir unsere cloud-basierte Planungslösung www.data1.io, welche SQL_ Azure als Datenbank nutzt.

Power BI selbst ist definitiv kein Tool zur Planung oder Datenerfassung. Aber es besteht die Möglichkeit, über spezielle Visuals die Direct Query fähige Datenquelle direkt zu beschreiben und über die Echtzeitaktualisierung die Daten in Power BI (near) real time anzuzeigen:

Das Power Apps Visual ist ein solches spezielles Visual, das über den Filter Context der Power BI Berichtsseite gefiltert wird und über das die User die SQL Azure Datenquelle direkt beschreiben können:

1. Erstellen des Power Apps Visual

Das Power Apps Visual ist ein Standard Visual in Power BI und kann zu den "Embedded Applications" gezählt werden:

Von zentraler Bedeutung ist die Feldbelegung des Visuals, da der aktuelle Filter Context dieser Columns an die Power Apps Anwendung übertragen wird und sich damit die Power Apps Anwendung in Abhängigkeit von der aktuellen Filterung der Power BI Berichtsseite steuern lässt:

2. Erstellen der Power Apps Anwendung

Der Klick auf den Button "Create New" öffnet das Power Apps Studio im sog. "Create Portal" Modus (https://create.powerapps.com/studio/) und es wird automatisch eine einfache Power Apps Anwendung erzeugt.

Hinweis: vor der ersten Verwendung muß eine sogenannte Umgebung angelegt werden und ggfs. die Lizenzierung geklärt werden.

2.1 Basisfunktion Gallery

Die automatisch generierte Anwendung enthält einen Screen mit einer sogenannten Gallery zur Anzeige der (ggfs. gefilterten) Datensätze aus der Power BI Anwendung. In der Regel muß die Referenz für das Anzeigefeld auf das gewünschte Feld (aus Power BI) angepaßt werden:

ThisItem.'Project Name'

Die Gallery verwendet als Datenquelle das Objekt PowerBIIntegration, dieses wird nur dann erzeugt, wenn die Power Apps Anwendung aus dem Power BI Visual heraus erzeugt wird. Das Objekt trägt die Liste der ggfs. gefilterten Werte aus der Feldbelegung des Power Apps Visuals auf der Power BI Berichtsseite. Deshalb wird die Gallery ebenfalls gefiltert, wenn in Power BI eine Selektion (bspw. in einem Slicer) getroffen wird:

Die Gallery kann mit wenigen Klicks optisch ausgebaut werden:

• Begrüßungstext
• Profilbild des Users (aus Office 365)
• Anzeige der ProjektleiterIn
• Anzeige des aktuellen Wochendatums

Hier die nachbearbeiteten Formeln aus diesem Screen (die datumsspezifischen Formeln behandeln wir weiter unten):

Begrüßungstext (ohne Wochendatum) -> "Hello " & User().FullName &", please enter your project KPIs for the current week:

Profilbild -> User().Image

Projektleitung -> ThisItem.'Project Level 2'

Datum aktuelle Woche -> DateValue(ThisItem.'Date (as Text)')

2.2 Basisfunktion Erfassungsformular

Jetzt wird ein zweiter Screen für das Erfassungsformular auf Einzeldatensatzebene der data1.io Anwendung angelegt:

Das automatisch angelegte EditForm muß direkt mit der SQL Azure Datenbank verbunden werden, da das Objekt PowerBIIntegration nicht beschreibbar ist:

Der Verbinder zur SQL Azure Datenbank wurde zuvor angelegt, es handelt sich um eine Premium Datenquelle für die eine vollwertige Power Apps Lizenz (siehe dazu weiter unten) notwendig ist (die bei Office 365 inkludierten kostenlose Pläne können hier nicht verwendet werden):

Als nächstes wird die Tabelle aus der data1.io Anwendung ausgewählt, welche die Datensätze zur Bearbeitung enthält:

Die Felder des Erfassungsformulars können in Power Apps mit wenigen Klicks hinzugefügt werden und sind auch gleich nach der Anlage voll funktionsfähig ...

... aber es sollte dennoch etwas Zeit in das Layout investiert werden:

• Anzeige des Projektnamens im Header
• Anzeige des Wochendatums im Header (dieses gehört zum Erfassungsformular)
• Textfelder "This Week" und "Next Week" zur Strukturierung
• Grundsätzlcih 3-spaltiger Aufbau -> die Text- und Kommentarfelder werden auf volle Screenbreite vergrößert
• Einfärben der Felder zur intuitiveren Erfassung

Weiters muß das Erfassungsformular mit der Auswahl in der vorgeschalteten Gallery gesynced werden, dies geschieht über einen Lookup() Befehl:

LookUp(
    'appFPVGOJIAVT.T_FACT01';
    'DIM001.Key' = Gallery1.Selected.'Project Key'
    && 'SYSTEM.IsDeleted' = false
    && 'DATE01.Key' = varRecordDate
)

Zum Abschluß wird der Navigationsbutton in der Gallery verlinkt, hier wird auch das aktuelle Wochendatum mit dem Set() Befehl in eine Variable übergeben:

Navigate('Screen Edit');;
Set(
    varRecordDate;
    DateValue(ThisItem.'Date (as Text)';"de-DE")
)

Hinweis: die Möglichkeit zur Anlage neuer (Wochen-)Datensätze ist hier noch nicht implementiert, ist aber durchaus machbar.

2.3 Herausforderung unerwünschte Zeitzonenkonvertierung bei Datumsfeldern

Ein echtes Problem ist die Übergabe von Datumswerten über das Objekt PowerBIIntegration.

Aus den ursprünglichen Datumswerten in der SQL Azure Tabelle (Datentyp = date) ...

... kommen in Power Apps die Datumswerte vom Vortag an:

ThisItem.'Date'

Dazu folgende Beobachtungen:

• Bei der Übergabe des Datumswertes über das Objekt PowerBIIntegration wird der Datumswert auf die Zeitzone des Users verschoben.
  Das ist inhaltlich völliger Unsinn: ein verspeicherter Datumswert, der nichts mit der Zeitzone des Users zu tun hat, wird auf die Zeitzone des Users umgerechnet und damit unbrauchbar gemacht.
• Im Power Apps Studio (in der Abbildung links) ist genau zu sehen, was passiert ist: der ursprüngliche Datumswert (ohne Uhrzeit) wird für den aktuellen User um 2 Stunden zurückversetzt, also auf den Vortag 22:00 Uhr.
• Jetzt kommt ein zweiter Effekt hinzu, der eine systematische Lösung nach unserem Kenntnisstand unmöglich macht:
  In der Darstellung im Power BI Visual (in der Abbildung mittig und rechts) wird die Uhrzeit abgeschnitten und nur noch der Vortag angezeigt.
  Mittels der TimeZoneOffset() Funktion ließe sich die Zeitverschiebung des jeweiligen Users feststellen, aber die Uhrzeit 22:00 ist verloren gegangen. D.h. die Zeitverschiebung von bspw. 2 Stunden würde auf eine Uhrzeit 0:00 angewendet und damit wird noch immer nicht der richtige Tag "zurückermittelt".
  Eine Lösung wäre möglich, wenn die Umgebung, in der die Anwendung aktuell ausgeführt wird, festgestellt werden könnte. Eine solche Funktion ist uns aber nicht bekannt.
• Unklar ist auch, warum das Datum in Power BI Desktop (Gebietsschema "de") im englischen Format angezeigt wird und im Power BI Cloud Service (Sprache "en") das deutsche Datumsformat angewendet wird. Das ist zwar kein Problem, stört aber die Optik ...

Bei Verwendung der Funktion DateValue() oder DateTimeValue() wird der Datumstext in ein Datum (mit oder ohne Uhrzeit) konvertiert, aber das Problem wird dabei nur im Power Apps Studio gelöst (da hier noch die Uhrzeit vorhanden ist). Hier ist jetzt genau zu sehen, daß bei der Darstellung im Power BI Visual (in der Abbildung mittig und rechts) die Uhrzeit nach der Zeitzonenverschiebung verloren gegangen ist und eine Uhrzeit von 0:00 auf das veränderte Datum angewendet wird:

DateTimeValue(ThisItem.'Date';"de-DE")

Diese beiden Funktionen könnten übrigens das Problem schon deshalb nicht lösen, weil das Gebietsschema (hier "de-DE") hard-codiert ist und die Zeitzonenverschiebung user-spezifisch passiert.

Wir haben zahlreiche Tests durchgeführt, um zu sehen, ob die Zeitzonenkonvertierung möglicherweise nur in spezifischen Settings auftritt. In allen im folgenden aufgezählten Testvarianten ist das Problem jedoch unverändert aufgetreten:

• Direct Query Mode vs. Import Mode
• Import Mode: Format der Datumsspalte Date vs. Date/time
• Datumsfeld aus einer Fakten- oder Dimensionstabelle (Date Dimension)
• Date Dimension: Berechnet (DAX) vs. Importierte Tabelle
  Lediglich Anomalie in CALENDAR()-Funktion iVm dem Power Apps Visual beobachtet: das Komma in den beiden DATE()-Funktionen wird durch einen Punkt ersetzt und löst dann natürlich einen Fehler aus …
• Auto Date/Time = True/False
  Lediglich die Übergabe der Hierarchie anstelle des Datums würde das Problem lösen, da es sich dann ebenfalls um einen (aus den Levels der Hierarchie verketteten) Text handelt, der übergeben wird. Wir verwenden die Auto Date/Time Funktion (aus Prinzip), daher scheidet diese Lösung für uns aus.
• Feldname “Date” vs. “Reporting Date” (einsilbiges Schlüsselwort vs. mehrsilbig)

Die einzige uns bekannte Lösung ist die Übergabe des Datums im Textformat.

Wir haben dazu in Power Query (im Direct Query Datenmodell ) das originäre Datumsfeld dupliziert auf ein zusätzliches Textfeld:

Dieses Feld wird über das Power Apps Visual in Power BI in die PowerBIIntegration übergeben, die Konvertierung in ein Datum in der Power Apps Anwendung ist (glücklicherweise) überhaupt kein Problem:

DateValue(ThisItem.'Date (as Text)')

Hier der Filter für die Gallery auf das höchste Datum im aktuellen Filter Context der Power BI Berichtsseite:

Filter(
    PowerBIIntegration.Data;
    DateValue('Date (as Text)') = 
    DateValue(
        First(
            Sort(
                PowerBIIntegration.Data;
                'Date (as Text)';
                Descending
            )
        ).'Date (as Text)'
    )
)

Und die Formel für die Anzeige des höchsten verfügbaren Datums im Begrüßungstext:

"Hello " & User().FullName &", please enter your project KPIs for the current week "  & 
    DateValue(
        First(
            Sort(
                PowerBIIntegration.Data;
                'Date (as Text)';
                Descending
            )
        ).'Date (as Text)'
    )
& ":"

Links zur zur Zeitzonen Thematik:

• https://powerusers.microsoft.com/t5/Building-Power-Apps/Time-Zone-Issues-with-PowerBI-Integration-PowerApps-and/m-p/1260716
• https://powerusers.microsoft.com/t5/Building-Power-Apps/Power-BI-and-PowerApps-Visual-Date-field-not-coming-across/td-p/89904
• https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/show-text-dates-times
• https://powerusers.microsoft.com/t5/Building-Power-Apps/Date-value-from-PowerBIIntegration-Data-is-set-back-to-the/td-p/763456
• https://powerusers.microsoft.com/t5/Building-Power-Apps/PowerBI-to-PowerApps-converts-DateTime-to-Date-automatically/td-p/506905
• https://powerusers.microsoft.com/t5/Building-Power-Apps/Passing-Date-value-from-PowerBI-to-PowerApps-Received-unexpected/td-p/314148

2.4 Aktualisierungsimpuls

Eine große Stärke des Power Apps Visuals ist es, daß in der Power Apps Anwendung ein Aktualisierungsimpuls an die Power BI Berichtsseite übergeben werden kann. Der Befehl wird hier an den Submit-Button des Erfassungsformulars angehängt:

SubmitForm(EditForm2);;
PowerBIIntegration.Refresh()

Damit kann der Automatic Page Refresh (und damit auch die Premium / Premium-per-User Lizenz) in diesem Szenario überflüssig werden, muß aber nicht. Der Impuls wird ja nur für die eigenen Eingaben ausgelöst, nicht aber für die Eingaben der Kollegen im Hintergrund (egal ob mit Power Apps oder anderen Clients).

3. Verwenden der Power Apps Erfassungslösung

Das Power Apps Visual überträgt also den Filter Context der Power BI Berichtsseite auf die Power Apps Anwendung, dort wird die Gallery auf die Auswahl der Projekte und auf das höchste Wochendatum in der Auswahl gefiltert. Die User können dann die verbleibenden Datensätze über das Erfassungsformular direkt in der SQL Azure Datenquelle bearbeiten. Der Aktualisierungsimpuls hinter dem Submit-Button sorgt für den Refresh der anderen Visuals auf der Power BI Berichtsseite über Direct Query:

Etwas gewöhnungsbedürftig ist der Ladevorgang des Power Apps Visuals (inkl. Anmeldevorgang) beim Aktivieren der Berichtsseite, da das Power Apps Visual etwa 5-10 sec länger braucht für die Betriebsbereitschaft als die restlichen Visuals.

Lizenzierung

Wir hatten über die Office 365 basierte Lizenzierung hier schon mal geschrieben, den aktuellen Stand diesbezüglich haben wir nicht recherchiert.

Wie bereits oben beschrieben, erfordert SQL Azure als "Premium Datenquelle" eine vollwertige Lizenzierung. Hier die aktuellen Standardpreise:

• Pro-App Plan (= concurrent User Lizenz)
• Pro-Benutzer-Plan (= named User Lizenz)

Quelle: https://powerapps.microsoft.com/de-de/pricing/

Die "Pro-App-Plan" Lizenzen werden übrigens über das Power Platform Admin Center einer Umgebung zugewiesen:

Der Zugang kann über Power Apps Maker Portal -> Zahnrad rechts oben -> Admin Portal erfolgen.

Beachten Sie bitte, daß innerhalb der Lizenzen Limits für die Anzahl der täglich maximal möglichen API Requests bestehen können.

Handling

Eine Herausforderung war für uns anfangs die nachträgliche Bearbeitung der Power Apps Anwendung, da der Filter Context der PowerBIIntegration in den folgenden Szenarien leer war und die Weiterentwicklung der Anwendung ohne Daten schwierig und mühsam wäre:

• Öffnen der Anwendung über das Power Apps Maker Portal -> funktioniert nicht
  Mehr zum Power Apps Create Portal vs. Maker Portal hier.
• Öffnen der Anwendung über die "Edit" Funktion des Power Apps Visuals in PBI Desktop oder dem PBI Cloud Service
  Öffnet die Anwendung im Power Apps Create Portal
  Dies funktioniert bei uns nicht im Firefox Browser sondern nur im Chrome und Edge Browser.
  Da wir am Rechner Firefox als Standardbrowser hinterlegt haben, funktioniert die Bearbeitung aus PBI Desktop heraus nicht.
  Im PBI Cloud Service muß daher der Report im richtigen Browser geöffnet werden und über "Edit" die Anwendung im Create Portal geöffnet werden -> funktioniert.

Hier die Edit Funktion des Power Apps Visuals (im PBI Service):

Das nachträgliches Hinzufügen von Feldern zum Visual (= Änderung der Feldbelegung) funktioniert gut.

Der Automatic Page Refresh aus der Power BI Berichtsseite wirkt (wohl über die PowerBIIntegration) auf das Power Apps Studio durch -> das ist sehr störend und muß ggfs. vor der Bearbeitung temporär deaktiviert werden.

Etwas nervig finde ich den nicht enden wollenden "Share Hinweis" im Bearbeitungsmodus (betrifft daher nur die Power User und glücklicherweise nicht die End User):

Evalierung

Das Power Apps Visual liefert eine sehr niederschwellige Möglichkeit zur Datenerfassung in Power BI. Power Apps ist nach unserer Einschätzung eine hochentwickelte Technologie, ein tolles Low-Code-Tool für "formularbasierte" Erfassungsmasken.

Kritisch sehen wir die Integration in Power BI, diese erscheint uns nicht voll ausgereift und in den letzten Jahren wurde hier auch nichts Sichtbares weiterentwickelt. Die oben beschriebene Zeitzonenthematik zeigt, daß für scheinbar Triviales viel Zeit versenkt werden kann bevor ein tragfähiger Turnaround gefunden ist.

Ein Showstopper können auch die zusätzlichen Lizenzkosten für Power Apps sein, da nach unserer Einschätzung Planungs-/Erfassungslösungen sinnvoll nur mit relationalen Datenbanken (= Premium Datenquellen) realisierbar sind. Dabei sind auch eventuelle Limits für die Anzahl der täglich maximal möglichen API Requests zu evaluieren.

Bei Verwendung von Power Apps mit einer data1.io Anwendung würden wir die Verbindung über einen ODATA Connector gegenüber dem SQL Azure Connector bevorzugen, einen solchen gibt es derzeit aber nicht (jedoch müsste so etwas über einen Custom Connector realisierbar sein).

Quellen

Power Apps:

• https://powerapps.microsoft.com/
• https://powerapps.microsoft.com/de-de/pricing/
• https://powerusers.microsoft.com/t5/Community-App-Samples/bd-p/AppFeedbackGallery

Power Apps Visual in Power BI:

• https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/powerapps-custom-visual
• https://linearis.at/blog/2019/10/24/datenerfassung-in-power-bi-mit-powerapps-einfacher-einstieg/
• https://linearis.at/blog/2019/10/31/datenerfassung-in-power-bi-mit-powerapps-echtzeitaktualisierung-mit-direct-query/
• https://powerusers.microsoft.com/t5/Building-Power-Apps/Using-PowerApps-Gallery-with-PowerBi-data/td-p/500190