Doing Power BI The Right Way – for Enterprise Reporting

I started a series of blog posts back in 2020 about best-practice guidelines for planning and designing enterprise reporting solutions with Power BI. To make the topics covered in this series of posts easier to find and follow, they are listed on this page: Doing Power BI The Right Way – for Enterprise Reporting | Paul Turley’s SQL Server BI Blog which you can access from the main menu on the blog. We have a few more topics to go so check back and subscribe for notifications.

Doing Power BI the Right Way: 8. Delivery options

Part of the the series: Doing Power BI the Right Way

When you sign-up for the Power BI service at PowerBI.com (this address redirects to App.PowerBI.com), use establish a tenant for your organization, hosted in the Azure cloud. Even if you setup a 90-day trial account, you have a tenant that you can upgrade later on. By default, all of the datasets, reports and other assets you publish to the workspaces in your tenant, are in a shared capacity. This means that Microsoft will manage the storage and resource allocation among shared servers in their data centers. Larger organizations will benefit from a dedicated capacity “Premium” tenant. This means that Microsoft dedicates at least one virtual machine in the Azure data center, with guaranteed capacity, for your tenant. With Premium-per-User (PPU) licensing, you pay per user. Here are all the options in summary:

  1. Publish to shared capacity tenant, share reports with organization users who each have a Power BI Pro license.
  2. Publish to a dedicated capacity tenant, share with organization users who each have a Power BI Premium-per-User license.
  3. Publish to a Premium dedicated capacity tenant, share with users in the organization who do not require individual licenses to view reports.
  4. Publish to shared capacity and share un-securely for any Internet user, using “Publish to Web” – for free.
  5. Publish reports to your tenant and then share visual content through a custom web application using the Power BI Embedded service.
  6. Publish reports to on-premises Power BI Report Server. Some feature limitations apply. Requires Power BI Premium license or SQL Server Enterprise w/SA + 1 Pro license.

With Premium licensing, any user in the organization can view reports that are published as an app. Since you are buying a fixed amount of capacity (e.g. CPU cores, memory and storage), you can have as many users as you like. If you need more capacity to handle more users and/or more reports & datasets, you can upgrade to a higher capacity.

The Power BI Embedded service utilizes Premium shared capacity and allows you embed report content (a dashboard, a report page, or individual visuals) into a custom web application that you develop, using API code. Through the Embedded service, you can share Power BI report content with any users of your application, inside or outside of your organization.

Finally, Power BI Report Server is an on-premises hosting environment based on SQL Server Reporting Services (SSRS) that you can install on your in-house hardware or virtual machines. There are several technical restrictions and feature caveats, but this does give you the ability to host Power BI reports alongside Paginated reports, in your own environment. Before you decide that this is the right option for your organization, it is important to understand that there are a number of technical and feature restrictions with this option, so do your homework and understand these trade-offs.

Costs and Capabilities

The following guide may help simplify the options. For comparison, these are current and approximate retail prices from Microsoft, which are subject to change. Some customers also receive discounted pricing or reduced prices for bundled services. For example, discounts are offered to organizations that are not-for-profit or education.

Bottom Line

  • Some advanced and most attractive features are only available with Premium capacity and Premium-per-User licensing. This is the most complete and convenient option to deliver end-to-end Power BI solutions to users for an enterprise customer – at a cost of about $5,000/month or $20/user/month.
    Premium-only features include datasets over 1GB, auto-scaling and Paginated Reports.
  • For organization with less than a few hundred users, it may be more cost-effective to purchase a Pro license for every user at a retail cost of about $10/user/month.
  • The Power BI Embedded service allows reports to be exposed securely so they can be integrated into a custom-developed application. This is usually attractive for ISVs who want to integrate reports and visuals into some kind of service offering to their own external customers. This option requires custom development and programming skills.
  • The publish-to-web option allows a single report developer to expose a report to the entire Internet without security restrictions, and this option is free.
  • Organizations who cannot use cloud services for compliance reasons can use Power BI Report Server to host reports on-prem and in-house.

When to Use Paginated Reports vs Interactive Power BI reports

Technology evolves, the industry changes and the way businesses use technology changes. The road that has brought us to the current state of reporting capabilities in the Microsoft data platform has been long and winding. Ten years ago, we were using SQL Server Reporting Services (SSRS) to create interactive, dashboard-like reports scorecard reports. It wasn’t easy and the report interactivity wasn’t exactly “snappy” as the report server had to re-query data and re-render the report every time a link or chart series was clicked. But, because SSRS was the best tool that we had back in the day, the techniques we used for advanced report development were arcane by today’s standards. Over the years, I’ve written a few articles and posts about how to create reports with interactive KPI gauges, charts and scorecards using SSRS. Today we have a better tool for that style of reporting and analytics. Reporting Services remains a powerful and flexible tool for a few different styles of reports. Now that SSRS has been integrated into the premium Power BI service as “Paginated Reports”, this raises questions about how and when to use each tool – and how to use them together.
Related: Gauges in SSRS reports | Graphical Report Design

Power BI

Power BI makes the chore of creating highly visual and interactive reports much easier and the user experience is for more modern and engaging. However, Power BI reports are purposefully intended to be viewed with each page in a single browser window, with all visuals in view. Power BI reports are not optimized to be printed or split across multiple pages. To a small degree, it is possible to export some data to Excel, PDF or PowerPoint; but this is not the design purpose of Power BI – and it probably never will be. By contrast, SSRS/Paginated Reports was purposefully designed to render reports as continuous, paper-sized pages, with breaks and totals. Paginated reports can be exported to a variety of formats in high fidelity.

Can <> Should

When should we use Power BI vs Paginated Reports for our reporting needs? You can use either tool to create different styles of reports for different purposes, but should you? Just because you CAN doesn’t mean you SHOULD.

  • Can we use Power BI to create operational or transactional style reports?
  • Can we create scorecards and dashboard-style reports with SSRS/Paginated Reports?
  • Should we use a Power BI dataset as a data source for a Paginated report?

The following diagram depicts the relative capabilities and design purpose for these two reporting tools. See the overlap? On the left, you see that Power BI is optimized and best used to create interactive, visual reports, scorecards and dashboard-style reports. Contrast this with Paginated Reports on the right side, which is best used for “list-type” reports that continuously flow across multiple pages. SSRS/Paginated Reports was architected and works ideally with SQL queries, where filter values are passed into the query using parameters.

Let’s break-down a few of these important decision points.

Data Model vs SQL Queries

There are a few reporting use cases that would tip the scales in one direction. If the need is to run a query and dump the results into a long list of continuous report pages, that is a classic use case for Paginated Reports. Power BI relies on data organized and optimized in a semantic data model, with data shaped and purposefully structured to support interactivity and analytics. Although Power BI models can handle a lot of data if properly organized, handling transactional details are not a natural fit for Power BI reports, both due to the format and the way data must be modeled for reporting. Importing tables that are both tall and wide, with many columns, will tank Power BI performance and impede users’ ability to explore and interact with analytic data.

By contrast, Paginated Reports rocks at this sort of thing. Need to generate a running list of ten thousand order line items, split into formatted pages with breaks, headers and footers? This is a natural fit for a paginated report. Do you need to export a table-style report to Excel with each page in a separate worksheet? Paginated Reports is a good tool to use for this sort of thing. Under the covers, Power BI communicates with a tabular data model with the DAX query language. Although elegant and efficient, DAX queries can be very verbose and difficult to read. When SSRS was enhanced to work the option to query Power BI datasets, some effort was made to allow the Report Builder query designer to generate DAX query code. It is not robust nor very easy to maintain. If you need to enhance a DAX-query-based report with any degree of sophistication, you will likely not have a good experience. SSRS/Paginated Reports were developed to support, and are best used with, native SQL queries.

There are many nuances to the choice between these two options. A semantic data model provides a layer of abstraction for measures that define calculations and business rules. A model supports self-service analysis and discovery. Row-level security rules ensure that users only see their own protected data. Running a SQL query just gets the job done for a finite set of requirements, and perhaps only for one report.

Interactivity

In a Paginated Report, if you want to give a user the ability to click a chart column or cell in a table or matrix and then navigate or filter items in the report using that value as a parameter; this kind of interaction is possible, but it might take some code and development effort to get there. Even though SSRS and Power BI Paginated Reports are both defined as RDL files, the implementation of Paginated Reports in the Power BI service doesn’t yet support the same native report navigation and drill-though functionality as SSRS on-premises. Report navigation can be achieved using parameterized expressions to build clickable web URL links but this is a work-around solution at best. The product will evolve, and I think we can bet on seeing more and more parity between on-prem SSRS and Power BI Paginated Reports as the product continues to mature.

Guidelines

  • Can you create a dashboard-style report with SSRS or Paginated Reports? – Yes, but it is complex and time-consuming.
  • Can a Paginated report use a published Power BI dataset or SSAS model as a data source, with filters and parameters? – Yes, but DAX queries generated with the query designer quickly get large, complicated and difficult to maintain. Even using a small number of parameters and fields can produce an unruly query.
  • Can the page dimensions of a Power BI report page be resized or to accommodate more visuals than will fit on a standard browser page? – Yes, but several features won’t work as intended, and performance will suffer with more visuals on a single page.
  • Can a Power BI report page be formatted to print on a sheet of paper, or exported with formatted and high fidelity to Excel or a PDF file? – Maybe to a point, but perhaps not very effectively.

The bottom line is that both of these tools can be used to create a variety of report styles and to support a range of features, but one is better suited than the other for different purposes. Before stretching the limits of any tool, make sure you are using the right reporting tool for the job and consider how you can use both Power BI interactive reports and Paginated Reports together, to meet your analytic and operational reporting needs.