Many to Many Dimension

A many-to-many dimension is a dimension where members of a given hierarchy are linked to members of other hierarchies using a many-to-many relationship (instead of a standard one-to-one relationship). This implies that members of one hierarchy might be present several times in other hierarchies.

Aggregation

Having many-to-many relationships between hierarchies may result in 'strange' visual effects in pivot tables as parent members are possibly not the aggregation of their children anymore. When computing aggregations, same fact indexing members are not added multiple times. This will become clear in the following example.

Example

We can use a classical example: the people/bank account dimension. Different people have different accounts. Furthermore, two or more people can have a joint account. See the download section at the end of this page for the complete example data.

The account structure table is:

    Accounts      , Customers
    Luke          , Luke
    Marc          , Marc
    Paul          , Paul
    Robert        , Robert
    Marc-Paul     , Marc
    Marc-Paul     , Paul
    Marc-Robert   , Robert
    Marc-Robert   , Marc
    

As you can see, we created two accounts (Marc-Paul,Marc-Robert) with two customers. Now, we can add some amounts to these accounts as follows:

    Accounts    , Amount
    Luke        , 100
    Marc        , 100
    Paul        , 100
    Robert      , 100
    Marc-Paul   , 100
    Marc-Robert , 100
    

After creating the tables we can start building the dimension. Let's choose a multilevel dimension selecting the account structure table as the source for the data. Afterwards we'll create two hierarchies (Account, Customer) with a single level. The base hierarchy is the one which will be linked with the facts, in our case accounts. The dimension should look like:

Eventually, we will create a cube with a single measure (Amount) using our fact table.

Once the cube is deployed and loaded we can query the cube to see the 'special' result:

    select
        { [Account].[Account].[AccountN].members + [Account].[Account].[All-L].[All-M] } on 0,
        { [Account].[User].[UserL].members + [Account].[User].[All-L].[All-M] } on 1
    from
        [The Cube]
    

As you can see from the account Marc-Paul columns, the all member is not the sum of the children as with classical hierarchies. The engine is not adding the amount twice for Marc and Paul as both are sharing the same account.

What are the accounts for a given user? With the current dimension structure we can use the following MDX query:

    select
        { [Measures].[Amount] } on 0,
        { [Account].[Account].[AccountN].members * [Account].[User].[UserL].members } on 1
    from
        [The Cube]
    

This will return the expected results. The engine will automatically remove non-existing tuples (auto-exists). However, for large dimensions (>100k members) this query might be time consuming even though it's quicker than an non-empty statement which is based on the facts. How could we speed-up this request?

One solution is to add a new hierarchy which is holding the expected structure. We can do this by creating a new hierarchy (e.g.) [Acc-User] with two levels, Accounts and User. This will be by far the quickest way for retrieving the information we needed. The drawback is larger memory usage (to hold the new hierarchy). Note, you will have to remove key uniqueness for some levels in the hierarchy definition.

Once the new cube is deployed, the structure Account/Users can be retrieved easily from the new hierarchy:

    select
        { [Measures].[Amount] } on 0,
        { [Account].[Account-User].members - [Account].[Account-User].[All-L].[All-M] } on 1
    from
        [The Cube] ​
    

Download

Both the schema and the data of this example are available for download here.

Next chapter: Statistical Dimensions