Implementing & Testing Row Level Security in Power BI

I have suffered a great deal of pain while implementing and more so while validating Row Level Security in Power BI. Let me try to capture all the steps for implementing and validating RLS in Power BI. 

I have created a Sample Sales Dashboard in Power BI. 

The main dataset is from table SAMPLE_DATA. The objective is to implement RLS by geography. The lowest grain of the geography is city. 

Assuming there are 3 types of user,

• City - Has access to a single city only
• State - Has access to state level (multiple cities)
• Corp - Has access to all cities 

I have created table SAMPLE_DATA_CITY from SAMPLE_DATA to have 

 

aaa

Adoption of Digital Technologies for Finance Office

The decision support system plays a very important part for any organization, especially when it comes to finance the outcomes are often direct and measurable. 

As per Gartner, 80% of finance leaders agree finance must significantly accelerate the adoption of digital technologies, such as Analytics, Automation etc. to support the business by 2025. A survey of 400 finance leaders shows a broad expansion of the technology toolkit being used to drive efficiency, agility and productivity. And these technology need to be complimentary to each other to bring out the full potential. 

Pic Credit - Gartner (April 2022)

Now the question is how to build the capability for the adoption of digital technologies? 

As per MIT Sloan Management Review the digital maturity depends on 7 parameters,

1. Strategic use of (Advanced) Analytics 
2. Model Explainability
3. Cross-Functional Data Collaboration
4. Analytics Skill
5. Exploration and Experimentation
6. Data Driven Culture
7. Digital Inclusivity

And the following questionnaire helps to evaluate the maturity. Calculate the average score for each of the 7 parameters.

• Score ⫺ 4 : Leading in digital capability
• Score between 3 to 4 : Developing digital capability
• Score ⫹ 3 : Lagging in digital capability

Pic Credit - MIT Sloan Management Review

This metric will help the CFO to assess the Finance Office's digital maturity over time and can track current state and the progress over time. 

References: 

• How Digitally Mature Is Your Finance Office? by Kristof Stouthuysen, published in MIT Sloan Management Review - Summer 2023
• “Gartner Survey Shows CFOs Turning to Process Mining to Drive Better Returns From RPA,” - Gartner, April 27, 2022

Evolution of BI & the Future

Business Intelligence has come a long way from MIS reports to prebuilt dashboards to self-service to Chatbots. And along with this the end users have also evolved from mere consumers to explorers to data hungry power users.  

We can divide it into 3 periods,

1. Initial Era (1990 - 2005) - This was the beginning of the BI. There were static MIS reports and the end users were consumers who were heavily dependent on the IT team. 
2. Middle Era (2005 - 2020) - This was the middle ages, where BI was synonymous to interactive Dashboards with slice and dice capabilities. The end users have also evolved from consumers to explorers, who used features like slicers, drill down / through to envision different scenarios of the data.  
3. Modern Era (2020 onwards) - In the recent times there have been massive changes., The data hungry workforce wants more than dashboards, they want empowerment 💪, to create their own insights, or in other words self-service capability. With that comes SaaS models and cloud hosted platforms, which give flexibility of scale up and scale out with minimal effort. 

As per leading Marker Researchers, 

• Clients no longer concentrate on which BI platform provides better data visualization, dashboards, or OLAP (“slice and dice”) functionality; rather they are prioritizing platforms that are: 
• Native to their preferred hyperscaler platform
• Seamlessly integrate with business applications
• Built on API-first architecture
• In an ideal BI environment, 80% of all BI requirements should be carried out by the business users themselves 

But what lies ahead? What is beyond Self-Service? 

With introduction of AI, ML and other technology evolutions, the power users will no longer have create their own reports by themselves. The BI tools will be smart enough to understand their requirement and built reports for them. May be we can call it Autonomous Self-Service (like Autonomous cars 🚗) ..... 😇

In fact, it is no longer a thing of future. Let's take the following examples,

• Oracle Synopsis - It is the Mobile Analytics App that lets you quickly open and interact with spreadsheets and business data in a visual and intuitive way, while you're on the go.

It is a standalone mobile based BI reporting tool which takes excel as data source, available in both Android and IOS. Oracle Synopsis is very intuitive in nature and the end user do not need any technical training to use it or create and share reports. This app is ideal for the Travelling Salesmen, Medical Representatives, PMOs who are always on the go and have the need to share reports (mainly excel based) 24 x 7.

• Oracle Lens - It is also a mobile based tool, which can scan any scan any tabular data using mobile camera and create insights instantaneously. 

• ThoughtSpot Search - It helps Self-Service analysis using NLP. In the search bar the users can type the analysis / insight they are interested in, and the reports get generated. Therefore with minimal knowledge self-service can be performed.

• Power BI Insights - It creates auto insights from the data with minimal intervention. 

And the list goes on and on. 

That leads us to the next question about our future, what is going to happen to us the BI Consultants / Developers? Are we also get redundant and obsolete like our little Rexy 🦖(can't help it my son is a big fan of Rexy ... 😋) ?

To my opinion, we are not going to get extinct at least for now. However, with the advent of every evolution and modernization the old roles get replaced by new more advanced roles. I think the role of dashboard developer is going to get reduced as self-service becomes easier, smart and automated. But the backend structure and governance will become more important, the following activities would become more and more important. 

• Advisory - Helping clients and end users choose the right tools as they are going to get spoilt by choices. The set of tools must fulfil the requirement and complement the organization's long and short term technology roadmap.
• Designing BI Architecture - As BI tools becoming more advanced and complex and there are significant number of integration opportunities both upstream and downstream. Therefore designing a robust, scalable architecture becomes more important to support all the smart things on top.  
• Designing Data Mart - Somethings don't change, a solid scalable data model (Normalized, Denormalized, Star, Snowflake) is the foundation of the BI solution. And thanks to Mr. Ralph Kimball and Mr. Bill Inmon, there is enough of confusion already on which approach to choose, a top down or a bottom up.  
• Defining Metadata (Metrix, Dimensions, Hierarchies) - Once the ground work is done, the calculations and the logics need to be defined, so that the automation can use them create the answers / analysis users seek. 
• Governance - With empowerment comes governance. As more and more user are empowered the create their own analysis, that would lead to more and more confusion about the version of truth. To maintain single version of truth and standard across the organization, a governance process is very important. There has to be a Heimdall protecting the Bifröst. 

Working with Power BI Bookmarks - Cross filtering

Unlike few other data visualization tools such as Tableau or Qlik, for Power BI the cross filtering interaction across reports are limited to a single page, the only alternative being Drill Through, however that requires multiple clicks. I was trying to find out a way to recreate similar experience of cross filtering interaction across reports in Power BI. 

Let me first show the end output, I have created a simple sales dashboard using some dummy data. 

There are two pages Summary and Details,

And the interaction is present between the two pages. To illustrate that, let me select Product Category 'Furniture' from the top, and as expected other reports are showing for 'Furniture' only. 

Now, if I go to Details, it is also showing for 'Furniture' only. 

Let us try to explore the technicalities. I initially thought, this would be fairly simple using Bookmarks and Selections. I have created a single page with all the visuals, and have added a small empty section at the bottom.  

Next I have performed 3 steps,

1. Enabled the Bookmark and Selection view
2. Hidden the Details table
3. Created a bookmark called Summary

The same way I have hidden other charts to create another bookmark called Details.

Next I have added a Bookmark Button at the bottom and made it appear like tabs. And point to be remembered everything is in a single page. 

And I thought this would be it, but to my surprise I found out that cross filtering interaction is not working between 2 bookmarks also 👺👿💀. I have to find a work around to overcome this, and make it appear like cross filtering interaction across tabs. 

To overcome this I have added a white shape, and instead of hiding / unhiding the Details table I am hiding / unhiding this white shape in the bookmarks.

So essentially my Details table is always visible it is the shape that is acting as a curtain. 

And that does the magic of deception, it appears that we are cross filtering across two pages.

However, to emphasis this is not a full proof solution just a work around. 

BI Self-Service & Power BI

With the evolution Data & Analytics consumption, our lives has become full of data. From ordering food 🍔🍕🍰 to checking the delivery status, from binge watching📺👀, to online shopping 🛒 we are always producing and consuming data. 

Long gone are the days of statis MIS reports and dependency on the IT teams for getting those reports. Even the prebuilt interactive reports are also not adequate and flexible enough for the data hungry workforces. Business users want empowerment 💪, to to create their own insights, or in other words self-service capability. 

Good for us that the current modern BI tools such as Power BI, Tableau, Qlik are quite capable to handle such self-service capability. But with freedom comes responsibility, so the challenges of self-service BI are as,

• Standard - The organizational standards in terms of metrics definition, dimensional attributes, nomenclatures, UI etc. need to be consistent across.
• Single source of truth - With everyone creating their own version of truth, it is very important to define the certified version.
• Governance - To avoid lawlessness there has to be a strong governance structure and defined roles.

I will take the example of Power BI and try to explore how we can achieve self-service with overcoming the above challenges. 

There are 2 parts of the solution residing in Architecture and Access/Roles.

A. Architecture

To achieve this I would go with a layered architecture. Instead of keeping everything together in a single .PBIX or in a single workspace, let us split them to achieve greater flexibility. 

The left most layer in the dam represents the data layer and it is not part of the Power BI. Considering the data can be on-prem or cloud hosted and can be connected to Power BI directly or through Gateway. Again there can be different options of import vs direct query. In the case of later the the UI layer can be directly connected to the data.

Before going to the Power BI layers (from left to right), let us know that a .PBIX file has 3 major components, viz Data transformation (you can access by edit query), Data including model and tables, metrices, and reporting. These 3 components can be split into multiple workspaces spread across 3 layers. 

1. Dataflow Workspace - This layer need to be created online in Power BI services only. This will contain the data source connectivity and the data transformations.
2. Dataset Workspace - This will contain the imported data and the data model (relationship and cardinality). Basically these are .PBIX files developed offline but with blank pages.
3. Reporting Workspace - This will contain the Reports only. These are .PBIX files, which will connect to the Power BI dataset instead of data source.

Now the question comes why do we have to take so much of pain, when we can easily perform everything in a single .PBIX file? 😕

Now the reason is flexibility to control and govern. From a PBIX file we can connect to single Dataset only, but a single Dataset can connect to multiple Dataflows. The plan is to create separate workspaces for secured and open Dataflows. basically the dimension data which should be available to all can be loaded through Dataflows residing in the open workspaces. Whereas the sensitive factual data can be loaded through different Dataflows residing in different workspaces, based on the departments. While creating a Dataset, these different Dataflows can be accessed based on the privilege and data can be combined. Furthermore based on the sensitivity of the data the Dataset can be put into secured or open workspace. So you can image while connecting from a UI file we get multiple combination of accessing the data, and that is going to play a key part in providing data access for Self-service. Moreover these Dataflows and Datasets can be certified to ensure the credibility.   

In addition to this we need to maintain the RLS (Row Level Security) 🔏 data to ensure data level security.

The PowerApps is like a cherry on the top 🍒 here, it gives the end users to update the backend data by themselves. So our empowered end users are not only consuming the data, but they are producing also by themselves.

B. Access/Roles

Access / Role will play a major part in the governance, controlling who can access what and have gate keepers to ensure standards and consistency. There are 4 different roles in Power BI:

Let us try to understand and compare each role, what are the privileges they have or don't have.

Best practices for assigning roles:

• The Admin user to be used for admin related activities only such as creation / deletion of workspaces, adding other admins etc.
• The users who do not need to perform specific admin related activities, should not be with admin roles. 
• Member roles can be leveraged for all other controls and deployments. They can even manage security for roles other than admin. 
• The members should be responsible for all the content published to the larger audience. They should perform checks before final deployment.    
• Contributors should not be allowed to update an app, that will do away with the benefit of having gate keeper and the standard of the content can be compromised.
• The access should be given to user groups rather than individual users, that way controlling the access will be easier and there will be people to backfill others. 

Consumption 

Finally we are at the consumption layer. Here we will have 5 different user personas, starting from the top,

• User 1 : BI Developer / Admin - Able to connect to the DB from Power BI desktop. RLS doesn’t apply. Practically they can do almost everything.
• User 2 : Data Analyst - Able to connect to the dataflow and dataset from Power BI Desktop.
• User 3 : BI Power User - Able to edit prebuilt published reports and create own’s. 
• User 4 : BI Explorer - Able to personalize and share the prebuilt reports. The self-service is limited to to playing around with the prebuilt reports only.
• User 5 : BI Consumer - Able to view, slice and dice the reports with no self-service capability. 

 

Interesting Thoughts - Power of Shunya (Zero)

Our modern number system is heavily dependent on '0'. Thanks 👏👏 to Mr. Aryabhata for capturing the concept of 'shunya' or void, and suggesting the base of decimal number system.

"From place to place, each is ten times the preceding" - Aryabhatiya by Aryabhata

Different ancient Indian mathematician such as Brahmagupta and Bhaskara have tried to decode the '0' afterwards. 

What happens when we introduce '0' in any calculation? 

For addition and subtraction it has no impact, or we can say absolutely zero impact 😉

What happens in case of multiplication?

N x 0 = 0, basically repeating any number '0' times means nothing. 

 

The confusion starts with division. 

As per modern mathematics N/0 = Undefined. 

As per ancient Indian mathematician Bhaskara N/0 = ∞ (Infinite)

Let us try to do a simple calculation,

1/0.009 = 9000

1/0.0009=90000

1/0.00009 = 900000

And so on .....

So basically as the denominator is becoming smaller or getting closer to '0' the result is becoming bigger or getting closer to ∞ (Infinite).

Mathematically,     1/N   =   M

                               N→0      M→∞ 

Similarly, 

-1/0.009 = -9000

-1/0.0009=-90000

-1/0.00009 = -900000

And so on .....

So basically as the denominator is becoming smaller or getting closer to '0' the result is becoming bigger or getting closer to ∞ (Infinite).

Mathematically,     -1/N   =   -M

                               N→0      M→∞ 

Now, food for thought is N/0 = Undefined or Infinite 😯 ? And are there different infinites, like +∞ and  - ∞ ?

Reference: Numericon by Mariane Freiberger & Rachel Thomas