Using MS Excel to access OLAP cube partition

[wkenny]

Hi,

I have a SQL 2000 cube with 5 partitions, one partition includes all locations (dimension) and 4 others are filtered by location. These location filters are needed to filter the cube for ease of use and security reasons.

I am trying to find a way to use MS Excel to access a specific OLAP Cube partition, can this be done?

 

[MDXer]

You don't set security on Partitions you set security on dimension elements. So if your custom security roles disable access to Dimension elements whose data is contained within those partitions then you are blocking access to those partitions.

However I believe that you are using partitions for the wrong purpose. Partitions in Analysis services are a data storage and performance mechanism and not really designed to act as a security mechanism.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[wkenny]

Thanks for the clarification. The problem to resolve is when users are viewing cube data from MS Excel there is a requirement to restrict or filter the data to that specific user is allowed to view. It sounds like dimension security may be one option, are there any others? Again, when MS Excel is the front-end tool for accessing the cube.

 

[MDXer]

Custom Security or dimension security is designed for this purpose. You have the ability to create roles that allow a person to view only dimension elements that pertain to them.

For example A District Manager needs to see all the data relevant to his district so in a location dimension you could apply security to allow him to only see his district and it's children this allows him to see his district and all the elements that make up his district. If these elements are sales people we can apply a security role on a specific salesman and not allow access to any other dimension elements parents, children, or siblings.

I haven't used it but have read about the use of a security cube, not sure how to implement it but it is supposed to be very flexibl.

Your security plan should be well thought out and caution should be taken when implementing custom security. When Custom security i applied to a dimension it creates a copy of the dimension with the elements that permissions allow in memory. This dimension stays in memory until the service is restarted or rebooted. So creaing a lot of security roles with custom security on dimensions can become extremely memory intense.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck