Forum Discussion
Using Tabular ProviderDataSource with On Premise PowerBi Data Gateway DB Connection
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.
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(); //FAILI 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.
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.