Skip to Content
0

Information Design Tool - Left Outer Join not behaving as expected with BW>SQL join

Feb 28, 2017 at 02:57 PM

694

avatar image

Before details on my issue, we're running the following:

  • Information Design Tool 4.2 SP2P2
  • BOBJ Design Studio 1.6SP4P1

I've been experimenting with joining BW InfoProviders to SQL tables and have encountered a strange issue. My goal is to have an outer join using a SQL table (Table1) and a BW DSO (Table2) such that Table1 is the "Outer" table. End result: we should see all records from Table1 and matching records from Table2. The Table 1 Connection is a 2008 MS SQL Server 2008 OLE DB connection.

After not getting expected results initially (details explained in bullets below), I created a universe for every cardinality/join combination possible between the tables and tested the Universe result sets in Design Studio. This confirmed that cardinality has no effect on actual result sets, as stated in previous SCN posts. However, I get the following results for join combinations:

  • Inner Join: only records with matching values for join field between both tables (expected result)
  • Table 2 (Outer) to Table 1(Inner) - all records from Table 2, only matches from Table 1 (expected result)
  • Table 1 (Outer) to Table 2(Inner) - result is an inner join - only getting records with matching values for join field between both tables (unexpected result)

Does anyone have any thoughts as to the cause here? Could I be missing a setting somewhere that pertains to Table1? I don't know if Table1's connection type could somehow relate.

Any help would be appreciated.

Thanks

Scott

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
AMIT KUMAR
Mar 15, 2017 at 06:30 PM
0

Reference date will come automatically in the IDT table if there is any time dependant object in the DSO.

This is known limitation with reference date.Outer join will not work. Solution is for this to create derived table on I-table in data foundation and then join derived table with Sql server table.

check attached sap note

1834782 - Outer Join behaves as inner join in multisource universe based on SAP JCo connection

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks Amit! This did it. Appreciate all the help.

0
AMIT KUMAR
Feb 28, 2017 at 03:03 PM
0

Try with setting ANSI_92 parameter to Yes in Business & Data Foundation parameters and try.

Show 10 Share
10 |10000 characters needed characters left characters exceeded

Hi Amit,

Thanks for your reply! One question though. Documentation is limited on how to accomplish this and most seem to be for older versions of IDT. I was able to enable this property in the Business Layer via Properties>Parameters>Add ANSI92 to list from bottom dropdown then select "yes".

However, I can't seem to locate where to set this property to "Yes" in my Data Foundation. Could you provide a bit of detail on how to find it? Currently, I'm still getting the same result set in my universe, but I hope that that is only because I haven't yet been able to find the property in my dfx layer.

Thanks

Scott

0

check attached screenshot.

Click on Data Foundation->Parameters

untitled.png (33.8 kB)
0

Thanks for the extra detail! Unfortunately, I'm still getting the same result set. Even with the SQL table set to outer and with ANSI92 on at both the dfx and blx layers, I'm only getting records with matches in both tables. Any other thoughts on a potential issue?

Thanks

1

is there any reference date object in the BW table?

have you tried with outer join between two SQL server tables?

is it happening only when joining Sql server & BW table?

0

Amit,

Apologies for the late reply. First, there is a reference date field on the DSO I'm joining to that seems to be automatically generated in IDT.

I tested outer joins on SQL using the same methodology I'd used to test BW>SQL joins: three universes, the first LEFT OUTER on table1, second INNER, third LEFT OUTER on table 2. These joins do behave as I would expect when I view the universes in Design Studio, so to answer your last question, this does seem to be a problem specific to BW > SQL joins. When the SQL table is the "outer" table, the join still behaves like an inner join.

1

Hey Amit. I know you technically answered my main question, but can I resurrect this for one more question? The joins do work correctly when a SQL table is joined to a derived table based on my DSO. However, when the DF is set up initially, the main DSO table is joined to a number of other tables to correctly bring master data/texts/etc. into the output; it's very nicely done. When I join to the derived table, I lose all detail except DSO keys. I tried joining DSO to derived table, but this makes my join "inner" again. Is the only way to get all of the original info in the output to reconstruct all of the joins that were made to the DSO table using the derived table, or is there a faster way to get all texts and still have results that include an outer join?

0

can you please explain with some screenshot

0

Sure. In our case, the BW table we are using in our universe is "IZHE_O01". The SQL table ("outer" with all records) is "EMAS_2". The table derived from my BW table is "Admissions - Derived". W'hen I join "Admissions - Derived" to "EMAS_2", an outer join on "EMAS_2" works - I get all records from "EMAS_2", both with and without "Admissions - Derived" matches (screenshot below).

"Admissions - Derived" does not by default have all of the joins to automatically-generated dimension tables to use as attributes/texts that "IZHE_O01" has: it's only keys. I can recreate these attributes/texts manually by recreating joins in the Data Foundation, then creating attributes in the Business Layer, but this is a lot of manual labor.

To make these texts/attributes available while using an "EMAS_2" outer join without heavy manual work, I tried joining "Admissions - Derived" to "IZHE_O01" (screen capture below). I tried both inner and outer join configurations for this join. In all cases, once I add any attributes from "IZHE_O01" to the query definition in Design Studio, I get "inner join" results again.

not-joined.png (250.0 kB)
joined.png (266.1 kB)
0

You need to manually join the Admission derived table to all yours T- tables.

T-table->IZHE_001->Admission->EMAS2 is not going to work.

right one is this.

T-table->Admission->EMAS2

Edit T table join and select derived table.

I have seen first time so many T tables used someone in dtaa foundation.

0

Ok, thanks Amit! Yes, our DSOs have a lot of dimensions that have master data associated with them. That was why I was hoping to avoid manually recreating joins. Thanks for answering my question, though - guess I'll have to manually join in any master data we want to see associated with our DSO.

Thanks again for all your help Amit!

0