Utiliser des partitions de table chaude et froide pour optimiser les modèles de données Power BI très volumineux

Article
04/13/2024

Cet article explique comment utiliser des partitions de table chaude et froide pour optimiser les modèles de données très volumineux. Les partitions permettent de diviser les données d’une table en sous-ensembles discrets. Les partitions ne sont pas exposées directement dans les outils de modélisation des données Power BI standard, mais vous pouvez tirer parti des méthodes de partitionnement avancées en configurant une stratégie d’actualisation incrémentielle dans Power BI Desktop. L’actualisation incrémentielle s’appuie sur des partitions, comme expliqué dans Actualisation incrémentielle et données en temps réel pour les jeux de données. Toutefois, la configuration des partitions de table chaude et froide va au-delà de ce qu’une stratégie d’actualisation incrémentielle peut accomplir et suppose une connaissance des schémas de partitionnement de table classiques et des outils XMLA.

Prérequis

En raison de la complexité relative de cette technique de partitionnement, elle convient le mieux aux utilisateurs avancés ayant une expérience dans les domaines suivants :

Présentation des concepts de partitionnement de table, du fonctionnement des partitions en mode importation, du mode DirectQuery et du mode double .
Connaissance de la création de tables hybrides à l’aide d’outils XMLA. Les tables hybrides utilisent une ou plusieurs partitions en mode importation et une partition DirectQuery .
Connaissance des exigences des fonctions DAX que vous pouvez utiliser pour spécifier un DataCoverageDefinition. Il s’agit d’une nouvelle propriété pour les partitions DirectQuery afin de décrire les données que contient la partition DirectQuery d’une table hybride afin que le moteur Power BI puisse exclure cette partition du traitement des requêtes le cas échéant. L’exclusion de la partition DirectQuery peut aider à éviter les requêtes de source de données inutiles et à améliorer les performances du traitement des requêtes DAX.
Compréhension de la différence entre les relations de table régulières et limitées. Par exemple, la fonction RELATED est utile si vous souhaitez définir la couverture des données d’une partition de table de faits en fonction des valeurs d’une table de dimension de date associée. N’oubliez pas que la partition de la table de faits est une partition DirectQuery avec une chance d’avoir une relation limitée avec la table de dates sur laquelle la fonction RELATED ne peut pas extraire les valeurs. Dans ce scénario, RELATED fonctionne uniquement si la table de dimension de date est une table double. La table de dates doit être en mode DirectQuery ou Double . Il ne peut pas s’agir d’une importation pure.

N’oubliez pas qu’une définition incorrecte DataCoverageDefinition peut entraîner des résultats incorrects, car Power BI peut exclure incorrectement la partition DirectQuery du traitement des requêtes. Veillez donc à comparer les résultats avec et sans pour DataCoverageDefinition vous assurer qu’ils s’additionneraient.

Quand utiliser des partitions de table chaudes et froides

Voici un exemple dans lequel les partitions chaudes et froides peuvent aider à affiner une table hybride pour l’analyse historique. Supposons que vous disposez d’une source de données très volumineuse, accumulée sur de nombreuses années. L’utilisation principale consiste à analyser les données les plus récentes des deux dernières années. Parfois, vous souhaitez également analyser des données plus anciennes. Peut-être avez-vous remarqué une forte augmentation récente des ventes d’année en année. Cela s’est-il déjà produit avant ? Est-ce le pic de ventes le plus élevé depuis le début du suivi des ventes ?

Sans prise en charge des partitions chaudes et froides, ce type d’analyse historique vous obligerait à importer toutes les données historiques ainsi que les données plus récentes dans le tableau des faits. Au mieux, il s’agit d’une utilisation inefficace des ressources, car l’analyse primaire n’utilise même pas les données historiques plus anciennes. Au pire, le volume de données est si important qu’il ne peut même pas être importé dans son intégralité. Vous devez soit basculer le modèle de données en mode DirectQuery et accepter une pénalité de performances par rapport au mode d’importation, soit créer des modèles distincts et forcer vos utilisateurs à basculer entre les rapports. Une table hybride avec des partitions chaudes et froides vous offre une meilleure option.

Comment utiliser des partitions de table chaudes et froides

Tout d’abord, configurez la table sales avec une partition en mode importation à chaud pour les données les plus récentes et conservez les données plus anciennes dans une partition DirectQueryfroide, comme l’illustre le diagramme suivant pour la table FactInternetSales d’un exemple de modèle de données AdventureWorks. Toutes les lignes dont la valeur OrderDateKey est supérieure ou égale à 20200101 sont importées dans le modèle de données via la partition en mode importation à chaud. Les lignes dont la valeur OrderDateKey est inférieure à 20200101 sont couvertes par la partition DirectQuery froide. Maintenant, Power BI peut fournir les cas d’usage principaux rapidement en mode importation, et vous n’avez pas besoin d’importer de grands volumes de données historiques que vous analysez seulement occasionnellement, car la partition DirectQuery est couverte.

Capture d’écran de la table Fact Internet Sales d’un exemple de modèle de données Adventure Works. Le fait que la table des ventes Internet est ouverte avec les lignes filtrées affichées.

Si vous avez un exemple d’entrepôt de données AdventureWorks et que vous souhaitez suivre, voici les étapes générales :

Créez le jeu de données. Utilisez Power BI Desktop pour créer un jeu de données et un rapport AdventureWorks. Incluez toutes les tables en mode DirectQuery pur. Ensuite, convertissez toutes les tables à l’exception de la FactInternetSales table en mode Double . Laissez la FactInternetSales table en mode DirectQuery .
Chargez le jeu de données. Utilisez un espace de travail hébergé sur Power BI Premium avec le point de terminaison XMLA activé pour les opérations d’écriture.
Mettez à jour le niveau de compatibilité. Ouvrez l’espace de travail avec votre jeu de données AdventureWorks dans SQL Server Management Studio (SSMS). Cliquez avec le bouton droit sur le jeu de données> AdventureWorksScript>Database en tant queCréer ou Remplacer par, puis sélectionnez La fenêtre Nouvel éditeur de requête. Définissez la propriété compatibilityLevel sur 1603 (ou une version ultérieure). Sélectionnez Exécuter ou appuyez sur F5. Vérifiez que l’opération se termine correctement.

Configurez les partitions de table FactInternetSales. Cliquez avec le bouton droit sur le jeu de données> AdventureWorksScript>Database en tant queCréer ou Remplacer par, puis sélectionnez La fenêtre Nouvel éditeur de requête. Remplacez l’intégralité de la section partitions par la section suivante. Veillez à mettre à jour les lignes Sql.Database pour pointer vers la base de données AdventureWorksDW dans votre environnement. Sélectionnez Exécuter ou appuyez sur F5. Vérifiez que l’opération se termine correctement.

   "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\"" 
        ] 
      } 
    } 
  ],

Traiter le modèle de données. Dans le portail Power BI, ouvrez l’espace de travail avec votre jeu de données AdventureWorks et effectuez une actualisation à la demande du jeu de données pour charger la partition d’importation avec des données.
Vérifiez que les rapports affichent des données récentes et historiques. Ouvrez votre AdventureWorks et vérifiez que le rapport est en mesure d’afficher les résultats des transactions de vente avant et après le 1er janvier 2020, comme dans la capture d’écran suivante.

Capture d’écran de deux rapports différents. L’un affiche les données de 2020 et l’autre de 2019.

Définir la couverture des données de la partition DirectQuery

La solution fonctionne en toute transparence sur les données récentes et historiques. Toutefois, par défaut, Power BI interroge toutes les partitions de table, car il ne sait pas quelles données couvrent chaque partition. Par conséquent, Power BI interroge toujours la partition DirectQuery, même pour les années que la partition DirectQuery ne couvre pas. Les données de vente sont facilement disponibles dans la partition d’importation et la partition DirectQuery ne contribue à aucune ligne, mais cette interrogation de source superflue peut toujours entraîner une charge notable sur la source de données et entraîner des retards dans le traitement des requêtes DAX. Pour éviter cette interrogation de source superflue, utilisez .DataCoverageDefinition

Comme le montre la capture d’écran suivante, le rapport Power BI envoie toujours plusieurs requêtes SQL inutiles pour 2020 à la source de données, car la requête DAX de chaque visuel amène Power BI à interroger la partition DirectQuery .

Capture d’écran des requêtes DAX.

En définissant la dataCoverageDefinition propriété sur la partition DirectQuery comme dans l’extrait de code TMSL suivant, ces requêtes SQL sont évitées. Gardez toutefois à l’esprit que vous devez actualiser le jeu de données après avoir appliqué ou modifié une définition de couverture des données. Un calcul de processus est suffisant pour évaluer la définition de la couverture des données. Si vous oubliez cette étape, les requêtes qui touchent la partition échouent avec un message d’erreur indiquant « DataCoverageDefinition de la partition DQ dans la table « [Nom de la table] » n’est pas encore calculé après une modification récente. Il doit être retraité ».

        { 
          "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}"  
                }  
        }

Comme mentionné précédemment, la dataCoverageDefinition propriété permet d’éliminer la charge inutile de la source de données. Il améliore également les performances d’analyse des données récentes, car Power BI peut désormais exclure la partition DirectQuery du traitement des requêtes DAX le cas échéant. Vous pouvez définir des expressions de couverture de données simples pour des valeurs uniques ainsi que des plages avec des opérateurs SIMPLES AND, OR et NOT. Vous pouvez également utiliser la fonction RELATED pour définir la couverture des données en fonction d’une colonne d’une table de dimension qui a une relation régulière avec la table de faits. Si une expression de couverture de données utilise des colonnes d’une table de dimension, assurez-vous que la table de dimension est en mode double . Vous pouvez également définir la couverture des données en fonction des colonnes de la table de faits elle-même. Reportez-vous au tableau suivant pour connaître les opérations prises en charge, classées en trois groupes. 

Type	Commentaires	Exemples
Prédicat unique (basé sur la valeur)	Opérateurs d’égalité, d’inégalité et d’IN Prendre en charge les tables de dimension et de faits	RELATED('Date'[Année]) = 2020 NOT RELATED('Date'[Année]) = 2020 RELATED('Date'[Année]) IN {2020, 2021, 2022} InternetSales'[SalesAmt] = CURRENCY(100.0) NOT InternetSales'[SalesAmt] = CURRENCY(100.0) InternetSales'[SalesAmt] IN {CURRENCY(100.0), CURRENCY(200.0)}
Prédicat unique (basé sur une plage)	Il peut s’agir d’opérateurs de comparaison tels que >, <, >=, <= Exiger que la table de dimension soit en mode double	RELATED('Date'[Année]) > 2020 RELATED('Date'[Année]) <= 2020
Prédicats multiples	Égalité, inégalité et comparaison Ne prend pas en charge l’opérateur IN Limité à une table de dimension unique en mode double	RELATED('Date'[Année]) > 2010 && RELATED('Date'[Année]) > 2020 RELATED('Date'[Année]) = 2020 && RELATED('Date'[Trimestre calendaire]) = 1 RELATED('Date'[Année]) > 2020 && NOT RELATED('Date'[Trimestre calendaire]) = 1 RELATED('Date'[Année]) > 2020 && RELATED('Date'[Trimestre calendaire]) < 3 RELATED('Date'[Année]) > 2020 && (RELATED('Date'[Trimestre calendaire]) = 1 \|\| RELATED('Date'[Trimestre calendaire]) = 2)

La DataCoverageDefinition propriété sur les partitions DirectQuery vous permet d’optimiser même les plus grands modèles de données Power BI en fonction des partitions à chaud en mode importation et des partitions froides en mode DirectQuery en évitant les requêtes inutiles de la source de données. Cette réduction des requêtes sources permet d’améliorer les performances des rapports lors de l’analyse des données à chaud. Il permet également de réduire la charge sur la source de données, ce qui permet d’optimiser la mise à l’échelle de votre source de données. Pourtant, gardez à l’esprit que l’optimisation d’un modèle de données à l’aide de la dataCoverageDefinition propriété est toujours un scénario avancé. Veillez à vérifier soigneusement les résultats.

Considérations et limitations

Actuellement, la DataCoverageDefinition propriété sur les partitions DirectQuery nécessite des valeurs statiques, telles que RELATED('Date'[Année]) = 2020 ou RELATED('Date'[Année]) IN {2020, 2021, 2022}. Les affectations dynamiques ne sont pas prises en charge, telles que RELATED('Date'[DateKey]) = TODAY().
L’actualisation incrémentielle avec des données en temps réel ne tire pas parti de la DataCoverageDefinition propriété. Si vous appliquez une définition de couverture des données à une partition DirectQuery (temps réel), l’actualisation incrémentielle supprime la définition de couverture des données lors de la recréation de la partition.

Partager via