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.

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