Chasm Trap Why???

[Former Member]

hi all,

this question is regarding that why we get the chasm trap

if suppose one dimension table is connected to 2 other fact tables and i'am only taking measures value from both of these fact tables withount using multiple sql for each context and each measure, it is just a simple query without context , then how it will effect the answer, whether it will give the right answer or wrong

if wrong then why?? same for right

this is the simple sql generated

select CLIENT.CLIENT_FIRSTNAME,

sum(Rental.rental_total),

sum(SALE.SALE_TOTAL)

from client,sale,rental where client.client_id=sale.client_id and

client.client_id=rental.client_id

group by

CLIENT.CLIENT_FIRSTNAME+CLIENT.CLIENT_LASTNAME

here rental total is coming from fact table rental and sale total is coming from the sale fact table , remember i'am not taking any dimension from the fact tables..

rohan

[Former Member · Accepted solution]

Hello.

You're approaching the chasm trap question from the wrong angle with respect to universe design.

You need to consider that rental value and sales value are both objects that can be dragged into a query without a dimension object. When you consider that, then you will see that there is no way of relating them, thus they need to be in separate fact tables. If you have sales one day but no rental, or vice versa, you could run into more issues in that circumstance. If you have a context for each fact table, the correct values will be returned. While you may get results the other way, it may not work.

Consider

SELECT
Date_Table.Date,
sum(Sales_Table.Sales_Value),
sum(Rental_Table.Rental_Value)
from
Date_Table, Sales_Table, Rental_Table
where
Date_Table.Date = Sales_Table.Sale_Date
and
Date_Table.Date = Rental_Table.Rental_Date
group by
Date_Table.Date

You may well get results. But they will only be right for the days where you have sales and rentals unless you make a data warehouse design decision to populate zeros on all empty sales and rental combinations.

As for your code, I'd suggest it is wrong.

select CLIENT.CLIENT_FIRSTNAME

would not generate a group by of

CLIENT.CLIENT_FIRSTNAME+CLIENT.CLIENT_LASTNAME

The general rule for building a universe over a dimensional model (data mart, data warehouse, star schema, snowflake schema, etc.) is one context per fact table.

[Former Member · Accepted solution]

hi,

i just want to know what is the effect of the Chasm trap, i know the solution that by context we can resolve the trap, i just want to know its effect

[Former Member · Accepted solution]

There are potential for multiples and unrelated data.

Think of your sales and rental example.

How can I relate a rental dimension to a sale?

Select

Rental_Item.Damage_Waiver_Taken,

sum(Rental_Fact.Rental_Value),

sum(Sale_Fact.Sale_Value)

From....... ?

How would the universe create a query based on the above?

You would be creating a cartesian product. The query above wouldn't know how to navigate from the rental details to the sale details and would return every possible combination of rows for one measure with every possible combination of rows for the other measure.