Row Level Security in Power BI – Star Wars Challenge

This is the second blog post in Power BI Row Level Security (RLS) series (Part 1 is here). Implementing RLS in Power BI is a fascinating topic. You will usually find multiple ways to implement it. As the requirements become more complicated, you will no longer be able to rely on dedicated AD groups, and will need to implement dynamic rules. Will you choose a complex DAX filters, or create new lookup tables to adjust your Data Model?

In today’s post, we will present two Row Level Security challenges, using a cool dataset as an exercise – The Star Wars API here.

Subscribe to Data Chant and download the solutions here (You will need the subscribers’ password).

[rad_rapidology_inline optin_id=optin_5]

Ready to start?

Star Wars RLS – Challenge 1

Download this Power BI Report file. In this report you can find which Star Wars characters own starships, and what is the cost to own them,

The Characters table includes crucial information for this exercise. Each row describes a unique character in Star Wars:

• Email contains their fictitious email address (highlighted in red)
• Homeworld column contains an ID to their corresponding planet in Planets table

The Starships table describes the starship models in Star Wars. As each ship can be owned by multiple characters, and each character can own multiple starships, we have a typical many-to-many relationship between Characters and Starships tables, which is implemented with the help of the Starship Owners table.

The challenge at hand – Assume that your Power BI users are the actual Star Wars characters. Each character can login to powerbi.com with their email address and access this report. Your goal is to edit the Power BI report, so the logged-in Star Wars user will be able to view all the characters from his home world, and all their owned starships.

Tips:

1. No need to use powerbi.com service. You have all the tools in Power BI Desktop to implement and test your solution.
2. In the RLS DAX Filter apply USERNAME() on the email that is provided in Characters table.
3. To test your solution, view the Role as Luke Skywalker. Here is his email: luke.skywalker@starwars.com. The following screenshot shows you how to set the View as roles dialog.

When you resolve challenge 1, you will see that when Luke Skywalker logs in, he can view all the characters from Tatooine (his home world) in the left table, and can view the total cost of starships for those characters in the top right visual, and the specific starships they own in the bottom right table.

Not sure how to continue, wait for the next blog post in this series, or subscribe to DataChant and download the solution.

Star Wars RLS – Challenge 2

Here is where things get more interesting, and relevant for real business scenarios. Download the 2nd challenge file here.

The Security requirements have become trickier. Star Wars corporation has promoted some characters to become managers. There are two types of managers: A Planet Manager and a Species Manager, and each manager can be of both types.

Your assignment (if you choose to accept it) –

• Ensure that only managers can access the grey visuals, and their corresponding tables (highlighted in red).

• A planet manager can view all the characters from his own planet, and their owned starships.
• A species manager can view all the characters of the same species, and their owned starships.

For example, Luke Skywalker is both a planet manager (Tatooine), and a species manager (Human). He can access rows of characters who are from Tatooine or rows of characters whose species is Human (The logic is OR, not AND, and not XOR).

The following screenshot is taken from the solution report. Tested on Luke Skywalker.

Tips:

1. No need to use powerbi.com service. You have all the tools in Power BI Desktop to implement and test your solution.
2. In the RLS DAX Filter apply USERNAME() on the manager email that is provided in the relevant table.
3. To test your solution, view the Role as Luke Skywalker. Here is his email: luke.skywalker@starwars.com.
4. No need to apply complex DAX here (But if you managed to do it, please share it in the comments below).
5. The solution assumes that you cannot use separate roles for separate AD groups.
6. Consider using Power Query to create the necessary security filter flow.

Hope you will enjoy this exercise.

Spoiler Alert:

Here is a screenshot from my solution. Stay tuned for the next post for the details.