My experience with Power BI is successful, but I am using an ODBC connector. I am not sure that there is a way to directly connect and refresh without that connector. If you are doing this directly from your computer, the connector will be installed on your computer. If you are going to put this on a server and want your data to refresh automatically, you will need to install the ODBC driver on the server. As far as an auto-refresh, you will need an on-premises gateway installed on your computer or a shared gateway on the server and point your file to that refresh. The refresh will not happen if this is on your computer and your computer is off. Through the gateway refresh, you can set it up to refresh 8 times per day.
Once the driver is installed, from within Power BI, you will select Get Data > More > Other > ODBC.
You will still use a user token and add the apps to that
Here is a link to the ODBC driver
https://www.qunect.com/appnotes.html
If you follow the directions and have permission to make a change to the ODBC System DNS, then you will see all of your tables and the reports that you have under them. If you are locked down like I am, and you cannot modify the System DNS, then you can use an SQL select statement and the table/report ID to pull in a specific report. I typically set up a report with the info on it that I need for a dashboard. In my case, I created a whole other Power BI app, and did a table-to-table import from five other apps into a single table in my KPI app to get all of the data that I need.
