Whatever business intelligence tool you’re using, it connects to Excel. “If you want to build a new BI product, the first feature you build is export to Excel,” jokes Arun Ulag, CVP of Microsoft Power BI. “People want to be able to work with data in the tools that they use.” he adds.

But when you export a report to Excel to dig into the numbers, what happens to any rights management that’s been applied to sensitive company data? Role-based access permissions, row-level security and object-level security may not be enough to protect data – particularly with so many people working at home.

“Data travels, and if the security is left behind as the data travels from your data warehouse to your BI system to Excel to PowerPoint to PDF, then how good is it?” Ulag points out. “If I can export my data out to Excel and then email it to somebody and that’s where the security stops, it really breaks down, especially in the world of remote work. This is a paradox: you’re taking your most sensitive corporate data, you’re giving it to everybody – and everybody’s working from home.”

Blocking export to PDF to protect confidential data is frustrating for employees who need to work with the data. So Power BI will now use sensitivity labels from Microsoft Information Protection (MIP) to protect information in Power BI Desktop, in the Power BI service and when reports are exported to Excel, PowerPoint or PDF. This will allow you to use the same data security policy, compliance and auditing tools for Power BI as for Office (and third-party applications that build in MIP). You can label and classify the PBIX files that Power BI Desktop works with, and you can label datasets and reports in the Power BI service.

“MIP allows you to use labels like ‘highly confidential’ in an Office document, and if you apply that label that encrypts the document with your Active Directory credentials,” Ulag explains. “So if you email that document outside of the organisation, if somebody who shouldn’t have access tries to open it, or you stop being an employee and you try to open the document, you don’t have access. If you’re using Power BI Desktop to author Power BI reports, it’s just like using Excel to author workbooks: you can apply a MIP label right in Power BI Desktop and it will encrypt the file with your company’s policy. When it goes to the Power BI service, the service recognises that this data set is highly confidential.”

Power BI users will see that datasets and reports have been labelled, and will know they’re working with sensitive data.

“And then if you export that out of Power BI into Excel, PowerPoint or PDF – which is very, very common for BI tools – you don’t have to block that export because that exported entity in Excel, PowerPoint or PDF is also encrypted with the same highly confidential tags, with the same AD credentials. If an unauthorised user tries to open these labelled files, they won’t be able to access the data, even when it leaves Power BI,” Ulag says.

The label also shows up if you connect to the Power BI data set directly from Excel: the label will be in the dataset list and if you use it to create a pivot table, that will have the labels as well. MIP also stops users from taking screenshots of protected data (on any platform that allows applications to disable screenshots). If they pull out a phone or recreate the data by hand that’s more of a management issue: you can’t stop employees doing things like that, but they can’t claim they didn’t realise they weren’t supposed to.

You don’t have to rely on employees labelling datasets and reports manually; they can inherit them from data that’s been classified in Azure Synapse, Ulag says. “In Azure Synapse Analytics you can label a table in the database – like an employee salary table or a performance reviews table – as highly confidential. Then, when Power BI connects to the table, even with permissions, it inherits the label from Synapse and if you export the data, it passes that on to Office. This allows you to string the whole data lifecycle together, all the way from the point of origin in Synapse to the point of consumption, either in Power BI or Office.”

Third-party software can offer Microsoft Information Protection support, but Ulag claims that Power BI is the first BI platform to integrate natively with it, and says Synapse will be the only data warehouse with MIP support.

Power BI won’t be the only Microsoft service to build in this end-to-end information protection. Power Apps is working on a similar integration and Dynamics 365 will also get the same capabilities because it’s built on Azure SQL DB, Ulag explains. When the integration is ready, “if you go to flag this table as highly confidential, any access through Dynamics or export out of Dynamics will inherit the same level.” Dataverse (previously known as Project Oakdale) is similarly built on Azure SQL DB, and “will probably get pretty much the same capabilities”.

SEE: Home office deduction guide and checklist (TechRepublic Premium)

If highly secret data isn’t your only worry with remote work, Power BI is also taking advantage of Azure AD and Microsoft Cloud App Security to let admins create conditional access rules for ensuring that the devices on which employees load data are up to date and fully managed by blocking downloads on unmanaged personal devices, as well as warning about suspicious access attempts. That includes spotting attackers trying to log in from somewhere they couldn’t have travelled to since the last legitimate login.

“You cannot access Power BI from Seattle and Tel Aviv within 30 minutes, because that’s impossible travel,” Ulag points out.

Share and share aright

Data security is important, but you don’t want it to stop people being able to share important trends and comparisons with colleagues who don’t have access to the underlying data – and don’t need that to understand the top-level measures. Executives and managers aren’t likely to check whether the employees to whom they’re sending a Power BI report have exactly the right data permissions. But they want them to be able to understand the visualisations, and also to trust that Power BI won’t let them see any underlying data they shouldn’t have access to.

Because Power BI enforces access policies (for data in the service and down to the data tier level in direct query mode), someone who doesn’t have access to the data table a graph is built from won’t see the graph when they look at a report; and if they only have access to certain rows, they’ll only see those in the visualisation.

Nine times out of ten, that’s what you want, Ulag points out. “A common scenario is, I’m a sales manager – say I’m the VP of sales for the US and I sent this report to one of my managers who is the director of sales for California. The whole report would work for them, they would see all the data – but only for California because they don’t have the same level of permissions I do. They have access to all the tables, but they can only see a subset of the rows.”

Send the same report to the directors of sales for Texas or Florida and they’ll see data for their region. That’s all done without showing the report creator a lengthy dialog explaining who will see what, Ulag says: “We don’t actually prompt them proactively in the process because it’s a very complex calculation to say ,’Hey, who are you sharing it with and what do they have access to?’, and often business users, when you put those kinds of messages in front of them, just get confused.”

SEE: Windows 10 Start menu hacks (TechRepublic Premium)

But when you want to make sure that everyone sees the trends or top-level figures that you want them to pay attention to, Power BI will soon have a way to let you share those without revealing levels of data you don’t want to share (or having to resort to a screenshot), using non-contingent security for the measures or metrics being analysed. This might sound simple, but it’s actually a complex calculation that can affect performance and takes a certain amount of configuration in something like SQL Server Analysis Services.

“It will allow you to make benchmarks visible to your direct [reports] without giving access to the actual data,” Ulag explains. “So I can create a graph that allows me to benchmark my directs – California versus Florida, for example – and then when I send it to them they can see the benchmark. But if they try to drill through and see California’s data, then Power BI will step in and say ‘you have the rights to see the benchmark, but you don’t have the rights to see the underlying data'”.

That will be particularly popular with organizations that handle data from multiple businesses, Ulag suggests: “Consulting organisations that use Power BI wanted to be able to provide these kind of benchmark reports to their customers. Power BI supports secure business-to-business access, so they want to enable benchmarking, but they don’t want their customers to be able to drill down and see each other’s data.”

Subscribe to the Data Insider Newsletter

Learn the latest news and best practices about data science, big data analytics, artificial intelligence, data security, and more. Delivered Mondays and Thursdays

Subscribe to the Data Insider Newsletter

Learn the latest news and best practices about data science, big data analytics, artificial intelligence, data security, and more. Delivered Mondays and Thursdays