I thought maybe folks would be interested in an update. I finally have redesigned this into a “proper multi-star schema”, each fact table in its own context, pulling dimension objects from the dimension tables, etc. Then I realized I needed to create universe formula objects that need to pull data between the fact tables. Well, of course they are in different contexts now, so they are incompatible. So I did research on the forum and found 5 options:
- Just leave the formulas at the report level (I don’t like this option since the users would have to recreate all these formulas for every report).
- Alias the fact tables and convolut the universe even more
https://bobj-board.org/t/1355/4 - Create a view or aggregate table that does the join between the fact tables
- Create a join between the fact tables (I found this quite funny in a crazy way) https://bobj-board.org/t/31667/5
- Turn off the “Multiple SQL statements for each measure” - if you can guarantee that each record exists in all fact tables (which I can’t guarantee - so it stays on) https://bobj-board.org/t/7267/4
The ideal solution, that was proposed in 1999, would be a universe object that was evaluated at the client/report level. But I don’t think this exists. Hopefully it will in 6.5 - anyone know if it will?
So, at this point I believe option 1 is the only answer (especially since I redesigned this whole thing so that the fact tables would not be joined) for now and perhaps option 3 later.
Thanks,
Nile
Nile 
(BOB member since 2004-02-12)