Combining Web Data with On-premise gateway SQL server in direct query

[CheenuSing]

Hi

I am trying to use on-premise sql server data in direct query mode  and web data. Power BI desktop is not allowing. Is it allowed in Power Bi Service or any workaround.

Cheers

CheenuSing

[Seth_C_Bauer]

@CheenuSing You can't mix connection types within the same data set. And there is no way to combine datasets out in the Service... You would either need to pull the web data into the SQL Server, or change the connection to Import in the Desktop in order to combine the datasets and use them within the same visuals.

[CheenuSing]

Hi @Seth_C_Bauer

Thanks for confirming that you can not combine two different types of data sources in direct-query. 

What if there are two different SQL Servers from which data needs to be pulled. Will this work in direct-query ?

Cheers

CheenuSing

[Seth_C_Bauer · Accepted solution]

@CheenuSing From Power BI you can only connect to a single SQL database to remain in Direct Query mode. You can however, utlize SQL connections behind the scene's like Linked Servers etc within the query and that will work in Power BI.

[CahabaData]

@Seth_C_Bauer

I would like to ask this generically for the data's entire life cycle from In to Presentation: so if there are let's say 3 separate data sources to mash together; let's say some data from a web site, some from an excel sheet, and some from a database (SQL Server).

Then one must bring the data into Desktop product.  Perform the ETL Design and set up reports in Desktop.

One would set up a Gateway so that those 3 can update periodically; would it be 1 gateway or 3 gateways?

And then move the data up to Service to set up Dashboards - after that is set up, with what tool does one automate that to update when new data is brought in by the Gateway(s) to Desktop?

Is this the correct approach - just seek a conceptual overview, not specific implementation detail......

[Seth_C_Bauer]

@CahabaData The Gateway is needed for the Service to refresh from datasources. You don't need a gateway when you are designing in the Desktop.

You would set up 1 gateway, and that gateway would handle all 3 data sources. You can't currently mix data sources in the same dataset across gateways.

After the gateway is installed, you can manage the refresh of the dataset to utilize the gateway. (setup and config is different for the On Premises Data Gateway- create datasources, etc)

Personal just works if it is a supported datasource.

If you use import, you can schedule a refresh up to 8x per day.

[CahabaData]

got it: gateway for refresh of data into desktop

how about the refresh of new data up to Service for the dashboards.... is that automated?