Row Level Security with SSAS Tabular Live Connection in Power BI

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

2016-07-04_00h10_06

You can define Row Level Security in Power BI itself, However sometimes you do use SQL Server Analysis Services Tabular as the source for Power BI through Live or DirectQuery connection. SSAS Tabular allows you to create same type of Row Level Security. So it would be much better to use Row Level Security defined in Tabular from Power BI, rather than duplicating that in Power BI. In this post I’ll show you an end-to-end solution which contains elements below;

  • Power BI Live Connect to SSAS Tabular through Enterprise Gateway
  • Row Level Security Configuration in SSAS Tabular
  • Viewing the result filtered by RLS in Power BI

Note that this method is different from defining Row Level Security in Power BI Desktop. In fact we won’t use that method. If you like to learn more about that method read this blog post. If you are interested to learn more about Power BI, read Power BI online book; from Rookie to Rock Star.

Prerequisite

For this demo I will be using my demo machine which has SSAS Tabular and Enterprise Gateway. In a separate post I’ll explain how to create such demo VM. You also need to have SSAS Tabular database example AW Internet Sales Tabular Model 2014 from here.

Install and Configure Enterprise Gateway

Purpose of gateways in Power BI is to create connection from Power BI cloud service to on-premises data sources. There are two types of gateways; Enterprise, and Personal. Naming of these gateways are a bit misleading. Personal doesn’t mean you have to install gateway on your laptop only, and Enterprise doesn’t mean only for organizations. There are more differences which I will talk in different post. In general Enterprise is built for more live connections specially to Analysis Services, and Personal is more for importing data from many data sources. Let’s leave details of that for now. For this example we will be using Enterprise Gateway because we want to connect to SSAS Tabular on-premises through a Live/DirectQuery connection.

You can download latest version of Enterprise Gateway from PowerBI.Microsoft.Com website under downloads

2016-06-30_22h33_56

Then from gateways download page, choose Enterprise gateway, and download it.

2016-06-30_22h35_14

Installation of Enterprise Gateway is just following a wizard, so continue that till end of the wizard where it asks for Power BI login. Sign in to your Power BI account;

2016-06-30_22h38_39

After sign in, configure the Power BI Gateway with a name, and key, and then you should be good to go. If you see green checkbox and Connected means your configuration was successful.

2016-06-30_22h41_44

As you can see after successful configuration, you can close the gateway (don’t worry it is running behind the scene as a service), and add a data source to this gateway in Power BI Service.

Or alternatively you can directly go to Power BI website, after login go to setting menu option and choose Manage Gateways

2016-06-30_22h43_50

In Gateway management window, you can see all gateways you have set up. My gateway here is named BIRADACAD, and I can see that it is successfully connected.

2016-06-30_22h45_14

Create Data Source

Now Let’s create Data Sources. You might think that one gateway is enough for connecting to all data sources in a domain. That is absolutely right, however you still need to add a data source under that gateway per each source. each source can be a SQL Server database, Analysis Services database and etc. For this example we are building a data source for SQL Server Analysis Tabular on premises. Before going through this step; I have installed AW Internet Sales Tabular Model 2014 on my SSAS Tabular, and want to connect to it. If you don’t have this database, follow instruction in prerequisite section.

For creating a data source, click on Add Data Source in manage gateways window (you have to select the right gateway first)

2016-06-30_22h52_21

Then enter details for the data source. I named this data source as AW Internet Sales Tabular Model 2014, I enter my server name, and database name. then I use Windows authentication with my domain user <domain>\username and the password. You should see a successful message after clicking on Apply. The domain name that I use is BIRADACAD (my SSAS Tabular domain), and the user is PBIgateway, which is a user of BIRADACAD domain (username: BIRADACAD\PBIgateway), and is an administrator for SSAS Tabular (explained in next few paragraphs).

2016-07-03_23h15_36

Note that the user account that you are using here should meet these conditions:

  • It should be a Domain User
  • the domain user should be an administrator in SSAS Tabular.

You can set administrator for SSAS Tabular with right clicking on SSAS Tabular instance in SSMS, and in Properties window,

2016-07-03_22h58_14

in the Security setting add the user to administrators list.

2016-07-03_22h59_12

Effective User Name

Gateway account used for accessing Power BI cloud service to on-premises SSAS Tabular. However this account by itself isn’t enough for the data retrieval. gateway then passes the EffectiveUserName from Power BI to on-premises SSAS Tabular, and result of query will be returned based on the access of EffectiveUserName account to SSAS Tabular database and model.

By default EffectiveUserName is the username of logged in user to Power BI, or in other words EffectiveUserName is the Power BI account. This means your Power BI account should have enough access to SSAS Tabular database to fetch required data. If Power BI account is the account from same domain as SSAS Tabular then there is no problem, and security configuration can be set in SSAS Tabular (explained later in this post). However if domains are different, then you have to do UPN mapping.

2016-07-04_00h04_39

UPN Mapping

Your SSAS Tabular is part of a domain (it should be actually, because that’s how Live connection works), and that domain might be the domain that your Power BI user account is. If you are using same domain user for Power BI account then you can skip this step. If you have separate Power BI user account than the domain account for SSAS Tabular, then you have to set the UPN Mapping.

2016-07-04_00h10_06

UPN Mapping in simple definition will map Power BI accounts to your local on-premises SSAS Tabular domain accounts. Because in my example I don’t use same domain account for my Power BI account, so I set up UPN as below.

2016-07-03_23h19_37

Then in Mapping pane, I create a new mapping that map my Power BI user account to reza@biradacad.com, which is my local domain for SSAS Tabular server.

2016-07-03_23h20_45

Now with this user name mapping, reza@biradacad.com will be passed as EffectiveUserName to the SSAS Tabular. If you want to learn more about UPN mapping, you can watch Adam Saxton’s great video about it.

Configure Row Level Security in SSAS Tabular

In SSAS Tabular opened in SSMS, expand AW Internet Sales Tabular Model 2014, and create a New Role

2016-07-03_23h24_23

We want to create a role for users who don’t have access to Bike sales across the database. so let’s name the role as No Bike Sales Manager, this role has Read access to the database.

2016-07-03_23h26_45

In the membership section you can add users to this role. Users should be domain users that you get through EffectiveUserName from Power BI (if Power BI accounts aren’t domain users, then create UPN mapping for them as explained above in UPN mapping section). I add user BIRADACAD\Reza here. (Note that I’ve created a UPN mapping for this user. so each time Power BI user associated with this logs in, this domain account will be passed through EffectiveUserName to SSAS Tabular).

2016-07-03_23h29_38

Now let’s define Row Filters with a basic filter on Product Category as below;

2016-07-03_23h31_02

Now that we defined row level security in SSAS Tabular, let’s build a simple report to test it.

Create Sample Report

Our sample report here would only show a Pie chart of Product Categories Sales. So create a Live connection to SSAS Tabular from Power BI Desktop.

2016-07-03_23h38_08

Then choose the model

2016-07-03_23h38_47

After creating the connection you should see the Live Connection: Connected status in Power BI Desktop down right hand side corner.

Create a simple Pie chart with Product Category Name (from Product Category table) as Legend, and Sales Amount (from Internet Sales table) as Values.

2016-07-03_23h34_14

Save the Power BI file, for example as SSAS Tab Live Ent DB.pbix, and publish it. Remember that you shouldn’t set up Row Level Security in Power BI itself. Because we will be using RLS configuration from Live Tabular connection.

Test the Result

In Power BI website or service, when you login, and refresh the SSAS Tab Live Ent DB report. you won’t see any sales from Bikes. you will only see sales of Clothing and Accessories.

2016-07-03_23h42_01

How doesn’t it work?

What happened here is that my Power BI account mapped to reza@biradacad.com domain user, and this domain user passed through enterprise gateway as the EffectiveUserName to the SSAS Tabular on-premises. SSAS Tabular identified that this user has only one role which is No Bike Sales Manager, and this role has a restricted view of sales for everything but Bike. So the data returned from SSAS Tabular to Power BI report doesn’t contain Bike’s sales.

Summary

SSAS Tabular as a Live connection source for Power BI used in many enterprise solutions for Power BI. There are different reasons for that, for example;

  • Some organization already have SSAS Tabular models ready, and they are using that for their on-premises reporting and data analysis. So they want to use the same source of truth.
  • Scale of data is larger than it fits into Power BI model. (Read step beyond 10GB limitation for Power BI as an example).
  • and many other reasons.

In this post you’ve seen how Row Level Security defined in SSAS Tabular will be passed through EffectiveUserName to Power BI accounts. This method will authorize users to only view part of the data that they are authorized to see.

Save

Save

Save

Save

Save

Save

Save

Save

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

2 Comments

  • Good demo Reza!

    Can the Tabular model in SSAS when connected from Power BI Desktop takes effect based on the RLS defined?
    Raj

    • Hi Raj,
      Do you mean using Power BI Desktop as your visualization point?
      when you create the live query connection then you need to use your windows account for it. and you will see only part of it that you have access to it.
      It means RLS is built-in with Live Query connection in Power BI.
      Cheers
      Reza

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">