SSAS Attribute security in a many-to-many form

Question

SSAS Attribute security in a many-to-many form

LouisBB

SSC Enthusiast

Points: 157
More actions
August 26, 2008 at 1:32 am

#189749

Dear Group,
I've got a fact table with five dimensions. I need dynamic attribute security on one of them, the PointOfSale dimension. For each point of sale zero, one or more users can be authorised. I know their userIDs.
When I try querying the cube in SSMS using this query I do get the expected results:
SELECT
NON EMPTY { [Measures].[Lunch Count], [Measures].[Lunch Amount] } ON COLUMNS
, NON EMPTY { ([Dim Point Of Sale].[Hierarchy].[Point Of Sale Description].ALLMEMBERS
* Filter([Autorisation Org Obj].[Autorisation User].Members
,MemberToStr([Autorisation Org Obj].[Autorisation User].CurrentMember)
= ('[Autorisation Org Obj].[Autorisation User].&[' + UserName() + ']' )) ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [Cube_ProLine]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I tried the next:
In the Cube Structure table AutorisationOrgObj (key: pointOfSaleKey+AutorisationUser) is a fact table related to the dimension table dimPointOfSale (key: pointOfSaleKey). I created a count measure on this table "Autorisation Org Obj Count" in a separate measure group "Autorisation Org Obj".
In the Dimension Usage I linked the dimension "Autorisation Org Obj" as Many-to-Many to my real measure group "Fact Lunch", using Intermediate measure group "Autorisation Org Obj".
I created a Role CommonUser.role for all users. On this role I added as MDX for the Allowed member set of the "Autorisation User" attribute of the "Autorisation Org Obj" dimension the Filter expression of the query above:
Filter([Autorisation Org Obj].[Autorisation User].Members
,MemberToStr([Autorisation Org Obj].[Autorisation User].CurrentMember)
= ('[Autorisation Org Obj].[Autorisation User].&[' + UserName() + ']' ))
When browsing the cube I see all data instead of only the points of sale I'm autorised for.
(I checked using SQL Server Profiler. I don't see my Filter functiom showing up anywhere in the trace.)
Where am I going wrong? Can anyone please give me a tip?
LouisBB.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

LouisBB SSC Enthusiast Points: 157 More actions · Answer 1

The article "Protect UDM with Dimension Data Security" by Teo Lachev helped to create the required functionality. The dynamic autorisations are in a separate measure group. On the dimension shared between this measure group and the "real" data security is implemented using a Role. Example formula:

Exists([Dim Point Of Sale].[Dim Point Of Sale].Members, StrToMember("[Dim Autorisation User].[Dim Autorisation User].&[" + UserName + "]") , "Autorisation Org Obj") does the trick. Each level has it's own function.

LouisBB.