on 02-07-2012 8:50 PM
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.
A little late but Scott's Blog discussing semi-joins might be of assistance for future use:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.