Innovate your data – castle of DWBI

Menu

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.

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

Objects are created based on the above tables as below.

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,

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

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.

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.

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.

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

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.

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.

Context 1 – GL Balance

Context 2 – Loan Balance

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.

4 thoughts on “Working on Trap Issue – Chasm Trap”

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