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.
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.
Andrew Wiles | 28-Nov-06 at 7:55 pm | Permalink
SAS Institutes CFO Vision was able to do this. I was the development manager for this product and we had to deliver exaclty this scenario for a large US customer.
I am not sure if the current product SAS/FM has continued this functionality but I can give you a contact in the Eurpoean HQ if you would like to find out.
It is not too difficult to implement this kind of logic in Analysis Services but it does require a bit of creative thinking.
Regards
Mosha Pasumansky | 30-Nov-06 at 3:26 pm | Permalink
Microsoft Analysis Services 2005 can do this kind of aggregation out of the box. It is called many-to-many dimensions. It allows you to compute distinct sum (similar to distinct count) - which is exactly what you need here.
Frank Backes | 30-Nov-06 at 9:21 pm | Permalink
@Andrew and Mosha:
Many thanks for your valuable comments. I appreciate the advice from OLAP gurus like you.
One more question:
Can Analysis Services manage “distinct sum” with non-leaf members with data? In one hierarchy there can be non-leaf members, which must be aggregated in three different ways:
1) distinct sum over their descendants
2) distinct sum over their descendants and the member itself
3) no aggregation, given value for non-leaf member
Cold Warehousing | 24-Aug-07 at 5:35 am | Permalink
Microsoft Analysis Services 2005 seems to be the best way to go.
Frank Backes | 09-Apr-09 at 9:49 am | Permalink
After some time I’m not sure that MS Analysis Services would do it right.
As far as I understand the “distinct sum”´function, it sums up the DISTINCT VALUES along the hierarchy and not the VALUES OF THE DISTINCT MEMBERS along the hierarchy.
Consider those values please:
* C1: 7.000
* C2: 7.000
* C3: 8.000
* C4: 4.000
Here are the expected results of the aggregation:
* R1: 14.000
* R2: 19.000
* M1: 26.000
Can MS Analysis Services really calculate the right results?