Unable to connect to the data source undefined

[Anonymous]

We are utilizing an enterprise Data Gateway in order to schedule our refreshes. I have an online data source from Smartsheet (like online Excel) as well as SQL Server. I can pull in the tables from the two sources in the same model and when I go to refresh the data on the Data Gateway it will work just fine.

The issue comes in when I use an online data source such as Smartsheet in order to filter some of the SQL Server data. I can refresh the data in Power BI Desktop manually just fine, but when I publish to Service and try to refresh the data on the Data Gateway, I get an error. Can anyone help me figure out what I am doing wrong?

Please see error below:

Last refresh failed: Mon Mar 11 2019 17:32:20 GMT-0500 (Central Daylight Time) 
Unable to connect to the data source undefined.

Underlying error code:	-2147467259 Table: MaterialDim.
Underlying error message:	Information is needed in order to combine data
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.MashupSecurityException.DataSources:	[{"kind":"SQL","path":"srvusaapp97;odin"},{"kind":"Smartsheet","path":"Smartsheet"}]
Microsoft.Data.Mashup.MashupSecurityException.Reason:	PrivacySetting
Cluster URI:	WABI-US-NORTH-CENTRAL-redirect.analysis.windows.net
Activity ID:	5a8c0dc8-38fc-4279-b18d-aaae7f060671
Request ID:	174a0627-e6b5-d2d1-3d67-de646133960f
Time:	2019-03-11 22:32:20Z

[Anonymous]

We are utilizing an enterprise Data Gateway in order to schedule our refreshes. I have an online data source from Smartsheet (like online Excel) as well as SQL Server. I can pull in the tables from the two sources in the same model and when I go to refresh the data on the Data Gateway it will work just fine.

The issue comes in when I use an online data source such as Smartsheet in order to filter some of the SQL Server data. I can refresh the data in Power BI Desktop manually just fine, but when I publish to Service and try to refresh the data on the Data Gateway, I get an error. Can anyone help me figure out what I am doing wrong?

Please see error below:

Last refresh failed: Mon Mar 11 2019 17:32:20 GMT-0500 (Central Daylight Time) 
Unable to connect to the data source undefined.

Underlying error code:	-2147467259 Table: MaterialDim.
Underlying error message:	Information is needed in order to combine data
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.MashupSecurityException.DataSources:	[{"kind":"SQL","path":"srvusaapp97;odin"},{"kind":"Smartsheet","path":"Smartsheet"}]
Microsoft.Data.Mashup.MashupSecurityException.Reason:	PrivacySetting
Cluster URI:	WABI-US-NORTH-CENTRAL-redirect.analysis.windows.net
Activity ID:	5a8c0dc8-38fc-4279-b18d-aaae7f060671
Request ID:	174a0627-e6b5-d2d1-3d67-de646133960f
Time:	2019-03-11 22:32:20Z

[Anonymous]

Hi Courtneyfinke,

This is because of the two sources which establshed connection for your data model.

Data Management Gateway would require data source to be defined in the PBI service. You can provide either SQL Server or Excel Online in a time.

If your PBI report has one connection to SQL server, then it would be fine or the vise versa.

The solution is to import your online excel data in to sql server table and build relationship between objects and then configure your gateway for data refresh. So, you will have only one source to confiure gateway.

Regards,

Pradeep

[Anonymous]

That's not really a viable solution because this list is being updated often by other people in our organization and would not be a one time import into SQL Server--is there a way to import the table from Excel Online into SQL Server any time someone makes a change?

Will there be a time when the Data Management Gateway would allow two data sources?