Programmation de modèles sémantiques Power BI avec le modèle objet tabulaire (TOM)

S’applique à : SQL Server 2016 et versions ultérieures Analysis Services Azure Analysis Services Fabric/Power BI Premium

Cet article a été créé à l’origine par l’équipe de conseil client (CAT) Power BI pour Power BI Dev Camp, une collection de sessions, d’articles et de vidéos sur la programmation avancée pour Power BI.

Power BI Premium modèles sémantiques incluent le point de terminaison XMLA. Le point de terminaison est important pour les développeurs Power BI, car il fournit des API pour interagir avec le moteur Analysis Services en cours d’exécution dans le service Power BI et pour programmer directement par rapport aux modèles Power BI. Un nombre croissant de professionnels Power BI ont découvert qu’ils peuvent créer, afficher et gérer des modèles Power BI à l’aide d’outils préexistants qui utilisent le protocole XMLA, tels que SQL Server Management Studio, l’éditeur tabulaire et DAX Studio. En tant que développeur .NET, vous pouvez désormais écrire du code C# dans une application .NET pour créer et modifier des modèles directement dans le service Power BI.

Le modèle d’objet tabulaire (TOM) est une bibliothèque .NET qui fournit une couche abstraite au-dessus du point de terminaison XMLA. Il permet aux développeurs d’écrire du code en termes de modèle de programmation intuitif qui inclut des classes telles que Model, Table, Column et Measure. En arrière-plan, TOM traduit les opérations de lecture et d’écriture dans votre code en requêtes HTTP exécutées sur le point de terminaison XMLA.

L’objectif de cet article est de commencer à utiliser TOM et de montrer comment écrire le code C# requis pour créer et modifier des modèles pendant qu’ils s’exécutent dans le service Power BI. Toutefois, TOM peut également être utilisé dans des scénarios qui n’impliquent pas le point de terminaison XMLA, par exemple lors de la programmation sur un modèle local s’exécutant dans Power BI Desktop. Pour en savoir plus sur l’utilisation de TOM avec Power BI Desktop, consultez la série de blogs Phil Seamark, membre de Power BI CAT, et veillez watch la vidéo Comment programmer des jeux de données à l’aide du modèle d’objet tabulaire (TOM) du Camp de développement Power BI.

TOM représente une nouvelle API puissante pour les développeurs Power BI, distincte et distincte des API REST Power BI. Bien qu’il existe un certain chevauchement entre ces deux API, chacune de ces API inclut une quantité importante de fonctionnalités non incluses dans l’autre. En outre, il existe des scénarios qui nécessitent qu’un développeur utilise les deux API ensemble pour implémenter une solution complète.

Prise en main avec le modèle objet tabulaire

La première chose que vous devez obtenir avant de pouvoir programmer avec TOM est l’URL d’une connexion d’espace de travail. L’URL de connexion de l’espace de travail fait référence à un espace de travail spécifique et est utilisée pour créer un chaîne de connexion qui permet à votre code de se connecter à cet espace de travail Power BI et aux modèles en cours d’exécution. Commencez par accéder à la page Paramètres d’un espace de travail Power BI exécuté dans une capacité dédiée.

Une fois que vous accédez à l’onglet Premium du volet Paramètres , copiez l’URL de connexion de l’espace de travail dans le Presse-papiers.

L’étape suivante consiste à créer une application .NET dans laquelle vous écrivez le code C# qui programme à l’aide de TOM. Vous pouvez créer une application web ou une application de bureau à l’aide de .NET 5, .NET Core 3.1 ou des versions antérieures sur le .NET Framework. Dans cet article, nous créons une application console C# simple à l’aide du Kit de développement logiciel (SDK) .NET 5.

Créer une application console

Commencez par utiliser l’interface CLI .NET pour créer une application console.

dotnet new console --name`

Ajouter le package NuGet du modèle objet tabulaire

Après avoir créé l’application console, ajoutez le package NuGet Microsoft.AnalysisServices.AdomdClient.NetCore.retail.amd64 qui contient le modèle d’objet tabulaire (TOM). Vous pouvez installer le package dans une application .NET 5 à l’aide de l’interface CLI .NET suivante :

dotnet add package Microsoft.AnalysisServices.NetCore.retail.amd64

Ajouter la chaîne de connexion

Lorsque votre projet a le package NuGet avec la bibliothèque TOM installée, vous pouvez ensuite créer l’application Hello World traditionnelle avec TOM. L’application se connecte à un espace de travail Power BI à l’aide de l’URL de connexion de l’espace de travail, puis énumère les modèles dans l’espace de travail et affiche leurs noms dans la fenêtre de console.

using System;
using Microsoft.AnalysisServices.Tabular;

class Program {
  static void Main() {

    // create the connect string
    string workspaceConnection = "powerbi://api.powerbi.com/v1.0/myorg/LearningTOM";
    string connectString = $"DataSource={workspaceConnection};";

    // connect to the Power BI workspace referenced in connect string
    Server server = new Server();
    server.Connect(connectString);

    // enumerate through models in workspace to display their names
    foreach (Database database in server.Databases) {
      Console.WriteLine(database.Name);
    }
  }
}

Dans cet exemple, le chaîne de connexion contient l’URL de connexion de l’espace de travail, mais aucune information sur l’utilisateur. Si vous exécutez l’application console avec ce code, l’application commence à s’exécuter, puis une fenêtre basée sur un navigateur vous invite à vous connecter. Si vous vous connectez avec un compte d’utilisateur qui dispose des autorisations nécessaires pour accéder à l’espace de travail référencé par l’URL de connexion de l’espace de travail, la bibliothèque TOM peut acquérir un jeton d’accès, se connecter au service Power BI et énumérer les modèles dans l’espace de travail.

Pour en savoir plus sur la connexion via le point de terminaison XMLA, consultez Connectivité du modèle Sematic avec le point de terminaison XMLA - Connexion à un espace de travail Premium.

Authentification avec nom d’utilisateur et mot de passe

Pour les scénarios de développement et de test où la sécurité n’est pas aussi importante, vous pouvez coder en dur votre nom d’utilisateur et votre mot de passe, ce qui évite d’avoir à vous connecter de manière interactive chaque fois que vous exécutez un programme pour tester votre code, comme indiqué dans le code suivant :

string workspaceConnection = "powerbi://api.powerbi.com/v1.0/myorg/YOUR_WORKSPACE";
string userId = "YOUR_USER_NAME";
string password = "YOUR_USER_PASSWORD";
string connectStringUser = $"DataSource={workspaceConnection};User ID={userId};Password={password};";
server.Connect(connectStringUser);

Authentification auprès d'un principal de service

Il est également assez facile de s’authentifier en tant que principal de service plutôt qu’en tant qu’utilisateur. Si vous avez créé une application Microsoft Entra avec un ID d’application et un secret d’application, vous pouvez authentifier votre code pour qu’il s’exécute en tant que principal de service pour l’application Microsoft Entra à l’aide de l’exemple de code suivant :

string workspaceConnection = "powerbi://api.powerbi.com/v1.0/myorg/YOUR_WORKSPACE";
string tenantId = "YOUR_TENANT_ID";
string appId = "YOUR_APP_ID";
string appSecret = "YOUR_APP_SECRET";
string connectStringApp = $"DataSource={workspaceConnection};User ID=app:{appId}@{tenantId};Password={appSecret};";
server.Connect(connectStringApp);

Pour programmer avec TOM et accéder à un modèle en tant que principal de service, vous devez configurer un paramètre Power BI au niveau du locataire dans le portail Administration Power BI. Les étapes de configuration de Power BI pour prendre en charge la connexion en tant que principal de service sont décrites dans Incorporer du contenu Power BI avec un principal de service et un secret d’application.

Authentification avec un jeton d’accès Microsoft Entra

TOM offre également de la flexibilité lors de l’établissement d’une connexion à l’aide d’un jeton d’accès Microsoft Entra valide. Si vous avez les compétences de développeur pour implémenter un flux d’authentification avec l’ID de Microsoft Entra et acquérir des jetons d’accès, vous pouvez mettre en forme votre chaîne de connexion TOM sans nom d’utilisateur, mais inclure le jeton d’accès comme mot de passe à la place, comme indiqué dans l’exemple de code suivant :

public static void ConnectToPowerBIAsUser() {
  string workspaceConnection = "powerbi://api.powerbi.com/v1.0/myorg/YOUR_WORKSPACE";
  string accessToken = TokenManager.GetAccessToken();  // you must implement GetAccessToken yourself
  string connectStringUser = $"DataSource={workspaceConnection};Password={accessToken};";
  server.Connect(connectStringUser);
}

Si vous achetez un jeton d’accès basé sur l’utilisateur pour vous connecter à un espace de travail Power BI avec TOM, veillez à demander les autorisations déléguées suivantes lors de l’acquisition du jeton d’accès pour vous assurer que vous disposez de toutes les autorisations de création dont vous avez besoin :

public static readonly string[] XmlaScopes = new string[] {
    "https://analysis.windows.net/powerbi/api/Content.Create",
    "https://analysis.windows.net/powerbi/api/Dataset.ReadWrite.All",
    "https://analysis.windows.net/powerbi/api/Workspace.ReadWrite.All",
};

Si vous avez été en programmation avec l’API REST Power BI, vous pouvez reconnaître des autorisations familières telles que Content.Create, Dataset.ReadWrite.All et Workspace.ReadWrite.All. Une observation intéressante est que TOM utilise le même ensemble d’autorisations déléguées que l’API REST Power BI définie dans l’étendue de l’ID de ressource Microsoft Entra de https://analysis.windows.net/powerbi/api.

Le fait que le point de terminaison XMLA et l’API REST Power BI partagent le même ensemble d’autorisations déléguées présente ses avantages. Les jetons d’accès peuvent être utilisés de manière interchangeable entre TOM et l’API REST Power BI. Une fois que vous avez acquis un jeton d’accès pour appeler TOM afin de créer un modèle, vous pouvez utiliser le même jeton d’accès pour appeler l’API REST Power BI afin de définir les informations d’identification de la source de données, comme décrit plus loin dans cet article.

Une chose qui tend à confondre les programmeurs Power BI est que les principaux de service n’utilisent pas d’autorisations déléguées. Au lieu de cela, lors de la programmation avec TOM, vous configurez l’accès pour un principal de service en l’ajoutant à l’espace de travail cible en tant que membre dans le rôle de Administration ou membre.

Présentation des objets serveur, base de données et modèle

Le modèle objet dans TOM est basé sur une hiérarchie avec l’objet Server de niveau supérieur qui contient une collection d’objets Database . Lors de la programmation avec TOM dans Power BI, l’objet Server représente un espace de travail Power BI et l’objet Database représente un modèle Power BI.

Chaque base de données contient un objet Model qui fournit un accès en lecture/écriture au modèle de données. Le modèle contient des collections pour les éléments d’un modèle de données, notamment DataSource, Table, Relationship, Perspective, Culture et Role.

Comme indiqué dans le code Hello World, une fois que vous appelez le serveur. Connectez-vous pour découvrir facilement les modèles qui existent dans un espace de travail Power BI en énumérant la collection Databases de l’objet Server, comme indiqué dans le code suivant :

foreach (Database database in server.Databases) {
    Console.WriteLine(database.Name);
}

Vous pouvez également utiliser la méthode GetByName exposée par l’objet de collection Databases pour accéder à un modèle par son nom, comme suit :

Database database = server.Databases.GetByName("Wingtip Sales");

Il est important de faire la distinction entre un objet Databaseet sa propriété Model interne. Vous pouvez utiliser les propriétés de l’objet Database pour découvrir des attributs de modèle tels que Name, ID, CompatibilityMode et CompatibilityLevel. Il existe également une propriété EstimatedSize qui permet de découvrir la taille d’un modèle. D’autres propriétés incluent LastUpdate, LastProcessed et LastSchemaUpdate , qui vous permettent de déterminer quand le modèle sous-jacent a été actualisé pour la dernière fois et à quel moment le schéma du modèle a été mis à jour.

public static void GetDatabaseInfo(string DatabaseName) {
  Database database = server.Databases.GetByName(DatabaseName);
  Console.WriteLine("Name: " + database.Name);
  Console.WriteLine("ID: " + database.ID);
  Console.WriteLine("CompatibilityMode: " + database.CompatibilityMode);
  Console.WriteLine("CompatibilityLevel: " + database.CompatibilityLevel);
  Console.WriteLine("EstimatedSize: " + database.EstimatedSize);
  Console.WriteLine("LastUpdated: " + database.LastUpdate);
  Console.WriteLine("LastProcessed: " + database.LastProcessed);
  Console.WriteLine("LastSchemaUpdate: " + database.LastSchemaUpdate);
}

Bien que l’objet Database ait ses propres propriétés, il s’agit de l’objet Model interne d’un objet Database qui vous permet de lire et d’écrire dans le modèle de données sous-jacent d’un modèle. Voici un exemple simple de programmation de l’objet model de base de données pour énumérer dans sa collection Tables et découvrir les tables qu’elle contient.

Dans le modèle objet TOM, chaque objet Table a des objets de collection pour ses partitions. colonnes, mesures et hiérarchies.

Une fois que vous avez récupéré l’objet Model pour une base de données, vous pouvez accéder à une table spécifique par nom dans le modèle à l’aide de la méthode Find de la collection Tables . Voici un exemple de récupération d’une table nommée Sales et de découverte de ses membres en énumérant la collection Columns et la collection Measures :

Model databaseModel = server.Databases.GetByName("Tom Demo").Model;

Table tableSales = databaseModel.Tables.Find("Sales");

foreach (Column column in tableSales.Columns) {
  Console.WriteLine("Coulumn: " + column.Name);
}

foreach (Measure measure in tableSales.Measures) {
  Console.WriteLine("Measure: " + measure.Name);
  Console.WriteLine(measure.Expression);
}

Modification de modèles avec TOM

Dans les sections ci-dessus, vous avez vu comment accéder à un objet Database et à son objet Model pour inspecter le modèle de données d’un modèle en cours d’exécution dans le service Power BI. Il est maintenant temps de programmer notre première mise à jour de modèle avec TOM en ajoutant une mesure à une table.

La capacité que vous utilisez doit être activée pour la lecture-écriture XMLA. Par défaut, le paramètre d’autorisations de point de terminaison XMLA est défini sur Lecture. Il doit donc être explicitement défini sur Lecture par une personne disposant d’autorisations de capacité Administration. Ce paramètre peut être consulté et mis à jour dans la page Paramètres de capacité du portail Administration.

Lorsque le point de terminaison XMLA a été configuré pour la lecture-écriture, vous pouvez ajouter une nouvelle mesure nommée Sales Revenue à la table Sales , comme indiqué dans le code suivant :

Model dataset = server.Databases.GetByName("Tom Demo Starter").Model;
Table tableSales = dataset.Tables.Find("Sales");
Measure salesRevenue = new Measure();
salesRevenue.Name = "Sales Revenue";
salesRevenue.Expression = "SUM(Sales[SalesAmount])";
salesRevenue.FormatString = "$#,##0.00";
tableSales.Measures.Add(salesRevenue);
dataset.SaveChanges();

Examinons de plus près ce code. Tout d’abord, vous créez un objet Measure à l’aide de l’opérateur C# et fournissez des valeurs pour Name, Expression et FormatString. Vous ajoutez ensuite le nouvel objet Measure à la collection Measures de l’objet Table cible en appelant la méthode Add . Enfin, appelez la méthode SaveChanges de l’objet Model pour réécrire vos modifications dans le modèle dans le service Power BI.

Gardez à l’esprit que les mises à jour d’un modèle sont traitées par lot en mémoire jusqu’à ce que vous appeliez SaveChanges. Imaginez un scénario dans lequel vous souhaitez masquer chaque colonne d’une table. Vous pouvez commencer par écrire une boucle foreach pour énumérer tous les objets Column d’une table et définir la propriété IsHidden pour chaque objet Column sur true. Une fois la boucle foreach terminée, vous avez plusieurs mises à jour de colonne qui sont traitées par lot en mémoire. Mais c’est l’appel final à SaveChanges qui renvoie toutes les modifications au service Power BI dans un lot, comme indiqué ci-dessous :

Model dataset = server.Databases.GetByName("Tom Demo").Model;
Table tableSales = dataset.Tables.Find("Sales");

foreach (Column column in tableSales.Columns) {
  column.IsHidden = true;
}

dataset.SaveChanges();

Supposons que vous souhaitiez mettre à jour la propriété FormatString pour une colonne existante. La collection Columns expose une méthode Find pour récupérer l’objet Column cible. Après cela, il suffit de définir la propriété FormatString et d’appeler SaveChanges, comme suit :

Model dataset = server.Databases.GetByName("Tom Demo").Model;
Table tableSales = dataset.Tables.Find("Products");
Column columnListPrice = tableSales.Columns.Find("List Price");
columnListPrice.FormatString = "$#,##0.00";
dataset.SaveChanges();

La capacité de TOM à découvrir dynamiquement ce qui se trouve à l’intérieur d’un modèle offre la possibilité d’effectuer des mises à jour de manière générique et radicale. Imaginez un scénario dans lequel vous gérez un modèle qui contient un grand nombre de tables et des dizaines, voire des centaines de colonnes basées sur le type de données DateTime . Vous pouvez mettre à jour la propriété FormatString pour chaque colonne DateTime de l’ensemble du modèle en même temps en utilisant les éléments suivants :

Database database = server.Databases.GetByName("Tom Demo Starter");
Model datasetModel = database.Model;

foreach (Table table in datasetModel.Tables) {
  foreach (Column column in table.Columns) {
    if(column.DataType == DataType.DateTime) {
      column.FormatString = "yyyy-MM-dd";
    }
  }
}

datasetModel.SaveChanges();

Actualisation des modèles avec TOM

Nous allons maintenant effectuer une opération de maintenance de modèle classique. Comme vous le voyez dans le code suivant, il n’est pas très compliqué de démarrer une opération d’actualisation de modèle à l’aide de TOM :

public static void RefreshDatabaseModel(string Name) {
  Database database = server.Databases.GetByName(Name);
  database.Model.RequestRefresh(RefreshType.DataOnly);
  database.Model.SaveChanges();
}

Tout comme avec l’actualisation manuelle et planifiée du modèle, les actualisations via le point de terminaison XMLA sont affichées dans l’historique des actualisations, mais avec l’étiquette , Via le point de terminaison XMLA.

Création et clonage de modèles

Imaginez que vous devez créer et cloner des modèles Power BI à l’aide du code écrit en C#. Commençons par écrire une fonction réutilisable nommée CreateDatabase qui crée un objet Database , comme suit :

public static Database CreateDatabase(string DatabaseName) {

  string newDatabaseName = server.Databases.GetNewName(DatabaseName);
  var database = new Database() {
    Name = newDatabaseName,
    ID = newDatabaseName,
    CompatibilityLevel = 1520,
    StorageEngineUsed = Microsoft.AnalysisServices.StorageEngineUsed.TabularMetadata,
    Model = new Model() {
      Name = DatabaseName + "-Model",
      Description = "A Demo Tabular data model with 1520 compatibility level."
    }
  };

  server.Databases.Add(database);
  database.Update(Microsoft.AnalysisServices.UpdateOptions.ExpandFull);
  return database;

}

Dans cet exemple, nous allons commencer par utiliser la méthode GetNewNamede l’objet de collection Databases pour nous assurer que notre nouveau nom de modèle est unique dans l’espace de travail cible. Après cela, l’objet Database et son objet Model peuvent être créés à l’aide de l’opérateur C# nouveau , comme indiqué dans le code ci-dessous. À la fin, cette méthode ajoute le nouvel objet Database à la collection Databases et appelle la base de données. Méthode de mise à jour .

Si votre objectif est de copier un modèle existant au lieu d’en créer un, vous pouvez utiliser la méthode CopyDatabase suivante pour cloner un modèle Power BI en créant un modèle vide, puis en appelant CopyTo sur l’objet Model pour le modèle source afin de copier l’intégralité du modèle de données dans le modèle nouvellement créé.

public static Database CopyDatabase(string sourceDatabaseName, string DatabaseName) {
  Database sourceDatabase = server.Databases.GetByName(sourceDatabaseName);
  string newDatabaseName = server.Databases.GetNewName(DatabaseName);
  Database targetDatabase = CreateDatabase(newDatabaseName);
  sourceDatabase.Model.CopyTo(targetDatabase.Model);
  targetDatabase.Model.SaveChanges();
  targetDatabase.Model.RequestRefresh(RefreshType.Full);
  targetDatabase.Model.SaveChanges();
  return targetDatabase;
}

Création d’un modèle réel à partir de zéro

Ok, imaginez maintenant que vous venez de créer un modèle à partir de zéro et que vous devez maintenant utiliser TOM pour composer un modèle de données réel en ajoutant des tables, des colonnes, des mesures, des hiérarchies et des relations de table. Examinons un exemple de code qui crée une table qui inclut des colonnes définies, ajoute une hiérarchie dimensionnelle à trois niveaux et fournit même l’expression M pour la requête de table sous-jacente :

private static Table CreateProductsTable() {

  Table productsTable = new Table() {
    Name = "Products",
    Description = "Products table",
    Partitions = {
      new Partition() {
        Name = "All Products",
        Mode = ModeType.Import,
        Source = new MPartitionSource() {
          // M code for query maintained in separate source file
          Expression = Properties.Resources.ProductQuery_m
        }
      }
    },
    Columns = {
      new DataColumn() { Name = "ProductId", DataType = DataType.Int64, SourceColumn = "ProductId", IsHidden = true },
      new DataColumn() { Name = "Product", DataType = DataType.String, SourceColumn = "Product" },
      new DataColumn() { Name = "Description", DataType = DataType.String, SourceColumn = "Description" },
      new DataColumn() { Name = "Category", DataType = DataType.String, SourceColumn = "Category" },
      new DataColumn() { Name = "Subcategory", DataType = DataType.String, SourceColumn = "Subcategory" },
      new DataColumn() { Name = "Product Image", DataType = DataType.String, 
                        SourceColumn = "ProductImageUrl", DataCategory = "ImageUrl" }
     }
  };

  productsTable.Hierarchies.Add(
    new Hierarchy() {
      Name = "Product Category",
      Levels = {
        new Level() { Ordinal=0, Name="Category", Column=productsTable.Columns["Category"] },
        new Level() { Ordinal=1, Name="Subcategory", Column=productsTable.Columns["Subcategory"] },
        new Level() { Ordinal=2, Name="Product", Column=productsTable.Columns["Product"] }
      }
  });

  return productsTable;
}

Une fois que vous avez créé un ensemble de méthodes d’assistance pour créer les tables, vous pouvez les composer ensemble pour créer un modèle de données, comme suit :

Model model = database.Model;
Table tableCustomers = CreateCustomersTable();
Table tableProducts = CreateProductsTable();
Table tableSales = CreateSalesTable();
Table tableCalendar = CreateCalendarTable();
model.Tables.Add(tableCustomers);
model.Tables.Add(tableProducts);
model.Tables.Add(tableSales);
model.Tables.Add(tableCalendar);

TOM expose une collection Relationships sur l’objet Model qui vous permet de définir les relations entre les tables de votre modèle. Voici le code requis pour créer un objet SingleColumnRelationship qui établit une relation un-à-plusieurs entre la table Products et la table Sales :

model.Relationships.Add(new SingleColumnRelationship {
  Name = "Products to Sales",
  ToColumn = tableProducts.Columns["ProductId"],
  ToCardinality = RelationshipEndCardinality.One,
  FromColumn = tableSales.Columns["ProductId"],
  FromCardinality = RelationshipEndCardinality.Many
});

Une fois que vous avez terminé d’ajouter les tables et la relation de table, enregistrez votre travail avec un appel au modèle. SaveChanges :

model.SaveChanges();

À ce stade, après avoir appelé SaveChanges, vous devriez être en mesure de voir le nouveau modèle créé dans le service Power BI et de commencer à l’utiliser pour créer de nouveaux rapports.

Exemple de projet

L’exemple de projet avec le code C# que vous avez vu dans cet article est disponible ici. Il est maintenant temps pour vous de commencer à programmer avec TOM et de trouver des moyens de tirer parti de cette nouvelle API puissante dans le développement de solutions personnalisées pour Power BI.

Voir aussi

Connectivité du modèle sémantique avec le point de terminaison XMLA
Résoudre les problèmes de connectivité des points de terminaison XMLA