Outer Joins defined in Universe Reverses when using WebI

building semantic layer
1

I have an issue that I cannot find a solution for. I have searched for Reversing Outer Joins and found multiple threads about errors when importing or transitioning between versions, but nothing related to my issue.

Essentially, I have multiple tables joined together by Outer Joins. Our data quality in our legacy db’s are horrible and this is a must to avoid losing any data elements. I’m defining the Outer Join and the Cardinality Statement is correct. However, when I go to WebI to test it, it reverses all of the Outer Joins and drastically reduces my results.

Example:

dbo.TableA.PackageID = dbo.TableB.PackageID
Outer Join Designated on TableA

Cardinality Statement Reads

“Each dbo.TableA has zero or one dbo.TableB. Each dbo.TableB has one or more dbo.TableA.”

Assumingly, this should create the following SQL Statement

dbo.TableA 
LEFT OUTER JOIN dbo.TableB ON dbo.TableA.PackageID = dbo.TableB.PackageID

Unfortunately, it is reversed when I look at the SQL Statement in WebI.

dbo.TableA 
LEFT OUTER JOIN dbo.TableB ON dbo.TableB.PackageID = dbo.TableA.PackageID

It only reverses the ON statement.

Also, I have 11 tables with Outer Joins and this only affects 3 of them. Correspondingly, the 8 where I’m not having the issue are in a different Schema and the 3 with the issue are in the same Schema as my main Fact Table.

Anyone have any idea what why this is happening?

Thanks in advance.
uniexample.png

travisbelt :us: (BOB member since 2010-06-14)

2

Unsure why its happenning, but, can you not just amend the join code to be correct?

Mak 1 :uk: (BOB member since 2005-01-06)

3

I would suggest something similar to what Mak1 has suggested, like, try writing a custom SQL (edit the automaticcally generated SQL) and see what happens.

Thanks
Kumar

kumarbop (BOB member since 2011-01-18)

4

We can do that, and it solves the issue.

With that being said, our end-users know basic SQL and nothing more. We can’t expect them to know how to do that each time they use that Universe.

travisbelt :us: (BOB member since 2010-06-14)

5

If that is the case, may be you can create a derived table at the universe level that has the (need to be modified SQL) modified SQL in it and create objects using that derived table. So, all the end user may need to do is be aware of this and use those objects from the derived table in their reports.

Thanks
Kumar

kumarbop (BOB member since 2011-01-18)

6

I didn’t mean custom SQL in Webi. I meant hardcoding the join tin the universe, rather than generating it.

What DB platform and middleware are you using?

Mak 1 :uk: (BOB member since 2005-01-06)

7

I really don’t understand what the problem is. Shouldn’t

dbo.TableA
LEFT OUTER JOIN dbo.TableB ON dbo.TableA.PackageID = dbo.TableB.PackageID

and

dbo.TableA
LEFT OUTER JOIN dbo.TableB ON dbo.TableB.PackageID = dbo.TableA.PackageID

return the same result set? What matters is the order of tables in your outer join statement:

dbo.TableA
LEFT OUTER JOIN dbo.TableB ON dbo.TableA.PackageID = dbo.TableB.PackageID

is of course different from

dbo.TableB
LEFT OUTER JOIN dbo.TableA ON dbo.TableA.PackageID = dbo.TableB.PackageID.

or

dbo.TableA
RIGHT OUTER JOIN dbo.TableB ON dbo.TableA.PackageID = dbo.TableB.PackageID

Besides the word “OUTER” is redundant, LEFT OUTER JOIN could (and in my opinion should) be simply written LEFT JOIN. Assuming of course, you are not using Oracle (in which case Webi usually writes sql with that annoying non-ansi (+) notation)

Pasi Tervo :finland: (BOB member since 2002-09-03)

8

If you set the value ANSI = Yes in universe parameters you will get ANSI syntax generated, if supported, whatever DB platform you use.

Mak 1 :uk: (BOB member since 2005-01-06)

9

You’re right of course. It’s just that by default it appears to be set to No. Also when you set it to yes, longer syntax is used (“inner join” and “left outer join” instead of just “join” and “left join”). Never found a parameter to change that.

All of that being of course beside the point here…

Pasi Tervo :finland: (BOB member since 2002-09-03)