I know, I know I searched I promise, but I am having problem with the terminology of trying to search based on my question.
Three tables A, B, and C. I need to create objects based on table C dependent on 4 values from table A and 1 from table B. Problem is, table A and B are joined and need to be, thus I have a loop. I am familiar with context and alias but not sure they will work for this situation. A context would define the correct path, but I really need both paths A-> C and B-> C defined in my join to return the correct values.
Right now, I have join A->C and place and then force join B->C in the where clause of the object. It looks like this will work but I do not know that this is the best solution.
Not all loops are a “bad thing”. If you need a loop in your universe, simply create a context that includes all of the joins from A, B, and of course C and let it go at that. BusinessObjects will then generate the sql code that includes a loop.
Put another way. If you have tables A, B, and C, and you do not do anything to resolve the loop, then BusinessObjects will complain. But if you say that the loop is okay by including every join in a context, then it will happily generate the correct SQL.
I have used this technique and it works just fine. For example, there was a universe where I needed three tables: the service provider, the service, and the customer. The customer may get one service from one service provider and a different service from a different service provider all on the same transaction. Think of a hospital stay… you have surgery, but the surgeon doesn’t do the anesthesia. You need the combination of all three entities (provider, service, customer) to get a valid result.
The issue may be that if you do this, then you will always get the sql with the loop in it…
Alternatively you can create a view with A, B, and C in it, then simply use the view in your universe. That isolates the loop at the database level, and BusinessObjects is none the wiser.
My problem with context are - once you use a context everthing must then be included in a context. Right now, this is a somewhat significant Universe and there are no context defined. We would need to go back and create all the context. We have considered the view; however, I am just trying to determine all my options and make the best decision.
I think the problem with a shortcut join (although technically the loop would be resolved) is - only one path is used and in this case if objects from all three tables are used it would always use the primary joins to associate the tables thus leaving out the necessary shortcut.
Do you guys think defining all the context or creating the view would provide a better solution that the one I suggested earlier (forcing the last join into the where clause of the object). Do you see anything inheritantly wrong with this?
So just create one context that includes the entire universe. 8) There’s nothing wrong with that. The point is, if you have an unresolved loop in your universe, BusObj won’t generate the sql. But as long as you have made an attempt to resolve it - and including a loop in a context is not the “typical” result but there is nothing wrong with it - then it will happily generate the sql that includes joins from A, B, and C all at the same time.
The problem is if you have more than one object, you will have to add the clause to each object. Not very elegant, especially from a maintenance standpoint.
I vote for the view thing. I don’t think a context is the answer. Just because joins exist in the context, doesn’t mean they will be used. You would still have the problem of forcing the B->C join. You could do it the way your are or by using the Tables button on the objects and then having a true join in the universe.
I also ran in similar problem. I think creating a view would be the best solution to this problem. Or if that is not possible, a very crude soln in case there is only 1 such object in the universe that needs to go over all three tables would be to
add the join b/w those two remaining tables in the WHERE clause of that object in the universe
I am facing a similar problem. Creating only one context for the whole universe works only if you have only one loop or if you want to force the loops all at the same time into your SQL code.
My scenario differs:
I have multiple loops, which are necessary (denormalized tables/ PeopleSoft). If I have two loops L1 and L2 and I want for one table to force loop L1, but not loop L2 I have to create separate multiple contexts.
I need to force my universe to loop based on a single Time dimension table joined to many type 2 / versioned dimension tables. Each of these are then joined to each other in a hierarchal fasion. For example the tables could be UPC / Product / Category. Each of these are versioned in it’s table by effective and end dates. The problem is that if I want a report showing which Categories were effective in a given week, BO will also join the Time table to the other 2 dimension tables. In my case, there are 7 very large versioned tables, and I can’t have all 7 included in every query, even if I just want time and a single dimension. I can try creating every combination of contexts possible, but BO will still prompt for which context to use, which we can’t allow. Is it possible to either limit which joins of this type of context are used to the shortest join path, or have BO automatically use a context based on fewest joins. I don’t think this is possible… but thought I’d ask.
I’ve got an issue that seems to belong in this thread. The objective is to be able to report how much time an employee works on any PROJOWNER’s projects (there are approx a dozen PROJOWNERs) , and the total time worked on all projects. I can get this to work fine using a subquery, but I also need it to work with webI 2.6 (I know subqueries work in v6, but we’re not quite there yet). I’m trying to get BO to mimic the following sample SQL, which produces the desired result:
SELECT c.EMPID, c.FULLNAME,
sum(( CASE WHEN ( b.PROJOWNER ) = 'Bert, Rat' THEN ( a.ACTUALHRS ) ELSE 0.00 END )),
sum(( a.ACTUALHRS ))
FROM v_pt_Resource_Time_MON a
left join v_pt_project_code b ON b.projectcodeID= a.PROJECTCODEID
left JOIN v_pt_people_info c ON a.EMPID= c.AEMPID
WHERE ( a.yearmonthend = 200404 )
GROUP BY
1,
2
HAVING
( sum(( CASE WHEN ( b.PROJOWNER ) = 'Bert, Rat' THEN ( a.ACTUALHRS ) ELSE 0.00 END )) > 0 )
ORDER BY
4 DESC
I’ve set up a context with the three tables. I have one object that sums total actual hours, and another with an owner’s name and sums for owner-specific hours:
@Aggregate_Aware(sum(CASE WHEN v_pt_project_code.projowner= 'Bert, Rat' Then v_pt_Resource_Time_MON.ACTUALHRS Else 0.00 END),sum(CASE WHEN v_pt_project_code.projowner= 'Bert, Rat' Then v_pt_res_proj_hours.ACTUALHRS Else 0.00 END))
If I don’t set up a subquery to return EMPIDs where the sum of PROJOWNER hours is greater than 0, I get all employees, whether or not they had hours for the specific PROJOWNER. Both objects return the correct sum of hours, but we don’t want to see the employees with zero for that specific owner. I’m wondering if the desired SQL can be forced somehow, or is this yet another case best resolved by views?
Sorry, let me clarify. The subquery was created by dragging EMPID to the conditions panel, choosing In List as the operator, and Create a subquery as the operand. Then on the subquery panel, EMPID as the result, and a condition where sum of actual hours (specific owner) > 0.
(BOB member since 2002-06-20)
(BOB member since 2002-08-08)
(BOB member since 2002-06-20)
(BOB member since 2002-06-20)
(BOB member since 2002-06-20)