Connect to Power BI Desktop from Visual Studio
We will connect to Power BI Desktop database from Visual Studio and read all of its Table and Column names. When we open Visual Studio, we choose to create a new project (1). As a type of project we will choose "Console App" (2). In new dialog we add a name to our project (3). Here we can choose where our project will be saved (4). Add this folder to your Antivirus program as an exception. My Antivirus program (Avast) blocked execution of a console app.
After we declared our project, we have to download two dll libraries. Those are "Microsoft.AnalysisServices.Core.dll" and "Microsoft.AnalysisServices.Tabular.dll". I added them to files for download, at the end of this post. We already have them on our computer if Tabular Editor is installed. If that is true, in "Program Files (x86)/Tabular Editor" folder, we have both libraries (1). Those two libraries have to be referenced in our project. In "Solution Explorer" of Visual Studio, we add both of them (2,3). Now, they are available in our project.
Below we can see the code of our project. In purple parts we have to enter credentials of our PBIX file, and fullpath of the file in which we will write the names of tables and columns. Orange part is for creating server object and connecting to database by using Connect method and connection string. Green part is preparation for writing to disk. Blue part is a loop which will get all names of a table and column names.
using System;
using Microsoft.AnalysisServices.Tabular;
using System.IO;
namespace TOMSamples
{
class Program
{
static void Main(string[] args)
{
string ConnectionString = "DataSource=localhost:58064";
using (Server server = new Server())
{
server.Connect(ConnectionString);
var OurModel = server.Databases["a02591ad-8656-4e1d-9a75-1672b223c8e6"].Model;
string file = @"C:\Users\Sima\Desktop\TablesColumns.csv";
using (StreamWriter fileWriter = new StreamWriter(file))
foreach(var Tbl in OurModel.Tables)
{
foreach (Column Col in Tbl.Columns)
{
fileWriter.Write(Tbl.Name + ";" + Col.Name + "\n");
}
}
}
}
}
}
You can find more samples of code to interact with tabular model on this address. This code is placed in Visual studio (1) and executed by clicking on the Start button (2).
Final result is CSV file with all table and column names. Interesting is that each table has column with the name "Row Number-2662979B-1795-4F74-8F37-6A1BA8059B61". This is probably primary key column used internally by Power BI Desktop.
PBIX sample file, dll libraries and C# code can be downloaded below.
.
