The Data Warehouse Blog

Hi all,

after going on hiatus for quite a long time I’m back with my favorite issue: The correct aggregation along diamond-shaped hierarchies.

So far I was thinking that MS Analysis would calculate the right values by using the function “distinct sum”.

As far as I understand this function it sums up all distinct values along a hierarchy. IMO this is not the right approach to sum up all values of all distinct members along a hierarchy.

Let’s modify our example data a bit:

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: 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

IMO, “distinct sum” would deliver the following values, which are NOT correct:

• R1: 7.000
• R2: 19.000
• M1: 19.000

I would highly appreciate to receive further comments by MSAS gurus . Maybe I’m all wrong and the “distinct sum” function would do the job perfectly.

Thanks to the valuable advice from two real OLAP gurus we now know, that MS Analysis Services 2005 is able to deliver correct aggregations for diamond-shaped hierarchies (please refer to my post from 2006/11/25 and the according comments). I consider this as a real quantum leap.

Now, let’s put it up another notch by adding another real-world requirement: Non-leaf members with data.

When there are non-leaf members with data, the aggregation can be done in one of the following three ways:

1. Distinct sum over the descendants of the non-leaf member
2. Distinct sum over the descendants of the non-leaf member and the non-leaf member itself
3. Data value of the non-leaf member itself

The newest crucial question: Which BI-Tool is able to aggregate distinct sums over many-to-many dimensions with non-leaf members with data?

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.

Welcome to my Data Warehouse Blog! To illustrate the motivations to come up with this blog I’d like to tell you a little story first.

Back around 1995 I was working for a software company, which had been the market leader for Sales Force Automation (SFA) and Electronic Territory Managment Systems (ETMS) software for the pharmaceutical industry in Germany. As the leader of the server development team I was in charge of the programs and the underlying Oracle database, which guaranteed a multi-directional data flow between the different sales reps and the head office.Reps were reporting sales calls and additional data about their activities as well as certain characteristics of the doctors, pharmacies, and hospitals they visited. Additionally, pharmaceutical companies were buying turnover and sales volumes data from external providers based on geographical segments, time, and different levels of a self-defined “product hierarchy”.

All this valuable data was residing in the database on the server, which obviously triggered the desire for analytical applications.

Long before I had been responsible for the server, an analytical application had been developed by a another team. This application did no longer satisfy the growing demands of the very heterogeneous user base and thus, we decided to abandon it and to develop a new suite of analytical applications, based on a real data warehouse, from scratch.

Then, someone had the very smart idea to check the market for out-of-the-box solutions! Plus, some of our customers were already using BI systems like Cognos. After contacting Cognos we agreed to hold a three day in-house workshop as a proof of concept and soon, three Cognos staffers showed up at our office in Heidelberg: The unavoidable sales guy, a senior pre-sales consultant, and a quite attractive and very nice young woman, who seemed to be new to the company and apparently doing some training on the job. I can’t recall her name today, so let’s call her Lucy.
Continue Reading »