View Full Version : Are outer joins available in the metadata editor?

07-30-2008, 11:17 AM

I've looked through the metadata editor documentation at the wiki.pentaho.com. A post on that site by Matt Casters on 4/18/08 states that "outer joins will be added in the next release".

Does anyone know specifically what release number that will be?
Does anyone know the date of the release?

I have not been able to find this information on the site.

Note: complex join workarounds like adding (+) in the WHERE clause is not an option - Teradata doesn't support this syntax.

Proof of concept I'm doing is using a Teradata database and I'm using a windows based preconfigured installation version of Pentaho on Windows 2003 (version 1.7.0.GA). And I'm using metadata editor version


07-30-2008, 12:14 PM
unfortunately in 1.7GA no they're not. The (+) syntax doesnt work either, whether or not your DB supports it.

The only way to achieve it is to create a database view which does the outer join, and then point the metadata at that.

For us, it's a pretty serious limitation of the metadata interface as it currently stands.. (However a lot of the other features of metadata are excellent. )

07-30-2008, 12:31 PM
Thanks - this is a big limitation to us also.

08-14-2008, 01:12 PM
Is it really a limitation or does it force one to rethink the model?

08-15-2008, 10:34 AM
Thank you stdoddn, for your wise remark.
That being said, outer joins will be supported in 2.0 as well as row level security.


08-15-2008, 11:33 AM
it is a huge limitation. Outerjoins are not an indication of a badly designed model, those who think that clearly havent worked in the real world.

08-15-2008, 01:53 PM
Easy there, codek.

I was not suggesting poor design, but sometimes we need to take a step back and ask, what is the end user really expecting? Can we deliver another way?

I do work in the real world, but I'm trying to get out.

08-16-2008, 08:45 AM
Hi codek, I would stipulate that with the arrival of free and easy to use ETL tools like Kettle, most if not all barriers for creating a proper warehouse have been eliminated.
More to the point the cost and time barriers are pretty much gone.
As such, it makes less and less sense to do reporting on a source system or an ODS. And as such, outer joins make less and less sense.
All that being said, the fact that we added outer joins support acknowledges the fact that some people indeed need it.
Personally, I just know that we will get a lot of false bug reports for complex outer-join situations.
I also think that our outer join support should come with a big warning sticker on the box detailing how easy it is to get incorrect figures while doing reporting with them.


08-18-2008, 04:29 AM
Ok, yes i agree that if your creating a warehouse, then yes it does make sense to model your data in a more sensible way. There's a lot you can do at that stage to make both your metadata model and therefore reporting much easier.

However, and this is what i meant by my real world comment, some people dont have the luxury of a data warehouse situation, and simply have to work with what they've got... That is a point proven by many requests from genuine end users for this feature.

Fortunately I work with dba's that understand that the database has to support the needs of the reporting environment, but I have worked before in places where there simply wasnt a DBA at all, so it was not possible to make any changes to the database and you simply have to provide the best you can with what you're given. This is surprisingly common! Especially with old legacy systems.


08-18-2008, 04:37 AM
I think it's up to us as consultants to point our customers to free(or non-free) databases and ETL tools that do the job 100x better than old legacy systems.

08-18-2008, 04:40 AM
Out of curiosity, this was the first post ever I made on the Pentaho forums, back in December 2005: