6 years later and it's non-intuitive that the dataset is created at the beginning of creating a PowerBI report and nowhere else. I don't see options for adding existing SQL tables to a dataset in paginated reports. Anyway, I watched 10+ other videos and this is the only one that actually showed BOTH creating the gateway AND creating a report. That was very helpful so thank you.
I went from video to video looking for a good explanation of how to set up a gateway and the data sources, your video was the one that helped me and really I managed to configure everything correctly and now I have my report up and running embed in my team SharePoint. Great job and thanks a lot!!!!!
Thank you for the video! quick question, can we do direct query or does it have to be Import for onprem? using personal or recommended gateway. thank you
Hi sql talk. As per my understanding gateways are used to refresh the data. And as live connection or direct query are always being a real time data then why we need the gateway feature enabled for this direct query and live connection methods also. Is there any special reason
Hello, As with the above explained example, two datasources are used to create the report. If in Power Bi Online, only one data source has been created. How will the refresh work during that scenario? Will it refresh only the part whose datasource is available on Power Bi Online?
So, when configuring a scheduled refresh I configure it for a dataset and not the gateway? For example; report A and report B have dataset A en dataset B both through gateway A. Then I can set the refresh for dataset A at night and for dataset B in the morning. Correct?
Hi...very useful video, thank you!!!...I Have a Pro License and I have a question... add to 8 different refreshing times, means that Power BI online will attempt to refresh each chosen hours as long as the administrator computer is on? Is there a way to get the data refreshed even when the administrator computer is off? (I´m getting the data out of Odata source which from MSproject output )...Thank you very much for your answer in advance!!!
Yes! What you do is install the free Data Gateway on a server somewhere in your environment. This will allow refreshes of your on-prem data without the need for the admin. computer to be involved. Check out my other video that explains how to set this up. ua-cam.com/video/GwuRQhm241c/v-deo.html
Is there any way to refresh data (from on-premises sql) on published url page(report)? I'd like to give users a way that refresh the report( with updated data) by themselves, not pre- scheduled event. Thanks in advance.
adding a local pc folder path to the gateway on BI service and entering my windows credentials, but unable to set it up.. get error>Details: "We could not register this data source for any gateway instances within this cluster. Please find more details below about specific errors for each gateway instance
I'm getting error when selecting gateway that "Failed to use the selected gateway. Please make sure the gateway is up and running and try again", but gateway service is running fine with no issues. Could you please help with this issue and fixing the same.
Which network ports does it use when connecting back to On Premises data please? Is it push from gateway up or pull from Power BI server ? Thanks in advance!
TCP 443, 5671, 5672, 9350 thru 9354. The gateway doesn't need inbound ports. Check out more details here: docs.microsoft.com/en-us/power-bi/service-gateway-onprem
This is pretty frustrating for me, all the demos seem to have the data source on the same PC as the Power BI desktop development. Is that required? I'm trying to design on PC#1 with SQL instance#1, but eventually have it pull data from SQL#2 (same schema). I can't figure out how to get desktop to design using the gateway connection, and I can't figure out how to change the connection string (when I publish, it says the gateway doesn't have the instance name of SQL#1, etc). It sounds like it's impossible to do anything other than design and publish on the same machine as the gateway? (I thought that's what the "personal gateway" was...). So confusing. I have been able to publish based on a dashboard built on a PBI desktop I installed on our data server, but I think it's crazy I have to use my data server as a dev machine as well.
Great point Steve! When you develop with Power BI Desktop you can ignore gateways since you don't configure them at that point. You only consider a gateway once the dataset and reports are published. You bring up a very valid point since once you set up your dataset connections to use the gateway (not in Power BI Desktop but in a browser) the report seems to be looking for the same "Server" name in your gateway as you used when you imported the data from your desktop. There may be a better way but what if you set up a SQL Server alias on your desktop so your local instance#1 responds to the same name as the SQL#2 (production instance)? Here is a link on how to set that up: docs.microsoft.com/en-us/sql/database-engine/configure-windows/create-or-delete-a-server-alias-for-use-by-a-client?view=sql-server-2017. Not 100% sure if this is the absolute best way but it may work. Let me know.
What If don't want the scheduled update? I just want to manually refresh the data without using the PBI Desktop? I mean the customer will do that using the report or dashboard link I've sent. Thanks.
You don't have to put a schedule on the report. Just leave that and you can manually refresh from the Power BI Service (aka cloud). You will still need to set up the gateway so Power BI knows how to get to the source.
thank you for this video it's very helpful, but I was wondering if you can help me with my problem which is that I work in a company and this company has a local server and I have installed Xamp to this server and now I want to connect the power bi to this server , can you please tell how can i do that?
marwan taha you need to create a dataflow with a “new entity” defined as sql database server then add your credentials and then save it then you should set up a dataset to set up your schedules.
Hi, Please, do you have some tutorial for creating a datasourse to SharePoint with a Excel file? Also, I need to setup Gateway in order to refresh automatically this spreadsheet in the same SharePoint. Thanks
6 years later and it's non-intuitive that the dataset is created at the beginning of creating a PowerBI report and nowhere else. I don't see options for adding existing SQL tables to a dataset in paginated reports. Anyway, I watched 10+ other videos and this is the only one that actually showed BOTH creating the gateway AND creating a report. That was very helpful so thank you.
I am so glad this helped in some way @jgrnht. Thank you for commenting.
I went from video to video looking for a good explanation of how to set up a gateway and the data sources, your video was the one that helped me and really I managed to configure everything correctly and now I have my report up and running embed in my team SharePoint. Great job and thanks a lot!!!!!
how do u embed this refreshable report in sharepoint online?
The best video I found on UA-cam reg. this issue. Many Many Tanks.
Thank you so much! Man, I wish I have watched this video before I spent 3 hours on call with unskilled Microsoft support!
Thank you zillion times!
Awesome ,one of the best video for BI developers..
Very helpful video! I was struggling to refresh the data. Appreciate it
Great no fuss video, many thanks saved me a lot of time.
awesome Video .It was helpful to schedule the refresh.
was this all just about how to set up a scheduled refresh for the data? thanks in advance
Excellent video. It worked perfectly, thanks for doing this.
very clean explanation sir.Thank u
Nice sharing. Thank you!
Thank you for the video! quick question, can we do direct query or does it have to be Import for onprem? using personal or recommended gateway. thank you
Direct query works too for on-prem!
Hi sql talk. As per my understanding gateways are used to refresh the data. And as live connection or direct query are always being a real time data then why we need the gateway feature enabled for this direct query and live connection methods also. Is there any special reason
how to know the name of the server of my gateway
Go to your datasets in PowerBI.com and then pick Settings. Under settings you should see a section for Gateway Connection.
Hello,
As with the above explained example, two datasources are used to create the report.
If in Power Bi Online, only one data source has been created.
How will the refresh work during that scenario? Will it refresh only the part whose datasource is available on Power Bi Online?
From my experience the refresh will not work if you do not have all the data sources set up.
So, when configuring a scheduled refresh I configure it for a dataset and not the gateway? For example; report A and report B have dataset A en dataset B both through gateway A. Then I can set the refresh for dataset A at night and for dataset B in the morning. Correct?
Correct. You got it.
perfect, it work very smooth.
Hi...very useful video, thank you!!!...I Have a Pro License and I have a question... add to 8 different refreshing times, means that Power BI online will attempt to refresh each chosen hours as long as the administrator computer is on? Is there a way to get the data refreshed even when the administrator computer is off? (I´m getting the data out of Odata source which from MSproject output )...Thank you very much for your answer in advance!!!
Yes! What you do is install the free Data Gateway on a server somewhere in your environment. This will allow refreshes of your on-prem data without the need for the admin. computer to be involved. Check out my other video that explains how to set this up. ua-cam.com/video/GwuRQhm241c/v-deo.html
Thanks for easy explanations! Can all of the steps be done using PowerBI Free licence?
Thank you. From everything that I have read I think you need to be a Pro user to use the on-premises data gateway.
Pretty sure the answer is Yes. However, with the free version only you can view the reports. You can't share with others.
Is there any way to refresh data (from on-premises sql) on published url page(report)?
I'd like to give users a way that refresh the report( with updated data) by themselves, not pre- scheduled event.
Thanks in advance.
I don't think so. If they are not the authors of the report they will rely on your refresh schedule.
adding a local pc folder path to the gateway on BI service and entering my windows credentials, but unable to set it up.. get error>Details: "We could not register this data source for any gateway instances within this cluster. Please find more details below about specific errors for each gateway instance
Excellent video.
can you explain this for postgres SQL >> not working >> DataSource.MissingClientLibrary
I'm getting error when selecting gateway that "Failed to use the selected gateway. Please make sure the gateway is up and running and try again", but gateway service is running fine with no issues. Could you please help with this issue and fixing the same.
is it also published with the schedule refresh?
Thank you so much
Thanks!
Which network ports does it use when connecting back to On Premises data please? Is it push from gateway up or pull from Power BI server ? Thanks in advance!
TCP 443, 5671, 5672, 9350 thru 9354. The gateway doesn't need inbound ports. Check out more details here: docs.microsoft.com/en-us/power-bi/service-gateway-onprem
What about the authentification method mentioned at 07:35? I would have expected to configure a corresponding SQL Server user account at that point...
Yes, that is a SQL Server user account that is entered. You can pick SQL Authentication or Windows (AD).
This is pretty frustrating for me, all the demos seem to have the data source on the same PC as the Power BI desktop development. Is that required? I'm trying to design on PC#1 with SQL instance#1, but eventually have it pull data from SQL#2 (same schema). I can't figure out how to get desktop to design using the gateway connection, and I can't figure out how to change the connection string (when I publish, it says the gateway doesn't have the instance name of SQL#1, etc). It sounds like it's impossible to do anything other than design and publish on the same machine as the gateway? (I thought that's what the "personal gateway" was...). So confusing.
I have been able to publish based on a dashboard built on a PBI desktop I installed on our data server, but I think it's crazy I have to use my data server as a dev machine as well.
Great point Steve! When you develop with Power BI Desktop you can ignore gateways since you don't configure them at that point. You only consider a gateway once the dataset and reports are published. You bring up a very valid point since once you set up your dataset connections to use the gateway (not in Power BI Desktop but in a browser) the report seems to be looking for the same "Server" name in your gateway as you used when you imported the data from your desktop. There may be a better way but what if you set up a SQL Server alias on your desktop so your local instance#1 responds to the same name as the SQL#2 (production instance)? Here is a link on how to set that up: docs.microsoft.com/en-us/sql/database-engine/configure-windows/create-or-delete-a-server-alias-for-use-by-a-client?view=sql-server-2017. Not 100% sure if this is the absolute best way but it may work. Let me know.
What If don't want the scheduled update? I just want to manually refresh the data without using the PBI Desktop? I mean the customer will do that using the report or dashboard link I've sent. Thanks.
You don't have to put a schedule on the report. Just leave that and you can manually refresh from the Power BI Service (aka cloud). You will still need to set up the gateway so Power BI knows how to get to the source.
Can I use: On Premises Data Gateway with Power BI Pro
Yes you can.
@@SQLTalk Thanks
thank you for this video it's very helpful, but I was wondering if you can help me with my problem which is that I work in a company and this company has a local server and I have installed Xamp to this server and now I want to connect the power bi to this server , can you please tell how can i do that?
marwan taha you need to create a dataflow with a “new entity” defined as sql database server then add your credentials and then save it then you should set up a dataset to set up your schedules.
Hi,
Please, do you have some tutorial for creating a datasourse to SharePoint with a Excel file? Also, I need to setup Gateway in order to refresh automatically this spreadsheet in the same SharePoint.
Thanks
Great idea. Give me a few days and I will post. Thanks!
Thanks
Noob here. Does this mean i no longer have to go to pbi desktop then refresh/publish my report manually?
Absolutely! This should make your life a little easier.
SQLTalk thanks so much!
@@SQLTalk Thank you so much! I was able to set it up easily. There were a few updates with the settings but everything went just fine.
i want files using on premise gateway example video
Does it works on ubuntu?
I don't think so no. However, you can install it on any Windows based server and point it to your data source even if that is on Ubuntu.