Last month I worked on a proof of concept testing Power BI Report Server for self-service BI. The client determined Power BI Report Server would work for them and considered the POC to be successful.
Here are the highlights and lessons learned during the project, in which we used the June 2017 version of Power BI Report Server.
Power BI Desktop
There is a separate version of Power BI Desktop for use with Power BI Report Server. You can tell that you have the correct version by checking that the month and year are shown in the title bar.
If you are using both PowerBI.com and Power BI Report Server, this means you will have to manage two versions of PBI Desktop.
Another confusing aspect is that the June release of Power BI Report Server only allows a live connection to SSAS, but the Get Data Button is still there in Desktop with all the data sources listed. If you happened to get data from a non-SSAS source, you might have made it through the development of the model and report. Only when you tried to save the report to the report server would you find out that you had used incompatible features. This is only a temporary issue and will be resolved in the next release, since it will allow for Power BI models that connect to a wide array of sources.
Another difference between the Cloud and Report Server versions of PBI Desktop is that you do not publish to Power BI Report Server. You Save As. The Publish button is gone from the ribbon but still present in the File menu. But what you want is Save As – Power BI Report Server.
Limitation of Live SSAS Connection Data Source
In addition to managing to versions of Power BI Desktop, I also found myself mentally managing two sets of features. I was constantly asking myself “Can I do that in Power BI Report Server?”. Some of that is because PBI Desktop for Report Server is on a quarterly release cycle rather than monthly, so I had to remember if a feature I wanted to use was new (or in preview) and therefore not available in this version. The other part is trying to remember what you can and cannot do with a Live Connection. For example, you can make report measures, but you can’t use ad hoc grouping and binning.
We had several scenarios where users wanted to be able to group fields in multiple ways that changed somewhat frequently. Since we couldn’t use grouping and binning in Power BI Desktop to accomplish this, we set up an Excel data source in the SSAS Tabular model, and allowed users to change the groups there and refresh the Tabular model when finished. This could get rather unwieldy if you had lots of users who needed this kind of flexibility.
While there are downsides like the above with using a centralized data source, there are some upsides as well. Having a centralized data source can help ensure calculations are tested and approved before being used in a report. It also allows the BI team to ensure that security is properly implemented in the model. This is reassuring for organizations that are just starting their self-service BI program. But it also places more burden on the BI team since they must be very responsive with new calculations and data sets. It’s also necessary for the BI team to publish a data dictionary and tabular model documentation (relationships, calculations) up front so users can understand how to use the data in the tabular model. Our users were excited to get started and immediately had questions as to how calculations were implemented.
Content Management and Sharing
When we were ready to migrate to production, we wanted to avoid moving content by hand. The RS.Exe utility ignores pbix files (I tried it to see what would happen). But works fine on any SSRS reports you want to move.
We had to do some research to understand how to set up mobile access for Power BI Report Server. Power BI Report Server uses the same app as the Power BI web service, but it requires a way for users to access the report server within the network. It requires either a VPN on your mobile device or use of Oauth with a web application proxy and ADFS running on Windows Server 2016. This is all fairly standard, but not usually something a BI team would handle, so make sure to involve your networking/security team early on to help plan.
Another important aspect to plan is the folder structure on the Power BI Report Server. In a self-service environment, content owners will manage the content and permissions, so you want to keep things as simple and painless as possible. You’ll want to decide if you want folders for each department, each audience, or each subject area (or some combination thereof). You’ll also want to identify the power users and data owners who will act as content managers and decide if you will have a review/certification process before new reports can be published. This may be different for each department. It doesn’t have to be one size fits all, but you probably want to start with some process and loosen up as you get the hang of things rather than beginning with a free-for-all and then trying to implement some rigor in the deployment process. Permissions in Power BI Report Server work the same as SSRS native mode, so you’ll probably have to teach users how that works.
One area that seemed to have some gaps was the lack of subscription and alerting capabilities in Power BI reports. The normal subscription capabilities for Reporting Services reports are present, but there is nothing for Power BI reports. We used this opportunity to discuss how we might not need much push reporting if users have access to reports on their phones and can easily get to the information that way.
Looking Forward
A lot of the issues noted above should be resolved in the next version of Power BI Report Server. An August 2017 preview was released with the ability to use Power BI models (as opposed to being limited to SSAS Live Connections), which removes a lot of the limitations. Right now the August Preview doesn’t provide a way to refresh a Power BI model, but there should be something in place when the next GA version hits in Q4.
If you would like to use Power BI, but your organization can’t/won’t move to the cloud, Power BI Report Server can be a viable alternative, especially if you get a license for free because you had SQL Server Enterprise with active Software Assurance.
Data Savvy 
Excellent article, I would like to see how Microsoft plan to handle data refresh with non-SSAS data source.
awesome write up!
Mind shedding some light on the decision making process and factors leading to the decision to go with the on-prem PBI Report Server vs PBI Service?
Thanks, Bill! In this case, the client is in a heavily regulated industry and wasn’t interested in moving to the cloud. They had lots of Reporting Services reports in use and had SQL Server EE + SA, so it seemed like a logical and low-cost move for them.
gotcha – makes sense – ty!
That’s a great insight you have provided!! Thanks so much. In our organization we already have SQL server 2016 and Power BI report server as Active software assurance, we need some clarity on whether we need Power BI Pro license to publish reports onto Power BI report server. Its pretty confusing with microsoft blogs & power bi forums. None of that answered this properly. Reason i ask this question was, we saved few reports from Power BI desktop to Power BI report server(without any license), we wanted to introduce to business users but we are not sure whether they need Power BI pro license if they want to create and save the report onto Power BI report server.
The report developers need a Pro License. This is not a technical restriction, its a licensing restriction. You could have 1 or 2 developers developing & publishing content and go for Pro licenses for these devs.
great and helpful blog to everyone.. thanks a lot for sharing
Excellent post and really informative. i found this post while researching for implementing a Power BI solution for the company I work. My organization is also a regulated one and trying to see how i can implement Power BI.I have few questions based on what you wrote.
1) When using the Power BI Report Server, how can we achieve self service functionality for users. Lets say we ask users to install power bi desktop and ask them to edit a report that i created as a template. Can we control the users not to change the model or query?
2) related to prasanna’s question above, i deployed few reports to report server. i don’t have a pro license and i was able to deploy without any roadblocks. Is pro license really necessary for PBI Report server.
Good questions.
1. There isn’t a way to lock down the Power BI model but open up the report in Power BI Report Server or in Power BI Desktop. A solution to this is to use a live connection to Analysis Services. Users would have a read only connection to the SSAS model, but be able to create and modify reports with Power BI Desktop.
2. There is no technical limitation keeping you from publishing from Power BI Desktop to Power BI Report Server, without a Pro license, but Microsoft has stated in its licensing requirements that you should have a Pro license to publish. https://community.powerbi.com/t5/Report-Server/Does-Power-BI-Report-server-require-annual-user-licensing/m-p/359584
Also this blog post was about the initial GA version of Power BI Report Server. You can find information about the March release at https://powerbi.microsoft.com/en-us/blog/power-bi-report-server-update-march-2018/.
Thanks for your response Meagan. that definitely helps.
On a different note, is there is a way for me to use the Power BI service to showcase its capabilities to my management. I tried using my company address but some how cant use it. Any ideas on how i cant set up a power bi account
My understanding is that you need an AAD account. This is usually an O365 account, but doesn’t have to be. Some organizations have changed their O365 settings to disallow users to sign up for Power BI. If you can, see if your O365 admin will allow you to sign up. You can ask your O365 admin to grant you a free license.
Thanks for the info Meagan. I was able to set up a power bi account separately and get a pro account for 60 days. I will use that for the demo for now. I will contact our IT admin or the O365 admin to know more about it.
Nice article. We are in the same process where we need to decide if we continue with cloud of switch to on Prem. The main argument to go on prem seems to be for the enterprises that dont want to go cloud. Thats not our case. For us, on prem is interesting for the possibility to centralize on BI Portal with SSRs reports, report builder and power bi. One shop stop for the users. With cloud, only power bi reports ends up there (as of today, I understand Microsoft road map might evoluate regarding the possibility to publish SSRS on PBI.com) . Also, the folder management is a simple feature but very interesting to organise and controle who can do what on the BI portal. The subfolder is also interesting for good department document organisation Can’t do that on cloud workspace. So to me On Prem seems more like an enterprise BI portal then PBI.com. But I don’t have the feeling that is what Microsoft want to promote. Any thought on that?
Hi, Patrick. Microsoft is definitely pushing cloud first. You get many more features in the cloud with a faster delivery cadence. They have committed to delivering SSRS paginated reports through PowerBI.com, so that is definitely coming. You will also see some changes to workspaces that should make things a little easier. With the addition of SSRS reports, you should be able to use Power BI as a BI portal with Power BI reports and dashboards, Excel, reports, and SSRS reports. An alternative is to create your own portal on a website or in SharePoint. I have seen a few people do this with great success. Also, look for increased admin functionality in PowerBI.com that will make enterprise management easier. For a list of upcoming Power BI features, you can reference http://aka.ms/businessapplicationsreleasenotes
Thank you Meagan. Fall will brings some interesting features that might help us consider Premium over On
prem.
I am assessing the limitations of Power BI Report Server. My main concern is shared data sources. From what I’ve read there is a significant manual administration overhead to explictly configure data source connections in each PBIRS report (data sources cannot be shared).
https://community.powerbi.com/t5/Report-Server/Shared-data-sources-on-premise/td-p/331573
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19832149-shared-data-source-connection-live-to-tabular-ssa
This should be fine to start on a small scale, but looks like a a potential showstopper for the roll-out and maintenance of enterprise on-premise Power BI reporting.
Does anyone have any experience of managing a large scale PBIRS implementaion? (Note: Office365 and cloud Power BI Service is unlilkey to happen at my organisation in the near future, so stuck with evaluating Power Bi on-premise)
Hi, John. You are correct that Power BI report data sources cannot be shared in PBIRS. I’m aware of two ways to handle this.
1) If all reports are pointing to the same database on a server, you can try changing the host file (https://community.powerbi.com/t5/Report-Server/Power-BI-Reports-Connection-String-change-on-Power-BI-Report/m-p/402294/highlight/true#M5290) Note that it requires restarting the PBIRS instance to take effect.
2) You can use PowerShell to unzip the PBIX file and modify the connection strings before deploying to PBIRS. If you need to change connection strings on already deployed reports, you can export them and run the PowerShell script, and then re-deploy them. (https://www.blue-granite.com/blog/power-bi-report-server-devops)
I’m a fan of the idea for larger implementations that anyone can deploy to a dev environment/folder, but an automated process should deploy to production. The whole unzip and change connection isn’t necessarily supported by Microsoft, but it works well.
Thanks Meagan, good to know there are options to manage this. Looks like the upshot is additional IT sweat to wrestle with the nuances and limitations of PBIRS. We have SQL Server EE + SA so looking cost effective. We’re on a creaky BusinessObjects legacy plaform at the moment so new-fangled Data Viz from Power BI will impress. Some steep learning ahead.
Hi Meagan – My org has deployed a new Power BI Premium virtual server in our cloud environment running ‘Report Server’. Unfortunately, we can’t save our .pbix files to the BI ‘Report Server’ due to incompatibility (.pbix files designed via Nov 2018 desktop client). After some research, there doesn’t appear to be a workaround for that scenario. We have ~1k users with no BI license or desktop app who require web (only) access to our files. I’m currently publishing those those files to ‘My Workspace’ for a handful of Pro license users. Is the publish to ‘App Workspace’ option the next best alternative for those 1k users? My apologies if there is an obvious best approach as I’m somewhat going in circles with all the documentation.
I’m not sure I understand what you mean when you say “Power BI Premium virtual server in our cloud environment running Report Server”. Do you mean you have Power BI Report Server installed on a virtual machine in your on-prem environment? Or do you mean you have provisioned Premium capacity in PowerBI.com?
The easiest way to handle working with both PowerBI.com and Power BI Report Server is to have everyone use the version of Power BI Desktop that is optimized for report server (https://www.microsoft.com/en-us/download/details.aspx?id=57271). The Nov 2018 version will have features that aren’t available to PBIRS. I would try opening your PBIX files in the PBIRS-optimized version and see if it will let you downgrade. It means you will only get new features 3 times a year, but it will always be compatible.
In PowerBI.com, to allow users on a free license to view content, you’ll need to provision Premium capacity, create a workspace and assign it to that capacity, publish your PBIX to that workspace, and then publish an app. When you share the app (not the workspace), your users on the free license will be able to consume the app.
If you haven’t read it, the Power BI Enterprise Deployment whitepaper might help answer some questions: https://aka.ms/pbienterprisedeploy
We have Aug version of PBIRS-optimized client installed on a VM in our on-prem environment. Sounds like our only option for web-only user access is to wait for the Jan 2019 PBIRS-optimized release and hope our Nov 2018 PBIX files will then open successfully in that optimized client. Currently getting an incompatibility error with no option to downgrade presented. My concern is that the Jan release won’t fix our problem since MS documentation states “…Most core functionality comes to PBIRS in the next few releases…”. If the Jan release doesn’t fix the incompatibility problem and there are no other options to allow web-only user access, it’s unclear how we can identify the functionality causing the incompatibility error. Once we can open the files in PBIRS, we’ll definitely do all design work in that version going forward. Of course, the users need the data yesterday. Thanks for insights Meagan! I’ll go through the white paper.
Yes, I think your only real recourse is to wait until January. But I think you can feel pretty confident that the next version of PBI Desktop optimized for PBIRS will be able to open the PBIX files. You can also try logging a support ticket to Microsoft and see if they can help you.
Hey Karen,
How many reports do you have that you are looking at hosting on PBIRS? Have you considered removing some of the visuals using the Power BI Desktop (normal monthly release version) and seeing if you could open it up in the version optimized for Report Server? If you remove new features, I imagine that you’d be able to open some of them up. I have had great success with this in the past. If there are a LOT of reports this could be a major pain, and if you have a LOT of visuals leveraging new features, this too could be a pain… You could also try opening up 2 versions of the November monthly release and copying and pasting (new feature of November) elements from the original report to a new report. After copying and pasting a few objects, save and attempt to open it up on the PBI Desktop optimized for RS… If it works, try more copying and pasting… you’ll get the hang of what visuals and features within the visual are causing issues… I know that this is not the answer you were hoping for, but it might help satisfy some of those 1,000 peeps that are wanting those reports. Perhaps you could start with the 10 most important/most used reports and gauge the effort and your ROI?
Thanks for sharing your experience with the compatibility issue. I plan to give the “amputate limbs until it works” path a try in the hope of not losing the patient. We have 2 very large .pbix files with multiple report tabs that require web-only access. We landed in the unenviable position of planning an internal PBIRS environment after several reports were developed with only a small team in mind. I originally found the free client a few months ago when we needed a way to summarize individual Sharepoint docs. That quickly developed into an addiction which solved several other reporting issues.
If you need some help with the “patient,” Meagan and the rest of the team at Blue Granite have have the street cred when it comes Power BI, Power BI Report Server and most importantly, Data Visualization. They are also a group of amazing people.
Hello! Has any one had experience with automation stress testing for an on prem Power BI server? We have a staging server set up and is looking to service about 250 + users. Ideally we would like to host our SSRS reports and PowerBI reports on the same server. We want to determine the core needed for to build out the production server by stimulated server usage.
Hello! Has anyone had experience using automated stress testing for an on-prem PowerBI server? We have a staging server set up and would like to gauge how much core the production server will need. The automated stress test should be able to stimulate 250+ user log ins at once and provide measurements on the server’s health.
This is a fantastic and useful blog to everyone, Thank you very much for sharing.