custom data warehouse in python vs. out-of-the-box ETL tool

[Tony Schmidt]

Hi, Marc-Andre - well, so far you seem to be the only one suggesting
that cross-database joins is the way to go - everyone else has been
telling me to build a warehouse.  I initially was trying to avoid the
warehouse idea to "avoid going through the external temporary
resource", as you say.  But then the situation came up where the data
warehouse would give me another benefit by enabling power users with
tools like OOBase to do their own reporting.  So I started to re-
consider it, and since then, no one has advised the direct approach
with connections/joins on multiple databases.

Have you had much luck circumventing the need for a data warehouse in
this way?

Thanks.

On Sep 24, 4:10 am, "M.-A. Lemburg" <m... at egenix.com> wrote:
> snfctech wrote:
> > @Lemburg: Thanks for the suggestion.  I'm sure you make a fine
> > product, but my development platform is Linux, and I don't want any
> > additional Windows servers to deal with (than the ones I'm already
> > stuck with.)
>
> Strange, EasySoft used to support their product on Linux as well...
>
> http://download.freshmeat.net/projects/easysoftsqlengine
>
> Looks like they stopped with supporting it on Linux and now only
> have Windows downloads available.
>
> mxODBC and mxODBC Connect work just fine on Linux, so you could use
> them to build a custom joining solution in Python.
>
> It really depends on whether you need to run those joins on
> an on-demand basis or not:
>
> AFAIK, Talend can only run jobs which
> then apply the JOINs and put the resulting data somewhere, e.g.
> into a file or another database table.
>
> With Python, you could do the join straight away and use the
> data directly without the Python script - without having
> to go through an external temporary resource.
>
> Anyway, just a suggestion.
>
>
>
> > On Sep 23, 2:02 am, "M.-A. Lemburg" <m... at egenix.com> wrote:
> >> snfctech wrote:
> >>> Does anyone have experience building a datawarehousein python?  Any
> >>> thoughts on custom vs using an out-of-the-box product like Talend or
> >>> Informatica?
>
> >>> I have an integrated system Dashboard project that I was going to
> >>> build using cross-vendor joins on existing DBs, but I keep hearing
> >>> that a datawarehouseis the way to go.  e.g. I want to create orders
> >>> and order_items with relations to members (MS Access DB), products
> >>> (flat file) and employees (MySQL).
>
> >>> Thanks in advance for any tips.
>
> >> You might want to look at this solution for doing cross-database JOINs:
>
> >> EasySoft ODBC Join-Engine:
>
> >>    http://www.easysoft.com/products/data_access/odbc_odbc_join_engine/in...
>
> >> and then use our mxODBC to access EasySoft's Engine:
>
> >>    http://www.egenix.com/products/python/mxODBC/
>
> >> or mxODBC Connect, if you have a client-server setup:
>
> >>    http://www.egenix.com/products/python/mxODBCConnect/
>
> >> Some database engines also allow integrating external ODBC
> >> data sources - the external tables then look like normal
> >> database tables and can be used in JOINs as well. I know that DB2
> >> and Oracle support this kind of setup. You can access those using
> >> mxODBC as well.
>
> >> --
> >> Marc-Andre Lemburg
> >> eGenix.com
>
> >> Professional Python Services directly from the Source  (#1, Sep 23 2009)>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
> >>>>> mxODBC.Zope.Database.Adapter ...            http://zope.egenix.com/
> >>>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
>
> >> ________________________________________________________________________
>
> >> ::: Try our new mxODBC.Connect Python Database Interface for free ! ::::
>
> >>    eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
> >>     D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
> >>            Registered at Amtsgericht Duesseldorf: HRB 46611
> >>                http://www.egenix.com/company/contact/
>
> --
> Marc-Andre Lemburg
> eGenix.com
>
> Professional Python Services directly from the Source  (#1, Sep 24 2009)>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
> >>> mxODBC.Zope.Database.Adapter ...            http://zope.egenix.com/
> >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
>
> ________________________________________________________________________
>
> ::: Try our new mxODBC.Connect Python Database Interface for free ! ::::
>
>    eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
>     D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
>            Registered at Amtsgericht Duesseldorf: HRB 46611
>                http://www.egenix.com/company/contact/