Sergey Nivens - Fotolia

Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Power BI Report Server adds new Power BI reporting options

Updates to Power BI Report Server expand the on-premises platform's reporting functionality and increase the number of data sources users can connect to when building Power BI reports.

In June 2017, Microsoft released Power BI Report Server, a platform for creating and managing Power BI reports...

in on-premises systems instead of the cloud. Since then, it has issued two updates with expanded reporting and data visualization capabilities as part of its rapid-release approach to adding Power BI functionality.

Power BI Report Server is built on top of SQL Server Reporting Services (SSRS), the enterprise reporting software that Microsoft bundles with SQL Server. The Report Server implementation adds support for Power BI reporting to SSRS; it's included with the company's Power BI Premium subscription plan, which offers customers dedicated processing and storage capacity to run Power BI workloads in the cloud, on premises or in hybrid deployments that combine the two approaches.

The most recent Power BI Report Server update, released in March 2018, provides a variety of new features, including the ability to bookmark different views of a report and display them one by one as a data storytelling technique. Views can also be linked to clickable images and shapes to make reports more interactive and app-like, according to Microsoft.

Other additions in the March update include improved default placement of data visualizations on report pages, a quick measures feature that streamlines the process of creating calculations with Power BI's Data Analysis Expressions formula language, and the ability for slicers that filter data sets for different visualizations to responsively rearrange themselves when resized.

The previous update in October 2017 was a bigger leap forward for Power BI Report Server. The full version of a preview release issued in August, it offered a number of new and improved features to connect to data sources and work with the Report Server platform, including a new API that enabled external applications to programmatically access Power BI reporting components.

Making connections in Power BI Report Server

Initially, Power BI Report Server could only access data in SQL Server Analysis Services (SSAS), Microsoft's analytical data engine for SQL Server users. But the August preview and October release removed that restriction.

Report Server can now connect to a wide range of data sources for Power BI reports -- 46 supported ones as of April 2018, including SQL Server and its cloud-based Azure SQL cousins, Excel, Facebook, Google Analytics, Oracle Database, Salesforce, and both SAP HANA and SAP Business Warehouse (BW). Microsoft also offers beta support for connections to 30 other data sources.

For a select set of data sources, Power BI Report Server provides access to live source data via DirectQuery, a Power BI Desktop option that delivers fresh data to a report whenever it's rendered. In this way, users view the most current information every time they access the report.

Comparison of Microsoft's Power BI Report Server and SQL Server Reporting Services
How Power BI Report Server and SQL Server Reporting Services compare

As of this writing, Report Server supports DirectQuery only in the following data sources: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Oracle, SAP HANA, SAP BW and Teradata. Power BI reporting users can also connect to live data in SSAS and Azure Analysis Services, a cloud-based offshoot of the SSAS engine.

However, in the October release, Microsoft also added scheduled data refresh capabilities to Power BI Report Server, enabling users to periodically update data that's embedded in reports when they're created. Embedded data is disconnected from the data source, so it often must be refreshed to ensure that the reports remain relevant. The scheduled refresh feature can be used with more than two dozen data sources, and report developers can configure multiple refresh schedules for individual reports.

Inside job to refresh Power BI report data

The refresh capabilities are possible because SSAS itself is now embedded internally in Power BI Report Server. Data that's being refreshed is first loaded into a report's data model in SSAS where it is processed for use in the report. One limitation to be aware of: Reports with any data sources that use DirectQuery or live SSAS connections are precluded from also using scheduled refreshes.

Hosting SSAS inside Power BI Report Server offers more than just the ability to crunch data for scheduled refreshes; for example, it enables IT administrators to expand Report Server's memory footprint by updating SSAS configuration settings like the maximum memory limit or the size of the disk cache.

The REST API could be a game-changer for IT teams and third-party software vendors that want to embed reporting into their applications

In the October release, Microsoft also added a group of advanced properties settings to manage server farms running the Power BI reporting software. For example, admins can now control the maximum amount of time a data refresh can take or how long after a data model is last used that it expires and is erased from memory. Additionally, Power BI Report Server can be configured to support up to 2 GB files, both for uploads and refreshes; previously, the largest permitted size was 1 GB.

Another notable addition was the ability to publish Excel workbooks to the Report Server web portal alongside Power BI reports. To use this feature, Office Online Server (OOS) must be added to the Report Server preview environment. Microsoft customers with Volume Licensing accounts can download OOS at no extra cost.

REST interface opens new reporting doors

One of the most important new features in Power BI Report Server is a REST API that enables applications to access objects in a report server's catalog, such as data sources, folders, reports and key performance indicators. Application developers can also use the API to create, update and delete these objects.

The REST API is built on the OpenAPI specification, which was previously known as Swagger. It's an extension of an API introduced in SQL Server 2017 Reporting Services with added support for Power BI and Excel files. The Power BI Report Server version of the API also includes a modified version of an SSRS feature for reusing data sets in different reports; connections to the shared data sets can be built into Power BI reports via the OData protocol.

A replacement for an older Simple Object Access Protocol interface, the REST API could be a game-changer for IT teams and third-party software vendors that want to embed reporting into their applications. Using the API, applications can be programmed to navigate folder hierarchies, view folder contents, retrieve report definitions, upload reports and carry out numerous other tasks.

Although Power BI Report Server is still a relatively young product, it's evolving quickly. The software can simplify the process of deploying hybrid Power BI reporting systems without forcing users to sacrifice existing on-premises or cloud-based setups. Of course, it also means that customers will become even more locked into the Power BI ecosystem, but that's often the price you have to pay for a unified BI environment.

Dig Deeper on SQL Server Business Intelligence Strategies