Traps inherently mushroom in all Enterprise Data Warehouses based on complexity data model, which are logical in nature. Issues crop at reporting layer, wherein the results are incorrect due to wrong aggregation logic on measures. Right data modeling solution will take into account such instances and device alternative approaches to resolve traps.
Several factors can contribute towards origins of such traps and most common instances arise due to
- Sharing dimensions (Conformed Dimensions) across multiple facts
- Usage of aggregate and detail tables based on fact data granularity (e.g. header & line item)
- Underlying business logic/data model that results in such scenarios
Most common traps are Fan & Chasm traps within a data model. A fan trap has a series of 1:M (One to many) relationships that fan out and span two or more levels. A report that uses a measure from different levels in a Fan trap will result in measures aggregated multiple times depending upon the carnality. In a similar fashion a Chasm trap converges multiple 1:M (One to many) relationships and using different measures from the divergent tables will result in improper aggregation of results.
SAP Business Objects meta data tool (Information Design Tool) provides option to define Contexts & Aliases as well as trigger multiple SQL queries on measures to address such issues.
Multipass SQL is often used to resolve such issues where conflicting measures are individually executed in separate SQL queries and finally merged at reporting layer. Another method relies on re-designing the data model to break such scenarios, which typically ends up complicating the data model and advisable only when changes are simple to adopt and maintain.
Good illustration with example(s) of Chasm Trap & Fan Trap from SAP Business Objects perspective