Search code examples
business-intelligencebusiness-objects

Webi Report aggregation produces incorrect variance for all data

I have a query regarding report-level aggregation.

Let's say my report contains 3 columns that give the sum of revenue in actuals, in budget and variance for different sector. I load the values in the granular level like for an area, product, year.

Now the problem is:

  • When I drill down and select at granular level I get the values correctly.
  • But at higher level, that is for all regions, for all products, for all years in all areas my value differs for the variance.

What can be the cause of this? Can anyone please help in this concern?

Thanks Niki

Solution

  • What happens is when you aggregate to a higher level, as is the case in your example, any calculation you do will also be done with the aggregated values of that level.

    Example:

    • Retrieve year and month from the database, as well as the average value of sales revenue.
    • Now create a table in your report with only year and the average value of sales revenue.

    The result will likely be wrong. Why? The average value is calculated using year and month as dimensions. Thus, if you remove one dimension, WebI will aggregated all measures (in this case the average value) according to the projection aggregation function in your universe. This is usually Sum.

    In other words, when you remove the month dimension and only report on year, you get a sum of the individual average values.

    You can solve this by changing your projection aggregation for that measure to database delegated. This means that whenever the dimensions change, the measure value will be invalidated (#TOREFRESH appears) and you need to refresh the query in order to allow WebI to send the calculation back to the database.

    Using calculation contexts you can change how a measure is calculated (in-report that is) by specifying the input and output dimensions for the aggregation. However, in case of an average, this won't solve the issue, as you need to recalculate the measure on database level.

    Calculation contexts are an advanced topic of Web Intelligence, and are explained in detail with examples in the Using functions, formulas and calculations in Web Intelligence manual, in chapter 4: Understanding calculation contexts.