Skip to Content

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Mar 15, 2017 at 06:30 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 28, 2017 at 03:03 PM

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

    Add comment
    10|10000 characters needed characters exceeded

    • 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!