Today I will talk about exporting data from the Power BI service.
Long wanted to start a new blog series covering governance considerations. It is such a huge topic so I decided to focus on security first.
This first post will cover one of the most frequently discussed security question. Should we enable the users to export data from the reports, datasets and to what extent?
When we talk about exporting we generally think about the Export Data option that comes up for a visual if I click on the ellipses.
I briefly talked about in the DataLounge video about Power BI Administration. You can check the blog post about it here: https://selfservicebi.co.uk/why-understanding-administration-matters-for-customer-experience/
In order to control this we will see there are much more considerations and dependencies. Let’s head to the Admin settings to check how we can control exporting.
Admin settings
In the Tenant settings there is a central area focusing on Export and Sharing:
What you can see here is all the different features that single toggle controls at once.
All of these different features basically can be used to export.
Personally I would prefer much greater control over all of these different aspects of Power BI.
What does disabling Export Data affects?
- Export Data: If you want to control the exporting of information from Power BI in general this is the toggle to change. But if you read the detail it controls a lot more than just simple export.
- Analyze in Excel: one very important capability that is controlled is Analyze in Excel. This enables you to connect excel to the published datasets to easily create pivot tables and dynamic excel reports.
- Dataset download: This is important mainly to report creators. This enables to download the embedded Power BI Desktop file to start modify the pbix file using the Desktop editor.
- Power BI Service Live Connect: This feature is enabling the ability to reuse existing datasets to create new reports in Power BI Desktop by using the Power BI datasets connector
Other layers of control
As you see disabling Export data has a huge effect on the consumer and creator experience. So what other options are out there?
Report level controls
In the report level you also have the option to set exporting from a particular report. It can be found in the Report Settings.
There are 3 options:
- Summarized data: only enables to export the same information that is already visible in the visual.
- Underlying data: it will allow you to see the detail rows up to 150.000 rows as xlsx.
- None: disables any extracts from this report
Visual level controls
There is one more slightly different layer to control exporting. It is not necessary was the intended way of use for this functionality, but it can help implement certain controls on a visual level.
In the visual formatting pane the Visual header section make it possible to turn of the More Options, so the export data
Let say your report has information about Sales and Profit. You are happy to let consumers export the Sales data but not the profit data. You can turn of the export button for the visuals that are containing profit data.
This is mainly usable if the consumers only have access to the report through an App without build permissions (more on this later).
One more thing: Embedding to Sharepoint, Teams or Portal and Publish to Web
As I explored all the finer details I wondered what happens to exporting when the report is embedded.
- All the embedding options apart from Publish to Web gives you the same option as a report in the Power BI service.
- Publish to Web automatically disables exporting so you don’t have to separately control. You can check out for example this published report: England & Wales Crime Statistics
Summary
As you see know it is not a simple decision to choose if you want to enable data exports from your reports/datasets.
Fortunately there a different levels you can control this to give you the right level of security while enabling your organization to extract the most value out of your data.
