I've configured the PowerBI On-premise gateway and can see it in the PowerBI Settings in app.powerbi.com. I've created a connection using that connection with all the authentication information for the on-premise SQL server and it tested correctly.
Now, I'm trying to use that connection to pull data from a table into PowerBI, but cannot figure out how to tell PowerBI Desktop to use the connection. And I have not found the place in the web app either.
I am brand new to PowerBI, so I'm sure this is a basic question for any experienced person to help solve.
Tried several ways to create a PowerBI report. All the Get Data options are for direct sources such as SQL server and have not found out how to specify I want the app to use the gateway connection.
The Gateway is needed to refresh the data from the cloud, since the cloud system needs to know how to connect to the on-premise data source. The Gateway has the information for that data source via the connections.
When you design the report, you will typically have direct access to the on-prem data source and define the data source in Power BI designer. The Gateway is not needed for this stage.
Once you publish the report, and once you have configured the connection in the Gateway, it can then use that connection to refresh the data.
Example: in Power BI Designer you access the data source as "CompanyServer - Database ABC", which the Designer can get to directly, since you have a direct connection to the database, be it because you're working on a domain joined computer or access the company network via VPN.
In the Gateway you have created a connection and called it "DB_ABC", and it points to "CompanyServer - Database ABC".
When you publish the report and set the refresh schedule, you then select which connection should be used to refresh that data source, so you select the connection "DB_ABC".
Alternatively, you can create Dataflows in the Power BI cloud service. You can create queries that use the connection and access the on-premises data source via the gateway. Once you have the Dataflows in place and refreshed, you can open Power BI Designer and create a new report where the source is the Dataflow. If you do that, you don't need direct access to the on-prem data source in Power BI Designer.
I hope that makes it a bit clearer.