Sql Server analysis Services 2008 Dynamic dimension security using MDX expression

Question

Some dimension tables in my warehouse:

DML
Site
    Id      bigint
    Name    nvarchar(256)
    Primary key (Id)

DATA
SiteId    Name
2         Site 2
3         Site A

DML
UserSite
    Username    nvarchar(256)
    SiteId      bigint
    Primary key (Username, SiteId)
    Foreign key (SiteId) referrences Site(Id)

DATA
Username    SiteId
EMSUser1    2
EMSUser1    3
EMSUser2    3

My SSAS Project: I have used these two dimension tables in one dimension in a BIDS project, created using the dimension wizard. The usernames relate to two local windows user accounts on the SSAS Server, they are members of a local windows group ReportBrowsers. I have created a Role for that group in BIDS and added an MDX Expression to the dimension Data tab for this dimension in the AllowedSet box for the Username attribute:

STRTOMEMBER("[UserSiteSite].[Username].["+Trim(Mid( UserName, InStr(1, UserName, "\")+ 1,128))+"]")

This dimension is used in a cube that's all processed and deployed. I then have an SSRS Report based on that cube.

My intention: What I'm trying to do is take the logged in user currently browsing SSRS and remove the "<domain>\" part of the string returned effectively filtering the results to just the sites that username has a record for.

So for EMSUser1 I should see records for both sites, but it appears to be allowing the first one only ("Site 2" with Id 2) There's no sign of "Site A" even though records exist for that site in the fact table.

I've been trawling through Analysis Services 2008 unleashed and the step by step book and all over blogs etc. I just can't quite grok the internal workings to achieve my goal.

What am I doing wrong?

Answer 1

Well it's been some time now. I don't have the complete answer even now but I did get some help else where that has lead me to a solution. The details can be found here.

There are a lot of ways to solve this kind of scenario. I feel there may still be a better one for me, but I have something that at least works. Basically create a seperate class library with a static class and function that makes it's own connection to the database and selects out the allowed set. If you reference the Microsoft.AnalysisServices and Microsoft.AnalysisServices.AdomdServer namespaces you can return a 'Set' object. You can then call this function in the Role objects Dimension data advance tab:

MyAssembly.MyMethod(UserName)