Analysis Services Security - SQL Server Analysis Services 2012 Cube Development - page 254

Implementing dynamic dimension

security

In the previous section, you learned how to restrict a role's access to certain dimen-
sion hierarchy members. This worked well for a dimension with few categories and a
single role. As long as you can group users into a few roles depending on their job
function, this approach will suffice. However, when the number of roles grows to hun-
dreds, you'll find that managing security can become very cumbersome and tedious.
Clearly, creating a new role to expose a specific data set to each retail customer is
unacceptable. Fortunately, you can work around this limitation using a security meas-
ure group.

Although it takes several steps to implement, the security measure group utilizes rel-
atively straightforward concepts. You need to identify the attribute to secure, perhaps
the sales territory that each cube user should be able to browse. Next, you create a
measure group defining the mapping between the user and sales territory. The secur-
ity role will then use the UserName function within an MDX expression to determine
the set of sales territories that the user is permitted to browse. This way, you can have
a single role that manages security dynamically based on values found in the security
measure group.

How to do it...

Let's get started by performing the following steps:

1. Create a user dimension table in the AdventureWorksDW2012 sample rela-
tional database using the following code:

CREATE TABLE [dbo].[dimUser](
[user_key] [int] IDENTITY(1,1) NOT NULL,
[user_login] [varchar](50) NULL,
CONSTRAINT [pk_user_key] PRIMARY KEY
CLUSTERED
(
[user_key] ASC
)WITH (PAD_INDEX = OFF,