Dave's Adventures in Business Intelligence

A few months ago I wrote a post about the new Designer feature “Delegated Measures” and suggested it could be used to avoid issues with projection averages. Today on BOB I answered a question about why this is important (why averages should not be projected within the universe) and decided that I should capture a more thorough example here on my blog.

Problem Statement

I have a database with sales transactions. Users want to be able to create reports using averages and percentages. Can I put these objects into the universe and still guarantee the correct results?

Average Formulas

There are several generally accepted ways for calculating averages. One of the following is probably going to work in most cases:

sum(x) / count(x)
sum(x) / sum(y)
sum(x) / count(y)

In each of these cases there is an aggregate applied to the numerator and another (potentially the same) aggregate applied to the denominator. Once the aggregation is done the division takes place. Consider a transactional database used to take orders from customers. I might want to know the average size of an order (total dollars divided by a count of orders) which seems like a simple calculation. But does it work?

How Projection Works

When a universe designer creates a measure object there are two places to set up an aggregation process. The first and probably most obvious is in the SQL select clause. The second is on the properties tab and is called the Projection function. The SQL clause happens on the database, and the projection happens on the report. When a user drags elements on to (or off of) the report the measures will adjust (project) based on this secondary function.

With a total measure this process works quite well. If I create a query containing Region, Sales Year, Sales Month, and Total Sales I can project the sales amount to any combination of those dimension values without seeing an incorrect result. A more technical way to put this is to say that a “sum” operation is fully transparent. You will get the same answer if you do any of the following operations:

1+2+3+4+5
(1+2)+(3+4)+5
((1+2+3)+(4+5))

Any of those expressions will give you the same answer: 15. Unfortunately when you mix operations (sums and divisions) the same is no longer true. (1+2)/(3+4) is not the same as 1+(2/3)+4. This is part of what causes problems for average projections, but it’s not the full story.

Aggregation Masks Source Data

Suppose I gave you a number, a nice round one like sales revenue of $100,000. Where did that number come from? How many possible ways are there for me to generate that value? It could have been one single order (at $100,000) or it could be the sum of 100 different orders at $100 each, or 10 orders for $10,000, or… there are an infinite number of ways that I can generate that number. If all I am concerned about is the total, I don’t really care. But averages require me to know more about the end result; I have to know two values rather than just one. Once the aggregation is done, I’ve lost the source data and cannot go any further.

Let me put some actual numbers in place to prove my point.

In January Widgets-R-Us had 100 orders for $100 each. The total revenue for the month was $100,000. In February all of our production was tied up in one very large order for a single client for $100,000. This means my total revenues for the two month period was $200,000. That number is interesting, but it doesn’t tell the full story. The two months had a very different sales pattern, and I would like to know that. I can show that by calculating the average order per month.

In my universe I can create an object with this formula:

sum(sales) / count(orders)

This object is called Average Order Size and it has been defined with a projection function of Average. I also have an object called Total Sales with a projection function of Sum. With these objects along with Sales Month on a report the resulting numbers should come out as follows:

Mon    Sales      Avg
Jan $100,000     $100
Feb $100,000 $100,000

However, the resulting answer will be correct only for the initial grain of the question. If I (or any report user) were to drag the Sales Month object off of the report, here’s what I would get:

   Sales      Avg
$200,000  $50,050

What happened? The total sales number projected correctly. I did have $200,000 in sales for the time period shown on my report. However, the average order is certainly not $50,050 as shown here. What happens is the average projection can only work with the data available. The source data has not been pulled down from the database, only the aggregate values are on the report. Since there are two values on the report, the average is being calculated as (100000 + 100)/2 and clearly that is wrong. There are far more values (higher count) with an order value of $100 than there are with $100,000 so those values should carry more weight. They don’t, because I don’t have the proper information available. This is how averages get “wrong” on reports. The same thing can happen to percentages or any other calculation that involves a division operation on summed values.

How Do We Fix This?

There are two ways to fix this that are currently available, and a third way that is often talked about as an awesome new feature for universe designers. (hint hint to Business Objects developers, we’ve been talking about this a very long time… 😉 )

The first (and oldest) solution is to provide the components of the average calculation (the sum and the count) as objects and let the report developer do the division. There is nothing wrong with summing a sum, and there is nothing wrong with summing a count. Once I perform these report-side calculations with the original source data I can do the division and get the correct answer. This allows me to ensure the answer is always correct, but the cost is I have to create (and recreate) report variables, often on more than one document. In my example above I would return the total sales (already on the report) and a count of orders (a new object) and the average would be calculated as sum(Total Sales) / sum(Order Count). The math behind the projected value for average order size (for when I remove the month from the report) would then become (100,000 + 100,000) / (100 + 1) or 200,000 / 101 and the final answer is 1980.20, a number that is much more believable.

The second option is to force the query to rerun (go back to the source data and recalculate) using the Database Delegated feature covered in my previous post. This also allows me to generate the correct answer, but the cost here is I have to be on the current version and I have to execute a new database query every time a user manipulates the report. Depending on the response time for the query, this may or may not be a viable option.

The third and currently unavailable option would be to allow a universe designer to create report variable objects in the universe. These objects would not be part of the query, but would be able to be included in the universe and dropped onto a report. This way the universe designer could create the required formulas and they can be reused on multiple reports. Unfortunately, we don’t have this feature, but I am still hopeful. 🙂

Conclusion

In a nutshell, averages and percentages and any other calculation that involves dividing projected values should be done on the report rather than the universe, unless you plan to use the delegated feature that recently became available. The math just doesn’t work with a standard projection. The one case that I can think of right now where I have used an average as a projection function was in a statistics universe where the sample size was always the same.

In my example above we had 100 sample data points for January and only one for February. If both months had exactly the same number of orders, then the average projection would have been correct. In most business scenarios this will not be the case, therefore the average projection function should rarely be used.