Multiple type data source in a single on premises (non-personal) gateway

[smpa01]

Hello experts,

I am a week old to Power BI service though extremly fluid with Power BI desktop. I am runing some intial tests to ensure that my reports on Power BI service are refreshed automatically without any problem.

Issue:

All the BI reports that we are planning to build and set up on the scheduled refresh through Power BI service would always involve multiple type of data source (e.g. SQL server table, excel from one drive, a table from web etc.). Power BI app service assigns a personal gateway for a dataset that involves multiple type datasource and the refresh fails because the gateway is not online.

Now if I am building a report which comes from a single data source type such as a SQL Table, Power Bi service assigns SQL On Prem gateway to this and the schedule refreshes go through nicely. It is also the same case if the data source is single data source type and coming from one drive excel files. The refreshes go through nicely without any problem. I have done my tests on a report that depends on a dataset that relies on singular type data source.

In our case SQL On Prem is a gateway set up by BI admin where only a SQL sever databse tables are configured currently.

But keeping in mind our requirement, I have done a testing to see what gateway Power BI app assigns once a dataset involves dataset from different data source type. For example, I have a dataset called test1 which utilizes a SQL Table, a one drive file and a table from web. Power BI app assigned a personal gateway and the refresh failed as I switched off my computer  on purpose to see if the dataset could still get refreshed.

Now it is neither practical nor possible for the report writer to keep his computer on to ensure the personal gateway is active always. The answer is definitely On-Prem gateway as it does not involve BI writers to keep their computer on.

My question to you at this point, is there a way to set up an on-premises gateway (non-personal) and cluster different data source type within a single on premises gateway? If yes, I can assign that on-prem gateway  (non-personal) for a report as above and I don’t need to worry about leaving my computer online. In that way, I can map different type data sources in that dataset to different data sources within that non-personal on prem gateway.

Thank you in advance.

[smpa01 · Accepted solution]

Issue :- Anyone new to PBI service, utilizing multiple type data source (SQL, one drive excel, web table etc) in their report and the dataset refresh fails in service and wondering why, the solution is following

Solution:- Whatever datasource is used, they must be set up in On Prem Enterprise gateway and multiple type data source can be clustered there. If the excel file comes from one drive , it is within AZURE and it does not need to be set up inside On Premises Enterprise gateway. It just  needs the credential to be edited with OAuth2 authentication.

If the data sources are not added within On Prem gateway, PBI service asigns a Personal Gateway. It means the dataset that depends on that gateway to get refereshed the computer for the gateway mucst be kept on which is neither practical nor possible.

[v-xuding-msft]

Hi @smpa01 ,

Yes, you could install the on-premises gateway from the link.  For more details, please reference the documents to learn.

On-premises data gateway in-depth 

Manage data sources 

Troubleshoot the on-premises data gateway 

Troubleshooting refresh scenarios 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.