The crucial question: Which BI-Tool is able to aggregate this correctly? I say: “NONE!”

I really want to know it! I’m making the assertion, that none of the BI-Tools on the market today is capable of calculating the right aggregations for the following real-world task.

Again, we’re looking at a dimension with diamond shapes:

On the right you can see a part of the sales force of a financial services company.Sales force structure
C1-C4 is a subset of the clients, R1 and R2 are two sales reps and M1 is a regional manager.

For each client, the measure “a” is given, which means the yearly amount of money available to the client for investment. In order to calculate the investment potential for each node in the hierarchy, “a” has to be summed up.

It is typical for financial services companies, that each rep is expected to fully exploit a client’s potential. That means, that it is no option to use weighting factors to share the client’s potential between reps. As you can see on the right, C2 is assigned to two reps, whereas C2’s full investment potential has to be assigned to R1 and to R2 respectively.

For M1 each client’s potential must be taken into account only once.

To have something to work with, I give you the investment potentials for each client:

  • C1: 2.000
  • C2: 7.000
  • C3: 8.000
  • C4: 4.000

Here are the expected results of the aggregation:

  • R1: 9.000
  • R2: 19.000
  • M1: 21.000

Please do not hesitate to leave your comments here. I’m still convinced, that there is no BI-tool available, which would be able to solve to above mentioned problem. These kinds of aggregations and hierarchies are everywhere and it would be a quantum leap to finally have an out-of-the-box solution that could cope with them.