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.