cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
retinabro Frequent Visitor
Frequent Visitor

Seriously need help on data sources, refreshes, etc

Hey guys, desperately need some insight here.

So I'm on the BI team and we use AWS Postgres and Redshift in a private subnet along with a random BI tool. We want to move to PowerBI because it pretty much fits the uses cases of the organization. Most of us use a Mac and all of us are on the Pro trial right now btw.

I tried somethings to get PowerBI running and ended up running EC2 with PowerBI desktop in it within the same subnet so now all the data sources are connected and the team can remote desktop in there and start the BI journey.

This was what I had in mind.

1. Create report on PowerBI Desktop
2. Export PBIX file of the report
3. Import PBIX file on PowerBI Web
4. Share report to a group of people
5. Those groups of people can access the reports via the PowerBI Web
6. The report will be updated whenever people use the reports since it's on Direct Query

But now I've hit a wall, I did all that but I now need to set up a gateway to apparently schedule refreshes?

Before I continue my efforts on setting this up (it has been hell lol), I want to know if I'm on the right track and that it is actually possible to achieve those steps? Essentially I want to be able to share whatever reports/etc we build on the PowerBI Desktop to be viewed by users in the organization using PowerBI Web. Anything I can be doing better?

And creating these App groups are not as straightforward either, after creating the groups I can't seem to share the PBIX file to a particular App Group. Do you guys have any recommended resources on how to get this done properly?

Also any of you on AWS running PowerBI?
3 ACCEPTED SOLUTIONS

Accepted Solutions
Ross73312 Super Contributor
Super Contributor

Re: Seriously need help on data sources, refreshes, etc

Ok so there are really just 2 questions here:

 

  1. Why do I need a gateway?
  2. How do you share your reports?

 

Firstly, yes you have taken the right approach based on your description.  Good job! Keep it up 🙂

 

Gateway:  When you publish to the web, your report is living inside the Microsoft Office 365 cloud.  Your servers are sitting in your organisations network behind its own firewalls.  For good security reasons, you don't have a direct connection from an external unknown cloud connection to your database.  If you did, your cyber security officer would have kittens.  This is where the On-Premise Data Gateway comes in.  You install it on a server inside your network, preferablly as close to the data sources as you can.  This program acts as a secured pre-authorized gate to allow certain connections coming in from Microsoft.  You then get to specify what database access credentials are used and which Power BI users can manage those connections for refresh timings.

 

Sharing:  The first part of sharing comes down to licensing.  If you want your users to share and consume they will need a Pro license.  If your userbase is large enough, your consumers only users can make use of the Premium capacity.  I wouldn't worry about that until you have nearly 500 consumer only users.

 

Once your licensing is sorted there are a few ways to share.  First off the workspace.  This is the place you need to upload into when you publish.  Every user has a "My Workspace", but this is not the workspace we want to use.  Workspaces are generally tied to an Office 365 group, but you can create them directly from within Power BI Web.  As long as you have the right access, your workspaces will appear in your Power BI desktop publishing window.  Once a report is published within the workspace, all of the user that have the correct workspace access can see the report and the dataset.  There is even an option to download out the PBIX file in order to make alterations should they have editting rights.

 

Apps are created from workspaces.  Once you have your workspace the way you want, you can click on the "Publish App" button and share the workspace (read-only) to anyone in your organisation.  Again, licensing is key here.  You can even share apps with distribution groups inside your Active Directory.  I often do this to make maintenance easier.

 

It is possible to share individual reports and dashboards with users, although i recommend caution as you will start to get a maintenance nightmare as more and more of your reports are individually shared with a long list of recipients.  If you had 200 reports and then someone is leaves, do you want to have to make 200 access checks?


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

Ross73312 Super Contributor
Super Contributor

Re: Seriously need help on data sources, refreshes, etc

The On-Premise data gateway should be on a single server. For the size you are talking about, i'd suggest you should only have one.  Since it will be on a server, yes that machine will be running 24/7 like all servers.  Once it is set up, you can provide access to all 5 of your users so they can add data sources.

 

Schedule refreshes don't send out notifications unless they fail.  They will notifiy the person who set them up.

 

Incremental refresh is part of the Premium License presently.  It is part of refreshing generally, so yes it is related to scheduled refresh.  You have to construct your Power BI Model to make use of it.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

Ross73312 Super Contributor
Super Contributor

Re: Seriously need help on data sources, refreshes, etc

I think i know the single step that is missing.  You need to link the publish report with its associated gateway.  This is done on the same screen as "Schedule Refresh".  You need to select the "Gateway Connection" area and click on your gateway. 

 

Capture.PNG


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

5 REPLIES 5
Ross73312 Super Contributor
Super Contributor

Re: Seriously need help on data sources, refreshes, etc

Ok so there are really just 2 questions here:

 

  1. Why do I need a gateway?
  2. How do you share your reports?

 

Firstly, yes you have taken the right approach based on your description.  Good job! Keep it up 🙂

 

Gateway:  When you publish to the web, your report is living inside the Microsoft Office 365 cloud.  Your servers are sitting in your organisations network behind its own firewalls.  For good security reasons, you don't have a direct connection from an external unknown cloud connection to your database.  If you did, your cyber security officer would have kittens.  This is where the On-Premise Data Gateway comes in.  You install it on a server inside your network, preferablly as close to the data sources as you can.  This program acts as a secured pre-authorized gate to allow certain connections coming in from Microsoft.  You then get to specify what database access credentials are used and which Power BI users can manage those connections for refresh timings.

 

Sharing:  The first part of sharing comes down to licensing.  If you want your users to share and consume they will need a Pro license.  If your userbase is large enough, your consumers only users can make use of the Premium capacity.  I wouldn't worry about that until you have nearly 500 consumer only users.

 

Once your licensing is sorted there are a few ways to share.  First off the workspace.  This is the place you need to upload into when you publish.  Every user has a "My Workspace", but this is not the workspace we want to use.  Workspaces are generally tied to an Office 365 group, but you can create them directly from within Power BI Web.  As long as you have the right access, your workspaces will appear in your Power BI desktop publishing window.  Once a report is published within the workspace, all of the user that have the correct workspace access can see the report and the dataset.  There is even an option to download out the PBIX file in order to make alterations should they have editting rights.

 

Apps are created from workspaces.  Once you have your workspace the way you want, you can click on the "Publish App" button and share the workspace (read-only) to anyone in your organisation.  Again, licensing is key here.  You can even share apps with distribution groups inside your Active Directory.  I often do this to make maintenance easier.

 

It is possible to share individual reports and dashboards with users, although i recommend caution as you will start to get a maintenance nightmare as more and more of your reports are individually shared with a long list of recipients.  If you had 200 reports and then someone is leaves, do you want to have to make 200 access checks?


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

retinabro Frequent Visitor
Frequent Visitor

Re: Seriously need help on data sources, refreshes, etc

Hey Ross, thanks so much for the reply


Tried the publish to groups and it works! Thanks I didn't realize I could do that

 

So I've set up the gateway, however I have a couple of questions. My remote desktop has 5 users, it seems that each of the users has to set up a gateway, and I'm assuming that these gateways have to be switched on whenever the scheduled refreshes need to take place? And if that's the case, there will be a bunch of gateways switched on 24/7 right? And I just read something about incremental updates, does that have anything to do with scheduled refreshes?

 

Have also an unrelated question, is it possible for PowerBI to send notifications once scheduled refreshes are done? Maybe on PowerBI app or maybe an email, etc

Ross73312 Super Contributor
Super Contributor

Re: Seriously need help on data sources, refreshes, etc

The On-Premise data gateway should be on a single server. For the size you are talking about, i'd suggest you should only have one.  Since it will be on a server, yes that machine will be running 24/7 like all servers.  Once it is set up, you can provide access to all 5 of your users so they can add data sources.

 

Schedule refreshes don't send out notifications unless they fail.  They will notifiy the person who set them up.

 

Incremental refresh is part of the Premium License presently.  It is part of refreshing generally, so yes it is related to scheduled refresh.  You have to construct your Power BI Model to make use of it.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

retinabro Frequent Visitor
Frequent Visitor

Re: Seriously need help on data sources, refreshes, etc

Thanks so much for the information up until now.

 

So far I managed to -

  • Setup PowerBI Desktop with connections to the private subnet DBs
  • Create groups and share them
  • Set up the gateway and assign users to the gateway and sources
  • Create reports and publish to specific groups

I think I'm almost there. I'm right now stuck at - 

  • Manual & scheduled refreshes 
  • Setting up notifications for reports
  • The use of Apps

For scheduled refreshes, this is what I did - 

  1. Create a report and publish to a group using PBI Desktop
  2. Access report on PBI Web from that group
  3. Schedule refresh on the dataset that the report uses

However, both when I click Refresh Now or the scheduled refreshes that I set up doesn't work, meaning the data does not get updated. When I went back to PBI Desktop and clicked refresh manually, it refreshed the dataset but that refreshed dataset in PBI Desktop is not at all reflecting on PBI Web. How do I get both the Desktop report and Web report stay in sync?

 

Below are some screenshots that might be relevant - 

 

Error on Web when I click on Refresh

Screenshot 2018-05-28 10.18.44.png 

 

Gateway on Desktop

Screenshot 2018-05-28 10.22.06.png

 

Gateway on Web

Screenshot 2018-05-28 10.39.43.png . 

Source on Gateway on Web

Screenshot 2018-05-28 10.39.49.png

 

Would really appreciate more of your help and guidance

Ross73312 Super Contributor
Super Contributor

Re: Seriously need help on data sources, refreshes, etc

I think i know the single step that is missing.  You need to link the publish report with its associated gateway.  This is done on the same screen as "Schedule Refresh".  You need to select the "Gateway Connection" area and click on your gateway. 

 

Capture.PNG


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors