Verwenden von partitionsbereiten und kalten Tabellen zum Optimieren sehr großer Power BI-Datenmodelle

Artikel
04/11/2024

In diesem Artikel wird beschrieben, wie Sie heiße und kalte Tabellenpartitionen verwenden, um sehr große Datenmodelle zu optimieren. Partitionen bieten eine Möglichkeit, die Daten einer Tabelle in diskrete Teilmengen aufzuteilen. Partitionen werden in den Standardmäßigen Power BI-Datenmodellierungstools nicht direkt verfügbar gemacht, aber Sie können erweiterte Partitionierungsmethoden nutzen, indem Sie eine Richtlinie für inkrementelle Aktualisierungen in Power BI Desktop konfigurieren. Die inkrementelle Aktualisierung basiert auf Partitionen, wie unter Inkrementelle Aktualisierung und Echtzeitdaten für Datasets erläutert. Das Konfigurieren von Heißen und Kalten Tabellenpartitionen geht jedoch über das hinaus, was eine Richtlinie für die inkrementelle Aktualisierung erreichen kann, und setzt voraus, dass Sie mit typischen Tabellenpartitionierungsschemas und XMLA-basierten Tools vertraut sind.

Voraussetzungen

Aufgrund der relativen Komplexität dieser Partitionierungsmethode eignet sie sich am besten für fortgeschrittene Benutzer mit Erfahrung in den folgenden Bereichen:

Grundlegendes zu Den Konzepten für die Tabellenpartitionierung, zur Funktionsweise von Partitionen im Importmodus, zum DirectQuery-Modus und zum dualen Modus.
Kenntnisse zum Erstellen von Hybridtabellen mit XMLA-basierten Tools. Hybridtabellen verwenden eine oder mehrere Importmoduspartitionen und eine DirectQuery-Partition .
Kenntnis der Anforderungen der DAX-Funktionen, die Sie verwenden können, um eine DataCoverageDefinitionanzugeben. Dies ist eine neue Eigenschaft für DirectQuery-Partitionen , um zu beschreiben, welche Daten die DirectQuery-Partition einer Hybridtabelle enthält, sodass die Power BI-Engine diese Partition ggf. von der Abfrageverarbeitung ausschließen kann. Das Ausschließen der DirectQuery-Partition kann dazu beitragen, unnötige Datenquellenabfragen zu vermeiden und die Leistung der DAX-Abfrageverarbeitung zu verbessern.
Grundlegendes zum Unterschied zwischen regulären und eingeschränkten Tabellenbeziehungen. Die RELATED-Funktion ist beispielsweise nützlich, wenn Sie die Datenabdeckung einer Faktentabellenpartition basierend auf den Werten aus einer verknüpften Datumsdimensionstabelle definieren möchten. Beachten Sie, dass die Faktentabellenpartition eine DirectQuery-Partition mit der Wahrscheinlichkeit einer eingeschränkten Beziehung zur Datumstabelle ist, über die die RELATED-Funktion keine Werte abrufen kann. In diesem Szenario funktioniert RELATED nur, wenn die Datumsdimensionstabelle eine duale Tabelle ist. Die Datumstabelle muss sich im DirectQuery - oder Dual-Modus befinden. Es kann sich nicht um einen reinen Import handelt.

Beachten Sie, dass eine falsch definierte Definition DataCoverageDefinition zu falschen Ergebnissen führen kann, da Power BI die DirectQuery-Partition möglicherweise fälschlicherweise von der Abfrageverarbeitung ausschließt. Vergleichen Sie also unbedingt die Ergebnisse mit und ohne, DataCoverageDefinition um sicherzustellen, dass sie sich addieren.

Verwendung von Partitionen für heiße und kalte Tabellen

Hier sehen Sie ein Beispiel, in dem heiße und kalte Partitionen dabei helfen können, eine Hybridtabelle für die verlaufsbezogene Analyse zu optimieren. Angenommen, Sie verfügen über eine sehr große Datenquelle, die sich über viele Jahre angesammelt hat. Die primäre Verwendung besteht darin, die neuesten Daten aus den letzten Jahren zu analysieren. Gelegentlich möchten Sie auch ältere Daten analysieren. Vielleicht haben Sie kürzlich einen starken Umsatzanstieg im Jahresvergleich bemerkt. Ist das schon einmal passiert? Ist es die höchste Umsatzspitze seit Beginn der Vertriebsverfolgung?

Ohne Unterstützung für heiße und kalte Partitionen würde diese Art der historischen Analyse erfordern, dass Sie alle verlaufsgeschichtlichen Daten zusammen mit den neueren Daten in die Faktentabelle importieren. Im besten Fall ist dies eine ineffiziente Nutzung von Ressourcen, da die primäre Analyse nicht einmal die älteren Verlaufsdaten verwendet. Im schlimmsten Fall ist das Datenvolumen so groß, dass es nicht einmal vollständig importiert werden kann. Sie müssen entweder das Datenmodell in den DirectQuery-Modus wechseln und eine Leistungseinbuße im Vergleich zum Importmodus akzeptieren, oder Sie können separate Modelle erstellen und Ihre Benutzer zwingen, zwischen Berichten zu wechseln. Eine Hybridtabelle mit heißen und kalten Partitionen bietet Ihnen eine bessere Option.

Verwenden von "Hot" und "Cold"-Tabellenpartitionen

Konfigurieren Sie zunächst die Tabelle sales mit einer Partition im heißen Importmodus für die neuesten Daten, und behalten Sie die älteren Daten in einer kaltenDirectQuery-Partition bei, wie das folgende Diagramm für die FactInternetSales-Tabelle eines AdventureWorks-Beispieldatenmodells veranschaulicht. Alle Zeilen, deren OrderDateKey größer oder gleich 20200101 ist, werden über die Partition im heißen Importmodus in das Datenmodell importiert. Zeilen mit einem OrderDateKey-Wert , der kleiner als 20200101 ist, werden über die kalte DirectQuery-Partition abgedeckt. Jetzt kann Power BI die primären Anwendungsfälle schnell im Importmodus bereitstellen, und Sie müssen keine großen Mengen von Verlaufsdaten importieren, die Sie nur gelegentlich analysieren, da dies in der DirectQuery-Partition abgedeckt ist.

Screenshot der Tabelle

Wenn Sie über ein AdventureWorks-Beispiel-Data Warehouse verfügen und die folgenden Schritte ausführen möchten, sind die folgenden allgemeinen Schritte aufgeführt:

Erstellen Sie das Dataset. Verwenden Sie Power BI Desktop, um ein AdventureWorks-Dataset und einen Bericht zu erstellen. Schließen Sie alle Tabellen in den reinen DirectQuery-Modus ein. Konvertieren Sie dann alle Tabellen außer der Tabelle in den FactInternetSalesDual-Modus . Belassen Sie die FactInternetSales Tabelle im DirectQuery-Modus .
Laden Sie das Dataset hoch. Verwenden Sie einen Arbeitsbereich, der auf Power BI Premium mit aktiviertem XMLA-Endpunkt für Schreibvorgänge gehostet wird.
Aktualisieren Sie den Kompatibilitätsgrad. Öffnen Sie den Arbeitsbereich mit Ihrem AdventureWorks-Dataset in SQL Server Management Studio (SSMS). Klicken Sie mit der rechten Maustaste auf das AdventureWorks-Dataset>Skriptdatenbank>alsErstellen oder Ersetzen von, und wählen Sie Neues Abfrage-Editor-Fenster aus. Legen Sie die compatibilityLevel-Eigenschaft auf 1603 (oder höher) fest. Wählen Sie Ausführen aus, oder drücken Sie F5. Stellen Sie sicher, dass der Vorgang erfolgreich abgeschlossen wurde.

Konfigurieren Sie die FactInternetSales-Tabellenpartitionen. Klicken Sie mit der rechten Maustaste auf das AdventureWorks-Dataset>Skriptdatenbank>alsErstellen oder Ersetzen von, und wählen Sie Neues Abfrage-Editor-Fenster aus. Ersetzen Sie den gesamten Partitionsabschnitt durch den folgenden Abschnitt. Aktualisieren Sie die Sql.Database-Zeilen so, dass sie auf die AdventureWorksDW-Datenbank in Ihrer Umgebung verweisen. Wählen Sie Ausführen aus, oder drücken Sie F5. Stellen Sie sicher, dass der Vorgang erfolgreich abgeschlossen wurde.

   "partitions": [ 
    { 
      "name": "FactInternetSales-DQ-Partition", 
      "mode": "directQuery", 
      "dataView": "full", 
      "source": { 
        "type": "m", 
        "expression": [ 
          "let", 
          "    Source = Sql.Database(\"demo.database.windows.net\", \"AdventureWorksDW\"),", 
          "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
          "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] < 20200101)", 
          "in", 
          "    #\"Filtered Rows\"" 
        ] 
      } 
    }, 
    { 
      "name": "FactInternetSales-Import-Partition", 
      "mode": "import", 
      "source": { 
        "type": "m", 
        "expression": [ 
          "let", 
          "    Source = Sql.Database(\"demo.database.windows.net\", \"AdventureWorksDW\"),", 
          "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
          "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] >= 20200101)", 
          "in", 
          "    #\"Filtered Rows\"" 
        ] 
      } 
    } 
  ],

Verarbeiten sie das Datenmodell. Öffnen Sie im Power BI-Portal den Arbeitsbereich mit Ihrem AdventureWorks-Dataset , und führen Sie eine bedarfsgesteuerte Aktualisierung des Datasets durch, um die Importpartition mit Daten zu laden.
Stellen Sie sicher, dass die Berichte aktuelle und verlaufsbezogene Daten enthalten. Öffnen Sie AdventureWorks , und vergewissern Sie sich, dass der Bericht Ergebnisse für Verkaufstransaktionen vor und nach dem 1. Januar 2020 anzeigen kann, wie im folgenden Screenshot dargestellt.

Screenshot: Zwei verschiedene Berichte. Eine zeigt Daten aus 2020 und eine daten aus 2019.

Definieren der Datenabdeckung der DirectQuery-Partition

Die Lösung funktioniert nahtlos über aktuelle und historische Daten. Standardmäßig fragt Power BI jedoch alle Tabellenpartitionen ab, da nicht bekannt ist, welche Daten die einzelnen Partitionen umfassen. Daher fragt Power BI die DirectQuery-Partition auch für die Jahre ab, die die DirectQuery-Partition nicht abdeckt. Die Verkaufsdaten sind in der Importpartition leicht verfügbar, und die DirectQuery-Partition trägt keine Zeilen bei, aber diese überflüssige Quellabfrage kann weiterhin zu einer spürbaren Auslastung der Datenquelle und zu Verzögerungen bei der DAX-Abfrageverarbeitung führen. Um diese überflüssige Quellabfrage zu vermeiden, verwenden Sie .DataCoverageDefinition

Wie der folgende Screenshot zeigt, sendet der Power BI-Bericht weiterhin mehrere unnötige SQL-Abfragen für 2020 an die Datenquelle, da die DAX-Abfrage jedes Visuals dazu führt, dass Power BI die DirectQuery-Partition abfragt.

Screenshot: DAX-Abfragen.

Durch Festlegen der dataCoverageDefinition Eigenschaft für die DirectQuery-Partition wie im folgenden TMSL-Codeausschnitt werden diese SQL-Abfragen vermieden. Beachten Sie jedoch, dass Sie das Dataset aktualisieren müssen, nachdem Sie eine Datenabdeckungsdefinition angewendet oder geändert haben. Eine Neuberechnung des Prozesses reicht aus, um die Datenabdeckungsdefinition auszuwerten. Wenn Sie diesen Schritt vergessen, schlagen Abfragen, die die Partition berühren, mit einer Fehlermeldung fehl, die besagt, dass "DataCoverageDefinition der DQ-Partition in der Tabelle '[Tabellenname]' nach einer kürzlichen Änderung noch nicht berechnet wird. Es muss erneut verarbeitet werden."

        { 
          "name": "FactInternetSales-DQ-Partition", 
          "mode": "directQuery", 
          "dataView": "full", 
          "source": { 
            "type": "m", 
            "expression": [ 
              "let", 
              "    Source = Sql.Database(\"demopm.database.windows.net\", \"AdventureWorksDW2020\"),", 
              "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
              "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] < 20200101)", 
              "in", 
              "    #\"Filtered Rows\"" 
            ] 
          },  
"dataCoverageDefinition": {  
                  "description": "DQ partition with all sales from 2017, 2018, and 2019.",  
                  "expression": "RELATED('DimDate'[CalendarYear]) IN {2017,2018,2019}"  
                }  
        }

Wie bereits erwähnt, trägt die dataCoverageDefinition -Eigenschaft dazu bei, unnötige Datenquellenlasten zu vermeiden. Außerdem wird die Analyseleistung für aktuelle Daten verbessert, da Power BI jetzt die DirectQuery-Partition nach Bedarf von der DAX-Abfrageverarbeitung ausschließen kann. Sie können einfache Datenabdeckungsausdrücke für einzelne Werte sowie Bereiche mit einfachen OPERATOREN UND, OR und NOT definieren. Sie können auch die RELATED-Funktion verwenden, um die Datenabdeckung basierend auf einer Spalte aus einer Dimensionstabelle zu definieren, die eine reguläre Beziehung zur Faktentabelle aufweist. Wenn ein Datenabdeckungsausdruck Spalten aus einer Dimensionstabelle verwendet, stellen Sie sicher, dass sich die Dimensionstabelle im dualen Modus befindet. Sie können die Datenabdeckung auch basierend auf Spalten aus der Faktentabelle selbst definieren. In der folgenden Tabelle finden Sie informationen zu unterstützten Vorgängen, die in drei Gruppen unterteilt sind. 

Typ	Kommentare	Beispiele
Einzelnes Prädikat (wertbasiert)	Gleichheits-, Ungleichheits- und IN-Operatoren Unterstützung von Dimensions- und Faktentabellen	RELATED('Date'[Year]) = 2020 NOT RELATED('Date'[Year]) = 2020 RELATED('Date'[Year]) IN {2020, 2021, 2022} InternetSales'[SalesAmt] = CURRENCY(100.0) NOT InternetSales'[SalesAmt] = CURRENCY(100.0) InternetSales'[SalesAmt] IN {CURRENCY(100.0), CURRENCY(200.0)}
Einzelnes Prädikat (bereichsbasiert)	Kann Vergleichsoperatoren wie >, <, >=, <= sein. Erfordern, dass sich die Dimensionstabelle im Dual-Modus befindet	RELATED('Date'[Year]) > 2020 RELATED('Date'[Year]) <= 2020
Mehrere Prädikate	Gleichheit, Ungleichheit und Vergleich Der IN-Operator wird nicht unterstützt. Beschränkt auf eine Tabelle mit einer einzelnen Dimension im dualen Modus	RELATED('Date'[Year]) > 2010 && RELATED('Date'[Year]) > 2020 RELATED('Date'[Year]) = 2020 && RELATED('Date'[Calendar Quarter]) = 1 RELATED('Date'[Year]) > 2020 && NOT RELATED('Date'[Calendar Quarter]) = 1 RELATED('Date'[Year]) > 2020 && RELATED('Date'[Calendar Quarter]) < 3 RELATED('Date'[Year]) > 2020 && (RELATED('Date'[Calendar Quarter]) = 1 \|\| RELATED('Date'[Calendar Quarter]) = 2)

Mit DataCoverageDefinition der -Eigenschaft für DirectQuery-Partitionen können Sie selbst die größten Power BI-Datenmodelle basierend auf heißen Partitionen im Importmodus und kalten Partitionen im DirectQuery-Modus optimieren, indem Sie unnötige Abfragen der Datenquelle vermeiden. Diese Reduzierung der Quellabfrage trägt dazu bei, die Berichtsleistung bei der Analyse heißer Daten zu steigern. Es trägt auch dazu bei, die Last für die Datenquelle zu verringern, und auf diese Weise hilft, die Skalierung Ihrer Datenquelle zu maximieren. Beachten Sie jedoch, dass die Optimierung eines Datenmodells mithilfe der dataCoverageDefinition -Eigenschaft immer noch ein fortgeschrittenes Szenario ist. Überprüfen Sie die Ergebnisse sorgfältig.

Überlegungen und Einschränkungen

Derzeit erfordert die DataCoverageDefinition Eigenschaft für DirectQuery-Partitionen statische Werte, z. B. RELATED('Date'[Year]) = 2020 oder RELATED('Date'[Year]) IN {2020, 2021, 2022}. Dynamische Zuweisungen werden nicht unterstützt, z. B. RELATED('Date'[DateKey]) = TODAY().
Bei der inkrementellen Aktualisierung mit Echtzeitdaten wird die DataCoverageDefinition -Eigenschaft nicht genutzt. Wenn Sie eine Datenabdeckungsdefinition auf eine DirectQuery-Partition (Echtzeit) anwenden, löscht die inkrementelle Aktualisierung die Datenabdeckungsdefinition beim Erneuten Erstellen der Partition.

Freigeben über