PDA

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



poctest
07-30-2008, 11:17 AM
Hi,

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 1.7.0.0321.

Thanks

codek
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. )

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

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

MattCasters
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.

Matt

codek
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.

stoddn
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.

MattCasters
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.

Matt

codek
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.

Dan

MattCasters
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.

MattCasters
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:

http://forums.pentaho.org/showthread.php?p=78448#post78448

;-)

Matt