SSAS MDX Namedset issue with hierarchy

0
votes

I'm using some MDX to create a named set in SSAS.

I have a hierarchy of Company, Group, Store

I'm trying to filter out a number of specific Store members.

I've used the Descendants function, as below, to do that, however it then doesn't have any proper hierarchy (expand and collapse etc) when viewing it in Performance Point. Any ideas? Is there a way of doing it without breaking the hierarchy?

Namedset:

Descendants([Company].[Company Hierarchy], [Company].[Company Hierarchy].
[Stores]) 
- [Company].[Company Hierarchy].[Stores].[Store1]  
- [Company].[Company Hierarchy].[Stores].[Store2] 
- [Company].[Company Hierarchy].[Stores].[Store3]
2
ssasmdxanalysisperformancepoint
As far as I know, a named set won't keep the hierarchical nature of your data. It will just return the requested members. – mmarie
@mmarie named sets usually keep the hierarchical structure intact - is the structure lost when used in performance point? – whytheq
Yeah, the structure is lost in performance point. In the cube browser it's fine. I've noticed that the filter command doesn't break the hierarchy, though it doesn't work with the < > operator, it only works with = – mbnesbit

2 Answers

0
votes

What you have looks ok. As an alternative maybe see if the following works:

EXCEPT(
  [Company].[Company Hierarchy].[Stores].MEMBERS
  ,{
     [Company].[Company Hierarchy].[Stores].[Store1]  
    ,[Company].[Company Hierarchy].[Stores].[Store2] 
    ,[Company].[Company Hierarchy].[Stores].[Store3] 
   }
)
0
votes

Try using HIERARCHIZE around the set.

HIERARCHIZE
    (
    Descendants
            (
                [Company].[Company Hierarchy], 
                [Company].[Company Hierarchy].[Stores]  
            ) 
        - [Company].[Company Hierarchy].[Stores].[Store1]  
        - [Company].[Company Hierarchy].[Stores].[Store2] 
        - [Company].[Company Hierarchy].[Stores].[Store3] 
    )

msdn reference for hierarchize