How do I resolve this loop in a better way? Product table is a shared dimension among the fact tables and I want to join two fact tables as shown in the attachment.
If I use Product table alias, the objects will be duplicated and I have several shared dimensions like this.
I added contexts to all many ended joins.
Is aggregate awareness an option? Please help!
shamanth (BOB member since 2007-03-01)
Create a context and add the following Joins to it.
Make Product>>Account short cut join.
Hope this helps.
Thanks,
Madduri
Madduri 
(BOB member since 2007-02-15)
How will that help, given that you’ve got the cardinality the wrong way round on your second join?
Create an alias of Account
1/ Join Product to Account ( 1 to many)
2/ Join Account to Alias of Account (1 to many)
3/ Join Account to Credit Activity (1 to many)
Add a context containing joins 1 and 2
Add a context containing joins 1 and 3
Review the objects that you currently have in your universe that use the Account table. Change any of these that are measure objects to use the Alias of Account to avoid a fan trap.
Regards,
Mark
Mark,
Please see the attached structure. I still get a loop. I am liitle bit lost in the context part. What joins shoiuld I add to the context. At the moment I have contexts for all joins from product dimension table. Should I create context on all 1-M joins?
We have many shared dimension tables like product and as Universe grows it will become more complex.
I don’t this shortcut join is a solution to break the loop.
Thanks for your help.
-SG
shamanth (BOB member since 2007-03-01)
Shortcut joins don’t break loops, aliases and contexts either break or resolve loops, depending upon the tables involved.
In the diagram you’ve posted, delete the join from PRODUCT to CREDIT_ACTIVITY.
You then have four contexts:
PRODUCT - TRADE
PRODUCT - DAILY
PRODUCT - ACCOUNT_PRODUCT and ACCOUNT_PRODUCT - ACCOUNT_PRODUCT2
PRODUCT - ACCOUNT_PRODUCT and ACCOUNT_PRODUCT - CREDIT_ACTIVITY
Don’t forget - measures from ACCOUNT_PRODUCT2, dimensions from ACCOUNT_PRODUCT. 
Thanks for your quick response. Few more doubts.
2.Your statement: “Don’t forget - measures from ACCOUNT_PRODUCT2, dimensions from ACCOUNT_PRODUCT”
Why dhould I do like this?
All dimensions should come from PRODUCT table. ACCOUNT_PRODUCT is not a pure dimension table. It has facts too. I don’t want any duplicate Measure Objects or Dimension Objects in the Universe. Users will get confused.
I appreciate your help!! 
shamanth (BOB member since 2007-03-01)
Why does the join path matter to you? Unless it is incorrect to travel through ACCOUNT_PRODUCT, then there is no problem. Make sure your join columns are indexed and there won’t be a problem.
I know you will have objects that come from the PRODUCT table. That is a conformed dimension and is relevant to DAILY, etc.
I presumed ACCOUNT_PRODUCT contained both dimensions and measures. The design that I have described shows you how to avoid a fan trap. For those objects that are derived from ACCOUNT_PRODUCT, measures should be derived from ACCOUNT_PRODUCT2 instead. dimensions from the ACCOUNT_PRODUCT table are not relevant in your DAILY context but are relevant to measures from both ACCOUNT_PRODUCT and CREDIT_ACTIVITY. This is simply standard fan trap resolution.
Got it!! Thanks for your help!
Last one and I will close the post. What is the trick behind Dimensions from base table and Measures from Alias table? I just wanto make sure that there are no duplicate Measure objects created in the Universe.
Also, Isn’t it appropriate to have 1-1 join between base table and Alias Table rather than having 1-M join?
shamanth (BOB member since 2007-03-01)
The trick is to avoid duplication of values.
The 1-M is to allow the context detection algorithm to pick the context up - if you’ve sorted your contexts out, then feel free to set it to 1-1; it doesn’t serve any other purpose as far as I’m aware.
Super 
…Many thanks for your help!
I used a shortcut join to avoid the long path. Is this a right solution? Do I have to add any extra contexts?
Account_Product is the combination of Account and Product dimension tables. So, it will be connected to the account table in the future. I have almost 20 shared dimension tables like Product and around 6 hybrid tables like Account_Product
The SQL is good for now. Attached is the updated schema.
-SG
shamanth (BOB member since 2007-03-01)
A shortcut join is fine if it is correct.
Once you have built your query, BusinessObjects will determine the context to use and then assess if there is a shortcut join that can be used to get the same results.
If you only have information requirements from PRODUCT and CREDIT_ACTIVITY, this will work.
Mark,
confusion again… why should I create an alias? I am not using any measures from that table. It is just another reference table. I cthink I can avoid alias… correct?
Thanks,
SG
shamanth (BOB member since 2007-03-01)
Mark,
Account_Product is another Reference table which is a combination of Product and Account Dimensions. FYI: Account table is not yet included in the schema.
So, There won’t be any measures in it. I guess I don’t need the Alias of Account_Product… right?! I can just use Context and Shortcut Join. Please let me know if my assumption is correct or not?
shamanth (BOB member since 2007-03-01)
Do you understand your schema and what it contains? Only you’ve confused me completely as to what data you have and how it is held.
I have so may shared dimension tables like Account and Account_Product. If I add more dimensions to the context it will prompt the user to select the context.
Without creating an Alias, is there any better way to handle this?
Sometimes the user don’t select any measures. They only select Dimensions. But still the query is including Fact tables in the joins. Our co-developers don’t want to see those fact table joins in the query. They also want to join the dimension tables. But that creates so many loops in the Universe. They also don’t want to see any context prompts at Web-I.
shamanth (BOB member since 2007-03-01)