I have built a Universe which has three fact tables. One of the fact tables, The Transaction fact table, has two measures associated with it. The transaction fact table is joined to an intermediate table which in turn is joined to an Invoice dimension table. So the join path is as follows -
The intermediate table is just to break the Many-Many relationship between the Transaction fact table and the Invoice dimension table. There is a Dimension object being created on the Invoice table. When my colleague reviewed the structure, she told me that there is Fan trap between the transaction fact table and the intermediate table. She suggested me to create an Alias for the transaction fact table, associate the Measures to the Alias table and create a separate context for the Alias table. The Alias is joined to the original Fact table with One-One relationship. So the Alias context will have all the joins coming into the Transaction fact table and its join with the original fact table. The Transaction fact table will have all the joins coming into it plus the join path including the Invoice dimension table. So as the Measures and the Invoice dimension objects are in the separate context, it will resolve the Fan trap.
But when I showed this structure to Data Architect, he was not happy with the idea of aliasing the Transaction fact table which has millions of rows. Every time the Transaction fact table is referred, its Alias will also come into picture which will affect the performance and there will be spool size issue.
Is there any way to resolve this issue without aliasing the Transaction fact table?
I tried the above suggested solution, but now I have another problem. When I try to use the Dimension and Measures together in one report, I get “Cannot drop here. Incompatible Objects” error. Since the Dimension and Measure objects are in separate context, I cannot use them in a single report. Please let me know if there is any other solution.
dont use the dimension object directly.
creaate a detail objects of it and associate it with dimension object and use that detail object in ur report it will allow u to drag.
There are two measures from fact table F - Actuals Posted and Actual Hours. There is a Dimension object “Invoice Number” associated with the Invoice table D. To resolve the Fan trap, I created an Alias (table A) for the Fact table F. It is joined to table F with one-to-one cardinality. I associated the measures “Actuals Posted” and “Actual Hours” with the Alias table A. I created Context 1 and included the join between table F, table X, and the table D. I created Context 2 for the Alias table. But now the problem is that I cannot pull the Dimension object and the Measure objects in the report as they are in different context and I get “objects not compatible error”. Then what I did is, I created “Invoice Number” as a Detail object. Now it allows me to pull the objects together, but the data I get for “Actuals Posted” is wrong. I get only one value and it is same for each “Invoice Number”
Please let me know if I’m doing anything wrong. It’s really confusing for me.
(BOB member since 2004-06-06)