Programmazione Power BI set di dati con il modello a oggetti tabulare (TOM, Tabular Object Model)
Si applica a:
SQL Server 2016 e versioni successive Analysis Services
Azure Analysis Services
Power BI Premium
Questo articolo è stato originariamente creato da Power BI Customer Advisory Team (CAT) per Power BI DevCamp,una raccolta di sessioni, articoli e video sulla programmazione avanzata per Power BI.
Power BI Premium set di dati includono l'endpoint XMLA. L'endpoint è significativo per gli sviluppatori Power BI perché fornisce API per interagire con il motore di Analysis Services in esecuzione nel servizio Power BI e per programmare direttamente Power BI set di dati. Un numero crescente di professionisti Power BI ha scoperto di poter creare, visualizzare e gestire set di dati Power BI usando strumenti preesistnti che usano il protocollo XMLA, ad esempio SQL Server Management Studio, l'editor tabulare e DAX Studio. Gli sviluppatori .NET possono ora scrivere codice C# in un'applicazione .NET per creare e modificare i set di dati direttamente nel servizio Power BI.
Il modello a oggetti tabulare (TOM, Tabular Object Model) è una libreria .NET che fornisce un livello astratto sopra l'endpoint XMLA. Consente agli sviluppatori di scrivere codice in termini di modello di programmazione intuitivo che include classi come Model, Table, Column e Measure. In background, TOM converte le operazioni di lettura e scrittura nel codice in richieste HTTP eseguite sull'endpoint XMLA.
L'obiettivo di questo articolo è iniziare a usare TOM e illustrare come scrivere il codice C# necessario per creare e modificare i set di dati mentre sono in esecuzione nel servizio Power BI. Tuttavia, TOM può essere usato anche in scenari che non coinvolgono l'endpoint XMLA, ad esempio durante la programmazione su un set di dati locale in esecuzione in Power BI Desktop. Per altre informazioni sull'uso di TOM con Power BI Desktop, vedere la serie di blogdi Tom Seamark, membro di Power BI CAT, e guardare il video How to Program Datasets using the Tabular Object Model (TOM) (Come programmare i set di dati usando il modello a oggetti tabulare ) di Power BI DevCamp.
TOM rappresenta un'API nuova e potente per Power BI sviluppatori, separata e distinta dalle API REST Power BI. Sebbene vi sia una certa sovrapposizione tra queste due API, ognuna di queste API include una quantità significativa di funzionalità non incluse nell'altra. Esistono inoltre scenari in cui uno sviluppatore deve usare entrambe le API insieme per implementare una soluzione completa.
Attività iniziali con il modello a oggetti tabulare
La prima cosa che è necessario ottenere prima di programmare con TOM è l'URL per una connessione all'area di lavoro. L'URL di connessione dell'area di lavoro fa riferimento a un'area di lavoro specifica e viene usato per creare una stringa di connessione che consente al codice di connettersi all'area di lavoro di Power BI e ai set di dati in esecuzione all'interno. Per iniziare, passare alla pagina Impostazioni di un'area Power BI lavoro in esecuzione in una capacità dedicata.
Nota
L'endpoint XMLA è supportato solo per i set di dati in esecuzione in una capacità dedicata. Non è disponibile per i set di dati in esecuzione in una capacità condivisa. Se si lavora con i set di dati in Power BI Premium capacità per utente, è possibile connettersi come utente, ma non come entità servizio.
Dopo aver selezionato la scheda Premium del riquadro Impostazioni, copiare l'URL della connessione all'area di lavoro negli Appunti.
Il passaggio successivo consiste nel creare una nuova applicazione .NET in cui scrivere il codice C# che consente di programmare usando TOM. È possibile creare un'applicazione Web o un'applicazione desktop usando .NET 5, .NET Core 3.1 o versioni precedenti nel .NET Framework. In questo articolo viene creata una semplice applicazione console C# usando .NET 5 SDK.
Creare una nuova applicazione console
Iniziare usando l'interfaccia della riga di comando di .NET per creare una nuova applicazione console.
dotnet new console --name`
Aggiungere il pacchetto modello a oggetti NuGet tabulare
Dopo aver creato l'applicazione console, aggiungere il pacchetto di NuGet Microsoft.AnalysisServices.AdomdClient.NetCore.retail.amd64 che contiene il modello a oggetti tabulare (TOM). È possibile installare il pacchetto in un'applicazione .NET 5 usando l'interfaccia della riga di comando di .NET seguente:
dotnet add package Microsoft.AnalysisServices.AdomdClient.NetCore.retail.amd64
Aggiungere la stringa di connessione
Quando il progetto ha il NuGet con la libreria TOM installata, è possibile creare l'applicazione Hello World tradizionale con TOM. L'applicazione si connette a un'area di lavoro Power BI usando l'URL di connessione dell'area di lavoro, quindi enumera i set di dati nell'area di lavoro e visualizza i relativi nomi nella finestra della 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 datasets in workspace to display their names
foreach (Database database in server.Databases) {
Console.WriteLine(database.Name);
}
}
}
In questo esempio la stringa di connessione contiene l'URL di connessione dell'area di lavoro, ma non contiene informazioni sull'utente. Se si esegue l'applicazione console con questo codice, l'applicazione inizierà a essere eseguita e verrà visualizzata una finestra basata su browser per l'accesso. Se si accede con un account utente che dispone delle autorizzazioni per accedere all'area di lavoro a cui fa riferimento l'URL di connessione dell'area di lavoro, la libreria TOM è in grado di acquisire un token di accesso, connettersi al servizio Power BI ed enumerare i set di dati nell'area di lavoro.
Per altre informazioni sulla connessione tramite l'endpoint XMLA, vedere Connettività del set di dati con l'endpoint XMLA - Connessione aun'area Premium lavoro.
Autenticazione con nome utente e password
Per gli scenari di sviluppo e test in cui la sicurezza non è importante, è possibile impostare come hard code il nome utente e la password, eliminando la necessità di accedere in modo interattivo ogni volta che si esegue un programma per testare il codice, come illustrato nel codice seguente:
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);
Autenticazione con un'entità servizio
È anche piuttosto semplice eseguire l'autenticazione come entità servizio anziché come utente. Se è stata creata un'applicazione Azure AD con un ID applicazione e un segreto dell'applicazione, è possibile autenticare il codice per l'esecuzione come entità servizio per l'applicazione Azure AD usando l'esempio di codice seguente:
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);
Per programmare con TOM e accedere a un set di dati come entità servizio, è necessario configurare un'impostazione di Power BI a livello di tenant nel portale Power BI di amministrazione. I passaggi per la configurazione Power BI per supportare la connessione come entità servizio sono descritti in Incorporare contenuto Power BI con un'entità servizio e un segreto dell'applicazione.
Autenticazione con un token Azure AD di accesso
TOM offre anche flessibilità quando si stabilisce una connessione usando un token Azure AD di accesso. Se si hanno le competenze di sviluppatore per implementare un flusso di autenticazione con Azure AD e acquisire i token di accesso, è possibile formattare la stringa di connessione TOM senza un nome utente, ma includere il token di accesso come password, come illustrato nell'esempio di codice seguente:
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 si sta acquisendo un token di accesso basato sull'utente per connettersi a un'area di lavoro di Power BI con TOM, assicurarsi di richiedere le autorizzazioni delegate seguenti quando si acquisisce il token di accesso per assicurarsi di avere tutte le autorizzazioni di creazione necessarie:
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 si sta programmando con l'API REST Power BI, è possibile riconoscere autorizzazioni note, ad esempio Content.Create, Dataset.ReadWrite.All e Workspace.ReadWrite.All. Un'osservazione interessante è che TOM usa lo stesso set di autorizzazioni delegate dell'API REST Power BI definita nell'ambito dell'ID risorsa Azure AD di https://analysis.windows.net/powerbi/api .
Il fatto che l'endpoint XMLA e l'API REST Power BI condividono lo stesso set di autorizzazioni delegate presenta vantaggi. I token di accesso possono essere usati in modo intercambiabile tra TOM e l Power BI aPI REST. Dopo aver acquisito un token di accesso da chiamare in TOM per creare un nuovo set di dati, è possibile usare lo stesso token di accesso per chiamare l'API REST di Power BI per impostare le credenziali dell'origine dati, come descritto più avanti in questo articolo.
Un aspetto che tende a confondere Power BI programmatori è che le entità servizio non usano autorizzazioni delegate. Al contrario, durante la programmazione con TOM si configura l'accesso per un'entità servizio aggiungendola all'area di lavoro di destinazione come membro nel ruolo di amministratore o membro.
Informazioni sugli oggetti server, set di dati e modello
Il modello a oggetti in TOM si basa su una gerarchia con l'oggetto Server di primo livello che contiene una raccolta di oggetti di database. Quando si programma con TOM in Power BI, l'oggetto Server rappresenta un'area di lavoro Power BI e l'oggetto Database rappresenta un set Power BI dati.
Ogni database contiene un oggetto Model che fornisce l'accesso in lettura/scrittura al modello di dati associato a un set Power BI dati. Il modello contiene raccolte per gli elementi di un modello di dati, tra cui DataSource, Table, Relationship, Perspective, Culture e Role.
Come illustrato nel codice Hello World, dopo aver chiamato server.Connessione è possibile individuare facilmente i set di dati presenti in un'area di lavoro di Power BI enumerando tramite la raccolta Databases dell'oggetto Server, come illustrato nel codice seguente:
foreach (Database database in server.Databases) {
Console.WriteLine(database.Name);
}
È anche possibile usare il metodo GetByName esposto dall'oggetto raccolta Databases per accedere a un set di dati in base al nome, in questo modo:
Database database = server.Databases.GetByName("Wingtip Sales");
È importante distinguere tra un oggetto Database e la relativa proprietà Model interna. È possibile usare le proprietà dell'oggetto database per individuare gli attributi del set di dati, ad esempio Name, ID, CompatibilityMode e CompatibilityLevel. È anche disponibile una proprietà EstimatedSize che consente di individuare le dimensioni di un set di dati. Altre proprietà includono LastUpdate, LastProcessed e LastSchemaUpdate, che consentono di determinare quando è stato ultimo aggiornamento del set di dati sottostante e quando è stato aggiornato lo schema del set di dati.
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);
}
Sebbene l'oggetto Database abbia proprietà proprie, è l'oggetto Model interno di un oggetto Database che consente di leggere e scrivere nel modello di dati sottostante di un set di dati. Di seguito è riportato un semplice esempio di programmazione dell'oggetto Model del database per enumerare tramite la relativa raccolta Tables e individuare le tabelle all'interno.
Nel modello a oggetti TOM ogni oggetto Table include oggetti raccolta per le relative partizioni. colonne, misure e gerarchie.
Dopo aver recuperato l'oggetto Model per un database, è possibile accedere a una tabella specifica in base al nome nel modello usando il metodo Find della raccolta Tables. Di seguito è riportato un esempio di recupero di una tabella denominata Sales e dell'individuazione dei relativi membri tramite l'enumerazione tramite la raccolta Columns e la raccolta 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);
}
Modifica dei set di dati con TOM
Nelle sezioni precedenti si è visto come accedere a un oggetto Database e al relativo oggetto Model per esaminare il modello di dati di un set di dati in esecuzione nel servizio Power BI. È ora possibile programmare il primo aggiornamento del set di dati con TOM aggiungendo una misura a una tabella.
La capacità in uso deve essere abilitata per XMLA in lettura/scrittura. Per impostazione predefinita, l'impostazione autorizzazioni dell'endpoint XMLA è impostata su Lettura, quindi deve essere impostata in modo esplicito su Lettura/Scrittura da parte di un utente con autorizzazioni di amministratore della capacità. Questa impostazione può essere visualizzata e aggiornata nella pagina Impostazioni di capacità nel portale di amministrazione.
Dopo aver configurato l'endpoint XMLA per la lettura/scrittura, è possibile aggiungere una nuova misura denominata Sales Revenue alla tabella Sales, come illustrato nel codice seguente:
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();
Di seguito viene ora preso in esame questo codice. Creare prima di tutto un nuovo oggetto Measure usando l'operatore new di C# e specificare i valori per Name, Expression e FormatString. Aggiungere quindi il nuovo oggetto Measure alla raccolta Measures dell'oggetto Table di destinazione chiamando il metodo Add. Chiamare infine il metodo SaveChanges dell'oggetto Model per scrivere di nuovo le modifiche nel set di dati nel Power BI dati.
Tenere presente che gli aggiornamenti a un set di dati vengono in batch in memoria fino a quando non si chiama SaveChanges. Imagine uno scenario in cui si desidera nascondere ogni colonna in una tabella. È possibile iniziare scrivendo un ciclo foreach per enumerare tutti gli oggetti Column per una tabella e impostando la proprietà IsHidden per ogni oggetto Column su true. Al termine del ciclo foreach, sono disponibili diversi aggiornamenti di colonna in batch in memoria. Ma è la chiamata finale a SaveChanges a eseguire il push di tutte le modifiche al servizio Power BI in un batch, come illustrato di seguito:
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();
Si vuole aggiornare la proprietà FormatString per una colonna esistente. La raccolta Columns espone un metodo Find per recuperare l'oggetto Column di destinazione. Successivamente, è sufficiente impostare la proprietà FormatString e chiamare SaveChanges, come illustrato di seguito:
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à di TOM di individuare dinamicamente il contenuto di un set di dati offre la possibilità di eseguire aggiornamenti in modo generico e dinamico. Imagine uno scenario in cui si gestisce un set di dati che include molte tabelle e decine o addirittura centinaia di colonne basate sul tipo di dati DateTime. È possibile aggiornare contemporaneamente la proprietà FormatString per ogni colonna DateTime nell'intero set di dati usando quanto segue:
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();
Aggiornamento dei set di dati con TOM
A questo punto si eseguirà una tipica operazione di manutenzione del set di dati. Come si può vedere nel codice seguente, non è molto complicato avviare un'operazione di aggiornamento del set di dati usando TOM:
public static void RefreshDatabaseModel(string Name) {
Database database = server.Databases.GetByName(Name);
database.Model.RequestRefresh(RefreshType.DataOnly);
database.Model.SaveChanges();
}
Analogamente all'aggiornamento manuale e pianificato del set di dati, gli aggiornamenti tramite l'endpoint XMLA vengono visualizzati in Cronologia aggiornamenti, ma con l'etichetta Via XMLA Endpoint.
Nota
Anche se TOM offre la possibilità di avviare un'operazione di aggiornamento, non può impostare le credenziali dell'origine dati per un set Power BI dati. Per aggiornare i set di dati con TOM, è necessario innanzitutto impostare le credenziali dell'origine dati nelle impostazioni del set di dati o usando le API REST Power BI dati.
Creazione e clonazione di set di dati
Imagine è necessario creare e clonare Power BI set di dati usando il codice scritto in C#. Per iniziare, scrivere una funzione riutilizzabile denominata CreateDatabase che crea un nuovo oggetto Database, come illustrato di seguito:
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;
}
In questo esempio si inizierà con il metodo GetNewName dell'oggetto raccolta Databases per assicurarsi che il nome del nuovo set di dati sia univoco all'interno dell'area di lavoro di destinazione. Successivamente, è possibile creare l'oggetto Database e il relativo oggetto Model usando l'operatore new C# come illustrato nel codice seguente. Al termine, questo metodo aggiunge il nuovo oggetto Database alla raccolta Databases e chiama il database. Metodo Update.
Se l'obiettivo è copiare un set di dati esistente anziché crearne uno nuovo, è possibile usare il metodo CopyDatabase seguente per clonare un set di dati Power BI creando un nuovo set di dati vuoto e quindi chiamando CopyTo sull'oggetto Model per il set di dati di origine per copiare l'intero modello di dati nel set di dati appena creato.
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;
}
Creazione di un set di dati reale da zero
Si supponga ora di aver appena creato un nuovo set di dati da zero ed è ora necessario usare TOM per comporre un modello di dati reale aggiungendo tabelle, colonne, misure, gerarchie e relazioni tra tabelle. Si esamini un esempio di codice che crea una nuova tabella che include colonne definite, aggiunge una gerarchia dimensionale a tre livelli e fornisce anche l'espressione M per la query di tabella sottostante:
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;
}
Dopo aver creato un set di metodi helper per creare le tabelle, è possibile crearle insieme per creare un modello di dati, come illustrato di seguito:
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 espone una raccolta Relationships nell'oggetto Model che consente di definire le relazioni tra le tabelle nel modello. Ecco il codice necessario per creare un oggetto SingleColumnRelationship che stabilisce una relazione uno-a-molti tra la tabella Products e la tabella Sales:
model.Relationships.Add(new SingleColumnRelationship {
Name = "Products to Sales",
ToColumn = tableProducts.Columns["ProductId"],
ToCardinality = RelationshipEndCardinality.One,
FromColumn = tableSales.Columns["ProductId"],
FromCardinality = RelationshipEndCardinality.Many
});
Dopo aver aggiunto le tabelle e la relazione tra tabelle, salvare il lavoro con una chiamata al modello. SaveChanges:
model.SaveChanges();
A questo punto, dopo aver chiamato SaveChanges, dovrebbe essere possibile visualizzare il nuovo set di dati creato nel servizio Power BI e iniziare a usarlo per creare nuovi report.
Importante
Tenere presente che è necessario specificare le credenziali dell'origine dati nelle impostazioni del set di dati o tramite l'API REST Power BI prima di poter aggiornare il set di dati.
Progetto di esempio
Il progetto di esempio con il codice C# illustrato in questo articolo è disponibile qui. È ora possibile iniziare a programmare con TOM e trovare modi per sfruttare questa nuova API potente nello sviluppo di soluzioni personalizzate per Power BI.
Vedere anche
Connettività ai set di dati con l'endpoint XMLA
Risolvere i problemi relativi alla connettività degli endpoint XMLA