Best practice for working with multi source (file+cubes or db) data and using on-prem data gateway

[oscara]

Hi,

I have a question about how to work in the best way with multi-source model (files + SSAS in this case), publishing these models and refreshing the data with the on-premise data gateway. 

Right now, I have a pbix model which loads data both from an Excel file on my synced OneDrive for Business folder as Web resource and an SSAS cube (import) in my on-premise SQL Server environment. I have also published the pbix model to Power BI Service. Refreshing the dataset with the personal gateway works fine, but in order to use the on-premise data gateway (since it is greyed out) I realize I have to set up these data sources. I have set up the SSAS data source and solutions only loading data from that works fine, but how do set up a data source for the Excel file in my OneDrive folder, to be used by my multi-source dataset? 

I have managed to set up a separate dataset for the Excel file by using "Get Data" and importing the file from OneDrive - Business, but that only gives me access to the file itself in the Power BI Service, but there is no data source for it. 

I've tried to set up a data source for the file, using the data source type "Web" as I've done in the pbix file, but that doesn't work, I only get the error message "Invalid connection credentials.". 

Or have I gone about this the wrong way? What is the best practice when it comes to setting up data sources for multi-source (local files + db or SSAS) datasets and using the on-premise data gateway? Isn't using OneDrive for the file a good idea? Or am I restricted to using the personal gateway when mixing these kind of sources?

Thankful for any help I can get!

BR, Oscar

[v-yuezhe-msft · Accepted solution]

Hi @oscara,

Firstly, Dimah has added comments in this idea, “you can combine gateway and most online data sources as long as you're not using oAuth. The item is on our backlog and we're planning to get it out in the next few months” . In other words, when you load data both from an Excel file on OneDrive for Business folder as Web resource and an SSAS cube (import) in a PBIX file, you are not able to use on-premises gateway to refresh the dataset in Power BI Service. Please use personal gateway as a workaround.

Secondly, when you add multiple data sources (local files + db or SSAS)in a single dataset, you can use on-premises gateway to refresh your dataset, please make sure you add all the data sources(local files + db or SSAS) under on-premises gateway.

Thanks,
Lydia Zhang

[v-yuezhe-msft · Accepted solution]

Hi @oscara,

Firstly, Dimah has added comments in this idea, “you can combine gateway and most online data sources as long as you're not using oAuth. The item is on our backlog and we're planning to get it out in the next few months” . In other words, when you load data both from an Excel file on OneDrive for Business folder as Web resource and an SSAS cube (import) in a PBIX file, you are not able to use on-premises gateway to refresh the dataset in Power BI Service. Please use personal gateway as a workaround.

Secondly, when you add multiple data sources (local files + db or SSAS)in a single dataset, you can use on-premises gateway to refresh your dataset, please make sure you add all the data sources(local files + db or SSAS) under on-premises gateway.

Thanks,
Lydia Zhang

[oscara · Accepted solution]

Great, thanks for the quick reply. I think my workaround will be to place the excel file on a fileshare on the gateway server so that I can utilize the on-premise gateway and avoid the personal gateway. Looking forward to the coming fix.

BR, Oscar

[mahimabedi]

Any ETA on this?