Seriously need help on data sources, refreshes, etc

[retinabro]

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?

[Anonymous · Accepted solution]

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?

[Anonymous · Accepted solution]

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.

[Anonymous · Accepted solution]

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?

[retinabro]

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

[Anonymous · Accepted solution]

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.

[retinabro]

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

 

Gateway on Desktop

Gateway on Web

 . 

Source on Gateway on Web

Would really appreciate more of your help and guidance

[Anonymous · Accepted solution]

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.