Saturday, February 11, 2012

#N/A in measures - Excel 2003 client

After giving some users read access to the cube, by using a readonly role -
The [Measures] are displayed with #N/A in Excel 2003. Only users with loal administrator previledge can see measures.

Please advise.

What I encountered with AS 2005 was that Cell Security defaulted to denying access to a reader role, resulting in #N/A. So I simply entered "1" as the MDX expression for "allow reading of cube content" - not sure if this is the issue for you as well:

http://msdn2.microsoft.com/en-us/library/ms174847(SQL.90).aspx

>>

Granting Custom Access to Cell Data

After you grant a database role in Microsoft SQL Server 2005 Analysis Services (SSAS) read or read/write permissions to a cube, you have to grant to this database role the permission to view some or all cell data. Only granting cube access to a database role does not give that database role access to specific cube cells.

...

>>

|||Can you please give more details about when AS2005 by default disables cell security ? Creating cell security, even as trivial as constant 1 may have very bad effect on performance, since there are many important optimizations which are disabled in the presence of cell security...|||

Hi Mosha,

From what I can tell (based on a simple prototype AS 2005 RTM cube), cell read is disabled by default, unless "Full control (Administrator)" is selected on this page:

http://msdn2.microsoft.com/en-us/library/ms188508(SQL.90).aspx

>>

General (Role Designer) (SSAS)

>>

And that is regardless of "Enable read permissions" being checked on this page (unless "1" is entered for the "Allow reading of cube content" MDX expression):

http://msdn2.microsoft.com/en-us/library/ms177279(SQL.90).aspx

>>

Cell Data (Role Designer) (SSAS)

>>

Is there perhaps a better way to configure cell read access for a non-admin role?

|||

Dear ASMK,

Please check your cell security MDX, the measure you are viewing might be in the

denied member list. This might be helpfull.

No comments:

Post a Comment