Thursday, March 29, 2012

Default member problem

Hi,

I have Employee dimension with DomainName attribute which is used for role - based access to cube data.

I've tried to set DefaultMember using MDX expression and username function, like this:

IIF(StrToMember('[EmployeeDim].[DomainName].&[' + username + ']') IS NULL,[EmployeeDim].[DomainName][All], [EmployeeDim].[DomainName].&[' + username + ']')

but when I try to deploy this dimension I've got error message "The level '&[PEXIMBG\borko.novakovic]' object was not found in the cube when the string, [EmployeeDim].[DomainName].&[PEXIMBG\borko.novakovic], was parsed" when there is no any EmployeeDim member which have my username defined!

I thought that usage of IIF will resolve this error, but did not.

Is there any advice or hint how to avoid this behavior, because it is not common case that cube developer is member of EmployeeDim dimension which is populated from customer table.

Thanks in advance

Borko

How about if you test StrToMember() for an error in IIF(), rather than for Null, like:

IIF(IsError(StrToMember("[EmployeeDim].[DomainName].&[" + username + "]")),

[EmployeeDim].[DomainName][All],

StrToMember("[EmployeeDim].[DomainName].&[" + username + "]"))

|||

It works great!

Why IsError is not described in SQL Server Books Online?

I'm new in MDX and I did not know how to prevent engine to raise an error.

Anyway, Deepak, thank you very, very much!

No comments:

Post a Comment