As we know the chasm traps are found in N-1-N relationship. The solution for solving the problem is to create 2 contexts on both side. However, there are always lots of cases that requires objects in those tables. For example, let consider the following case
The Person table records all person that joins the event. There is also a column (T/F) to know who is the key organizer (unique). So actually the relations between event and key coordinator is 1-1
Now I want to put the key organizer into the block, showing all the event information. Due to contexts for solving chasm traps, the system will reply incompatible error.
I have thought of using alias (Key Coordinator for Person) to solve this problem. But I concerned on the performance and maintenance because extra joins and dimensions are required.
Anyone got similar cases and would like to share?
Thanks
If Event is your fact table, where is your chasm trap?
If you have facts in your People table, you have a potential fan trap.
In which case, create an alias of the people dimension table, join it to the people table and create another context.
Therefore you have one context containing Date -< Event and D_People --< Event and another context containing D_People --< F_People (and optionally Date --< F_People if appropriate).
How does that get loaded into the database?
For example, what would it look like on the 2nd? Then the 4th?
Without knowing the data that well, I’d suggest that you have event headers and event items with one date per event item. Is this a reporting database or a transactional database (OLTP system) that you’re reporting against?
This is a reporting database. However, there are transactions in it because users need to breakdown up to transaction level whenever necessary. Therefore, these three tables are retrieved from OLTP database directly.
Let me provide an example. Assume there is an Event called “Event123”. The event date is 1/5, 3/5, 4/5, 5/5, 6/5, 7/5. The event is organized by 3 people John, Mary and Peter
In the Date table, there are records to indicate the occupied date of the event
In Person table, there are records to indicate who are in charged of the event. There is a number to indicate who are the main person of the event. In the following example, John is the leader
In Event table, it records the event name and the event period. The event period is the start/end date of the event
The user want to retrieve all event dates of Event123, and show the main person who are in charge of the event. So the result block will show like
(BOB member since 2008-12-18)
(BOB member since 2008-12-18)
(BOB member since 2009-06-23)