Programación Power BI conjuntos de datos con el modelo de objetos tabulares (TOM)

Se aplica a: SQL Server 2016 y versiones posteriores de Analysis Services Azure Analysis Services Power BI Premium

Este artículo fue creado originalmente por el equipo de asesoramiento al cliente (CAT) de Power BI para Power BI Dev Camp, una colección de sesiones, artículos y vídeos sobre la programación avanzada para Power BI.

Power BI Premium conjuntos de datos incluyen el punto de conexión XMLA. El punto de conexión es importante para Power BI desarrolladores, ya que proporciona API para interactuar con el motor de Analysis Services que se ejecuta en el servicio Power BI y para programar directamente con conjuntos de datos de Power BI. Un número creciente de profesionales de Power BI ha encontrado que pueden crear, ver y administrar conjuntos de datos Power BI mediante herramientas preexistentes que usan el protocolo XMLA, como SQL Server Management Studio, Editor tabular y DAX Studio. Como desarrollador de .NET, ahora puede escribir código de C# en una aplicación .NET para crear y modificar conjuntos de datos directamente en el servicio Power BI.

El modelo de objetos tabulares (TOM) es una biblioteca de .NET que proporciona una capa abstracta sobre el punto de conexión XMLA. Permite a los desarrolladores escribir código en términos de un modelo de programación intuitivo que incluye clases como Model, Table, Column y Measure. En segundo plano, TOM traduce las operaciones de lectura y escritura en el código en solicitudes HTTP ejecutadas en el punto de conexión XMLA.

El objetivo de este artículo es empezar a trabajar con TOM y demostrar cómo escribir el código de C# necesario para crear y modificar conjuntos de datos mientras se ejecutan en el servicio Power BI. Sin embargo, TOM también se puede usar en escenarios que no implican el punto de conexión XMLA, como al programar en un conjunto de datos local que se ejecuta en Power BI Desktop. Para obtener más información sobre el uso de TOM con Power BI Desktop, consulte Power BI serie de blog de Phil Seamark miembro de CAT y asegúrese de ver el vídeo How to Program Datasets using the Tabular Object Model (TOM) (Cómo programar conjuntos de datos mediante el modelo de objetos tabulares [TOM]) del Power BI Dev Camp.

TOM representa una API nueva y eficaz para Power BI desarrolladores que son independientes y distintas de las API REST de Power BI. Aunque hay cierta superposición entre estas dos API, cada una de estas API incluye una cantidad significativa de funcionalidad no incluida en la otra. Además, hay escenarios que requieren que un desarrollador use ambas API juntas para implementar una solución completa.

Introducción con el modelo de objetos tabulares

Lo primero que necesita para poder programar con TOM es la dirección URL de una conexión de área de trabajo. La dirección URL de conexión del área de trabajo hace referencia a un área de trabajo específica y se usa para crear una cadena de conexión que permita que el código se conecte a ese área de trabajo Power BI y los conjuntos de datos que se ejecutan dentro. Para empezar, vaya a la página Configuración de un área de trabajo de Power BI que se ejecuta en una capacidad dedicada.

Nota

El punto de conexión XMLA solo se admite para conjuntos de datos que se ejecutan en una capacidad dedicada. No está disponible para los conjuntos de datos que se ejecutan en una capacidad compartida. Si trabaja con conjuntos de datos en una capacidad de Power BI Premium por usuario, puede conectarse como usuario, pero no puede conectarse como entidad de servicio.

Una vez que vaya a la pestaña Premium del panel Configuración, copie la dirección URL de conexión del área de trabajo en el Portapapeles.

El siguiente paso consiste en crear una nueva aplicación .NET en la que se escribe el código de C# que programa con TOM. Puede crear una aplicación web o una aplicación de escritorio con .NET 5, .NET Core 3.1 o versiones anteriores en la .NET Framework. En este artículo se crea una aplicación de consola de C# sencilla mediante el SDK de .NET 5.

Creación de una nueva aplicación de consola

Empiece por usar la CLI de .NET para crear una nueva aplicación de consola.

dotnet new console --name`

Agregar el paquete de NuGet modelo de objetos tabulares

Después de crear la aplicación de consola, agregue el paquete de NuGet Microsoft.AnalysisServices.AdomdClient.NetCore.retail.amd64 que contiene el modelo de objetos tabulares (TOM). Puede instalar el paquete en una aplicación de .NET 5 mediante la siguiente CLI de .NET:

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

Incorporación de la cadena de conexión

Cuando el proyecto tenga instalado el paquete de NuGet con la biblioteca TOM, puede crear la aplicación Hola mundo tradicional con TOM. La aplicación se conecta a un área de trabajo de Power BI mediante la dirección URL de conexión del área de trabajo y, a continuación, enumera los conjuntos de datos del área de trabajo y muestra sus nombres en la ventana de la consola.

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 datasets in workspace to display their names
    foreach (Database database in server.Databases) {
      Console.WriteLine(database.Name);
    }
  }
}

En este ejemplo, la cadena de conexión contiene la dirección URL de conexión del área de trabajo, pero no hay información sobre el usuario. Si ejecuta la aplicación de consola con este código, la aplicación comenzará a ejecutarse y, a continuación, se le pedirá una ventana basada en explorador para iniciar sesión. Si inicia sesión con una cuenta de usuario que tiene permisos para acceder al área de trabajo a la que hace referencia la dirección URL de conexión del área de trabajo, la biblioteca TOM puede adquirir un token de acceso, conectarse al servicio de Power BI y enumerar los conjuntos de datos del área de trabajo.

Para más información sobre cómo conectarse a través del punto de conexión XMLA, consulte Conectividad del conjunto de datos con el punto de conexión XMLA: conexión a un área de trabajo de Premium.

Autenticación con nombre de usuario y contraseña

En escenarios de desarrollo y pruebas en los que la seguridad no es tan importante, puede codificar de forma rígida el nombre de usuario y la contraseña, lo que elimina la necesidad de iniciar sesión de forma interactiva cada vez que ejecute un programa para probar el código, como se muestra en el código siguiente:

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

Autenticación con una entidad de servicio

También es bastante fácil autenticarse como entidad de servicio en lugar de como usuario. Si ha creado una aplicación de Azure AD con un identificador de aplicación y un secreto de aplicación, puede autenticar el código para ejecutarse como la entidad de servicio de la aplicación de Azure AD mediante el ejemplo de código siguiente:

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 con TOM y acceder a un conjunto de datos como entidad de servicio, debe configurar un valor de Power BI de nivel de inquilino en el portal de administración de Power BI. Los pasos para configurar Power BI para admitir la conexión como entidad de servicio se describen en Inserción de contenido Power BI con la entidad de servicio y un secreto de aplicación.

Autenticación con un token de acceso de Azure AD

TOM también proporciona flexibilidad al establecer una conexión mediante un token de acceso de Azure AD válido. Si tiene las aptitudes para desarrolladores para implementar un flujo de autenticación con Azure AD y adquirir tokens de acceso, puede dar formato a la cadena de conexión de TOM sin un nombre de usuario, pero incluir el token de acceso como contraseña en su lugar, como se muestra en el ejemplo de código siguiente:

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 va a adquirir un token de acceso basado en el usuario para conectarse a un área de trabajo de Power BI con TOM, asegúrese de solicitar los siguientes permisos delegados al adquirir el token de acceso para asegurarse de que tiene todos los permisos de creación que necesita:

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 ha estado programando con la API rest de Power BI, es posible que reconozca permisos conocidos como Content.Create, Dataset.ReadWrite.All y Workspace.ReadWrite.All. Una observación interesante es que TOM usa el mismo conjunto de permisos delegados que la API rest de Power BI definida en el ámbito del identificador de recurso de Azure AD de https://analysis.windows.net/powerbi/api.

El hecho de que tanto el punto de conexión XMLA como la API rest de Power BI compartan el mismo conjunto de permisos delegados tienen sus ventajas. Los tokens de acceso se pueden usar indistintamente entre TOM y la API REST de Power BI. Una vez que haya adquirido un token de acceso para llamar a TOM para crear un nuevo conjunto de datos, puede usar el mismo token de acceso para llamar a la API rest de Power BI para establecer las credenciales del origen de datos, como se describe más adelante en este artículo.

Una cosa que tiende a confundir Power BI programadores es que las entidades de servicio no usan permisos delegados. En su lugar, al programar con TOM, configure el acceso para una entidad de servicio agregándolo al área de trabajo de destino como miembro en el rol de administrador o miembro.

Descripción de los objetos de servidor, conjunto de datos y modelo

El modelo de objetos de TOM se basa en una jerarquía con el objeto Server de nivel superior que contiene una colección de objetos Database . Al programar con TOM en Power BI, el objeto Server representa un área de trabajo de Power BI y el objeto Database representa un conjunto de datos Power BI.

Cada base de datos contiene un objeto Model que proporciona acceso de lectura y escritura al modelo de datos asociado a un conjunto de datos de Power BI. El modelo contiene colecciones para los elementos de un modelo de datos, incluidos DataSource, Table, Relationship, Perspective, Culture y Role.

Como se muestra en el código de Hola mundo, una vez que se llama a server.Conectar, puede detectar fácilmente qué conjuntos de datos existen dentro de un área de trabajo de Power BI enumerando a través de la colección Databases del objeto Server, como se muestra en el código siguiente:

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

También puede usar el método GetByName expuesto por el objeto de colección Databases para tener acceso a un conjunto de datos por nombre, de la siguiente manera:

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

Es importante distinguir entre un objeto Database y su propiedad Model interna. Puede usar las propiedades del objeto Database para detectar atributos de conjunto de datos como Name, ID, CompatibilityMode y CompatibilityLevel. También hay una propiedad EstimatedSize que permite descubrir el tamaño de un conjunto de datos. Otras propiedades incluyen LastUpdate, LastProcessed y LastSchemaUpdate , que permiten determinar cuándo se actualizó por última vez el conjunto de datos subyacente y cuándo se actualizó el esquema del conjunto de datos.

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

Aunque el objeto Database tiene sus propias propiedades, es el objeto Model interno de un objeto Database que proporciona la capacidad de leer y escribir en el modelo de datos subyacente de un conjunto de datos. Este es un ejemplo sencillo de programación del objeto Model de base de datos para enumerar a través de su colección Tables y descubrir qué tablas hay dentro.

En el modelo de objetos TOM, cada objeto Table tiene objetos de colección para sus particiones. columnas, medidas y jerarquías.

Una vez que haya recuperado el objeto Model para una base de datos, puede tener acceso a una tabla específica por su nombre en el modelo mediante el método Find de la colección Tables . Este es un ejemplo de recuperación de una tabla denominada Sales y la detección de sus miembros mediante la enumeración de la colección Columns y la colección 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);
}

Modificación de conjuntos de datos con TOM

En las secciones anteriores, ha visto cómo obtener acceso a un objeto Database y a su objeto Model para inspeccionar el modelo de datos de un conjunto de datos que se ejecuta en el servicio Power BI. Ahora es el momento de programar la primera actualización del conjunto de datos con TOM agregando una medida a una tabla.

La capacidad que usa debe estar habilitada para lectura y escritura XMLA. De forma predeterminada, la configuración de permisos de punto de conexión XMLA se establece en Lectura, por lo que debe establecerse explícitamente en Lectura y escritura por parte de alguien con permisos de administrador de capacidad . Esta configuración se puede ver y actualizar en la página Configuración de capacidad en el portal de administración.

Cuando el punto de conexión XMLA se haya configurado para lectura y escritura, puede agregar una nueva medida denominada Ingresos de ventas a la tabla Sales , como se muestra en el código siguiente:

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

Echemos un vistazo más de cerca a este código. En primer lugar, cree un nuevo objeto Measure con el operador nuevo de C# y proporcione valores para Name, Expression y FormatString. A continuación, agregue el nuevo objeto Measure a la colección Measures del objeto Table de destino llamando al método Add . Por último, llame al método SaveChanges del objeto Model para volver a escribir los cambios en el conjunto de datos del servicio Power BI.

Tenga en cuenta que las actualizaciones de un conjunto de datos se procesan por lotes en memoria hasta que se llama a SaveChanges. Imagine un escenario en el que desea ocultar todas las columnas de una tabla. Puede empezar escribiendo un bucle foreach para enumerar todos los objetos Column de una tabla y estableciendo la propiedad IsHidden para cada objeto Column en true. Una vez completado el bucle foreach , tiene varias actualizaciones de columnas que se procesan por lotes en memoria. Pero es la llamada final a SaveChanges que vuelve a insertar todos los cambios en el servicio de Power BI en un lote, como se muestra a continuación:

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

Supongamos que desea actualizar la propiedad FormatString para una columna existente. La colección Columns expone un método Find para recuperar el objeto Column de destino. Después, es cuestión de establecer la propiedad FormatString y llamar a SaveChanges, como se muestra a continuación:

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 capacidad de TOM para detectar dinámicamente lo que hay dentro de un conjunto de datos proporciona oportunidades para realizar actualizaciones de forma genérica y barrida. Imagine un escenario en el que se administra un conjunto de datos que tiene muchas tablas y decenas o incluso cientos de columnas basadas en el tipo de datos DateTime. Puede actualizar la propiedad FormatString para cada columna DateTime del conjunto de datos completo del mismo mediante lo siguiente:

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

Actualización de conjuntos de datos con TOM

Ahora vamos a realizar una operación típica de mantenimiento del conjunto de datos. Como ve en el código siguiente, no es muy complicado iniciar una operación de actualización del conjunto de datos mediante TOM:

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

Al igual que con la actualización manual y programada del conjunto de datos, las actualizaciones a través del punto de conexión XMLA se muestran en el historial de actualizaciones, pero con la etiqueta , Mediante punto de conexión XMLA.

Nota

Aunque TOM proporciona la capacidad de iniciar una operación de actualización, no puede establecer credenciales de origen de datos para un conjunto de datos de Power BI. Para actualizar los conjuntos de datos con TOM, primero debe establecer las credenciales del origen de datos en la configuración del conjunto de datos o mediante las API rest de Power BI.

Creación y clonación de conjuntos de datos

Imagine tiene un requisito para crear y clonar conjuntos de datos de Power BI mediante código escrito en C#. Comencemos escribiendo una función reutilizable denominada CreateDatabase que crea un nuevo objeto Database , como este:

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;

}

En este ejemplo, empezaremos usando el método GetNewName del objeto de colección Databases para asegurarnos de que el nuevo nombre del conjunto de datos sea único en el área de trabajo de destino. Después, el objeto Database y su objeto Model se pueden crear mediante el operador new de C# como se muestra en el código siguiente. Al final, este método agrega el nuevo objeto Database a la colección Databases y llama a la base de datos. Método Update .

Si el objetivo es copiar un conjunto de datos existente en lugar de crear uno nuevo, puede usar el siguiente método CopyDatabase para clonar un conjunto de datos de Power BI mediante la creación de un nuevo conjunto de datos vacío y, a continuación, llamar a CopyTo en el objeto Model para que el conjunto de datos de origen copie todo el modelo de datos en el conjunto de datos recién creado.

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

Creación de un conjunto de datos real desde cero

Ahora imagine que acaba de crear un nuevo conjunto de datos desde cero y ahora necesita usar TOM para crear un modelo de datos real mediante la adición de tablas, columnas, medidas, jerarquías y relaciones de tabla. Echemos un vistazo a un ejemplo de código que crea una nueva tabla que incluye columnas definidas, agrega una jerarquía dimensional de tres niveles e incluso proporciona la expresión M para la consulta de tabla subyacente:

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

Una vez que haya creado un conjunto de métodos auxiliares para crear las tablas, puede crearlas juntas para crear un modelo de datos, de la siguiente manera:

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 expone una colección Relationships en el objeto Model que permite definir las relaciones entre las tablas del modelo. Este es el código necesario para crear un objeto SingleColumnRelationship que establece una relación uno a varios entre la tabla Products y la tabla Sales :

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

Cuando haya terminado de agregar las tablas y la relación de tabla, guarde el trabajo con una llamada al modelo. SaveChanges:

model.SaveChanges();

En este momento, después de llamar a SaveChanges, debería poder ver el nuevo conjunto de datos creado en el servicio Power BI y empezar a usarlo para crear nuevos informes.

Importante

Recuerde que debe especificar las credenciales del origen de datos en la configuración del conjunto de datos o a través de la API rest de Power BI para poder actualizar el conjunto de datos.

Proyecto de ejemplo

El proyecto de ejemplo con el código de C# que ha visto en este artículo está disponible aquí. Ahora es el momento de empezar a programar con TOM y encontrar formas de aprovechar esta nueva API eficaz en el desarrollo de soluciones personalizadas para Power BI.

Vea también

Conectividad del conjunto de datos con el punto de conexión de XMLA
Solución de problemas de conectividad de los puntos de conexión XMLA