Programação de modelos semânticos do Power BI com o TOM (Modelo de Objeto Tabular)

Aplica-se a: SQL Server 2016 e posteriores do Analysis Services Azure Analysis Services Fabric/Power BI Premium

Este artigo foi originalmente criado pela CAT (Equipe de Consultoria de Clientes) do Power BI para o Acampamento de Desenvolvimento do Power BI, uma coleção de sessões, artigos e vídeos sobre programação avançada para o Power BI.

Power BI Premium modelos semânticos incluem o ponto de extremidade XMLA. O ponto de extremidade é significativo para os desenvolvedores do Power BI porque fornece APIs para interagir com o mecanismo do Analysis Services em execução no Serviço do Power BI e programar diretamente em modelos do Power BI. Um número crescente de profissionais do Power BI descobriu que eles podem criar, exibir e gerenciar modelos do Power BI usando ferramentas pré-existentes que usam o protocolo XMLA, como SQL Server Management Studio, Editor tabular e DAX Studio. Como desenvolvedor do .NET, agora você pode escrever código C# em um aplicativo .NET para criar e modificar modelos diretamente no Serviço do Power BI.

O TOM (Modelo de Objeto Tabular) é uma biblioteca .NET que fornece uma camada abstrata sobre o ponto de extremidade XMLA. Ele permite que os desenvolvedores escrevam código em termos de um modelo de programação intuitivo que inclui classes como Modelo, Tabela, Coluna e Medida. Nos bastidores, o TOM converte as operações de leitura e gravação em seu código em solicitações HTTP executadas no ponto de extremidade XMLA.

O foco deste artigo é começar a usar o TOM e demonstrar como escrever o código C# necessário para criar e modificar modelos enquanto eles estão em execução no Serviço do Power BI. No entanto, TOM também pode ser usado em cenários que não envolvem o ponto de extremidade XMLA, como ao programar em um modelo local em execução em Power BI Desktop. Para saber mais sobre como usar o TOM com Power BI Desktop, consulte a série de blogs de Phil Seamark, membro do Power BI CAT, e certifique-se de watch os conjuntos de dados Como programar usando o vídeo TOM (Modelo de Objeto Tabular) do Acampamento de Desenvolvimento do Power BI.

O TOM representa uma API nova e poderosa para desenvolvedores do Power BI separada e distinta das APIs REST do Power BI. Embora haja alguma sobreposição entre essas duas APIs, cada uma dessas APIs inclui uma quantidade significativa de funcionalidade não incluída na outra. Além disso, há cenários que exigem que um desenvolvedor use as duas APIs juntas para implementar uma solução completa.

Introdução com o modelo de objeto tabular

A primeira coisa que você precisa obter antes de poder programar com TOM é a URL de uma conexão de workspace. A URL de conexão do workspace faz referência a um workspace específico e é usada para criar um cadeia de conexão que permite que seu código se conecte a esse workspace do Power BI e aos modelos em execução no interior. Comece navegando até a página Configurações de um workspace do Power BI em execução em uma capacidade dedicada.

Depois de navegar até a guia Premium do painel Configurações , copie a URL de Conexão do Workspace para a área de transferência.

A próxima etapa é criar um novo aplicativo .NET no qual você escreve o código C# que programa usando TOM. Você pode criar um aplicativo Web ou um aplicativo desktop usando .NET 5, .NET Core 3.1 ou versões mais antigas no .NET Framework. Neste artigo, criamos um aplicativo de console C# simples usando o SDK do .NET 5.

Crie um novo aplicativo de console

Comece usando a CLI do .NET para criar um novo aplicativo de console.

dotnet new console --name`

Adicionar o pacote NuGet do Modelo de Objeto Tabular

Depois de criar o aplicativo de console, adicione o pacote NuGet Microsoft.AnalysisServices.AdomdClient.NetCore.retail.amd64 que contém o TOM (Modelo de Objeto Tabular). Você pode instalar o pacote em um aplicativo .NET 5 usando a seguinte CLI do .NET:

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

Adicionar a cadeia de conexão

Quando o projeto tiver o pacote NuGet com a biblioteca TOM instalada, você poderá criar o aplicativo Olá, Mundo tradicional com TOM. O aplicativo se conecta a um workspace do Power BI usando a URL de Conexão do Workspace e, em seguida, enumera por meio dos modelos no workspace e exibe seus nomes na janela do 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);
    }
  }
}

Neste exemplo, o cadeia de conexão contém a URL de Conexão do Workspace, mas nenhuma informação sobre o usuário. Se você executar o aplicativo de console com esse código, o aplicativo começará a ser executado e, em seguida, você será solicitado com uma janela baseada em navegador para fazer logon. Se você fizer logon com uma conta de usuário que tenha permissões para acessar o workspace referenciado pela URL de Conexão do Workspace, a biblioteca TOM poderá adquirir um token de acesso, conectar-se ao Serviço do Power BI e enumerar por meio dos modelos no workspace.

Para saber mais sobre como se conectar por meio do ponto de extremidade XMLA, confira Conectividade de modelo sematic com o ponto de extremidade XMLA – Conectando-se a um workspace Premium.

Autenticação com nome de usuário e senha

Para cenários de desenvolvimento e teste em que a segurança não é tão importante, você pode codificar seu nome de usuário e senha, eliminando a necessidade de fazer logon interativamente sempre que executar um programa para testar seu código, conforme mostrado no código a seguir:

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);

Autenticação com uma entidade de serviço

Também é muito fácil autenticar como uma entidade de serviço em vez de como um usuário. Se você tiver criado um aplicativo Microsoft Entra com uma ID do aplicativo e um segredo do aplicativo, poderá autenticar seu código para ser executado como a entidade de serviço do aplicativo Microsoft Entra usando o seguinte exemplo de código:

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);

Para programar com TOM e acessar um modelo como uma entidade de serviço, você deve definir uma configuração do Power BI no nível do locatário no portal de Administração do Power BI. As etapas para configurar o Power BI para dar suporte à conexão como uma entidade de serviço são descritas em Inserir conteúdo do Power BI com a entidade de serviço e um segredo do aplicativo.

Autenticação com um token de acesso Microsoft Entra

O TOM também fornece flexibilidade ao estabelecer uma conexão usando um token de acesso de Microsoft Entra válido. Se você tiver as habilidades de desenvolvedor para implementar um fluxo de autenticação com Microsoft Entra ID e adquirir tokens de acesso, poderá formatar seu TOM cadeia de conexão sem um nome de usuário, mas incluir o token de acesso como a senha, conforme mostrado no exemplo de código a seguir:

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);
}

Se você estiver adquirindo um token de acesso baseado no usuário para se conectar a um workspace do Power BI com TOM, solicite as seguintes permissões delegadas ao adquirir o token de acesso para garantir que você tenha todas as permissões de criação necessárias:

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",
};

Se você estiver programando com a API REST do Power BI, poderá reconhecer permissões conhecidas, como Content.Create, Dataset.ReadWrite.All e Workspace.ReadWrite.All. Uma observação interessante é que o TOM usa o mesmo conjunto de permissões delegadas que a API REST do Power BI definida no escopo do Microsoft Entra ID do recurso de https://analysis.windows.net/powerbi/api.

O fato de o ponto de extremidade XMLA e a API REST do Power BI compartilharem o mesmo conjunto de permissões delegadas tem seus benefícios. Os tokens de acesso podem ser usados de forma intercambiável entre TOM e a API REST do Power BI. Depois de adquirir um token de acesso para chamar o TOM para criar um novo modelo, você poderá usar o mesmo token de acesso para chamar a API REST do Power BI para definir as credenciais da fonte de dados, conforme descrito mais adiante neste artigo.

Uma coisa que tende a confundir os programadores do Power BI é que as entidades de serviço não usam permissões delegadas. Em vez disso, ao programar com TOM, você configura o acesso para uma entidade de serviço adicionando-o ao workspace de destino como membro na função de Administração ou Membro.

Noções básicas sobre objetos de servidor, banco de dados e modelo

O modelo de objeto no TOM baseia-se em uma hierarquia com o objeto Server de nível superior que contém uma coleção de objetos Database . Ao programar com o TOM no Power BI, o objeto Server representa um workspace do Power BI e o objeto Database representa um modelo do Power BI.

Cada Banco de Dados contém um objeto Model que fornece acesso de leitura/gravação ao modelo de dados. O Modelo contém coleções para os elementos de um modelo de dados, incluindo DataSource, Table, Relationship, Perspective, Culture e Role.

Conforme mostrado no código Olá, Mundo, depois de chamar o servidor. Conecte-se, você pode descobrir facilmente quais modelos existem dentro de um workspace do Power BI enumerando por meio da coleção Databases do objeto Server, conforme mostrado no código a seguir:

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

Você também pode usar o método GetByName exposto pelo objeto de coleção Databases para acessar um modelo por nome, desta forma:

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

É importante distinguir entre um objeto Databasee sua propriedade Model interna. Você pode usar as propriedades do objeto Database para descobrir atributos de modelo, como Nome, ID, CompatibilityMode e CompatibilityLevel. Há também uma propriedade EstimatedSize que possibilita descobrir o tamanho de um modelo. Outras propriedades incluem LastUpdate, LastProcessed e LastSchemaUpdate , que permitem determinar quando o modelo subjacente foi atualizado pela última vez e quando o esquema de modelo foi atualizado pela última vez.

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);
}

Embora o objeto Database tenha suas próprias propriedades, ele é o objeto Model interno de um objeto Database que fornece a capacidade de ler e gravar no modelo de dados subjacente de um modelo. Aqui está um exemplo simples de programação do objeto Modelo de banco de dados para enumerar por meio de sua coleção Tables e descobrir quais tabelas estão dentro.

No modelo de objeto TOM, cada objeto Table tem objetos de coleção para suas partições. colunas, medidas e hierarquias.

Depois de recuperar o objeto Model para um Banco de Dados, você poderá acessar uma tabela específica por nome no modelo usando o método Find da coleção Tables . Aqui está um exemplo recuperando uma tabela chamada Sales e descobrindo seus membros enumerando por meio da coleção Columns e da coleção 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);
}

Modificando modelos com TOM

Nas seções acima, você viu como acessar um objeto Database e seu objeto Model para inspecionar o modelo de dados de um modelo em execução no Serviço do Power BI. Agora é hora de programar nossa primeira atualização de modelo com TOM adicionando uma medida a uma tabela.

A capacidade que você está usando deve ser habilitada para leitura/gravação XMLA. Por padrão, a configuração de permissões de ponto de extremidade XMLA é definida como Leitura, portanto, ela deve ser definida explicitamente como Ler Gravação por alguém com permissões de capacidade Administração. Essa configuração pode ser exibida e atualizada na página Configurações de capacidade no portal Administração.

Quando o ponto de extremidade XMLA tiver sido configurado para leitura/gravação, você poderá adicionar uma nova medida chamada Receita de Vendas à tabela Vendas , conforme mostrado no seguinte código:

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();

Vamos dar uma olhada mais de perto neste código. Primeiro, você cria um novo objeto Measure usando o novo operador C# e fornece valores para o Nome, Expressão e FormatString. Em seguida, adicione o novo objeto Measure à coleção Measures do objeto Table de destino chamando o método Add . Por fim, chame o método SaveChanges do objeto Model para gravar as alterações de volta no modelo no Serviço do Power BI.

Tenha em mente que as atualizações para um modelo são agrupadas em lote na memória até que você chame SaveChanges. Imagine um cenário em que você gostaria de ocultar todas as colunas de uma tabela. Você pode começar escrevendo um loop foreach para enumerar todos os objetos Column de uma tabela e definindo a propriedade IsHidden para cada objeto Column como true. Após a conclusão do loop foreach , você terá várias atualizações de coluna que são agrupadas em lote na memória. Mas é a chamada final para SaveChanges que envia todas as alterações de volta para o Serviço do Power BI em um lote, conforme mostrado abaixo:

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();

Digamos que você queira atualizar a propriedade FormatString para uma coluna existente. A coleção Columns expõe um método Find para recuperar o objeto Column de destino. Depois disso, é apenas uma questão de definir a propriedade FormatString e chamar SaveChanges, da seguinte maneira:

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();

A capacidade do TOM de descobrir dinamicamente o que está dentro de um modelo oferece oportunidades para realizar atualizações de forma genérica e abrangente. Imagine um cenário no qual você está gerenciando um modelo que tem muitas tabelas e dezenas ou até mesmo centenas de colunas com base no tipo de dados DateTime . Você pode atualizar a propriedade FormatString para cada coluna DateTime em todo o modelo ao mesmo tempo usando o seguinte:

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();

Atualizando modelos com TOM

Agora, vamos executar uma operação típica de manutenção de modelo. Como você vê no código a seguir, não é muito complicado iniciar uma operação de atualização de modelo usando TOM:

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

Assim como ocorre com a atualização manual e agendada do modelo, as atualizações por meio do ponto de extremidade XMLA são mostradas no Histórico de atualização, mas com o rótulo , via ponto de extremidade XMLA.

Criando e clonando modelos

Imagine que você tenha um requisito para criar e clonar modelos do Power BI usando o código escrito em C#. Vamos começar escrevendo uma função reutilizável chamada CreateDatabase que cria um novo objeto Database , desta forma:

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;

}

Neste exemplo, começaremos usando o método GetNewNamedo objeto da coleção Databases para garantir que nosso novo nome de modelo seja exclusivo no workspace de destino. Depois disso, o objeto Database e seu objeto Model podem ser criados usando o novo operador C#, conforme mostrado no código abaixo. No final, esse método adiciona o novo objeto Database à coleção Databases e chama o banco de dados. Método Update .

Se sua meta for copiar um modelo existente em vez de criar um novo, você poderá usar o método CopyDatabase a seguir para clonar um modelo do Power BI criando um novo modelo vazio e, em seguida, chamando CopyTo no objeto Model para o modelo de origem copiar todo o modelo de dados para o modelo recém-criado.

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;
}

Criando um modelo do mundo real do zero

Ok, agora imagine que você acabou de criar um novo modelo do zero e agora precisa usar o TOM para compor um modelo de dados do mundo real adicionando tabelas, colunas, medidas, hierarquias e relações de tabela. Vamos examinar um exemplo de código que cria uma nova tabela que inclui colunas definidas, adiciona uma hierarquia dimensional de três níveis e fornece até mesmo a expressão M para a consulta de tabela subjacente:

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;
}

Depois de criar um conjunto de métodos auxiliares para criar as tabelas, você poderá redigi-las juntas para criar um modelo de dados, desta forma:

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 expõe uma coleção Relationships no objeto Model que permite definir as relações entre as tabelas em seu modelo. Este é o código necessário para criar um objeto SingleColumnRelationship que estabelece uma relação um-para-muitos entre a tabela Products e a tabela Sales :

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

Depois de terminar de adicionar as tabelas e a relação de tabela, salve seu trabalho com uma chamada para o modelo. SaveChanges:

model.SaveChanges();

Neste ponto, depois de chamar SaveChanges, você poderá ver o novo modelo criado no Serviço do Power BI e começar a usá-lo para criar novos relatórios.

Projeto de exemplo

O projeto de exemplo com o código C# que você viu neste artigo está disponível aqui. Agora é hora de começar a programar com o TOM e encontrar maneiras de aproveitar essa nova API poderosa no desenvolvimento de soluções personalizadas para o Power BI.

Confira também

Conectividade de modelo semântico com o ponto de extremidade XMLA
Solucionar problemas de conectividade de ponto de extremidade XMLA