I am trying to understand Chasm Traps and how context can fix them.
I have a table Customer which is joined with SALES and PURCHASE 1 to Many relationship.
SALES <— Customer —>Purchase.
Customer has multiple sales and multiple purchase. If I get the amount from SALES and PURCHASE then the amount will be incorrect due to multiple entries in SALES and PURCHASE.
I created 2 context. One for SALES and one for PURCHASE. I was then able to pull the measures from both Sales and Purchase which worked fine.
I also able to use the dimensions from either PURCHASE table or SALES table but not both.
I did confirm that if I selected fields measures and dimensions from both Sales and Purchase then web I will create 2 queries.
Why am I not able to use dimension from both tables?
F1 has a dimension, sales receipt
F2 has a dimension, purchase order number.
If there is one and only one sale for each purchase and one and only one purchase for each sale, you may need to revisit your database design, normalising Purchase Order and Sales Receipt into dimensions and having three dimensions and two facts.
However, in your situation, I would suspect that you may have, say, 2 rows for a purchase and 3 rows for a sales. A single query against both tables would create six rows, inflating both results. If there was only 1 purchase row, it would still be represented 3 times and you’d see the correct sales but inflated purchases.
In terms of your actual dimensions in fact tables, if you take my example above of 2 purchase rows and 3 sales rows for a customer, how would you know which of the two dimensions in F1 related to which of the three dimensions in F2 and the other way round?
I know what chasm trap is and how to resolve it. My question was about the DIMENSIONS that can be added after creating the context.
As I mentioned in my original post. I want to bring in dimensions from both PURCHASE and SALES table. In my query panel, I have added all the dimensions from both tables. I also confirmed there were 2 separate SQL generated. One for each table.
But when I tried to add the dimensions from both tables, I get ‘Incompatible object’ message. I can only add dimension either from PURCHASE or SALES.
As a work around, I can create a detail variable and add it to the report but my understanding is that 2 separate SQLs should let me add dimensions from both tables.
You can bring them in to a section based on a dimension from the dimension table, but not use them in the same table. They are from different contexts.
Think about your data - do the dimensions from one fact logically relate to only one row in the other fact?
Still confused though. I thought Multiple Measure option in Universe was to make sure we can use measures from tables that had 1 to many to 1 relationship and it only worked for Measures. If we needed to use the dimensions then context was the option and Multiple Context option in universe addressed it. If it is creating 2 sperate queries based on context, then why we can’t use measure from both tables.
(BOB member since 2003-11-27)