Aggregation: DECODE & CASE calculations returns different values between Discoverer Plus and Discoverer Desktop 10.1.2

(This post does not want to discuss how aggregation works, it assume that you already knows aggregation behavior, but it gives you solution to most known scenarios)

From Discoverer 9.0.2 and above i.e Discoverer 9.0.4 and Discoverer 10.1.2 has enhanced roll-up and calculation capabilities a feature called Enhanced Data Aggregation where when connected to a 9.0.1.4 or higher database, the Discoverer server will rely on the CUBE, ROLLUP and GROUPING SET database functionality to compute roll-up values. This new capability correctly handles many of the restrictions described in the provided note, so that a roll-up value can be computed where the 4i server would have to resort to using the "N.A" string.

Additionally, enhanced Data Aggregation is present only in the Discoverer Middle-tier(Plus and Viewer) server -- Not Discoverer Desktop. You will see aggregations in Plus and Viewer that show up as "N/A" in Desktop.

By the way, we can define:

Linear Calculation
A calculation for which the aggregate value is same as the value obtained by summing up the individual values is a linear calculation.

Locally computable Calculation
Discoverer can locally compute all primitive arithmetic operations (+, -, *, / etc), a subset of aggregates (Sum, Sum_Squares, Count, Max, Min, Average and Variance), some string operators, and Decode. Discoverer can locally compute most calculations that can be broken down into linear parts and that involve the above set of operators.
Calculations such as Sum (Profit) / Count (Profit) can be locally computed as it can be broken down into two Linear Parts Sum (Profit) and Count (Profit) combined by the division operator. Calculations such as Sum (Profit) / Count_Distinct (Profit) cannot be locally computed as one of the parts (Count_distinct (Profit)) is not Linear.

Non-Aggregable Calculation
A pre Oracle 9i database allows data to be fetched at only one level of aggregation. From this data discoverer has to compute values at higher levels of aggregation to support things like totals, outline values etc. This is possible only if the calculation is either Linear or Locally computable.
If a calculation is neither linear nor locally computable it will be marked as non-aggregable. The value specified in the option “Show non aggregable values as “ is displayed for such cells.

Case / Decode
Totals on aggregate calculations involving a case expression might yield non-intuitive results. This is because the item values involved in the condition part of the case expression will become NULL when they are rolled up on that item.



In most of cases the solution is to add in the PC Client where Discoverer Desktop is installed regedit entries, like:

o Add two DWORD values named: AggregationBehavior & AllowAggregationOverRepeatedValues = 1 under the key: HKEY_CURRENT_USER\Software\ORACLE\Discoverer 10\Application\ and set both to 1;

Then, Discoverer Desktop should be closed and re-launched.

Aggregation : What Happens with Fan-Trap ?

I have received recently a claim from a customer regarding the aggregation behaviour of his workbook. In particular he was afraid to see an item on the Group By section clause of the Discoverer Query. At first look I immediately recognized that the architecture selected for his workbook was resulting in a fan-trap schema and so could generate ambiguous data, but due to his insistence I reviewed inside his workbook the reason of this. So, let's first define what is a fan-trap and its rules.

What are fan traps, and how does Discoverer handle them?

A fan trap is a group of joined database tables that might return unexpected results. The most common manifestation of a fan trap occurs when a master table is joined to two or more detail tables independently. Figure shows a simple fan trap schema





Although this construction is relationally correct, you are likely to return incorrect results if you use a straightforward SQL statement to aggregate data points. However, if you use Discoverer to aggregate the data points, Discoverer will never return incorrect results, provided fan trap detection is enabled. Every query that Discoverer generates is interrogated for potential fan traps. If a fan trap is detected, Discoverer can usually rewrite the query using inline views to ensure the aggregation is done at the correct level. Discoverer creates an inline view for each master-detail aggregation, and then combines the results of the outer query.

In some circumstances, Discoverer will detect a query that involves an unresolvable fan trap schema, as follows:

• if the detail folders use different keys from the master for the join
• if there is a direct join relationship between the detail folders (thereby creating an ambiguous circular relationship)
• if non-aggregated values are chosen from more than one of the detail folders if more than one detail folder has a separate join relationship to a different master folder
• if more than one detail folder has a separate join relationship to a different master folder

How the aggregation works in fan-traps ?

Discoverer interrogates the query, detects a fan trap, and rewrites the query to ensure the aggregation is done at the correct level. Discoverer rewrites the query using inline views, one for each master-detail aggregation, and then combines the results of the outer query.

Now, back to the customer issue, I verified the following schema in the Workbook (in green the folders which items are used in the Workbook) :


Each one of this folder, joins the other folders (or Master Folders) on a different keys, generating a fan-trap schema. So, if he creates a crosstab workbook with the following layout :







The aggregation will be made on

• Department.Item (Because it is Page Item and rollup at page item level)


• Customers.Item (Because it is an axis item and rollup at row level)


• Product Details.Item (Because it is an axis item and rollup at row level)

but will add to the Group-By expression also the Sales SUM.Item, because by looking at customer schema, this item is from Sales SUM folder, which is detail of Stores folder which is the master of Products folder, which is iself master of Product Details, for which items are in the workbook. So, basically Sales SUM Item is interpreted in this case as Master of involved items, and so added to the Group By clause, as the aggregation is made for each master-detail relationship.

The only solution here is to create a custom folder just for this workbook, otherwise one should re-architecture the joins so that workbook will not be built on a fan-trap schema.