Power BI Ecosystem Report Authoring Tools Demystified

There are a lot of discussions these days around Power BI tools to create reports and for sure many of you may have already downloaded and worked with some of them if not all of them. You may already think that some of the tools’ names are confusingly similar. I recently had an interesting conversation with a fellow who has a lot of SSRS report writing background. I was talking about Paginated reports and said, I downloaded the latest version of Power BI Report Builder… that he immediately said, wait for a second…

• John: Power BI Report Builder? Oh I see, that’s the one that you can create paginated reports with then you can deploy those reports into an SSRS instance.
• me: NOPE! That’s not the case I’m afraid.
• John: Oh I know, I meant Power BI Report Server, you can deploy the reports to an instance of Power BI Report Server. I knew it!
• me: NO! That’s not what I’m talking about…
• John: What the…?

I bet some of you had a similar conversation with a friend or a customer. OK, in this post I explain a little bit about report authoring tools available to you and your organisation to get the most out of your Power BI ecosystem.

Here is a list of all reporting tools currently available to you:

• Power BI Service: It is a SaaS (Software as a Service) offering from Microsoft in the cloud. The users in an organisation, based on their access rights, may be able to create and publish data, reports, dashboards in Power BI Service. The users can also schedule data refreshes on the published data as well as securely sharing and distributing the contents. While creating or editing reports is possible in Power BI Service, it is strongly recommended to avoid this method for several reasons. The most obvious one is that the changes you make in a report may be soon get overwritten by someone else that republishes the same report from Power BI Desktop. Check this blog post from SQLChick to see why you should avoid creating or editing reports directly from Power BI Service. The reports are downloadable in PBIX format. Use Power BI Service here.
• Power BI Desktop: It is a desktop report authoring tool that can be used to connect to, or loading data from, varies types of data sources, preparing, transforming and cleansing that data and at last visualising the data. Power BI Desktop is the predominant report authoring tool with a lot more functionalities and flexibility than Power BI Service. For instance, setting up Role Level Security (RLS) is NOT available in Power BI Service. The format of the report file is PBIX. Download Power BI Desktop from here.
• Power BI Report Builder (Paginated): Paginated reports aka “pixel perfect reports”, as the name resembles, are formatted in a way to fit perfectly on a page. That report page might later be printed. You have exact control over the page formatting to display your data in tables or charts. The reports are not as interactive as Power BI Desktop reports are. Paginated reports are based on RDL technology which is standard report format in SQL Server Reporting Services. The tool for developing paginated report in Power BI ecosystem is Power BI Report Builder. The reports file type is RDL. You can currently publish Paginated reports only to a Workspace that is backed with a premium capacity. Download Power BI Report Builder from here.

• Power BI Desktop Report Server (RS) Optimised: If you have a Power BI Report Server up and running in your organisation then you’ll need to create your reports in a special edition of Power BI Desktop which is optimised for Power BI Report Server. This edition is different from Power BI Desktop, which you normally publish your reports to Power BI Service. For instance, Power BI Desktop RS do NOT include the preview features we used to see in Power BI Desktop until those features are released. Using Power BI Desktop RS to build the reports to be deployed to the Power BI Report Server guarantees that they are fully functional after deployment. You can install Power BI Desktop and Power BI Desktop RS side-by-side on the same machine. Download Power BI Desktop RS from here.
• Report Builder: It is a stand-alone report authoring tool installed on your PC. You can create paginated reports in the Report Builder and deploy the reports to an instance if Power BI Report Server. It is indeed mostly referred to as SQL Server Report Builder. The tool is very similar to Power BI Report Builder. The difference between the two is that with Report Builder you can deploy the reports to an on-premises Power BI Report Server but with Power BI Report Builder you can currently publish the reports to a Workspace in Power BI Service that is backed by a Premium capacity. Download Report Builder from here.
• Mobile Report Publisher (aka DataZen): Microsoft SQL Server Mobile Report Publisher is a report authoring tool for SQL Server 2016 Reporting Services (SSRS) and above. Mobile Report Publisher’s original name is DataZen which initially released in 2013 and acquired by Microsoft in 2014. Microsoft integrated DataZen into SQL Server Reporting Services 2016. You can quickly create Reporting Services mobile reports, optimized for mobile devices and publish those report to a Power BI Report Server instance. Download Mobile Report Publisher from here.
• Microsoft Excel: Yes! Everyone knows what Excel is. It is a fantastic spreadsheet software with a lot of analytics capabilities. Without a doubt Excel is one the most widely used software every accountant has in their toolbox. It is robust, flexible and very powerful in data visualisation and data analysis. With Microsoft Excel, you can connect to many different data sources. Just like Power BI Desktop, and you can also build a semantic model in Power Pivot. You can use Power Query for Excel (Data Mash-up engine) to load data into workbooks, transform and combine that data and load it into a data model available in Power Pivot. The report can then be published to Power BI Service Workbooks section. The datasets created in Excel are available to authorised people in Power BI Service. The Excel datasets, like other published datasets into Power BI Service, can also be used to create reports in Power BI Service or Power BI Desktop. Try Excel for free from here.

Let’s have a quick look at Power BI architectures available as of today along with different report authoring tools available to you under that architecture.

Power BI Architecture

Power BI is an ecosystem including SaaS (Software As A Service), desktop applications, on-premises services and connectors to make the creation of the reports and dashboards possible. Here are examples of the above:

• SaaS: Power BI Service
• Desktop Application: Power BI Desktop
• On-premises Services: Power BI Report Server
• Connectors: On-premises Data Gateway

One of the most important decisions every organisation should make is to come up with the right architecture that supports the organisation’s analytical requirements. Here is a very brief explanation of currently available architectures. I’m going to explore a little bit around different architectures along with different tools available to you under each architecture. This way I believe it would be quite clear to understand what tool of choice should be used under what type of architecture.

Power BI Service

Power BI Service is a SaaS offering from Microsoft in the cloud which the users can

• create reports
• publish those reports into the service
• create dashboards
• schedule automatic data refresh over published data
• share and distribute content across the organisation or even outside of the organisation

Whether you are a Power BI Pro user or a free user, Power BI Service is available for you to use. Obviously, Power BI Service comes with some certain limitations like 10GB cloud storage capacity per user and 1 GB file size limit per Power BI report (PBIX).

Available Reporting Tools

If you’re using Power BI Service architecture the following reporting tools are currently available:

• Power BI Service
• Power BI Desktop
• Microsoft Excel

Power BI Premium

Power BI Premium is an Enterprise BI offering in the Power BI ecosystem. If one or more of any of the below points apply to your organisation needs then Power BI Premium is for you:

• You have a large number of report consumers while the number of report writers is not significant
• You need to refresh your data more than 8 times a day (up to 48 times daily refreshes)
• You have individual datasets larger than 1 GB
• You need to refresh your data incrementally
• Publishing Paginated Reports in Power BI Service
• You need more than 10 GB per user
• You need on-premises Power BI Report Server and you don’t have a licensed SQL Server Enterprise Edition with Software Assurance (SA).

Note: SQL Server Enterprise Edition with SA comes with Power BI Report Server. Therefore, if you already have a license and having Power BI Report Server is the only thing you need from a Power BI perspective then you don’t need to buy a Premium capacity.

Learn more about Power BI Premium here.

Available Reporting Tools

If you’re using Power BI Premium architecture the following reporting tools are currently available to you:

• Power BI Service
• Power BI Desktop
• Power BI Desktop RS
• Microsoft Excel
• Power BI Report Builder (Paginated)
• Report Builder (In conjunction with Power BI Report Server. You your reports in Report Builder and deploy then in Power BI Report Server)
• Mobile Report Publisher (when creating mobile reports and deploy in Power BI Report Server)

Power BI Embedded

Power BI Embedded is another service offering from Microsoft mainly for software development companies who want to embed Power BI reporting functionality into their applications. You may use Power BI Embedded in your organisation either you’re a software vendor (ISV) or you’re building in house applications that include Power BI reporting functionality or you’d like to test some Power BI Premium Capacity functionalities. Yes! That’s right. Power BI Embedded capacity works very similar to Power BI Premium capacity and a lot of Premium functionalities are available in Embedded capacity as well like the ability to use XMLA endpoints over both Embedded and Premium capacities. Read more here to see how you can test an Embedded capacity for free. Learn more about Power BI Embedded here.

Available Reporting Tools

If you’re using Power BI Embedded architecture the following reporting tools are currently available:

• Power BI Service
• Power BI Desktop
• Microsoft Excel
• Power BI Report Builder (Paginated) (Only available in A4 SKU and above)