Making SQL sense

+44 (0)20 3051 3595 info@coeo.com
coeoClose

Making SQL sense

+44 (0)20 3051 3595 info@coeo.com

Careers

We're looking for people who share our commitment to excellence in Microsoft's data platform to join us consultants working on exciting business intelligence, analytics, and SQL Server projects on-premises and in the cloud.

Power BI – 15 Ways to Share Data

The Coeo Blog

Power BI is free of charge to use. Anyone can download the Power BI Desktop application from the Microsoft Store, import and mashup data from various sources and create beautiful visualisations of business metrics.There comes the point, however, when you wish to share this content with your colleagues and customers.

Power BI provides numerous options to achieve this, each with different cost implications. This is the Freemium model so common today - the tool is free to use until we start using more advanced features, sharing in Power BI is one such feature. There is a lot of confusion with how to share data in Power BI. The most common questions from attendees of Coeo’s Power BI Training courses are about sharing content. Hopefully, this post will clarify the options available to you.

1 Share Dashboard

There is an important distinction between reports and dashboards. You create reports within Power BI Desktop by adding visualisations to the report page. Reports can have many pages, and we have the option to drill down from a high-level summary page to another page containing more detail.

You can then publish the Power BI Desktop content to the powerbi.com cloud service (subsequently referred to as the service) and create dashboards. These are overarching visualisations of a business overview, created by pinning visualisations from one or many reports.

You publish dashboards inside and outside your organisation by hitting the share button when viewing the dashboard. Recipients of the share require a Pro licence (you pay per user per Month), or you deploy the content to premium capacity (more on this below). You can configure the ability for internal recipients to re-share during the sharing process. Re-sharing can also be turned off completely in the Power BI admin portal.

2 Power BI Premium

Power BI Premium has been mentioned already in this post, so it is time for clarification. The default mode is for you to deploy content to shared capacity in the Power BI Service. When distributing content from shared capacity, the author and each consumer require a Power BI Pro licence. As an alternative, you can deploy content to dedicated capacity via Power BI Premium. Power BI Premium has three benefits:

  • Users consuming content on dedicated capacity can be Power BI free users.
  • Shared capacity has limits of 1 GB per dataset, 10 GB per user and eight refreshes per day. These limitations are far more lenient with Premium. 100 TB of storage is available, the refresh limit is 48x per day, and a dataset can be 10 GB.
  • Shared capacity means you are sharing infrastructure with other Power BI users. Purchasing Power BI premium capacity provides dedicated resources to service your organisation's data visualisation requirements.

Power BI Premium is available in different flavours of “A”, “EM” and “P” SKUs, read on to see which SKU support which sharing options. Each SKU also has different sub-options and price points depending on your specific requirements for CPU, memory and renders.

3 Share PBIX

The development environment for creating content is Power BI Desktop. When saved, you encapsulate content as a file with a pbix extension. This file is then available to be shared with colleagues to collaborate and edit as appropriate. All users could collaborate on-premises using this method for free without requiring a Power BI Pro licence. You should avoid this approach as you would produce silos of content where multiple end-users would have different versions of what was originally the same file.

4 Share Template

Like saving Power BI content as a pbix file, you can save Power BI template files with a pbit extension. Templates allow you to save the development content of your work, the difference being the template file contains an empty dataset, you don’t retain any imported data. You can share template files for free with colleagues. Again, this approach should be avoided to distribute content to self-service end-users to avoid having to support many versions.

5 Embed within Microsoft Teams

You can embed Power BI content within Microsoft Teams. Using Teams is the “user owns data” embedded model, where you still authenticate to Power BI. Office 365 groups that govern the security of both Power BI workspaces and Teams can be used to provide a consistent authentication mechanism across both products. You can deploy such content to Power BI workspaces backed by Premium Capacity on “P” SKUs. However, there is a more attractive option with this scenario of deploying the content to workspaces on “EM” SKUs. EM SKUs are significantly cheaper than P SKUs. You can also embed within Teams to distribute to Power BI Pro users using shared capacity without requiring premium capacity.

6 Embed within SharePoint

Power BI content can be integrated into SharePoint online. The same rules are applicable as with Microsoft Teams, so see that section for further detail.

7 App Workspace - View Only or Edit

A Power BI Pro user can deploy content to an app workspace within the Power BI service. This a logical grouping of entities with a central security mechanism. Users can then be assigned permission to view the content within that workspace. You can assign two privilege levels when assigning workspace permissions, view-only or edit. View-only is still a fully interactive experience where users can apply filters, select from slicers and click on charts to slice the data. Users are unable to edit any content when assigned view-only permissions.

Users are assigned edit permission on a workspace via the same method as for view-only. The difference here is you can edit content within the Power BI service to add more charts or alter existing.

8 Embed - App Owns Data

This scenario is where you wish to give end-users a Power BI visualisation experience without having to exit your proprietary application. Power BI content can be embedded within third-party applications and websites to add rich, professional reports without requiring end-users to authenticate to Power BI. In fact, end-users do not need to know they are using Power BI and the reporting experience is fully integrated. You can deploy content to the cheapest A SKUs in addition to the more expensive EM or P SKUs when using Power BI embedded.

9 Apps

Content deployed to an app workspace can be packaged up into a self-contained entity known as an app within the Power BI service. Users can then be assigned permissions to consume this app via the convenient mechanism of the Apps menu item. Apps avoid users having to navigate through the service to locate the content relevant to them. They simply click on the appropriately named app and visualise the data.

10 Content Packs

Another slight variation on a theme here. Content packs have been superseded by Power BI apps, but are still available as of January 2018. You package items within a content pack and assign permissions to that single entity for consumption by other users. Such users can click the get data link in the power bi service and import a content pack into their current workspace.

11 Publish to Web

You can publish Power BI reports to the web. Publishing to the web allows you to provide content to a wide audience with no Power BI authentication required by end-users. Publishing to the web is one of the few instances where Power BI free users can share content via the Power BI service. We must be very cautious with the data exposed via this method as anyone on the internet can view such content. The feature can be turned off by a Power BI admin.

12 Export to PowerPoint or Print

You can export reports to PowerPoint via the Power BI service. Exporting provides a non-interactive static version of the report without the capability to drill down, slice or filter data. Exporting might be useful if you want to present data visualisations to colleagues, albeit without the ability to interact with the data. Power BI reports can also simply be printed to produce a static hard-copy. These features can be turned off by a Power BI admin.

13 Export to Excel or CSV

We can export the data from a single Power BI visualisation to Excel or CSV format. You can expose the source data that was used to build a visualisation using this method. Click the ellipses on the top right of a visualisation and select “export data”.

14 B2B for Guest Users

Azure AD business-to-business (B2B) collaboration gives you the ability to add users from another tenant as a guest in your tenant. The external user then can view Power BI content using a single sign-on they are using within their tenant. You license Power BI content in one of three ways in this scenario:

  • You deploy content to premium capacity.
  • You deploy content to shared capacity. The guest user brings their Power BI pro licence.
  • You deploy content to shared capacity. The guest user is assigned a Power BI pro licence from your tenant and does not require a Power BI pro licence from their tenant. Assigning a licence provides the ability to view pro content within your tenant only and not within any other tenant, including their own.

15 On-Premises Report Server

For customers with concerns over data sovereignty and deploying data to the cloud, there is the option of the on-premises Power BI Report Server. A Report Server is a superset of the traditional SQL Server Reporting Services (SSRS) installation. Power BI Report Server allows you to deploy Power BI reports in addition to paginated SSRS reports.

You purchase Power BI Report Server via Power BI premium. Using Report Server and Premium allows you to migrate the on-premises installation to cloud-based premium capacity in the future should you so desire. Power BI Report Server is also available to SQL Server Enterprise Edition customers who have taken the additional option of Software Assurance.

Close

This post describes 15 ways to share data with Power BI. The golden rule when sharing Power BI content is the author and recipient must both be Power BI Pro users. Your Office 365 admin assigns Power BI Pro Licences, and you pay per user per Month. There is an alternative to shared capacity where you purchase dedicated capacity via Power BI Premium. As a Power BI Pro User, you share content to Premium capacity that any number of free users can consume. There are also free options to share data as detailed above.

General Data Protection Regulation (GDPR) is happening 25/05/2018. GDPR is extremely relevant for Power BI where the beauty of the product is the simplicity of sharing data. GDPR is a whole other blog post, suffice to say you should avoid importing personally identifiable data into Power BI datasets. That data could potentially be shared or exported using any of the methods detailed above. You have then lost control and governance of that data. Data visualisation tools provide meaning by grouping large volumes of data into visually appealing graphs and charts. There is usually no requirement to report a customer’s email address, national insurance or social security number using such a tool, so avoid giving the report author permission to read such data where possible.

There are numerous variables within a Power BI Project, including:

  • Data source - e.g. Analysis Services, database, flat file.
  • Data source location – cloud or on-premises.
  • Dataset – import, direct query, live connection, streaming.
  • Security – row level security or not.

The compatibility matrix of all these features with all sharing methods is too detailed to include in this post. Perform a proof of concept with your proposed architecture to ensure support.

Do you have any additional ways you share Power BI content? I would love to hear them, so please comment below.

 Get the best blog posts from 2017 here

Subscribe to Email Updates