10

On-Premises Solutions with Power BI Report Server

Throughout this book, we’ve focused on building reports that will ultimately be deployed to the web through the Power BI service or shown in the mobile application. In this chapter, we’ll show you how to deploy reports to Power BI Report Server, which is likely being hosted on-premises at your company. For many companies, this is a must-have, since cloud deployments are often not allowed with their type of data or industry.

Power BI Report Server is an on-premises version of the Power BI service that gives you a subset of the features of the full service. Unlike the full service, which is sometimes updated daily with new features, Power BI Report Server is updated every few months. There is also an additional Power BI Desktop specially made for the server.

In this chapter, we will cover the following topics:

• Accessing Power BI Report Server
• Deploying to Power BI Report Server
• Securing reports
• Refreshing data

Accessing Power BI Report Server

To get started, you will need to know how your organization’s IT governs its servers, hardware, applications, and data. You may need to submit a help desk ticket, or you may be authorized to go directly to Microsoft’s Download Center and install it yourself. Regardless of the path forward, it’s very important to know and abide by any data governance that’s established.

If you are installing it yourself, simply go to https://powerbi.microsoft.com/en-us/report-server/ and select Download Free Trial as shown in Figure 10.1 below, or use Advanced download options to specify what you need:

Figure 10.1: Download the free trial or go to Advanced download options to start

If you intend to use the paid version, then you will need Power BI Premium or SQL Server Enterprise Software Assurance (SA)—this is why you will want to coordinate with your central IT.

It is very important that you select the right language on the dropdown of the download page. This will impact the specifications assigned to that Report Server based on the language chosen. Additionally, the Microsoft documentation recommends reading through the requirements before installing, and it specifically calls out the following details:

While you can install Power BI Report Server in an environment that has a Read-Only Domain Controller (RODC), Power BI Report Server needs access to a Read-Write Domain Controller to function properly. If Power BI Report Server only has access to a RODC, you may encounter errors when trying to administer the service.

If you’ve gone down the Advanced download options route and have the IT privileges to install, then you will choose the right configuration:

Figure 10.2: Power BI Report Server download options

Once you’ve made your selection, you can manually launch the setup process by opening it in your Downloads folder, or it may appear automatically. The following steps walk through the installation process.

Figure 10.3: Select Install Power BI Report Server

You will need to click on the Install Power BI Report Server option to proceed. After you select that, it will provide the options available, and you select the free trial or you can submit your product key, as shown in Figure 10.4. It’s important that you follow all the standards for installations that your company sets.

Figure 10.4: Choose your install edition

From there, it’s important to choose the best location on your machine. Figure 10.5 shows that you can use a default location provided, or select Browse to specify another path:

Figure 10.5: Choose the install location

After this, the package will download as specified:

Figure 10.6: Power BI Report Server download options

Deploying to Power BI Report Server

If you’re a traditional BI developer who has built Reporting Services reports, you might feel right at home with Power BI Report Server, as the configuration and portals were largely borrowed from Reporting Services. You’re going to use a special Power BI desktop that is optimized for the server. The main reason for the separate desktop is to ensure that the desktop doesn’t promote a feature that the server does not support. This is an important point to note. One key advantage of using this approach is that Report Server can also host your traditional Reporting Services reports, KPIs, and mobile reports.

Deploying a Power BI report

Before deploying your report, you may want to create some folders to simplify finding your reports later. For example, creating a folder for finance, HR, inventory, IT, operations, and sales is a common starting point. Don’t worry, you can always move the reports later if you’ve already deployed them. Once you’ve created a folder, if you feel it’s needed, you can deploy your Power BI reports in one of two ways, from the Power BI desktop or by uploading:

1. Make sure you have a Power BI Desktop instance installed that supports Power BI Report Server and open the report that you wish to deploy.

   This flavor of Power BI Desktop also supports deploying to the cloud, but note that you will be at least 3–4 months behind the main Power BI application. With the quarterly Report Server edition updates, you will not have access to preview features.

2. Next, click File | Save As Power BI Server. If this is your first time deploying your report to the server, everything will be blank. Simply type the Report Server HTTP address in the box to connect to Power BI Report Server. It should look something like http://servername/reports. If you have a port number, you’ll need to use something like http://servername:portnumber/reports. You can find the exact location to enter by going to your Report Server Configuration Manager tool from the server and copying the URL from the Web Portal URL section. The port in this screenshot is port 80, which you don’t have to enter as it is the default port:

Figure 10.7: Find the location using the Report Server Configuration Manager

3. If the desktop can successfully connect to the server, you will be prompted for the folder name that you want to deploy to and the name of the report you want to use. Typically, you don’t want to use this opportunity to change the name of the report since it will become difficult to find your source report later.
4. After clicking OK, the report will deploy and provide you with a link that will take you directly to the report that now resides on your server. From this point forward, you can click Save without going through this process and it will save directly to the server.

You can also upload the report directly in the browser. To do this, take the following steps:

1. Go to the folder you wish to upload the report to and click Upload in the top-right corner.
2. You will then be prompted for the location of the folder, and then you’re done. Power BI Report Server will scan to make sure that the report is compatible. For example, if you built the report on a very new version of Power BI Desktop that is using features that aren’t supported in Power BI Report Server yet, then you will receive an error before the upload occurs.
3. Once the report is deployed, any user with the appropriate access can also click the Edit button in Power BI Desktop to open the report up in Desktop so that they can make changes.

If you wish to move the report to a new folder, you can go to the report listing and click Move under the ellipsis button. You will be prompted for the folder you wish to move the report to, and then you’re done. You can also do this under Manage under the same ellipsis.

In the Manage area of a report, you can also hide the report by clicking Hide this object. This can be used to hide reports that are built on other reports, for example. It’s important to note, though, that this is not a security mechanism. There’s nothing to stop a user from seeing the report if they unhide the object.

The next step we will cover is deploying a paginated report from Power BI Report Builder.

Deploying a paginated report

In this section, we will use a sample paginated report provided by Microsoft, as shown in Figure 10.8 below. To do so, take the following steps:

1. Find the report at https://github.com/microsoft/Reporting-Services.
2. Click on the Code button in the top-right corner.
3. Choose Download ZIP.
4. Extract the files in a location you will remember:

Figure 10.8: Accessing Microsoft’s paginated report examples

Once you’ve downloaded the .rdl file, you could explore it in Power BI Report Builder. For our purposes, we are going to focus on publishing to Power BI Report Server, which is actually done from within Power BI Report Server.

In Figure 10.9 below, you simply do the following:

1. Locate the Upload button along the top ribbon on the right side of the screen.
2. Choose the path where you extracted the paginated report’s ZIP folder. Do not use the ZIP folder. Choose the CountrySalesPerformance .rdl file to upload.
3. Choose Open:

Figure 10.9: Upload the sample paginated report

When this is complete, you will see your report under Paginated Reports on the home screen under Browse, as seen in Figure 10.10:

Figure 10.10: The paginated report is found under Paginated Reports

Simply click on the report to open it, and you will see your paginated report in its published form. Now that we know how to publish .pbix and .rdl files to Power BI Report Server, let’s cover securing reports.

Securing reports

You can secure a report in the Manage screen of a report or folder. To access this area, select a report folder or report, select the ellipsis button, and click Manage. Then, go to the Security tab. By default, reports and folders inherit security from their parent folder, but this can be undone quickly by clicking Customize Security.

Keep in mind that there’s quite a bit of overlap in the roles that can be assigned, mainly due to the context of what you’re securing (My Reports or the public folder of reports). The security roles that you can select are listed as follows:

• Browser: Can view the reports and folders, and subscribe to the reports.
• Content Manager: Can manage folders, reports, and resources.
• My Reports: Can publish reports and manage folders, reports, and resources in a user’s My Reports folder.
• Publisher: Can publish reports on your Power BI Report Server.
• Report Builder: Can review any definitions or metadata about the report, as well as permissions.

You can also select the gear in the top-right corner of the screen and select Site Settings to secure the entire server. Even if you have rights to the folder or report, you may not have rights to the server, which creates a lot of confusion with system administrators and users trying to view their data. When you go to Site Settings, you can add a user to one of two roles: System Administrator or System User. The system administrator can manage the security on the server and the schedules, to name just a couple of options. The system user grants the user rights to log in to the server, and then requires security to view the folders and reports, as shown earlier in this section.

Scheduling data refreshes

Refreshing data in Power BI Report Server comes with a lot more caveats than using the Power BI cloud service. For example, refreshing is contingent on the data source that the report is using. Since you’ve installed this server inside your firewall, there’s no need for a data management gateway to refresh the data either. As you create refreshing schedules, the server will simply create SQL Server Agent jobs to control the refreshes, such as Reporting Services.

If you plan on refreshing data sources that are derived from files, make sure you use a network path for that file (\computernamesharenamefile.csv), not a local path (such as C:DownloadsFile.csv). You can do this in Power BI Desktop by going to the Home ribbon and selecting Edit Queries | Data Source Settings. Click Change Source and change any file references to a network path, such as \MyComputerc$DownloadsFile.csv.

Once you do that, publish the report to the server again. Then, select the report and select Manage. For most data sources, you will need to confirm the source of the data in the Data Sources tab. For flat files, confirm that you see the network path and type in the credentials for the machine that’s holding those files. This will need to be a Windows Authentication username and password. Click Test Connection to confirm that a connection can be successfully achieved. If it tests successfully, click Save.

To schedule the refresh, click Scheduled Refresh in the report management area. Then, click New Scheduled Refresh Plan to create a new schedule. You can also use a shared schedule resource or a one-off schedule. Simply type the time you wish the refresh to occur and the refresh interval. While there are workarounds, the lowest grain of a scheduled refresh is typically hourly.

Test the job by selecting the job and clicking Refresh Now. If any errors occur, you will see the error inline in the Status column. For example, the following error would show as a data source error: Login failed for data source ‘Unknown’. This is not nearly enough information to debug with, so click the information icon next to the error to see a more actionable error, such as the following:

[0] -1055784932: Could not find file '\desktop-l2qu18gc$OneDriveDocumentsCountyClerksFL.csv'. The exception was raised by the IDbCommand interface.

In many cases, doing a simple search online with the error message will lead you to documentation, blogs, and community forums with solutions that others have found already. In this instance, dozens of articles appear to help the user resolve the connection issue quickly.

Summary

In this short chapter, you learned how to take Power BI service practices on-site with Power BI Report Server. This server has many restrictions on what’s available, so be careful that you have the right version of Power BI Desktop so that it matches your version of the server. As you’ve learned, the server resembles the Reporting Services server and uses SQL Server Agent to handle data refreshes.

Join our community on Discord

Join our community’s Discord space for discussions with the authors and other readers:

https://packt.link/ips2H