How to implement Dynamic Security in PowerView

Question

I have created a PowerView using a BISM connection in Enterprise Portal of AX. That PowerView report will be used by 100+ users. I want every user to his/her data in the PowerView instead of viewing the complete data. One option is to create 100+ security roles in SSAS (multidimentional) which is not a viable option. Please guide me how can i achieve dynamic security in PowerView so that every user sees its own view. Thanks.

Answer 1

Power View doesn't not offer any kind of security. You will need to do this in SSAS, but you don't need 100+ security roles. You will want to look into dynamic security. To create dynamic security, you will need some way to relate a user to the information they should see. This usually means adding a field to an existing table or creating new tables.

If all users are secured by the same attributes, they can be contained in a single role. If some users are secured based on one attribute and others based upon another attribute, then you may need multiple roles.

Here's how this might work.

1. Create a table that contains all users that will need access to your cube.
2. Create a bridge table that ties the users to the attribute on which you are securing their access. For instance, maybe users can only see certain products so you have a table of User IDs and Product IDs.
3. Add these tables to your DSV.
4. Create a user dimension.
5. Create a measure group based upon your security bridge table
6. Create a role for this user type and add an MDX statement to the Allowed Member Set. Also, set the Enable visual totals checkbox.
   7. Populate the members for the role, preferably through an AD group rather than individually if you have 100+ users.

Your allowed member set will look something like

   Exists(
    {[Product].[Product ID].members},
    STRTOSET("[Users].[UserName].[UserName].&[" + Username() + "]"), 
    "Bridge User Product"
    )

You can find a good blog post here and a good video about SSAS security here (dynamic security starts around the 35 minute mark).