cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
retinabro
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 Community Champion
Community Champion

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 Community Champion
Community Champion

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 Community Champion
Community Champion

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 Community Champion
Community Champion

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

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 Community Champion
Community Champion

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

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 Community Champion
Community Champion

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors