cancel
Showing results for 
Search instead for 
Did you mean: 

Inner Join between 2 SAP BW Tables

bo_team
Explorer
0 Kudos

Hi,

I am trying to create an universe to join 2 BW tables. I am using "Information Design Tool". I have created a JCO connection for each table (cube) and a Data Federator joining them.

As result of this join in Data Federator, I got this SQL:

SELECT

"IIACCO_O1"."CA_ACCNT",

"DCA_PLTINS"."CA_PLTINS"

FROM

"DCA_PLTINS" INNER JOIN "IIACCO_O1" ON ("CA_ACCNT"="CA_ACCNT")

WHERE

"IIACCO_O1"."CA_APPLIC" = '8'

I just create a join between "CA_ACCNT" that exist in both tables. A simple join, with n:n relation.

So, I was expecting that BW server ran a SQL just like I described above, but instead of execute just one simple SQL, BW runs two selects:

SELECT

"IIACCO_O1"."CA_ACCNT"

FROM

"IIACCO_O1"

WHERE

"IIACCO_O1"."CA_APPLIC" = '8'

SELECT

"DCA_PLTINS"."CA_PLTINS"

FROM

"DCA_PLTINS"

In other words, it does a fulll table scan in the second table and I don't know how to solve this.

Could anyone help me? What should I do to make BW undestand that it needs run just one select?

Tks.

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

A little late but Scott's Blog discussing semi-joins might be of assistance for future use:

http://scn.sap.com/community/semantic-layer/blog

Henry_Banks
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi,

Your universe is far from developed.

Keys -> Joins -> Cardinalities -> Contexts -> Aliases = still some work to do

I suspect that if you do an integrity check now, there will be many unresolved issues.

Regards,

H

bo_team
Explorer
0 Kudos

Hi,

I have already defined keys, join, cardinalities, but not aliases.

I did an integrity check and everything was fine, except the "cardinality same as detected" finished with a warning. I also tried to determine the cardinality directly in the "Join Definition Details" but it was not able to detect.

I changed the cardinality (n:n to 1:n) but it didn't work as well.

Any help?

Former Member
0 Kudos

Hi there,

As far as I know joining cardinality N:N is not possible in BO. Try to join hte table with 1:N or N:1 cardinality and check if this works for you.

If you need to implement N:N than there are so many other ways to do that. You can not get directly N:N.