Actualisation incrémentielle avancée et données en temps réel avec le point de terminaison XMLA

Les modèles sémantiques d’une capacité Premium avec le point de terminaison XMLA activé pour les opérations de lecture-écriture permettent une actualisation plus avancée, la gestion des partitions et des déploiements de métadonnées uniquement via l’outil, les scripts et la prise en charge des API. De plus, les opérations d’actualisation via le point de terminaison XMLA ne sont pas limitées à 48 actualisations par jour et la limite de temps d’actualisation planifiée n’est pas imposée.

Partitions

Les partitions de table de modèle sémantique ne sont pas visibles et ne peuvent pas être gérées à l’aide de Power BI Desktop ou du service Power BI. Pour les modèles d’un espace de travail affecté à une capacité Premium, les partitions peuvent être gérées via le point de terminaison XMLA à l’aide d’outils tels que SQL Server Management Studio (SSMS), l’éditeur tabulaire open source, scripté avec le langage TMSL (Tabular Model Scripting Language) et par programmation avec le modèle objet tabulaire (TOM).

Lorsque vous publiez d’abord un modèle sur le service Power BI, chaque table du nouveau modèle comporte une partition. Pour les tables sans stratégie d’actualisation incrémentielle, cette partition contient toutes les lignes de données pour cette table, sauf si des filtres ont été appliqués. Pour les tables avec une stratégie d’actualisation incrémentielle, il n’existe qu’une partition initiale, car Power BI n’a pas encore appliqué la stratégie. Vous configurez la partition initiale dans Power BI Desktop lorsque vous définissez le filtre de plage de dates/heures pour votre table en fonction des paramètres RangeStart et RangeEnd, ainsi que tous les autres filtres appliqués dans Power Query Editor. Cette partition initiale contient uniquement les lignes de données qui répondent à vos critères de filtre.

Lorsque vous effectuez la première opération d'actualisation, les tables sans politique d'actualisation incrémentielle actualisent toutes les lignes contenues dans la partition unique par défaut de cette table. Pour les tables avec une stratégie d’actualisation incrémentielle, les partitions d’actualisation et historiques sont automatiquement créées et les lignes sont chargées dans ces dernières en fonction de la date/heure de chaque ligne. Si la stratégie d’actualisation incrémentielle inclut l’obtention des données en temps réel, Power BI ajoute également une partition DirectQuery à la table.

Cette première opération d’actualisation peut prendre un certain temps en fonction de la quantité de données qui doit être chargée à partir de la source de données. La complexité du modèle peut également être un facteur important, car les opérations d’actualisation doivent effectuer un traitement et un recalcul supplémentaires. Cette opération peut être amorcée. Pour plus d’informations, consultez Empêcher les délais d’expiration lors de l’actualisation complète initiale.

Les partitions sont créées pour et nommées par granularité de période : années, trimestres, mois et jours. La ou les partitions les plus récentes, la ou les partitions d’actualisation, contiennent des lignes dans la période d’actualisation que vous spécifiez dans la stratégie. Les partitions historiques contiennent des lignes par période complète jusqu’à la période d’actualisation. Si le temps réel est activé, une partition DirectQuery récupère toutes les modifications de données qui se sont produites après la date de fin de la période d’actualisation. La granularité de l’actualisation et des partitions historiques dépend des périodes d’actualisation et historiques (stockées) que vous choisissez lors de la définition de la stratégie.

Par exemple, imaginons que la date du jour est le 2 février 2021 et que notre table FactInternetSales à la source de données contient des lignes jusqu’à aujourd’hui, et que notre stratégie spécifie d’inclure les modifications en temps réel, d’actualiser les lignes dans la dernière période d’actualisation d’une journée et de stocker les lignes de la période historique des trois dernières années. Dans ce cas, avec la première opération d’actualisation, une partition DirectQuery est créée pour les modifications à venir, une nouvelle partition d’importation est créée pour les lignes d’aujourd’hui, et une partition historique est créée pour hier, une période de journée entière correspondant au 1er février 2021. Une partition historique est créée pour la période du mois précédent (janvier 2021), une partition historique est créée pour la période de l’année précédente (2020) et des partitions historiques sont créées pour les périodes des années entières 2019 et 2018. Aucune partition n’est créée pour le trimestre entier, car le premier trimestre de 2021 n’est pas encore terminé.

À chaque opération d’actualisation, seules les partitions de la période d’actualisation sont actualisées et le filtre de date de la partition DirectQuery est mis à jour pour inclure uniquement les modifications qui se produisent après la période d’actualisation actuelle. Une nouvelle partition d’actualisation est créée pour les nouvelles lignes avec une nouvelle date/heure dans la période d’actualisation mise à jour, et les lignes existantes avec une date/heure déjà comprise dans les partitions existantes de la période d’actualisation sont actualisées avec des mises à jour. Les lignes dont la date/heure est antérieure à la période d’actualisation ne sont plus actualisées.

À mesure que les périodes entières se ferment, les partitions sont fusionnées. Par exemple, si une période d’actualisation d’une journée et une période de stockage historique de trois ans sont spécifiées dans la stratégie, le premier jour du mois, les partitions de chaque jour du mois précédent sont fusionnées dans une partition de mois. Le premier jour d’un nouveau trimestre, les partitions des trois mois précédents sont fusionnées dans une partition de trimestre. Le premier jour d’une nouvelle année, les partitions des quatre trimestres précédents sont fusionnées dans une partition d’année.

Un modèle conserve toujours les partitions pour l’ensemble de la période de magasin historique ainsi que les partitions de période entières jusqu’à la période d’actualisation actuelle. Dans l’exemple, trois années complètes de données historiques sont conservées dans des partitions 2018, 2019, 2020 et des partitions pour la période mensuelle 2021Q101, la période journalière 2021Q10201 et la partition de la période d’actualisation du jour actuel. Comme l’exemple conserve les données historiques pendant trois ans, la partition 2018 est conservée jusqu’à la première actualisation du 1er janvier 2022.

Avec l’actualisation incrémentielle Power BI et les données en temps réel, le service gère la gestion des partitions pour vous en fonction de la stratégie. Le service peut prendre en charge pour vous toute la gestion des partitions, mais en utilisant des outils via le point de terminaison XMLA, vous pouvez actualiser sélectivement des partitions individuelles, en séquence ou en parallèle.

Actualiser la gestion avec SQL Server Management Studio

SQL Server Management Studio (SSMS) peut être utilisé pour afficher et gérer les partitions créées par l’application de stratégies d’actualisation incrémentielle. En utilisant SSMS, vous pouvez par exemple actualiser une partition d’historique spécifique qui ne fait pas partie d’une période d’actualisation incrémentielle pour effectuer une mise à jour rétroactive sans devoir actualiser toutes les données de l’historique. SSMS peut également être utilisé lors de l’amorçage pour charger des données historiques pour les modèles volumineux en ajoutant/actualisant incrémentiellement des partitions historiques dans des lots.

Remplacer le comportement d’actualisation incrémentielle

Avec SSMS, vous avez également plus de contrôle sur la façon d’appeler des actualisations en utilisant le Langage TMSL (Tabular Model Scripting Language) et le Modèle d’objet tabulaire (Tabular Object Model, TOM). Par exemple, dans SSMS, dans l’Explorateur d’objets, cliquez avec le bouton droit sur une table, puis sélectionnez l’option de menu Traiter la table, puis sélectionnez le bouton Script pour générer une commande d’actualisation TMSL.

Ces paramètres peuvent être utilisés avec dans la commande d’actualisation TMSL pour remplacer le comportement d’actualisation incrémentielle par défaut :

• applyRefreshPolicy. Si une table a une stratégie d’actualisation incrémentielle définie, applyRefreshPolicy détermine si la stratégie est appliquée ou non. Si la stratégie n’est pas appliquée, une opération Traiter entièrement laissera les définitions des partitions inchangées et toutes les partitions de la table sont entièrement actualisées. La valeur par défaut est true.

• effectiveDate. Si une stratégie d’actualisation incrémentielle est appliquée, elle doit connaître la date actuelle pour déterminer les plages de fenêtre dynamique pour l’actualisation incrémentielle et les périodes historiques. Le paramètre effectiveDate vous permet de remplacer la date actuelle. Ce paramètre est pratique pour les tests, les démonstrations et les scénarios métier où les données sont actualisées de façon incrémentielle jusqu’à une date dans le passé ou le futur, par exemple, des budgets futurs. La valeur par défaut est la date actuelle.

{ 
  "refresh": {
    "type": "full",

    "applyRefreshPolicy": true,
    "effectiveDate": "12/31/2013",

    "objects": [
      {
        "database": "IR_AdventureWorks", 
        "table": "FactInternetSales" 
      }
    ]
  }
}

Pour en savoir plus sur le remplacement du comportement d’actualisation incrémentielle par défaut avec TMSL, consultez la Commande d’actualisation.

Garantir des performances optimales

À chaque opération d’actualisation, le service Power BI peut envoyer des requêtes d’initialisation à la source de données pour chaque partition d’actualisation incrémentielle. Vous pouvez peut-être améliorer les performances de l’actualisation incrémentielle. Pour ce faire, réduisez le nombre de requêtes d’initialisation en vérifiant la configuration suivante :

• La table pour laquelle vous configurez l’actualisation incrémentielle doit obtenir les données d’une source de données unique. Si la table obtient les données de plusieurs sources de données, le nombre de requêtes envoyées par le service pour chaque opération d’actualisation est multiplié par le nombre de sources de données, ce qui peut réduire les performances de l’actualisation. Vérifiez que la requête de la table d’actualisation incrémentielle s’applique à une source de données unique.
• Pour les solutions avec l’actualisation incrémentielle des partitions d’importation et des données en temps réel avec requête directe, toutes les partitions doivent interroger les données d’une source de données unique.
• Si vos exigences de sécurité le permettent, définissez le paramètre de niveau de confidentialité de la source de données sur Organisationnel ou Public. Par défaut, le niveau de confidentialité est Privé, mais ce niveau peut empêcher l’échange de données avec d’autres sources cloud. Pour définir le niveau de confidentialité, sélectionnez le menu Autres options, puis choisissez Paramètres>Informations d’identification d’identification>Modifier les informations d’identification>Paramètre de niveau de confidentialité pour cette source de données. Si le niveau de confidentialité est défini dans le modèle Power BI Desktop avant la publication sur le service, il n’est pas transféré dans le service au moment de la publication. Vous devez toujours le définir dans les paramètres de modèle sémantique dans le service. Pour en savoir plus, consultez Niveaux de confidentialité.
• Si vous utilisez une passerelle de données locale, veillez à utiliser la version 3000.77.3 ou une version ultérieure.

Empêcher les délais d’expiration lors de l’actualisation complète initiale

Après avoir publié sur le service Power BI, l’opération d’actualisation complète initiale pour le modèle crée des partitions pour la table d’actualisation incrémentielle, charge et traite les données historiques pendant toute la période définie dans la stratégie d’actualisation incrémentielle. Pour certains modèles qui chargent et traitent de grandes quantités de données, la durée pendant laquelle l’opération d’actualisation initiale prend peut dépasser la limite de temps d’actualisation imposée par le service ou une limite de temps de requête imposée par la source de données.

L’amorçage de l’opération d’actualisation initiale permet au service de créer des objets de partition pour la table d’actualisation incrémentielle, mais pas de charger et de traiter les données d’historique dans les partitions. SSMS est alors utilisé pour traiter sélectivement des partitions. Selon la quantité de données chargées pour chaque partition, vous pouvez traiter chaque partition séquentiellement ou par petits lots afin de réduire le risque qu’une ou plusieurs de ces partitions provoquent un dépassement du délai d’expiration. Les méthodes suivantes fonctionnent pour toutes les sources de données.

Appliquer une stratégie d’actualisation

L’outil open source Tabular Editor 2 offre un moyen simple d’amorcer une opération d’actualisation initiale. Après avoir publié un modèle avec une stratégie d’actualisation incrémentielle définie pour celle-ci de Power BI Desktop au service, connectez-vous au modèle à l’aide du point de terminaison XMLA en mode Lecture/écriture. Exécutez Appliquer la stratégie d’actualisation sur la table d’actualisation incrémentielle. Quand seule la stratégie est appliquée, les partitions sont créées, mais aucune donnée n’y est chargée. Ensuite, connectez-vous avec SSMS pour actualiser les partitions séquentiellement ou par lots pour charger et traiter les données. Pour plus d’informations, consultez Actualisation incrémentielle dans la documentation de l’éditeur tabulaire.

Filtre Power Query pour les partitions vides

Avant de publier le modèle sur le service, dans l’Éditeur Power Query, ajoutez un autre filtre à la colonne ProductKey qui exclut toutes les valeurs différentes de 0, ou en excluant toutes les données de la table FactInternetSales.

Une fois que vous avez sélectionné Fermer et appliquer dans l’Éditeur Power Query, défini la stratégie d’actualisation incrémentielle et enregistré le modèle, le modèle est publié sur le service. À partir du service, l’opération d’actualisation initiale est exécutée sur le modèle. Les partitions pour la table FactInternetSales sont créées en fonction de la stratégie, mais aucune donnée n’est chargée et traitée, car toutes les données sont filtrées.

Une fois l’opération d’actualisation initiale terminée, de retour dans l’Éditeur Power Query, l’autre filtre sur la colonne ProductKey est supprimé. Une fois que vous sélectionné Fermer et appliquer dans l’Éditeur Power Query et enregistré le modèle, le modèle n’est pas republié. Si le modèle est à nouveau publié, il remplace les paramètres de stratégie d’actualisation incrémentielle et force une actualisation complète sur le modèle lorsqu’une opération d’actualisation ultérieure est effectuée à partir du service. Au lieu de cela, effectuez un déploiement de métadonnées uniquement à l’aide du kit de ressources ALM (Application Lifecycle Management) qui supprime le filtre sur la ProductKeycolonne du modèle. SSMS peut alors être utilisé pour traiter sélectivement des partitions. Lorsque toutes les partitions ont été entièrement traitées, ce qui doit inclure un recalcul de processus sur toutes les partitions, à partir de SSMS, les opérations d’actualisation suivantes sur le modèle à partir du service actualisent uniquement les partitions d’actualisation incrémentielle.

Pour en savoir plus sur le traitement des tables et des partitions sur SSMS, consultez Traiter la base de données, la table ou les partitions (Analysis Services). Pour en savoir plus sur le traitement des modèles, des tables et des partitions à l’aide de TMSL, consultez commande Refresh (TMSL).

Requêtes personnalisées pour les modifications de données détectées

TMSL et TOM peuvent être utilisés pour remplacer le comportement des modifications de données détectées. Non seulement cette méthode peut être utilisée pour éviter la persistance de la colonne de la dernière mise à jour dans le cache en mémoire, mais elle peut aussi permettre des scénarios où une table de configuration/instructions est préparée par des processus d’extraction, transformation et chargement (ETL) pour marquer seulement les partitions qui doivent être actualisées. Cette méthode peut créer un processus d’actualisation incrémentielle plus efficace où seules les périodes nécessaires sont actualisées, quelle que soit la date des mises à jour des données.

La pollingExpression est destinée à être une expression M légère ou le nom d’une autre requête M. Il doit retourner une valeur scalaire et sera exécutée pour chaque partition. Si la valeur retournée est différente de celle lors de la dernière actualisation incrémentielle, la partition est signalée pour un traitement complet.

L’exemple suivant couvre la totalité des 120 mois de la période historique pour les modifications antidatées. Le fait de spécifier 120 mois au lieu de 10 ans signifie que la compression des données n’est peut-être pas aussi efficace, mais évite d’avoir à actualiser une année historique entière, ce qui serait plus onéreux quand un mois suffit pour une modification antidatée.

"refreshPolicy": {
    "policyType": "basic",
    "rollingWindowGranularity": "month",
    "rollingWindowPeriods": 120,
    "incrementalGranularity": "month",
    "incrementalPeriods": 120,
    "pollingExpression": "<M expression or name of custom polling query>",
    "sourceExpression": [
    "let ..."
    ]
}

Déploiement de métadonnées uniquement

Lors de la publication d’une nouvelle version d’un fichier .pbix de Power BI Desktop vers un espace de travail, si un modèle portant le même nom existe déjà, vous êtes invité à remplacer le modèle existant.

Dans certains cas, vous ne souhaiterez peut-être pas remplacer le modèle, en particulier par l’actualisation incrémentielle. Le modèle dans Power BI Desktop peut être beaucoup plus petit que celui du service Power BI. Si le modèle dans le service Power BI a une stratégie d’actualisation incrémentielle appliquée, il peut avoir plusieurs années de données historiques qui seront perdues si le modèle est remplacé. L’actualisation de toutes les données historiques peut prendre des heures et entraîner un temps d’arrêt du système pour les utilisateurs.

Au lieu de cela, il est préférable d’effectuer un déploiement de métadonnées uniquement, qui permet le déploiement de nouveaux objets sans perdre les données d’historique. Par exemple, si vous avez ajouté de nouvelles mesures, vous pouvez déployer les nouvelles mesures sans devoir actualiser les données, en gagnant du temps.

Pour les espaces de travail affectés à une capacité Premium configurée pour la lecture-écriture du point de terminaison XMLA, les outils compatibles permettent le déploiement de métadonnées uniquement. Par exemple, ALM Toolkit est un outil de différences de schéma pour les modèles Power BI et peut être utilisé pour effectuer uniquement le déploiement de métadonnées.

Téléchargez et installez la dernière version de ALM Toolkit à partir du référentiel Git Analysis Services. Les instructions pas à pas sur l’utilisation d’ALM Toolkit ne sont pas incluses dans la documentation Microsoft. Des liens vers la documentation ALM Toolkit et des informations sur la prise en charge sont disponibles sur le ruban Aide. Pour effectuer un déploiement de métadonnées uniquement, effectuez une comparaison et sélectionnez l’instance Power BI Desktop en cours d’exécution comme source et le modèle existant dans le service Power BI comme cible. Considérez les différences affichées et ignorez la mise à jour de la table avec des partitions d’actualisation incrémentielle ou utilisez la boîte de dialogue Options afin de conserver des partitions pour des mises à jour de table. Validez la sélection pour garantir l’intégrité du modèle cible, puis mettez-le à jour.

Ajout d’une stratégie d’actualisation incrémentielle et de données en temps réel par programmation

Vous pouvez également utiliser TMSL et TOM pour ajouter une stratégie d’actualisation incrémentielle à un modèle existant via le point de terminaison XMLA.

Ce processus comprend les étapes suivantes :

1. Vérifiez que le modèle cible a le niveau de compatibilité minimal requis. Dans SSMS, cliquez avec le bouton droit sur le [nom du modèle]>Propriétés>niveau de compatibilité. Pour augmenter le niveau de compatibilité, utilisez un script createOrReplace TMSL ou consultez l’exemple de code TOM suivant pour obtenir un exemple.

   a. Import policy - 1550
   b. Hybrid policy - 1565
   

2. Ajoutez les paramètres RangeStart et RangeEnd aux expressions de modèle. Si nécessaire, ajoutez également une fonction pour convertir les valeurs de date/heure en clés de date.

3. Définissez un objet RefreshPolicy avec l’archivage souhaité (fenêtre enchaînée) et les périodes d’actualisation incrémentielle, ainsi qu’une expression source qui filtre la table cible en fonction des paramètres RangeStart et RangeEnd. Définissez le mode de stratégie d’actualisation sur Importer ou Hybride en fonction de vos exigences en matière de données en temps réel. Hybride force Power BI à ajouter une partition DirectQuery à la table pour extraire les dernières modifications de la source de données qui se sont produites après l’heure de la dernière actualisation.

4. Ajoutez la stratégie d’actualisation à la table et effectuez une actualisation complète afin que Power BI partitionne la table en fonction de vos besoins.

L’exemple de code suivant montre comment effectuer les étapes précédentes à l’aide de TOM. Si vous souhaitez utiliser cet exemple tel quel, vous devez disposer d’une copie pour la base de données AdventureWorksDW et importer la table FactInternetSales dans un modèle. L’exemple de code suppose que les paramètres RangeStart et RangeEndla fonctionDateKey n’existent pas dans le modèle. Importez simplement la table FactInternetSales et publiez le modèle dans un espace de travail sur Power BI Premium. Mettez ensuite à jour le workspaceUrl afin que l’exemple de code puisse se connecter à votre modèle. Mettez à jour d’autres lignes de code si nécessaire.

using System;
using TOM = Microsoft.AnalysisServices.Tabular;
namespace Hybrid_Tables
{
    class Program
    {
        static string workspaceUrl = "<Enter your Workspace URL here>";
        static string databaseName = "AdventureWorks";
        static string tableName = "FactInternetSales";
        static void Main(string[] args)
        {
            using (var server = new TOM.Server())
            {
                // Connect to the dataset.
                server.Connect(workspaceUrl);
                TOM.Database database = server.Databases.FindByName(databaseName);
                if (database == null)
                {
                    throw new ApplicationException("Database cannot be found!");
                }
                if(database.CompatibilityLevel < 1565)
                {
                    database.CompatibilityLevel = 1565;
                    database.Update();
                }
                TOM.Model model = database.Model;
                // Add RangeStart, RangeEnd, and DateKey function.
                model.Expressions.Add(new TOM.NamedExpression {
                    Name = "RangeStart",
                    Kind = TOM.ExpressionKind.M,
                    Expression = "#datetime(2021, 12, 30, 0, 0, 0) meta [IsParameterQuery=true, Type=\"DateTime\", IsParameterQueryRequired=true]"
                });
                model.Expressions.Add(new TOM.NamedExpression
                {
                    Name = "RangeEnd",
                    Kind = TOM.ExpressionKind.M,
                    Expression = "#datetime(2021, 12, 31, 0, 0, 0) meta [IsParameterQuery=true, Type=\"DateTime\", IsParameterQueryRequired=true]"
                });
                model.Expressions.Add(new TOM.NamedExpression
                {
                    Name = "DateKey",
                    Kind = TOM.ExpressionKind.M,
                    Expression =
                        "let\n" +
                        "    Source = (x as datetime) => Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x)\n" +
                        "in\n" +
                        "    Source"
                });
                // Apply a RefreshPolicy with Real-Time to the target table.
                TOM.Table salesTable = model.Tables[tableName];
                TOM.RefreshPolicy hybridPolicy = new TOM.BasicRefreshPolicy
                {
                    Mode = TOM.RefreshPolicyMode.Hybrid,
                    IncrementalPeriodsOffset = -1,
                    RollingWindowPeriods = 1,
                    RollingWindowGranularity = TOM.RefreshGranularityType.Year,
                    IncrementalPeriods = 1,
                    IncrementalGranularity = TOM.RefreshGranularityType.Day,
                    SourceExpression =
                        "let\n" +
                        "    Source = Sql.Database(\"demopm.database.windows.net\", \"AdventureWorksDW\"),\n" +
                        "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],\n" +
                        "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] >= DateKey(RangeStart) and [OrderDateKey] < DateKey(RangeEnd))\n" +
                        "in\n" +
                        "    #\"Filtered Rows\""
                };
                salesTable.RefreshPolicy = hybridPolicy;
                model.RequestRefresh(TOM.RefreshType.Full);
                model.SaveChanges();
            }
            Console.WriteLine("{0}{1}", Environment.NewLine, "Press [Enter] to exit...");
            Console.ReadLine();
        }
    }
}

Contenu connexe

• Partitions dans les modèles tabulaires
• Outils externes dans Power BI Desktop
• Configurer une actualisation planifiée
• Résoudre les problèmes en lien avec l’actualisation incrémentielle et les données en temps réel