Now that we’ve established the why and how of Dax aggregations, let’s dive into applications. Specifically, how we might pair this with Row Level Security. Or at least how I did pair it with RLS. This post assumes you have read part 1. You have been warned.

Let’s talk about World Wide Importer. Not the database, but the business. They sell stuff. From looking at the data, it looks like they mostly sell coffee mugs to DBAs and Devs. Somehow, they employ over 100 salespeople in this endeavor. They also employ a bunch of people to move them around the warehouse. All of these employees are listed in our model in ‘Dimension Employee’. ‘Fact Sale’ rows are related to salespeople, and other fact tables in the model are related to warehouse staff. However, ‘Dimension Employee’ doesn’t mention anything about anyone managing these people. Never the less, this anarchical mug factory has generated appears to have $168 millon in sales.

Now that I’m in charge of this company, its time to impose some good old fashion capitalism.

First, to properly motivate the sales force, we’re going pays the sales team commission on revenue generated. HR doesn’t like it when people know what their peers make, so as to keep people paid as little as possible. As a result employee sales data is confidential.

Second, we will impose order. We’ll hire management, operations staff, and most importantly, analysts to help run the show and set sales targets for our new commission system. Neither Sales Operations or Sales Management appear in the data warehouse yet so I’m going to have to create that data. We’ll want to be able to filter by manager in our reporting. However, HR still doesn’t want sales managers knowing how their peers are getting paid.

Some operations staff is assigned directly to a specific sales team rather than HQ. This might be distinct from the company’s actual organizational structure. As a result, their access to employee sales data from needs to be limited to just the employees they are responsible for. We can pull org structure out of the HR database, but this kind of conditional access is going to need something more…custom.

Finally, this new management class will need reporting. We want to provide a single data model that can be accessed by the whole company and we want as many dimensions as possible to be available to everyone. We want to make sure that even Sales Managers can navigate the model using Analyze in Excel.

You know: Data Democratization; Single Source of Truth; Scalable Solutions; Clicky-Clicky Draggy-Droppy; Justify playing with premium only features. Obvious stuff like that.

To recap, we want to design a data model security scheme that protects the privacy of people’s bonuses while also providing visibility into the performance of the business for all employees.

1. Sales people can see their own sales but not a peers'.
2. Sales managers can see the performance of their team but not their peers.
3. Operations staff can see the performance of the team to which they are assigned.
4. Employees in other departments can't see any employee performance data.
5. Everyone can see sales across non-employee dimensions.
6. The user experience is seamless: measures are fast and you don’t need to know which version of measures to use for which dimensions

Where we’re going we don’t…have enoughh tables.

So. I’ve created more tables.

1. A leveled org table that relates Sales Managers to Employees. Manager(s) could also be dimensions of your employee table, but this depends on your Data Warehouse. I’m not modifying anything from the sample DW for my example, so we’ll just use an outrigger table with a 1:1 relationship to Employee Key.
2. A flat employee security table. This is the org table unpivoted plus everyone listed as their own manager. Also, this table needs employees’ AAD User Principal Name.
3. A disconnected delegate access table that relates Operations staff with Managers. Also needs User Principal Names. By using a separate table, you can easily maintain it in excel or on your DB without having to get this to conform to any existing process. Unless one exists that works for you. Then use that.
4. The relationship between the security table and the fact table is many to many, so we have an employee bridge table.

It gets more complicated if you have multiple sales roles and a multi-level ragged hierarchy of sales managers (like Bing!) but let’s not go there today.

Thankfully, now that we’ve created a good data model, the DAX is very straightforward. This is always preferred.

We can create a single role for all users with one simple DAX filter applied to the Security table to accomplish goals A-D above.

OR (
    [AAD UPN] = USERPRINCIPALNAME (),
    [AAD UPN]
        = LOOKUPVALUE (
            ‘Delegate Table’[Delegate AAD UPN],
            [AAD UPN], USERPRINCIPALNAME ()
        )
)

This will filter ‘Fact Sale’ down to rows where the user is listed in the security table, or where the person for whom they have delegate access is listed. Warehouse staff don’t appear in the fact table, so they don’t see anything. Ops staff have to be listed in the delegates table. That covers A-D. To accomplish E & F, we’re going to use a Manual Aggregation.

We never want to include a security table in the manual agg measure. This is because those columns are always filtered by our security rule, and thus would always cause us to hit our detail table. This means that the security table must be a hidden table that is not used in any report visuals.

To finish this off, all we need to do is set up our DAX Measures for Total Sales.

Total Sales Detail :=
SUM ( ‘Fact Sale’[Total Excluding Tax] )

Total Sales Agg :=
SUM ( ‘Fact Sale Agg’[Total Excluding Tax] )

Total Sales :=
IF (
    ISFILTERED ( ‘Dimension Employee’[Employee] )
        || ISFILTERED ( ‘Dimension Employee’[Employee Key] )
        || ISFILTERED ( ‘Dimension Employee’[Employee] )
        || ISFILTERED ( ‘Dimension Employee’[Is Salesperson] )
        || ISFILTERED ( Org[Manager 1] )
        || ISFILTERED ( Org[Manager 2] )
        || ISFILTERED ( Org[Manager 3] )
        || ISFILTERED ( Org[Manager 4] )
        || ISFILTERED ( Org[Employee Key] )
        || ISFILTERED ( ‘Dimension Invoice Date’[Date] )
        || ISFILTERED ( ‘Dimension Invoice Date’[Calendar Month Label] )
        || ISFILTERED ( ‘Dimension Invoice Date’[Calendar Year Label] )
        || ISFILTERED ( ‘Dimension Invoice Date’[Fiscal Month Label] )
        || ISFILTERED ( ‘Dimension Invoice Date’[Fiscal Year Label] )
        || ISFILTERED ( ‘Dimension Invoice Date’[ISO Week Number] )
        || ISFILTERED ( ‘Dimension Invoice Date’[Month] )
        || ISFILTERED ( ‘Dimension Invoice Date’[Short Month] )
        || ISFILTERED ( ‘Dimension Invoice Date’[Day] )
        || ISFILTERED ( ‘Fact Sale’[Description] )
        || ISFILTERED ( ‘Fact Sale’[Package] )
        || ISFILTERED ( ‘Fact Sale’[Salesperson Key] )
        || ISFILTERED ( ‘Fact Sale’[Delivery Date Key] ),
    [Total Sales Detail],
    [Total Sales Agg]
)

Now we just hide everything but [Total Sales] so that all the user sees when using the model is that measure. Now they are seamlessly directed to the right granularity for their query and when they filter by the employee or org table, they only see the Sales rows that they are permissioned for. However, when they don’t, they see complete and correct results.

You may later decide to include some columns of the employee to your aggregate fact table. For example, alignment between your sales regions and sales managers may be a bit messy (like Bing!) and so you might want to expose revenue for a higher-level manager to the report audience regardless of role . If you wanted to get crazy you can even add another, totally different, RLS rule to your Agg table.

The sample .pbix file is available here. Good luck.