Categories (icCube specific)

Categories allow to dynamically define new members as a subcube or set of MDX tuples in a new hierarchy.

Overview

Working in MDX you might encounter the need to define a hierarchy based on an MDX calculation. For example, we can imagine a new hierarchy that splits a list of products based on sales: Top 5 %, 5-10% .. 50%. The classical MDX solution would be to add some calculated members to perform calculations or include the information in your database to build a new hierarchy. This last solution is a valid one but it cannot always be feasible, practical or flexible enough. A calculated measure is indeed a valid solution but you can quickly face different problems. The first one can be calculation issues (due to solve orders or inconsistencies) or wrong calculations due to many-to-many measures; for example, if you want to calculate the number of unique customers across different members. Another one might be performance-related if your set spans over thousands or hundreds of thousands of MDX members. The last one is creating structures that are reporting tools friendly.

How are they defined

The first step is defining a category hierarchy where all members will be attached. Afterwards you can add category members in your logical hierarchy. It's important to note there is no need to respect dimensionality; category members of the same hierarchy do not need to be defined based on the same hierarchies. The context of categories can be Schema, Session or request depending on your needs.

As an example, let's create a new hierarchy [Top] with as members [Top 2 Products] and [Others Products] based on Sales amount.

 CATEGORY HIERARCHY [Stats].[Top]

 CATEGORY MEMBER [Stats].[Top].[All-M].[Top 2 Products]
                   as TopCount( [Product].[Product-L].MEMBERS, 2, [Measures].[Amount]),ADD_CHILDREN=TRUE
 CATEGORY MEMBER [Stats].[Top].[All-M].[Others Products]
                   as [Product].[Product-L].MEMBERS -  TopCount( [Product].[Product-L].MEMBERS, 2, [Measures].[Amount]),ADD_CHILDREN=TRUE

    

The complete syntax definition can be checked here

How category members work?

It's important to understand that category members work on the actual facts (loaded measures) and that they cannot be associated with calculated members. Category members are a way to classify or name actual subcubes, they are similar to members of no base hierarchies. As they are part of a new hierarchy, you will be able to combine them in a tuple or an Axis in an MDX query.

The following syntax is valid.

SELECT
 [Product].[Product-L].MEMBERS on 0,
 [Stats].[Top].MEMBERS on 1
FROM [M2M]

Many-to-many relationships are correctly calculated :

SELECT
 [Employees].[Employees].members on 0,
 [Stats].[Top].MEMBERS on 1
FROM [M2M]

An additional side effect is that you can combine categories defined from different hierarchies. In our simple case, we could define an employee as child of [Top 5 Products] member.

The self-contained working schema is available for download here.