Persisting user-defined groupings of members in SSAS cube

Question

I would like to allow users to specify their own dynamic grouping of members. In the AdventureWorks tutorial, that would mean a user could define "Product Group 1" as Accessories and Clothing, and "Product Group 2" as Accessories and Bikes." I could have an

I can get results like that by defining members as part of the MDX query:

with 
  member [Product].[Category].[Product Group 1] as 
    aggregate({[Product].[Category].[Accessories], [Product].[Category].[Clothing]})
  member [Product].[Category].[Product Group 2] as 
    aggregate({[Product].[Category].[Accessories], [Product].[Category].[Bikes]})    
select  [Measures].[Internet Sales-Sales Amount] on 0,
   {[Product].[Category].[Product Group 1], [Product].[Category].[Product Group 2] } on 1
from [Analysis Services Tutorial]

My question is: is there any way I could save these product groups as part of the cube, so you could just reference {[Product].[CustomGroups]} rather than having to include with member group1 as ... member group2 as .. on the query?

Sure, I could incorporate them into the dimensional model itself, which is almost what I want, but I'd like changes to take effect without refreshing the cube.

how can they become part of the cube but you'd "like changes to take effect without refreshing the cube." ? I would think that you can just create two custom members [Product Group 1] and [Product Group 2] and a custom set [mySetofProdGroups] in the cube-script? — whytheq
@whytheq do you mean something like create member [product group 1] as ... ; create set [mySetOfProdGroups] as {[product group 1], ...} ? — wrschneider
exactly - but I assumed you'd already tried that: so maybe just misinterpreting the Queation. — whytheq

whytheq whytheq · Accepted Answer · 2015-09-21T08:36:50

As mentioned in the comments. Inside the cube-script you should be able to:

1. CREATE the custom members that you mentioned:

create member [product group 1] as ...
create member [product group 2] as ...

2. CREATE the custom set that you mentioned:

create set [productSet] as ...{[product group 1],[product group 2]}

Exact syntax for the above will be on MSDN

Persisting user-defined groupings of members in SSAS cube

1 Answers