Dynamic bucketing in ActivePivot

Question

In our ActivePivot project, we have designed our cube with predefined hierarchies and levels. Among them, we have our currencies which all belongs to the same level.

Underlyings
|_ALL
|_UnderlyerCurrency

Our users would like to group those currencies, each group being possibly different based on user choice.

I know it is already possible to bucket/group members in ActivePivot using Analysis Dimension, but this is too static for our use case.

Is it possible to group those currencies per user ? is it possible to configure it without restarting the server ?

PS: we use Excel and ActivePivot Live as UI

score 2 · Accepted Answer · answered Sep 03 '14 at 12:24

Using MDX, it is possible to define bucket or group.

Here is a sample MDX that creates dynamically two buckets Europe and Asia in your existing hierarchy (I assume dimension and hierarchy have the same name) :

WITH 
Member [Underlyings].[Underlyings].[ALL].[Europe] AS Aggregate(
  {
    [Underlyings].[Underlyings].[ALL].[AllMember].[EUR],
    [Underlyings].[Underlyings].[ALL].[AllMember].[GBP],
    [Underlyings].[Underlyings].[ALL].[AllMember].[CHF]
  }
) 
Member [Underlyings].[Underlyings].[ALL].[Europe].[EUR] AS Aggregate(
  {
    [Underlyings].[Underlyings].[ALL].[AllMember].[EUR]
  }
) 
Member [Underlyings].[Underlyings].[ALL].[Europe].[GBP] AS Aggregate(
  {
    [Underlyings].[Underlyings].[ALL].[AllMember].[GBP]
  }
) 
Member [Underlyings].[Underlyings].[ALL].[Europe].[CHF] AS Aggregate(
  {
    [Underlyings].[Underlyings].[ALL].[AllMember].[CHF]
  }
) 
Member [Underlyings].[Underlyings].[ALL].[Asia] AS Aggregate(
  {
    [Underlyings].[Underlyings].[ALL].[AllMember].[JPY]
  }
) 
Member [Underlyings].[Underlyings].[ALL].[Asia].[JPY] AS Aggregate(
  {
    [Underlyings].[Underlyings].[ALL].[AllMember].[JPY]
  }
) 
SELECT NON EMPTY {
  [Underlyings].[Underlyings].[ALL].[Europe],
  [Underlyings].[Underlyings].[ALL].[Europe].[EUR],
  [Underlyings].[Underlyings].[ALL].[Europe].[GBP],
  [Underlyings].[Underlyings].[ALL].[Europe].[CHF],
  [Underlyings].[Underlyings].[ALL].[Asia],
  [Underlyings].[Underlyings].[ALL].[Asia].[JPY]
} ON ROWS 
FROM [YourCube]

With ActivePivot Live, user can write their own MDX (you can't do it with Excel though).

Then the interface will be smart enough to give the user all regular controls (wizard manipulations, drill down, sort, filtering, ...) on this table. On top of that, they will be able to navigate in it as if it was a regular hierarchy !

enter image description here

If you don't want users to write their own MDX, you have two solutions :

have someone with enough MDX knowledge that will write those queries, save it in a bookmark that will be shared with non technical users
extend the interface to create your own UI that generate those buckets: non technical users will be autonomous

Dynamic bucketing in ActivePivot

1 Answers1