MDX Equivalent to Excel Group

Question

Excel 2007 has a nifty feature whereby it allows you to group members of an OLAP hierarchy into temporary groups (see example). I have a tool that will let me extract the MDX that excel sends to the server however the generated MDX just references the grouping activity by name (mycube_XL_GROUPING7)...i.e. the grouping code is either buried deep in Excel innards or is somehow cached on the server.

My question: how can I implement something similar to this 'soft' hierarchy in MDX? My use case goes something like this: a hierarchy exists of DEPARTMENT-BILL AREA, however some departments are further organized thus DEPARTMENT-DIVISION-BILL AREA (where a certain group of bill areas = a division). The DIVISION is not uniformly used throughout the business, so therefore it is not part of the hierarchy...however some areas use it and would like to group their activity by it...and as mentioned we are able to group them using the EXCEL GROUP feature but I would like to be able to do this in other reports generated using SSRS or other tools.

How would I go about this?

---

mmMmmm...yummy...

Answer 1

You can see whether the "Multiple Groups" pattern in this many-to-many dimensional modeling paper helps you:
...

Multiple Groups

Sometime it is not easy to describe attributes related to a dimension member. In a typical cube dimension, attributes are defined at the data warehouse designing stage and adding an attribute is an operation that necessitate changes in all layers of the BI solution. While a rigid design is good for performance optimization, this is a limitation for end users like marketing analysts, who try to jump over these limits by extracting data from data warehouse and working with them offline, making custom groups of dimension elements based on some characteristics which was not known until a few days before. There are many examples of this situation, but we can generalize it by assuming that a user may want to group some dimension members together, associating them to a group name. Moreover, a single dimension member may belong to more than one group: it can belong to N groups, where N is not defined beforehand. The "Multiple Groups" model I am going to introduce has an interesting characteristic. It is based on a fixed relational and multidimensional schema, while loaded data may define new groups that are immediately available to all clients. Moreover, a new group can be added by only reprocessing a possibly small measure group (corresponding to the factless fact table for a many-to-many relationship), giving the opportunity to create custom solutions that enables a user to create custom groups on the fly, thus getting almost immediate results.
...

 

 

---

- Deepak

Answer 2

Ludis,

Maybe Excel creates session members. But you can as well use query level members in the WITH clause of your MDX query, e. g.
WITH member [dimension1].[hierarchy1].[my Division] AS Aggregate({[dimension1].[hierarchy1].[Bill Area 1], ({[dimension1].[hierarchy1].[Bill Area 2]})
SELECT ...

However, if the divisions tend to be used by many people, why not implement them in the cube as another attribute? This way, the users who want to use it can do so, and the others can leave it unused.

Frank

Answer 3

You are right...it would be great to sharpen my MDX chops to know how to create session cubes or other ways of manually aggregating dimensions, but there is no substitute for just putting them into the cube and be done with it.

Thanks for the common sense pointer...

L

---

mmMmmm...yummy...