Hi, I need some help please.

In SSAS I have created a Dynamic role which restricts the Id on the measures dimension under the Dimension Data.  The MDX code behind it works fine. 

When I view the data in the Browser without selecting a role I can see all the members under the Dimension, which is correct.  However when I impersonate a user (change roles) I see no members under the Dimension even though they should have access to some members (see image attached).  When I drag the measures into the query window it give me the overall value for that users, but when I add the Id it returns No rows found, Click to execute the query message. 

When I reprocessed it fully and the members for the impersonated user is visible for a bit but then they disappear again. 

This is very strange can anyone help me understand why this is happening?

No Roles Selected                                  Dynamic User Role

             

Thanks in advance.

Hi,

your user has records/rights in the factless facttable for the OppurtunityID. Do u impersonate the user as a role or as auser?

Hi,

The user has records in the fact table - when you dont include the OppurtunityID as a column the total of the measures is correct however when you include OppurtunityID it returns No rows found, Click to execute the query message.

I impersonate the user using the userid i.e. Test/UserA, entering it under Other users.  I have created a DynamicUser role which holds the MDX to filter the data but dont use to impersonate.  

"The user has records in the fact table - when you dont include the OppurtunityID as a column the total of the measures is correct however when you include OppurtunityID it returns No rows found, Click to execute the query message." sound ok for me if th user doesn't have records in the security table.

Can u post the MDX of the Role?

Also if u select Visual Totals u would see any records if u dont' include the OppurtunityId.

Hi,

The user does have access to records in the security table (in this case the Contract User table). 

I.e. User A has 100 records, when you drag the measure count into query window it give you 100 as a total, but then when you enter the OppurtunityID it gives you No rows found, Click to execute the query message. 

The MDX query for the role:

NONEMPTY

(     

 ( [Opportunity].[Opportunity ID].[Opportunity ID].MEMBERS ),

 ( StrToMember( "[Contact User].[Full Name].&[" + USERNAME() + "]" )

                ,[Measures].[Opportunity Team Owners Count]  )

)

The MDX query is enter under Allowed member set at the Measure level of the cube under the Dimension Data tab.  I have also tried entering it at the Dimension level but still get the same issue.  And have tried hard coding the USERNAME (for testing only) but still the members disappear. 

I have tried it with and without the Visual Totals it makes no difference. 

• Edited by FM_BIDev Wednesday, September 09, 2015 8:47 AM

Hi,

u also have an Security on your USer Dimension? which how many record do he see if he drags the userdimension in ?. USername returns "Domain\User" maybe there is a mismatch.

If u weant to secure Dimensionelements u have to do it on the attribute of the Dimension!!!!

I use :

EXISTS([Dim].[Attr].[Attr].Members, STRTOMEMBER("[Users].[COD DOMAIN].["+UserName+"]"), "FactSecurity")

Hi,

The Contact User Dimension is the only place the Security is on, basically that is User Dimension. 

I have used the MDX you recommended and still get the same issues. 

EXISTS

(     

    [Opportunity].[Opportunity ID].[Opportunity ID].MEMBERS,

    STRTOMEMBER("[Contact User].[Full Name].["+ USERNAME +"]"), "Opp Measure Group"

)

I have placed the MDX in both the Dimension and Measure level in the Dimension Data tab and it still doesnt show any members after the first initial time following deployment/process.  As it is showing after deployment/process for a bit then it makes me to believe it something other than the MDX query that I am doing wrong.  

if execute this one:

select [Opportunity].[Opportunity ID].[Opportunity ID].MEMBERS*"[Contact User].[Full Name].members on rows,

 [Measures].[Opportunity Team Owners Count] on columns

from cube

looks the resultset good?

Hi,

The mdx you provided timed out so I amended slight using NON EMPTY and it worked fine returning all the data from the cube:

SELECT

NON EMPTY

       { ( [Opportunity].[Opportunity ID].[Opportunity ID].MEMBERS *

           [Contact User].[Full Name].[Full Name].MEMBERS

       ) } ON ROWS ,

NON EMPTY

{ [Measures].[Opportunity Team Owners Count] } ON COLUMNS

FROM [Cube]

Full name is the windows login name i.e. Test/UserA. 

Do you think its the mdx that causing the issue?

Hi FM_BIDev,

Thank you for your question.  

I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.  

Thank you for your understanding and support. 

Thanks,