Working on Trap Issue – Chasm Trap

Trap issues are common problem in relational database schemas in which a join path returns more data than expected. There is no specific option to find trap issues in universe designer and it can be identified only by looking the join relation between tables visually.

There are 2 different types of trap issues can occur in universe designer.

  • Chasm Trap
  • Fan Trap

Chasm Trap

When two “many to one” joins converge on a single table is called Chasm Trap and there should be many to one to Many join relation between 3 three tables. Like the below example, General Ledger (GL) is joined with GL balance Fact and Loan Balance Fact tables in Many ends.

Chasm Trap1


Let see how the data looks like in the above tables.

Chasm Trap2Objects are created based on the above tables as below.

Chasm Trap3

Report Execution:

1. Create a report based on GL Number, GL Name and GL balance Amount objects and your report would return correct result. For example,

Chasm Trap4

2. Create a report based on GL Number, GL Name and Loan balance Amount objects and your report would return correct result. For example,

Chasm Trap5

3. When you create a report combining above 2 queries into a single report, then your report would return wrong results in the aggregated measure object due to many to one to many joins.

Chasm Trap6

Above result shows wrong data for ID 1101 & 1103 because both 1101 & 1103 is having multiple records in both the Fact tables and the result set is multiples of each row with other fact tables. Below is the complete data set how the query executed at back end and report shows grouping of GL Number and GL Name.

Chasm Trap7

4. Chasm Trap issue can identify manually by looking at the table joins relation and to solve this issue, you have to check how you are using the objects from these tables into the report. If you are using only Measures from both the fact table, then you can enable the “Multiple SQL Statements for each Measure” in universe designer parameter, SQL tab.

Chasm Trap8

When you enable this option, your report will show two queries for each measure and will return correct result.

Chasm Trap9

5. If you are using Measure objects along with some dimension object from those fact tables, then your report won’t work as expected. Report will create a Cartesian product and will return more data than expected.

Chasm Trap10

6. So, to get the above request, you have to create a context for each fact tables and enable the “Multiple SQL Statements for each Context” in universe designer parameter, SQL tab. This is common solution for Chasm Trap.

Chasm Trap11

Context 1 – GL Balance

Chasm Trap12

Context 2 – Loan Balance

Chasm Trap13

Now you can create the report using Measures and their relative dimensions from fact table and each fact table will create separate SQL and data will get synchronized based on common dimension GL Number.

Chasm Trap14

Your final report would look like as below with correct result.

Chasm Trap15


7 thoughts on “Working on Trap Issue – Chasm Trap

  1. Hi,
    Sorry for being late to this forum and all the contents are very interesting to read..!! I’m learning new things from here..!!

    One question – Instead of using context in the above scenario, looks like we can we solve the same by creating alias on the dimension table and use context then? Like below.
    Context 1: General_Ledger_Dim -> G_balance_Fact
    Context 2: Alias_General_Ledger_Dim -> loan_balance_Fact

    • Hi Selva,

      We can create alias and create context to resolve the Chasm Trap, but what is the use / benefit you will get by adding unnecessary alias in universe structure?

      It will increase the size of your universe structure and context is the best way to resolve Chasm trap.


      • Hi Madhes, I just went over your Chasm trap article and it is the first time that I noticed someone had explained with in very detail. In my chase everything works and I am able to use the measure from both tables. But I can use Dimension from one table a time. If I use measure from both and bring in dimension from table 1 then table 2 dimension show as incompatible and vice verso. Any idea?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.