SSAS TOM - how to access datasource for PowerBI model

Question

I'm using TOM to interrogate and manipulate a PBIX file open in Power BI. The PBIX file uses a parameter to set the connection string to the sql database that sources the data for the model. I cannot figure out how to return the datasource from the metadata for the file. Here is the code I have:

//this all works fine
Server server = new Server(); //from Microsoft.AnalysisServices.Tabular library
server.connect(connStr); //defined elsewhere
Model model = server.Databases[0].Model; //I am using this model to loop through tables and columns and it works fine

//these do not
model.DataSources
model.DataSources[0]
model.DataSources.find...

model.datasources.tostring() returns Microsoft.AnalysisServices.Tabular.DataSourcesCollection

So it seems I have a collection, but trying to reference an item in the collection using DataSources[0] throws a parameter out of bounds error, which sort of makes sense in that the DataSources.count property returns 0 (which seems wrong ->). In looking at the datasources in the model via PowerBI desktop, the one I want is in there, fully formed with the database name filled from the parameter.

Can anyone give me a clue as to what's going on here or how I can get the datasource from the model?

Answer 1

DataSources are used in SSAS proper, but not from Power BI Desktop models.

A Power BI model will only have Expressions and Partitions with M code in the QueryDefinition column. Expressions are Power Queries that aren't loaded to a table, and Partitions are Power Queries that are loaded directly to a table. You should find the connection info in one of these 2 places. You have to parse out connection info yourself from the query, unlike DataSources that have connection info in columns.

You can view all of this easily by installing the Tabular Editor v2 tool and launching it from the External Tools menu in Power BI. See this page for details on its scripting capabilities.

Launching Tabular Editor

Object tree from Tabular Editor

Shared Expression properties from Tabular Editor