Identifying a Chasm Trap

Objectives

After completing this lesson, you will be able to:

  • Identify a Chasm Trap

Chasm Trap Identification

In a chasm trap, there is a many-to-one-to-many relationship among tables in the universe structure. For example, in the figure Many-to-One-to-Many Join Flow there is no loop, but the flow around the three tables is many–to–one–to–many.

When running a query that uses objects Y and Z , the inferred SQL includes tables B, A, and C that have a many-to-one-to-many relationship respectively. The chasm trap causes a query to return every possible combination of rows for one measure or dimension with every possible combination of rows for the other measure or dimension. This results in the values for each object being multiplied by the other. The effect is similar to a Cartesian product, but is known as a chasm trap.

Unlike loops, chasm traps are not detected automatically by the Information Design Tool. However, you can detect them in one of the following ways:

  • Analyze the one-to-many (1-N) join paths in your schema to detect chasm traps graphically.

  • Choose ToolsDetect Contexts or choose the Detect Contexts button to automatically detect and propose candidate contexts in your schema.

    Detect Contexts examines the many-to-one (N–1) joins in the schema and proposes contexts to separate the queries run on the table. This is the most effective way to ensure that your schema does not have a chasm trap.

  • Add additional dimension or detail objects to display more information in the report. If there is a chasm trap, aggregated values are multiplied, alerting you to the problem.

    Any two tables that have multiple rows converging on a single row in the table with the "one" relationship can potentially cause a chasm trap.

Log in to track your progress & complete quizzes