Comprehensive Guide to Power BI Report Server
Power BI is more than just a cloud-based reporting technology. Recognizing the need for on-premises data and reporting solutions for some businesses, Power BI offers the option of a fully on-premises deployment known as Power BI Report Server. This article provides a detailed tutorial on Power BI Report Server, covering installation, requirements, configurations, and its advantages and disadvantages.
Understanding Power BI Report Server
Power BI reports can be hosted in two environments: cloud-based or on-premises. The cloud-based hosting is known as Power BI Service. Power BI Report Server is a specialized version of SQL Server Reporting Services designed to host Power BI reports on your own infrastructure.
Key Features and Components
Power BI Report Server is an on-premises report server featuring a web portal for displaying and managing reports and KPIs. It includes tools for creating Power BI reports, paginated reports, mobile reports, and KPIs. This server is similar to both SQL Server Reporting Services and the Power BI service, but with distinct characteristics.
Like the Power BI service, Power BI Report Server hosts Power BI reports (.pbix), Excel files, and paginated reports (.rdl). However, similar to Reporting Services, Power BI Report Server is deployed on-premises. The features of Power BI Report Server are a superset of Reporting Services, meaning it supports everything you can do in Reporting Services, along with Power BI reports.
Licensing
Power BI Report Server is available through two licensing options: Power BI Premium or SQL Server Enterprise Edition License with Software Assurance. For Power BI Premium, Power BI Report Server is included only with P SKUs, not with EM SKUs.
Read also: Body, mind, and community through yoga
With SQL Server 2025, customers can run Power BI Report Server software on the Licensed Server, on any allowed Fail-over OSE, or in Azure. The software can run on a maximum number of cores equal to the number of SQL Server Standard or Enterprise Edition Core Licenses assigned to the Licensed Server, with a minimum of four core licenses per OSE. Alternatively, if the software is run in Azure, one SQL Server Standard or Enterprise Edition Core License must be allocated per virtual core, with a minimum of four core licenses per OSE.
For versions of SQL Server Enterprise Edition released prior to 2025, Power BI Report Server use rights apply only to Enterprise Edition Core Licenses with active SA. Similar to SQL Server licensing, customers can run the Power BI Report Server software in a physical or virtual OSE with up to the number of cores included under their Fabric F64+ capacity. If a customer purchases Fabric F64+ reserved instances, a Power BI Pro license is required to publish both paginated and Power BI reports in PBIRS. However, for SQL Server editions, a Power BI Pro license is only required for publishing Power BI reports in PBIRS.
Accessing the Web Portal
The entry point for Power BI Report Server is a secure web portal accessible through any modern browser. This portal allows you to access all your reports and KPIs, organized in a traditional folder hierarchy. Content is grouped by type: Power BI reports, mobile reports, paginated reports, KPIs, and Excel workbooks. Shared datasets and shared data sources are in their own folders, serving as building blocks for your reports. You can also tag favorites to view them in a single folder.
Depending on your permissions, you can manage the content in the web portal, schedule report processing, access reports on demand, and subscribe to published reports.
Installation and Configuration
Downloading the Setup Files
To begin, you don't need an SQL Server installation disk to run Power BI Report Server; the Report Server comes with its own setup files. You can download these files from the Microsoft website.
Read also: College Park Power Plant: History and Significance
Initial Setup
After downloading the setup files, start the installation process. The setup will guide you through the initial steps. If you have a Power BI Premium license, enter it when prompted. Otherwise, you can select the evaluation option, which allows you to use the Power BI Report Server for 180 days from the date of installation. Accept the license terms and proceed.
Database Configuration
Power BI Report Server requires a database engine instance to install and create internal databases necessary for its operation. If you don't have a database instance, you can choose to install the Database Engine during the setup. If you already have a database engine installed, select "Install Power BI Report Server only" and continue.
Specify the installation location for the report server. By default, it is installed in the Microsoft Power BI Report Server directory. After the installation is complete, you will be prompted to configure the report server.
Configuring the Report Server
Select “Configure Report Server” to launch the Reporting Services Configuration Manager. This manager allows you to connect to the report server instance. By default, the Power BI Report Server instance is named PBIRS.
The configuration process is similar to that of SSRS Server. The first step is to configure the web service URL. After configuring the URL, set up the database connectivity to install and configure the internal databases of the Power BI Report Server. Provide the credentials to connect to the database server.
Read also: Learn about the Alabama Power Scholarship
To avoid confusion with SSRS Server, you can name the database PowerBIRS instead of Report Server. Provide the appropriate service credentials for the report server service to connect to internal databases.
Once the databases are installed and configured, the final step is setting up the web portal URL. Apply the settings to set up the web portal.
Accessing the Web Portal
Open the Power BI Report Server web portal using the URL you configured. The portal should display the Power BI logo on the top left, confirming a successful installation.
Completing the Configuration
After completing the setup, you can open the configuration section by clicking on “Configure Report Server.” The instruction asks you to restart and then go to the report server, but both options are valid. To configure the Report Server, connect to the server you've just installed.
The first step is to configure the databases. You can create databases for the report server in the Change Database wizard. Connect to the SQL Server database instance where the report server databases will be created. If you have only one local instance of the SQL Server database, you can connect to it with a single dot (.). Otherwise, enter the database server and the necessary username and password.
There will always be a second database called the Temp database, which doesn't require any configuration. After confirming the settings in the Summary step, the setup will continue and finish.
You can set up configurations such as the address of the server, the port that this web service will be running on, and other specific configurations. Changing these configurations is only required when you want to set up the server on a different port or server with a specific configuration.
On the report server's page, you will see the version and name of the Report Server. The web portal should display the environment of the Power BI Report Server's admin view. This completes the installation and configuration of the Power BI Report Server.
Addressing Common Errors
During the configuration, you might encounter the error: "System.Data.SqlClient.SqlException (0x80131904): Windows NT user or group '(null)' not found." To resolve this, change the service account to either Network Service or a domain account.
Developing and Publishing Reports
Power BI Desktop Report Server Edition
Power BI reports hosted on the report server need to be developed with a specific edition of Power BI Desktop called Power BI Desktop Report Server. After a successful installation, you can open this edition and start creating reports, similar to the standard Power BI Desktop.
The report development experience in these two editions is very similar. However, Power BI Desktop Report Server is slightly behind the regular Power BI Desktop in terms of feature updates. Power BI Desktop updates monthly, while Power BI Desktop Report Server updates every four months. One feature you will miss in this edition is the Power BI Desktop preview features.
Publishing Reports
There are two ways to publish a Power BI report to the report server. One way is directly from the Power BI Desktop Report Server edition. First, set up the URL to your report server. In this window, you can connect to a report server.
A Power BI report published to the report server can be configured to refresh. If your report is sourced from a file, you may need to schedule it for a refresh. Save the configuration after this step. The scheduled refresh configuration of the report server offers more options than the Power BI Service, allowing you to schedule refreshes hourly, daily, weekly, monthly, or with any custom period.
Data Connections and Gateways
The Report Server environment has similar configurations to the Power BI Service, including data source settings, scheduled refresh, security, and sharing. However, you do not need a gateway with Power BI Report Server. Gateways are only required for connections from the Power BI Service to on-premises data sources. With Power BI Report Server, everything is hosted on-premises.
In earlier releases, you could only create a live connection to SQL Server Analysis Services from Power BI reports. Now, you can use any connection type except the Composite model. For example, you can use Import Data and schedule the report to refresh.
Creating Power BI Reports
You create Power BI reports (.pbix) with the version of Power BI Desktop optimized for the report server. A Power BI report offers a multi-perspective view into a data model, with visualizations that represent different findings and insights from that data model. A report can have a single visualization or pages full of visualizations.
If you have already created Power BI reports in Power BI Desktop, you are almost ready to create Power BI reports for Power BI Report Server. It is recommended to install the version of Power BI Desktop for Power BI Report Server to ensure the server and the app are always in sync.
Data Sources and Authentication
You can connect to a variety of data sources. Fill in the Server and, optionally, the Database. Drag the field to be counted to the Values well. Note that some visuals, such as the paginated report visual, will only work on the Power BI service.
Power BI Desktop only supports integrated authentication for publishing reports to Power BI Report Server. If you choose to edit the report in the future, the report data you see in the desktop will always be the cached data from when the report was initially created.
Key Considerations and Best Practices
Read-Only Domain Controllers
You can install Power BI Report Server in an environment that has a read-only domain controller (RODC). However, Power BI Report Server needs access to a read-write domain controller to function properly.
Product Key Activation
If you want to use a product key instead of installing a free edition, locate your product key by going to On-premises reporting with Power BI Report Server and selecting Download free trial.
Default URLs
By default, Power BI Report Server uses certain URLs to provide access to its report server and web portal. The default prefix is HTTP, the default host name is a strong wildcard (+), and the default port is 80. Open up the Transmission Control Protocol (TCP) port that you configure for your report server URL and web portal URL.
Advantages and Disadvantages
Power BI Report Server offers numerous benefits, but it also has some drawbacks.
Advantages
- On-Premises Solution: Power BI Report Server is a fully on-premises solution, ideal for organizations with strict data governance and compliance requirements.
- No Gateway Required: You do not need a gateway, and all types of connections (Scheduled Refresh, DirectQuery, and Live Connection) are supported (except the composite model).
- Hybrid Deployment: With the ability to pin visuals from reports on Power BI Report Server into the Power BI cloud service, you can create dashboards that span both cloud and on-premises reports.
Disadvantages
- Isolation from Power BI Service: One of the main drawbacks of the Power BI Report Server is its isolation from the Power BI Service.
- Licensing Costs: Power BI Report Server comes in only two types of licensing: Power BI Premium or SQL Server Enterprise License with Software Assurance.
- Feature Lag: The Power BI Desktop Report Server edition is typically a few months behind the regular Power BI Desktop in terms of feature updates.
tags: #Power #BI #Report #Server #tutorial
