Troubleshooting: Computed fields with cross products

A cross product normally occurs when a table of data is joined with itself resulting in redundant data.

In the case of computed fields as they relate to Cognos® we encounter a slightly more complex version.

For example, the pre-8.3 ORM solutions schema has computed fields on the Loss Event object type that aggregate associated Loss Impacts and Loss Recoveries. In effect the schema is joining the Loss Event data with itself because the schema has two associations (joins) from the same object type and this causes a cross product.

If you have the following associations between Loss Event and Loss Impact:
  • LE - LI1
  • LE - LI2
  • LE - LI3
And the following associations between Loss Event and Loss Recovery:
  • LE - LR1
  • LE - LR2
When a query is written to access all three object types the following data is returned:
  • LE, LI1, LR1
  • LE, LI2, LR1
  • LE, LI3, LR1
  • LE, LI1, LR2
  • LE, LI2, LR2
  • LE, LI3, LR2

In the case where we are aggregating values on the Loss Impact, we end up with twice the desired value. And on the Loss Recovery, we get three times the value. One way to work around this is as follows:

Instead of:

total (Loss Impacts for Loss Events)

Use:

average (Loss Impacts for Loss Events) * count (distinct Loss Impacts for 
Loss Events)

Mathematically, we can say that average x distinct_count = total/count x distinct_count = total x distinct_count/count.

So if we are trying to total the Loss Impacts for a Loss Event in the previous example we would be performing a total on the cross product result and then multiplying by 1/2 to factor out the cross product. If we are trying to total the Loss Recoveries for a Loss Event in the previous example we would be performing a total on the cross product result and then multiplying by 1/3 to factor out the cross product.