Using Tabular ProviderDataSource with On Premise PowerBi Data Gateway DB Connection

[dalton_stegner]

I am attempting to use Microsoft.AnalysisServices.Tabular .NET library in an attempt to programmitcally generate a Tabular model in powerbi. I am able to upload my table structure just fine. My issue is with my ProviderDataSource for my database. All documentation examples show using "localhost:port". I need to access an azure hosted database. Currently we have non programmitically generated datasets that use the on premise data gateway to fetch data. I believe that is my only way at this time to fetch data for my generated set as well. My question is how to select the gateway as a data source in c#. To the best of my understanding there is not a connection string. Even in hosted power bi I am unable to 'discover data sources'. Here is an example of the docs I am attempting to follow: https://docs.microsoft.com/en-us/analysis-services/tom/create-tables-partitions-and-columns-in-a-tab...

[fmms]

Hi @dalton_stegner did you ever resolve the issue?

I have just encountered the same. I have reported the details at ProviderDatasources Published to Power BI Premium Workspace cannot be refereshed afterwards with XML....

Any help is appreciated. 

[DSMau]

I was finally able to get this working end to end, at least in my scenario.

Possible caveats: I am using a service account (not principle) for this automation. The same user was used to: configure the gateway datasource, write the dataset to the Power BI workspace, and interact with the Power BI REST interface.

According to Microsoft, the XMLA endpoing used to write TOM to Power BI Premium does not allow a datasource to be bound at the time of upload. However, the datasource information needs to be included with the dataset upload. Credentials cannot be bound in this step and so are no required.

Example:

Database database = new Database({DATASET_NAME});
database.CompatibilityLevel = 1500;1500
pbiServer.Databases.Add(database);
model = new Model();
database.Model = model;
ProviderDataSource datasource = new ProviderDataSource()
{
	Name = {DATASET_NAME},
	Description = {DATASET_NAME},
	ConnectionString = "Provider=SQLNCLI11;Persist Security Info=False;Data Source={SQL_SERVER};Initial Catalog={SQL_DATABASE}",
};
model.DataSources.Add(datasource);

After the dataset is written to the Power BI workspace, it will need to be bound to the gateway datasource. I was able to do this through the rest interface:

URL
https://api.powerbi.com/v1.0/myorg/groups/{WORKSPACE_ID}/datasets/{DATASET_ID}/Default.BindToGateway
BODY
{ "gatewayObjectId": "{GATEWAY_ID}", "datasourceObjectIds": [ "{GATEWAY_DATASOURCE_ID}" ] }

URL
https://api.powerbi.com/v1.0/myorg/groups/{WORKSPACE_ID}/datasets/{DATASET_ID}/refreshes
BODY
{ "notifyOption": "none" }

A 404 on the first call indicates it was not able to identify the datasource in the dataset to bind to the provided gateway datasource ID and the datasource added to the TOM during the dataset upload is either incorrect or does not match the server/database of the gateway datasource configured.

[rogersmj_ali]

I'm working with Dalton on this issue and we've spent many, many hours poring over documentation and the code. The current problem can be best summarized this way:

When constructing a model with the TOM library, after publishing the model to the Power BI service (aka adding it to the database), the datasource we added in the code is not present. When trying to configure refresh in the Power BI portal, there are no datasources available in the dataset. When downloading a PBIX of the model we deployed with TOM, Power BI Desktop shows there are zero data sources in the model.

There's no error, no nothing...it just silently fails/ignores our declaration of the data source. We are closely following the examples in the documentation, with the only changes being the names and connection string appropriate to our data source.

Our data source is an on-premise SQL Server 2016, using the On Premises Data Gateway. Other datasets we publish with Power BI Desktop have no issues connecting to this data source.

Here's the code we're currently using to add the datasource:

database.Model.DataSources.Add(new ProviderDataSource
    {
        Name = "REDACTED (Data source name in Gateway settings)",
        Description = "Description string",
        ConnectionString = "Provider=MSOLEDBSQL;Data Source=REDACTED_DATABASE_SERVER_FQDN;Initial Catalog=REDACTED_SQL_DATABASE_NAME;Integrated Security=False;"
    });

Then we add it to the database (aka push it up to the Power BI service), and the model successfully deploys and includes all the tables we defined...but has no datasource.

Again, we have this data source registered in the Gateway settings in the portal, with the correct credentials. Other datasets can use it just fine. But anything we publish with the TOM library acts like we never added a DataSource to it at all, and says there are no data sources in the model.

[DSMau]

I am not seeing exactly the same. I am able to write the Database as a dataset to Power BI, add the tables, roles, etc to the model that I wish and then successfully update the model. I request a refresh and then try to save the changes. It does seem to remember the datasource I attached to the model when uploading, as the error calls out that is not able to connect to a datasource of that name.

Database database = new Database(NAME);
database.CompatibilityLevel = 1500;
ssasServer.Databases.Add(database);
model = new Model();
database.Model = model;
ProviderDataSource ds = new ProviderDataSource()
{
     Name = "PBI_DS_NAME",
     ConnectionString = "Provider=SQLNCLI11;Data Source={SERVERNAME};Initial Catalog={DBNAME},
};
model.DataSources.Add(ds);
// add dimentions, facts, calculated measures, security roles
ValidationResult vs = model.Validate();       //PASS
model.Database.Update();                      //PASS
model.Model.RequestRefresh(RefreshType.Full); //PASS
model.Model.SaveChanges();                    //FAIL

 I think you are right that the ProviderDataSource is not in the correct format, but I have not found any documentation suggesting the correct format for a dataset written to Power BI Premium that must call back to a server through a data gateway.

[DSMau]

Further testing reveals that if I do not request a refresh at the time of upload, it will upload successfully. The tables, measures, and dimensions are all present, but requesting a refresh fails and like the other posters, discovering datasources on the dataset does not list any. The error given for a manual refresh, is that it is references an unsupported data source, not that it cannot find a datasource associated with the dataset. When exploring the dataset with the REST API, no datasources are listed in its metadata.

It looks like when not requesting a refresh at the time of upload, the datasource is completely discarded as invalid. It does try to access it when requesting a refresh at the time of upload, but cannot find the referenced datasource on Power BI given the way we have tried to define it so far.

[DSMau]

I am in the same boat. I have tried to configure datasources with only names and with connection strings as well. The bare datasource name, the gateway/datasource as the name, and the server/database as the name. In all cases, the upload process errors with Microsoft.AnalysisServices.OperationException "data source information is not found in Power BI service"

[v-shex-msft]

Hi @dalton_stegner,

Do you mean dynamic change connection data sources that created by your code?
If that is the case, you can change the steps to add some conditions or fully parameterize your connection string, then you can modify the parameters to dynamically change the target data source.

Initialize Datasource steps: (from the sample)

string dataSourceName = "SQL Server Data Source Example"; 
dbWithTable.Model.DataSources.Add(new ProviderDataSource() 
{ 
Name = dataSourceName, 
Description = "A data source definition that uses explicit Windows credentials for authentication against SQL Server.", 
ConnectionString = "Provider=SQLNCLI11;Data Source=localhost;Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;Persist Security Info=false", 
ImpersonationMode = Microsoft.AnalysisServices.Tabular.ImpersonationMode.ImpersonateAccount, 
Account = @".\Administrator", 
Password = "P@ssw0rd", 
});

Regards,

Xiaoxin Sheng

[rogersmj_ali]

Copying and pasting what is already in the documentation does not help. The problem is that the Power BI service is completely discarding our data source that we attach to the model in the code. It doesn't tell us why, it just discards it and then there is no data source attached to the model. Please read my other comment on this post for more details.

[dalton_stegner]

Sorry to clarify on top of your sample code, I mean how could I create a connection string for a ProviderDataSource for a PowerBI Gateway? My issue stems from powerbi not being able to connect to my database since powerbi is outside the vpn of our database hosted in Azure. All samples I have found are for local host which is not my use case. Or is my only option to expose a database with a user account and allow that account to log in.