2006/10/19

#N/A displayed in cells in Analysis Services 2005

Some days ago I realised that, after having created a Cube reader role and after having granted read access on the cubes, the some data was not still available and the cells showed a #N/A message under particular conditions. The problem arose when the user dragged a measure (any) to the center of the table and then a dimension (any) to the filter area, and then used the filter to select everything but a single dimension value.

If the filter was not showed or not used (selected everything) the measure was display correctly, but if a single dimension value was excluded from the calculation, the #N/A message appeared.

I immediately thought of this problem as a lack of rights on the cube, so I started to browse through the properties for the Cube reader role. In particular, I changed the cube to allow Drillthrough access and then Drillthrough and local cube access, but none of them solved the problem.

After doing some research I found Analysis Services / Cell Level Security - Null Measures and Read Access where Dan Meyers pointed me in the right direction: On the Cell Data tab, we had set (enabled) the checkbox for Enable read permissions for every cube, and Allow reading of cube content field was set to:

[Measures]
According Dan's information, we just disabled Enable read permissions checkbox for every cube and everything worked as expected.

2 comments:

Matt said...

Thank you so much for posting this. We have been stumped for, literally, weeks on this issue! We were just days away from tearing down the box and rebuilding it from scratch in an attempt to resolve. Rock & Roll!

Peter Moore said...

Thanks. Handy tip. I also found a workaround where you add 1 to the Cell data "allow reading of cube content" field and that appears to work also.