Hi all,
we have our schema with 5 fact tables and most of our reports use min of 2 fcat tables at a time.when i tried to create a univeres on this i ended up with 5 contexts one for each fact and 120 loops covered by one context or the other and somtimes with 2 or 3 contexts with 13 alias.what are the best practices to decrease the loops.and how to define the contexts properly to avoid the loops covered by multiple contexts.
Thankyou.
cheedalla (BOB member since 2004-04-13)
Do you actually get a warning that there are any unresolved loops left?
Loops covered by contexts are actually good: it means you broke the loop by using contexts (unless I am mistaken).
Can you give an example of one loop?
Is this a multiple star schema or OLTP system?
Andreas 
(BOB member since 2002-06-20)
There are no unresolved loops
Its a multiple Star Schema.
five facts are
Charges
Payments
Adjustments
Denials
Encounters
theres a loop between DrRend–Patient–Payment–Adjustment
DrRend–lineofbusiness–payment–encounter.
lineofbusiness–patient–svcplace–charge–adjustment–payment
cheedalla (BOB member since 2004-04-13)
Why can’t you have them as seperate universes?
Sandeep.
chinnu (BOB member since 2002-11-22)
Then I do not understand your problem. Since all loops are resolved (via contexts) there is no loop problem.
Andreas 
(BOB member since 2002-06-20)
We are still in the process of getting data to our schema, I have not worked with linked or multiple universes before so I am not aware of the perfomance and maintainance issues with multiple universes, all our reports are in WEBI. but still Right now I am working on Multiple universes approach with universe based on each fact and other approach I am trying is Creating a view from all facts data (which may cut some of our hirearchies) which ended with a simple universe but waiting for the data to work with these univrses.
cheedalla (BOB member since 2004-04-13)
OK, I do not understand what the problem is you are facing. It looks like you have done everything right:
Multiple Star Schema --> one context for each fact table, which should resolve all loops and chasm traps.
Andreas (BOB member since 2002-06-20)
As most of the loops are covered by multiple contexts, I am wondering when I run my queries its going to prompt for the context, and i am trying to avoid this prompting for context.
cheedalla (BOB member since 2004-04-13)
If you have a truly classic star schema (conformed dimension tables shared between multiple fact tables), and have each fact table covered by its own context, then there is only one explanation for being prompted for a context … you haven’t included an object from a fact table in your query. If you select objects ONLY from dimension table(s), the context is ambiguous and the user is prompted.
Dwayne Hoffpauir 
(BOB member since 2002-09-19)
I am not sure if I got your issue right.
Since you say that most of your reports use 2 or more fact tables, you cannot prevent contexts prompting by creating one context per fact table. You may have to create contexts for different combination of Fact tables and make all those contexts unique(in selecting the joins - May be you can use a dummy table for a particular Join/Context).[/code][/quote]
Ashokkumar (BOB member since 2004-05-20)
Andreas and Dwayne are spot on.
Multiple fact tables - one context per fact table, not one universe for each fact table
Context selection will only be required if no objects derived from fact tables are selected.
The most likely reason that I could see a query based on two fact tables falling over is if you have not set your universe parameters up correctly. Make sure that if you offer the user the opportunity to include facts from two tables in one query you allow them to run the query. Make sure that the Allow Selection of multiple contexts and multiple sql statements for each context boxes are ticked in your SQL tab in the parameters
Although not so often chosen (I would imagine), setting up different fact tables/contexts as separate universes can be a valid decision if you need very strong security and differentiation between them - to only allow each universe available to a different business group and/or repository domain, for example. But then you have to be willing to accept the multiplied administration overhead.
JP Brooks 
(BOB member since 2002-10-22)
I am starting a fresh universe that is absolutely similar to what is defined in this post! 
I have seven fact tables and the majority of queries will only require one fact table at a time. There is a report that compare between one fact table and other.
I also want the schemas all in one universe for ad hoc reporting.
Here’s my question…I’ve started designing this a couple of different ways and I’m looking for the best practice on how to do this right the first time! 
How do you start? I’ve built each schema as a “separate entity” within a universe to make sure that everything was correctly modeled. Now I want to join all of the pieces.
Do you suggest I just pull these pieces together and remove my aliased dimensions? Do I start fresh…and if I do, then what order would you follow?
If I hadn’t done anything yet, what would be the best way to approach it fresh? I don’t have a problem with starting fresh…
Also…if it matters, these are primarily factless fact tables. There is little or no aggregation involved.
Eileen King (BOB member since 2002-07-10)
Put the fact tables in the middle and join them to the appropriate dimensions. I like to start with 1 fact at a time. I build my contexts manually along the way.
It sounds pretty simple, Eileen.
Steve Krandel (BOB member since 2002-06-25)
No pressure then Eileen.
Seriously, Steve is right, fact tables in the middle, dimensions around the side as you would sketch it on paper. As Steve says, it is then easy to build the contexts because all the joins fan out from your fact tables.
Regards,
Mark
I have to say that I was making it a lot harder than it really was supposed to be! I’ve done so many universes on OLTP and simple data marts but this was the first time I had seven fact tables and 14 dimension tables!
I did a prototype universe for each one as a stand-alone within one universe. I knew I didn’t want them in separate universes!
But then when it came time to do it…I just wasn’t sure where to start! 
Trust me…this way is much easier than what I did to model the prototype!!!
Eileen King (BOB member since 2002-07-10)
I agree with Mark (who agreed with Steve). The only thing I would add is that you should only be creating aliases of dimension tables if they are being used for two different purposes within the same fact.
MichaelWelter 
(BOB member since 2002-08-08)
That’s exactly how I knew it wasn’t going to do what I had in mind! 
Eileen King (BOB member since 2002-07-10)
Is there any way that I’m ever going to have a pretty universe? Now I can’t get it to look like a nice little schema with pretty lines that don’t cut through everywhere… 
Eileen King (BOB member since 2002-07-10)
Remember my motto:
The goal of a universe developer is to make things as easy as possible for the end users; even if it makes it more difficult for me.
You get nice consulting rates to build these complex things. Simple universes are easy. If you show a big ugly universe to someone, they’ll think they got their money’s worth. 
Steve Krandel (BOB member since 2002-06-25)