OT: Star Schema implementation in a front end reporting tool (Business Objects)
- From: "Ken Naim" <kennaim@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 15 Nov 2006 15:15:39 -0500
I have a star schema with 3 dimension tables (days, policies,
classes_and_flat_charges) and 4 fact (calendar_year_premium,
accident_year_premium, calendar_year_eanred_premium,
accident_year_earned_premium) tables. Each fact table joins to the dimension
tables in the same way. Implementing one fact table with the 3 dimension
tables in business objects is quite simple and works well. If I include the
other facts in the universe then I get loops for which I need a solution. I
have come up with the following and wanted to know what your thoughts on any
of the solutions provided are and how you have implemented similar
situations.
1. Implement contexts to resolve the loops. I haven't done this in the
past but I was warned that contexts are a pain.
2. Implement aliases which I have done but in this case I would have to
duplicate all the dimension objects form the 3 dimension tables and I am
worried about keep future changes synchronized between each of the classes
for each fact table
3. Implement each star (1 fact and 3 dimension tables) in its own
universe, again synchronization issues.
4. Link a universe containing 1 fact table to a one containing the 3
dimension tables in its own universe, which avoids the synchronization
issues, easy to implement, just going to have many universes.
5. create a table that logically full outer joins the 4 fact tables
into 1 "super fact table" which requires an etl change or addition neither
of which is too bad), which would simplify business objects design and query
generation as well as report design as one would not need to query multiple
universes, however it would make the table much larger (3 times most
probably) both in length and width potentialy making simple queries slower.
I know this is off topic and appreciate any input. I have tired
unsuccessfully finding a good list for business objects related questions so
if you know one please let me know.
Ken
Other related posts:
