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

Advertisements

4 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.

      Thanks
      Madhes

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s