Reusing part of an existing measure group

Hello,

I have a large cube called Sales and inside that a measure group called SalesDetail. I'd like to reuse only a small slice of this cube in another cube, just 1 product subcategory in fact to meet that specific departments needs. They only want a subset of dimensions and only their data. Would perspectives allow filtering of data, i don't think so...

Rather than creating another view over the fact, and building another cube is there a way of reusing a slice of a cube?

So in the AventureWorks example i'd just like to specifically reuse "[Product].[Product Categories].[Subcategory].&[1]" subcategory..

Regards,

Ben

How about setting up a security role, with just that 1 allowed member on the Product Subcategory atttribute - visual totals can be enabled, so that users in this role only see totals contributed by that member?

SQL Server 2005 Books Online

Granting Custom Access to Dimension Data

...

Understanding the AllowedSet Property

The AllowedSet property uses a Multidimensional Expressions (MDX) expression to determine which attribute members can be viewed by the database role (the allowed set). The allowed set can include no (default), all, or some attribute members.

...

Understanding the VisualTotals Property

The VisualTotals property indicates whether the aggregated cell values that are displayed are calculated according to all cell values or only according to the cell values that are visible to the database role.

|||

I thought about using security, but will dimension security mean that these users cannot see all categories in another cube?

The intention is that this is a targeted cube for these users, and they won't be distracted by the whole set of data.

|||

"will dimension security mean that these users cannot see all categories in another cube?" - not necessarily, since you can define dimension security just on cube dimensions. This might not be obvious from BOL; but the "Dimension" drop-down tree in the "Dimension Data" tab of the "Role" designer in BIDS shows something like:

-- Database

- Dimensions in Database

-- Cube(s)

- Dimensions in Cube

Answer Re: Security on role-playing dimensions

When you define dimension security, you can set it either on database dimension or on cube dimension. You need to choose one of the cube's role-playing dimensions and set dimension security only on it.

---

Mosha - http://www.mosha.com/msolap

|||

thanks.. this is exactly what i am after... will try it out today.