PBIRS is a superset of SQL Server Reporting Services (SSRS), a mature BI technology that has been a key component of the Microsoft BI stack for more than 15 years. The main difference between the two platforms is that PBIRS includes Power BI reports, giving on-premises implementations a modern BI experience.
As with any enterprise BI product, careful planning, and consideration of how the technology will be used should be made from the beginning. This will help you avoid stumbling blocks during implementation and ensure a smooth ride as you roll out your business intelligence and analytics solutions. Whether you’ve been using PBIRS since the beginning or are just getting started, the goal of this post is to show you some tips and tricks that you can use in your own environment.
Meet and ask the Microsoft Report Server team anything
We’ll also compare specific features of each platform below for those considering a hybrid Power BI implementation (Power BI service and Power BI Report Server).
This article will go over five specific suggestions. The topics are organised in a chronological order based on how a typical implementation goes.
- Configure Your Report Server
- Understand, Plan, and Implement Folder Level Security
- Leverage Analysis Services for Reusability
- Simplify Administration Using PowerShell
- Monitor Your Report Server
Configure Your Report Server
After you’ve installed your report server, you should go over some of the default settings. We’re not talking about the Reporting Services Configuration Manager’s configurations, but rather the advanced properties you can change from SQL Server Management Studio (SSMS). The default values of these properties will suffice for many implementations. However, there may be times when you need to change a setting for a specific reason. The advanced properties in PBIRS are similar to the tenant settings in the admin portal for Power BI Service.
You must first connect to your report server from SSMS in order to see the advanced properties dialogue.
Select Reporting Services from Object Explorer.
- To connect to your report server, enter your credentials.
- Select Properties from the context menu when you right-click on your instance name.
- The Server Properties dialogue box will appear; on the left-hand pane, select Advanced.
You’ll notice a slew of options in the advanced properties menu that you can toggle. Three specific properties that we have changed for some of our customers’ implementations are listed below.
By default, this setting is disabled, but many organisations prefer to enable it. Every report server user will have their own dedicated folder on the server as a result of this feature. Users in the Power BI service has a concept called My Workspace, which is similar to this. Using My Reports to encourage self-service BI is a great way to get started.
The number of days of report execution history stored in the underlying ReportServer database can be toggled using this setting. 60 days is the default setting. By increasing this value, more metadata will be collected, allowing you to track adoption over time. In tip #5, we discuss how to keep an eye on your report server.
PBIRS uses this setting to determine the level of detail to which users can export data from a visual. If you’re not careful, exporting underlying data can result in major performance bottlenecks. This is especially true if the underlying model (in terms of data and attributes/measures) is large. As a result, we recommend that customers disable this setting. Most users will be content to simply export the raw data that they see within the visual they are exporting from.
Understand, Plan, and Implement Folder Level Security
Power BI Report Server and Power BI service are very different when it comes to storing content like Power BI and paginated reports. App Workspaces and Apps are concepts within the Power BI service. App Workspaces are collaborative sandboxes where teams can work together to create Power BI content. The content can then be published to a larger audience of read-only consumers via apps. In Power BI Report Server, these concepts do not exist. We can instead make (and secure) folders.
Folders in Power BI Report Server (and SSRS) function in the same way that folders do in a file system. Access to all content within a folder can be restricted using Folder-Level Security. A folder hierarchy can also be created, much like a file system. The flattened nature of App Workspaces within the Power BI service differs from this. Whether you’re hosting reports in the service or PBIRS, careful planning is required ahead of time to ensure that your content is properly secured. Creating folders for different departments or subject areas is common practise (e.g. Financials, Marketing, Sales).
You can also define security on individual items (for example, a single Power BI report) in PBIRS, but we typically advise customers against doing so. Hundreds of reports are not uncommon in larger enterprise deployments. It would be impossible to keep track of each one individually. As a result, the primary reason we recommend creating subject-specific folders to secure multiple related reports for a subset of users is to secure multiple related reports for a subset of users. We also recommend that customers use a flattened folder structure in most cases. This not only simplifies the process of securing folders, but it also conforms to the flattened structure of App Workspaces in the Power BI service. If you decide to move your PBIRS content to Power BI Service in the future, this could make the process easier. Finally, when granting access to your user base, it’s best to use AD groups rather than individual AD accounts. Using AD groups, similar to securing folders over individual items, greatly simplifies the maintenance of your implementation.
Leverage Analysis Services for Data Re-usability
This is one of the first things we mention when people ask us about Power BI Report Server best practises. “I will not create siloed reports!” repeat after me. While multiple reports in Power BI service can share the same dataset, each Power BI report in Power BI Report Server can only reference its own data model. For example, if your environment is expecting 50 Power BI reports, there will be 50 separate data models under the hood, one for each report. As a result, we strongly recommend using Analysis Services in conjunction with PBIRS. Analysis Services is a well-established technology for creating in-memory enterprise semantic models. Analysis Services, in other words, can deliver both performance and a single version of the truth. When connecting to an Analysis Services tabular model live, Power BI only serves as a visualisation layer (i.e., you will only see the report canvas, no modelling or data tabs while authoring reports in Power BI Desktop).
While Power BI data models are fine for self-service BI, they should not be used in enterprise scenarios because the underlying data model cannot currently be reused for other reports.
Hybrid Use Case
If you already have an investment in SQL Server and its BI components, Analysis Services is a great option. If you’re using Power BI Report Server as part of a larger, hybrid implementation with Power BI Premium, you can use datasets that are available in Premium capacity as your reusable data models. As if it were an Analysis Services model, you can create a live connection from your Power BI, paginated, and Excel reports to a Premium dataset. To begin, make sure that read is enabled in the XMLA Endpoint setting for your Premium capacity. The Workspace Connection for the dataset you want to connect to must then be obtained.
Finally, select the following settings to establish a connection:
- Microsoft SQL Server Analysis Services
- Connection String
- Data Source = “<Power BI Premium Workspace Connection>”; Initial Catalog = “<Power BI dataset name>”
A sample Power BI Premium dataset connection string.
Simplify Administration with PowerShell
This is a subject about which we’ve previously written. The Reporting Services Tools PowerShell module is one of our favourites for automating various deployment tasks. Both PBIRS and standard SSRS will be supported by the module.
For example, we’ve used this module to complete the following tasks:
- Analyst reports are being exported in order to be integrated into our Git repository.
- Report deployment to various report servers (Dev –> Test –> Prod).
Our report server’s basic inventory is displayed.
Monitor Your Report Server
Monitoring report adoption, visualising usage trends, creating an inventory of your report server, and identifying any security gaps are all part of our final tip. There is no monitoring solution included with Power BI Report Server out of the box. The underlying Report Server database, on the other hand, contains a wealth of useful metadata that is just waiting to be analysed!
NOTE: At your own risk, create reports against the underlying Report Server database. With each new release of the product, the underlying database schema may change.
There are many views and tables in the Report Server database that the platform uses to store its metadata. However, there are a few key tables/views to start with when inventorying and monitoring your report server.
You don’t have to build your own custom monitoring solution if it sounds tedious or isn’t your cup of tea. You can download a Power BI template file for a plug-and-play monitoring solution that we’ve used for several customer projects by clicking the link below. Your report server must be on the January 2020 release or later to run the report. Simply enter your own Report Server instance and database name to use the report. The queries will then connect to and ingest the metadata from your own report server’s database!
You’ll be prompted to provide your own server and database name when you open the companion PBIRS Monitoring.pbit file.