Wir haben dieses Jahr 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 wird der Direct Query Modus beleuchtet, der Voraussetzung ist für die Anzeige der erfassten Daten in Echtzeit. Über den Direct Query Modus haben wir schon hier und hier geschrieben, in diesem neuen Beitrag geht es speziell um Planungsanwendungen und um die mittlerweile stark weiterentwickelten DQ Funktionen.

Überblick

Eine Power BI Anwendung, die in Echtzeit (vorerst außerhalb von Power BI) erfasste Echtdaten anzeigen soll können, benötigt eine Direct Query fähige Datenquelle, ein Datenmodell im Direct Query Modus und einen Mechanismus zum Auslösen eines Page Refreshes. Als Planungsanwendung verwenden wir unsere cloud-basierte Planungslösung www.data1.io mit der darunter liegenden SQL Azure Datenbank:

1. Direct Query fähige Datenquelle

In Power BI können insbesondere relationale Datenquellen im sogenannten Direct Query Modus angebunden werden, wir verwenden in diesem Blogbeitrag eine SQL Azure Datenbank:

Konkret verwenden wir die SQL Datenbank des "Weekly Project Status" Showcase unserer cloud-basierten Planungslösung www.data1.io als Datenquelle:

Der Showcase ist öffentlich zugänglich, registrieren Sie sich, um selbst Daten in den Showcase einzugeben:

data1.io liefert nicht nur eine cloud-basierte relationale Datenbank sondern eine Komplettlösung für die dezentrale Planung und Datensammlung:

• Excel-basierte Datenerfassung
• Echtzeit Aggregation bei Doppelklick im Strukturbaum
• Workflow Steuerung für den Planungs-/Datensammlungsprozess
• Automatisierte Verwertung der gesammelten Daten über ODATA
• Sauberes Datenmodell mit Fakten- und Dimensionstabellen (Star Schema)

2. Direct Query / Composite Model

Die Anbindung der Tabellen aus der SQL Datenbank erfolgt mit dem Azure SQL Connector in Power BI:

Entscheidend ist die Auswahl des Direct Query Modus, da dieser nachträglich nicht mehr aktiviert werden kann:

Die Tabellen werden - ganz normal wie im auch Import Mode - zum Datenmodell verknüpft (zu den Modellierungsoptionen iZm Direct Query siehe letztes Kapitel):

Das Setting Assume referential integrity sollte jedenfalls aktiviert werden, da die DQ Abfragen mit diesem Setting performanter sind (Inner Join statt Outer Join) und nicht "zur Reserve" eine Blank Row auf der 1-Seite generiert wird (Voraussetzung dafür ist, daß SQL seitig sichergestellt ist, daß die Dimensionstabelle vollständig ist):

Nach dem Upload der Anwendung in den PBI Cloud Service sind die Credentials für die Verbindung zur SQL Azure DB einzugeben, in unserem Fall hier darf die Option nicht aktiviert werden, da der Zugriff auf die Datenquelle nicht mit den Credentials des jeweiligen End Users sondern mit dem immer gleichen DB User erfolgen muß:

Auf das DQ Datenmodell wird ein "ganz normaler" Power BI Report (oder Dashboard) aufgebaut:

3. Automatic Page Refresh

Ohne Automatic Page Refresh muß der jeder User selbst den Refresh Button drücken, um garantiert die letztgültigen Daten aus der Planungs-/Erfassungsanwendung zu sehen:

Für die automatische Aktualisierung eines offenen Power BI Reports gibt es grundsätzlich drei Methoden:

• Methode #1: Auto Page Refresh
  Kurze Intervalle erzeugen hohen Traffic auf die Datenquelle und reduzieren die Usability durch Aktualisierungsblockaden
  Sinnvoll nur mit Premium / Premium-per-User (PPU) Lizenz nutzbar
  
• Methode #2: Change Detection
  Sehr viel effizienter als Methode 1
  Nur bei Premium / Premium-per-User (PPU) Lizenz verfügbar
  
• Methode #3: Auslösen eines Refresh Impulses aus der Erfassungsanwendung heraus
  Derzeit (nach unserem Wissenstand) nur in Power Apps möglich

Methode #1 und #2 werden über den Schieberegler Page Refresh (= Setting auf Page Ebene) aktiviert, diese Option wird nur in Datenmodellen mit zumindest einer Tabelle im Direct Query / Dual Mode verfügbar:

Bei Methode #1 Auto Page Refresh wird lediglich der Aktualisierungsintervall festgelegt, in dem die Berichtsseite automatisch aktualisiert werden soll:

Bei Methode #2 Change Detection ...

... wird im Editor ein Measure bestimmt, das im angegebenen Intervall auf Änderungen abgefragt wird:

Der Change Indicator selbst muß zuverlässig sämtliche Änderungen in der Datenquelle abfragen, in der data1.io Datenbank reicht es, den letzten Datensatz-Timestamp abzufragen:

Change Indicator = MAX('Fact Table'[SYSTEM.Created.UTC])

Alternativ würde (bei fehlendem Timestamp) auch ein Measure nach dieser Bauart den Zweck erfüllen:

Change Indicator = 
SUM('Fact Table'[Hours]) + SUM('Fact Table'[Items]) + SUM('Fact Table'[Completion])
+ SUM('Fact Table'[Hours nW]) + SUM('Fact Table'[Items nW]) + SUM('Fact Table'[Completion nW])
+ SUMX('Fact Table',LEN('Fact Table'[Comment This Week]))
+ SUMX('Fact Table',LEN('Fact Table'[Comment Next Week]))

Das Measure, das als Change Indictor angegeben wird, erhält ein spezielles Symbol in der Feldliste, um die besondere Rolle in der Anwendung zu unterstreichen:

Mit dem aktivem Page Refresh (auf Basis Change Detection) braucht jetzt im Power BI Report der Refresh Button nicht mehr gedrückt zu werden, die Aktualisierung erfolgt jetzt automatisch nach jeder Änderung in der SQL Datenbank:

Sehr wichtig ist die Feststellung, daß der Aktualisierungsintervall in Power BI Desktop beliebig kurz konfiguriert werden kann, dieser im Power BI Cloud Service dann „heruntergestuft“ wird auf das in der jeweiligen Lizenz mögliche! In der Pro Lizenzierung ist der kürzeste Aktualisierungsintervall 30 min, das ist für Planungsanwendungen idR unbrauchbar. :) Daher ist davon auszugehen, daß eine Premium Lizenz oder Premium-per-User (PPU) Lizenz für die automatische Berichtsaktualisierung notwendig sein wird:

Genau genommen legt der Power BI Tenant Admin im Admin Portal den kürzesten möglichen Aktualisierungsintervall fest (dieser kann aber natürlich nicht kürzer sein als der in der jeweligen Lizenz mögliche Mindestwert). Wird also in Power BI Desktop 5 sec eingestellt und der Tenant Admin hat 20 sec als kürzesten Intervall definiert, so wird die Anwendung nach dem Upload in den PBI Cloud Service im 20 sec Intervall aktualisiert:

Evaluierung

Echtzeit Anwendungen in Power BI sind anspruchsvoll, es sind folgende Besonderheiten im Direct Query Datenmodell zu handhaben:

1. Die erfassungsrelevanten Tabellen müssen im Direct Query angebunden werden, um die Echtzeitaktualisierung zu ermöglichen.
   Leider zählt bisher ODATA in Power BI nicht zu den DQ fähigen Datenquellen, obwohl die Technologie dies beherrscht.
2. Bei Problemen mit der Abfrageperforamnce können
   über das Setting Storage Mode (siehe Screenshot oben) auf den Dual Mode (= Direct Query mit Caching) gewechselt werden
   über die Query Reduction Settings die Anzahl der Aktualisierungen bei Filterselektionen reduziert werden
3. Es können Tabellen im Direct Query Modus (= Planungsdaten) mit Tabellen im Import Modus (= detaillierte IST-Daten) zu einem sog. Composite Model kombiniert werden.
4. Zu beachten ist, daß sog. Cross Island Beziehungen - also Beziehungen zwischen einer DQ und einer Importtabelle - schwach sind.
   d.h. die Performance der Beziehungen ist gering und es fehlt der Blank Row Mechanismus.
5. Das Setting Assume referential integrity auf der Beziehung sollte jedenfalls aktiviert werden, da die Abfragen mit diesem Setting wesentlich performanter sind (Inner Join statt Outer Join) und nicht "zur Reserve" eine Blank Row auf der 1-Seite generiert wird (Voraussetzung dafür ist, daß SQL seitig sichergestellt ist, daß die Dimensionstabelle vollständig ist):

Auch bei der Gestaltung von Planungs-/Erfassungsanwendungen sind Modellierungsthemen zu lösen:

• Umgang mit unterschiedlichen Granularitäten (Planung idR auf höherem Aggregationsniveau als IST-Daten)
• Multi-Fakten (Ist- und Plandaten in getrennten Tabellen) vs. Star Schema (Ist- und Plandaten in der gleichen Tabelle)

Nicht zuletzt löst auch die automatische Berichtsaktualisierung weitreichende Entscheidungen aus:

• Kann in der Erfassungsanwendung nach einer Eingabe ein Aktualisierungsimpuls für Power BI ausgelöst werden, dann sollte diese Funktion genutzt werden (uns derzeit nur in Power Apps bekannt).
• Die intervallbasierte Aktualisierung ist realistisch nur mit einer Premium / Premium-per-User (PPU) Lizenz umsetzbar (Power BI Pro >= alle 30 min !!!)
• Dabei ist ganz klar der Change Detection der Vorzug zu geben, da damit die Belastung der Datenquelle als auch der End User deutlich geringer ausfällt.

Fazit

Die Umsetzung einer Echtzeit-Anwendung für Planungs- und Erfassungslösungen ist nicht trivial, die Mechanismen in Power BI sind mittlerweile aber weit entwickelt und nach unserer Einschätzung absolut praxistauglich.

Quellen

Power BI Direct Query:

• https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about
• https://docs.microsoft.com/en-us/power-bi/connect-data/power-bi-data-sources
• https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-assume-referential-integrity

Power BI Auto Page Refresh:

• https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-automatic-page-refresh
• https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data
• https://docs.microsoft.com/en-us/power-bi/admin/service-premium-per-user-faq

data1.io Cloud Service (zur dezentrale Datensammlung):

• https://data1.io
• https://data1.io/releases
• https://data1.io/pricing
• https://app.data1.io