Search code examples
sql-server-2008sql-server-2012ssas

How to select multiple nodes in SSAS hierarchical dimension?

I'm working on a SSAS project in order to load information in PowerPivot.

I need to create something easy to use : allow user to select multiple nodes in a hierarchy. For example to get all sales for "Europe" sub entities and "Pacific" sub entities.

Here is a sample of a dimension.

enter image description here

So, is there a way to be able to select multiple nodes : for example "Europe" and "Pacific". In fact the SSAS import wizard only allow to pick one node.

If I display the attribute dimension I can filter leaf by leaf, but it's not relevant, a node can own more than 50 sub elements, and it's tricky to select all of them.

Any idea to do that ?

PS : my dimension is based on a parent/child relationship (self referenced by a parent_id field).

Solution

  • Finally, it seems it's not possible to select some nodes including elements by using SSAS Import Wizard (drag & drop).

    To solve my issue, i added a filter on my parent/child hierarchy and I selected a MDX query to add the following script :

    UNION(DESCENDANTS([Dimension].[Hierarchy].[&<node1>]), DESCENDANTS([Dimension].[Hierarchy].[&<node2>]))

    and are the IDs of my nodes.