MultiLevel Hierarchy Advanced
In the last chapter we defined a basic multi-level hierarchy. In this chapter we are going to introduce a more complex example, adding multiple hierarchies.
Let´s start be explaining that facts are linked to a dimension. Each fact row is linked to a dimension through the ´default´ or base hierarchy. All other hierarchies define a relationship to the base hierarchy members and not directly to the facts. One member of a no ´default´ hierarchy is linked with a set of members of the ´default´ hierarchy.
Another way to express this is be saying that the 'default' hierarchy defines the dimension coordinates in the cube (aka fact). Other hierarchies represent different ways of grouping these coordinates, similar to the a parent-child relation. It should be clearer with an example. We are going to use a Customer with a list of attributes on which we will define different hierarchies.
First, we need to define our data. For the sake of simplicity, we're going to use customer dimension with a few rows:
Customer, CuID, Continent , Country , CoID, Language Pierre ,1 , Europe , France , FR , French Peter ,2 , America , United States , United States , US, English Calen ,3 , America , Canada , CA , French Jose ,4 , America , Mexico , MX , Spanish
CuID, Tokens 1 , 176 2 , 191 3 , 183 4 , 201
Each column of the customer table defines an attribute. We are free to combine these attribute to build new hierarchies. The constraint will be that our 'default' hierarchy's last level will have to be the customer, as we have to link the dimension with the fact table.
For our example we're going to add three hierarchies ( [Customer], [Country], [Language] ). [Customer] and [Language] being flat (one level) and [Country] with both [Continent] and [Country] levels.
After successfully deploying the schema, we would like to query the customer by country. A possible query could be:
select [Measures].[Num. Tokens] on 0, [Customer].[Customer].members * [Customer].[Country].[Country].members on 1 from [Cube]
You can see from the result that no existing tuples (e.g. [Jose],[France]) are automatically removed. This is not due to the tuple being empty (no facts), it's due to the tuple not existing as per definition of our dimension. This behavior is also called autoexists. This is different to dimensions where we need to perform a NON EMPTY to remove empty tuples. It's important to note that autoexists is faster than the nonempty version.
We could go one step further in the performance and pre-build a structure that mimics our request. In our example it would entail creating a [Country-Cust] hierarchy with [Country] and [Customer] as levels. Once created, and the schema deployed, we can rewrite our MDX query as:
select [Measures].[Num. Tokens] on 0, [Customer].[Country-Cust].members on 1 from [Cube]
The result is similar to the first one with a crossjoin. Furthermore, to get the customers for a country we can perform a children MDX function on a country.
Both the schema and the data of this example are available for download here.
Next chapter: Parent/Child OLAP hierarchies