We have a fairly simple SSAS cube published and accessed via Excel. We would like users to be able to group dimension members which can be displayed as columns or rows. For example a user may have Regions shown as columns which are a series of two character codes. The user may want to group these by country but the grouping requirement may relate to other dimensions.
As a Domain Admin user I can create a report and group without any problems. However if a user in a cube role tries Excel just appears to hang. If the AD group associated with the cube role is added to SQL Analysis Services as an Admin user then they too can group without any problems but this cant be the answer as this seems to override dimension security?
Can anyone provide some guidance on what privileges or security is required to allow users to group in their report without having to compromise dimension security?