Skip to content

Fine-grained Access Control on Azure Analysis Services and PowerBI

If you haven’t been bombarded with messages about attaining “data nirvana” on the cloud, you must be living under a rock. Cloud adoption is on the rise, with an increasing number of business users realizing the benefits of API-first, scalable cloud architectures. However, data migrations and subsequent data management on the cloud is a road less travelled, one still fraught with “data mines.”

Azure Analysis Services offers a cloud-hosted data analytics platform-as-a-service, allowing massive amounts of data to be queried for ad-hoc data analysis. It’s really easy to get started, by ingesting data from a variety of sources and building semantic tabular models.

In this article, I plan to address how Okera solves for this last mile problem of ensuring the right data access at the right level securely.

Okera and Azure Analysis Services

Okera is a Microsoft Gold Partner and is transactable on the Microsoft Azure Marketplace. Our data access platform can embrace and extend solutions to our joint customers across the entire Microsoft product portfolio from Microsoft Data, Dynamics & Power Platforms, to Azure Cloud, to newer technology releases like Purview and Data Catalog.

The combination of AAS and PowerBI provides amazing capabilities to create, publish, and review reports that our enterprise customers love. But they also need the ability to filter out certain records – otherwise known as fine-grained access control – for the end users who need to look at them. Okera does this seamlessly by introducing a secure layer between AAS and PowerBI Services.

Sensitive Data Protection Use Case

One of our customers using Azure Analysis Services to enable data exploration and ad-hoc analysis by their data consumers needed to restrict access to certain columns based on the user and their associated cost center.

A dimension table has several attributes, with some attributes considered sensitive and other attributes considered non-sensitive. Non-sensitive attributes would be accessible to all users, while a subset of the sensitive attributes relating to their corresponding cost center would be accessible to each individual user. The challenge here is that Azure Analysis Services supports column-level security but not at this level of complexity.

When a model in AAS is ready to be deployed, the data warehouse architect uses Okera to read the model and the source data from AAS. Sensitive values such as credit card and IP address are automatically tagged, and the model and its components are registered in Okera. He uses the Okera policy builder to simplify the roles/rules matrix by creating an attribute-based policy that eliminates complexity. Analysts then build the reports and visualizations in PowerBI, only viewing what they are authorized to access, before publishing to a larger audience.

To meet our first requirement, Okera read the model (in our example, adventureworks), registered the various components of the model including tables and columns, and then tagged the following as PII in the customer table.

The PII tags are shown below.

The architect then built a policy using the Okera Policy Builder. In our example: credit card and address should be redacted, and customer’s last name and email address should be de-identified using tokenization. One single policy controls the privilege on multiple columns!

When a user logs in PowerBI as an analyst, PowerBI does not connect to the AAS model. Instead, it queries Okera first, with Okera applying the policy and rewriting the query before sending it to AAS, which processes the rewritten query in order to return the result set to PowerBI. All of this is done by Okera within a fraction of a second!

As shown below, the columns for address and credit card number are not visible to the analyst. Dynamic tokenization policy is implemented on the values for last name and email address; although the column is visible, the actual contents are obfuscated.

It is important to note that Okera’s tokenization function preserves referential integrity. If an analyst needs to join any two tables based on the obfuscated email, the resulting report will still provide accurate results. Our customer was delighted with this solution!

Financial Reporting Use Case

Another requirement that we come across very frequently is the need to secure access on the hierarchy nodes for financial reporting. Financial analysts should only view financial transactions for their particular business unit or cost center. While this sounds simple in theory, when multiplied across the number of roles and access policies needed in a large conglomerate, it quickly becomes anything but.

To cater to this roles/rules heady mix, the report authors end up creating a large number of PowerBI reports using DAX filters. For some clients with cross-BU reporting, even the DAX filters don’t meet the access requirements. This can result in significant delays at the end of the quarter, with analysts working late nights and sending a bevy of reconciliation emails with spreadsheets attached to fix inaccurate roll-ups and currency translation adjustments.

This requirement can be met by Okera using the user profile, which is enriched with additional attributes such as the region ID or cost center ID. In the following example, the Regional Manager responsible for France, Germany, and the UK is able to view details about only the sales transactions in his region in PowerBI, thanks to an Okera policy.

In conclusion, the two use cases above demonstrated how Okera is able to:

  1. Import metadata from an Azure Analysis Services Model and execute policies on data to de-identify a particular dimension PII attribute (customer address, lastname, email) for a certain set of users;
  2. Leverage the Azure Analysis Services hierarchy of the geography dimension and provide secure access to select transactions (in the above case, Europe) at a particular hierarchical level.

Okera brings simple but powerful attribute-based control to Azure Analysis Services and delivers fine-grained access to PowerBI users at an enterprise level. Indeed, as shown in the above examples, Okera and Azure Analysis Services working together can significantly enhance access and cast a wider net for cloud adoption securely!

Sorry. No data so far.