on 10-13-2014 8:05 AM
I am using SAP BO universe designer version 12.6.1.1691.
I use BO Infoview version 3.1 to create reports.
The scenario consists of a fact table, Fact_Table1 and 3 master tables MT1,MT2,MT3. The fact table is joined with Master table 1 and 2 on the respective Ids.
The 3rd master table MT3 is joined with MT1,MT2 using a complex join. The query generated in BO is of the form
select MT1.dim1,
MT2.dim2,
sum(FT1.measure1)
FROM MT3 INNER JOIN MT2 ON (MT3.id1=MT2.id1 and MT3.id2=MT1.id2)
INER JOIN FT1 ON (FT1.id2=MT2.id2)
INNER JOIN MT1 ON (FT1.id1=MT1.id1)
group by 1,2
This throws an error ' *** Failure 3782 Improper column reference in the search condition of a joined table. ' since the join in the first line refers a column from a table that is mentioned in the last line.
However this doesn't occur if the order of joins is interchanged.
MT3 can't be joined with MT1 and MT2 with a simple join since this will create a loop.
Is there any way I can specify the order of joins in BO designer, except putting them in a context, to avoid this problem?
Is there an alternate way to obtain the above query without using complex joins?
Hi
MT3 is dimension table, so you can create alias table, so alias breaks a loop by using the same table twice in the same query.
Find the below links for more info
http://www.youtube.com/watch?v=ezss-VMjQRA
http://www.dagira.com/2009/07/22/context-versus-alias-overview/
Note: PLEASE mark blog as helpful & answered
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
You still have a loop in the universe:
MT1 ----- Fact ----- MT2
| |
| |
------------MT3--------
So you'd better solve the loop either by using contexts or aliases. Maybe MT3 can be aliased if it plays 2 different roles? Or one of the other 2 master tables (either MT1 or MT2) can be aliased? It all depends on your data model.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.