RE: Which data warehouse use?

["jim@xxxxxxx" <jim@xxxxxxx>]

Can't say I've tried it with a db of that size, but would be curious to see how it performed.  Are you talking about just adding 30-40k per day or are you also potentially updating the other 20-30 million?  30-40k of inserts wouldn't be an issue.

I just ran a little test where I'm inserting about 400,000 records.  It took about 15 minutes to transfer the data.

Here are the some other things that I'm doing that would slow the process down.
- I'm running this on my development workstation, with about a dozen other apps running
- I'm connected to the AS/400 and MySQL db over 100meg Ethernet, our servers are all 1G connections
- I'm dropping all indices before and recreating after the transfer
- At the time of the transfer I'm also flattening out the database, pulling in data from a lot of lookup tables

But, that should give you a general idea of the performance.  Not great, but acceptable in my environment.

	-Jim

Jim Steil
VP of Information Technology
Quality Liquid Feeds, Inc.
608.935.2345 office
608.341.9896 cell


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan Kimmel
Sent: Tuesday, October 05, 2010 10:44 AM
To: Midrange Systems Technical Discussion
Subject: RE: Which data warehouse use?

Can this setup handle a largish database? Say, 20 to 30 million records with 30K to 40K new records a day?

I'd surmise ODBC would be inadequate to handle it.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jim Steil
Sent: Tuesday, October 05, 2010 10:09 AM
To: Midrange Systems Technical Discussion
Subject: Re: Which data warehouse use?

  I write Python scripts to handle it all.  Python has a great database API that is really easy to implement.  I connect to the AS/400 database using ODBC and to the MySQL db with the Python MySQL driver.  The programs are quite simple.  If you haven't used Python before, don't be intimidated by it.  It is really easy to get started with.  And, if you are already familiar with SQL then writing these scripts would be really easy.

I'm happy to help as much as possible to get you going if you're interested.  I really like using Python and enjoy getting others hooked...

     -Jim

On 10/5/2010 9:32 AM, Jack Kingsley wrote:
> Jim, what are you using to move the data between the I and your MYSQL
> Server.
>
> On Fri, Oct 1, 2010 at 4:17 PM, Jim Steil<jim@xxxxxxx>  wrote:
>
> >   I take the opposite approach.  In my mind the data warehousing data
> > is all reproducible from my production data.  Therefore, I don't need
> > the robustness (and extra cost) the i offers.  I take all of my data
> > to MySQL running on commodity hardware with Ubuntu.  Low cost and
> > really very fast.  Users can hit this database with their reporting
> > tool of choice all they want with no ill effects on the production systems.
> >
> > As for data warehousing software, we don't do anything fancy.  We
> > write programs to generate all necessary standard reports to excel or
> > pdf.  Ad hoc is done using MS Access (yuck) by our end users or using
> > SQL and programming as necessary when it gets too complicated for the end users.
> >
> > Just my $.02
> >
> >      -Jim
> >
> > On 10/1/2010 2:27 PM, Nathan Andelin wrote:
> > > I think that managing disparate systems is the biggest challenge in
> > > IT,
> > and the
> > > biggest waste of time.  If it were up to me, I'd migrate MS SQL
> > > Server
> > and
> > > PostgreSQL server data and applications to IBM i.  And not
> > > surprisingly,
> > I'd do
> > > data warehousing under IBM i, too.  I can't recommend any separate
> > > data warehouse system.  Just create SQL views of IBM i tables, and
> > > run queries against them.
> > >
> > > -Nathan
> > >
> > >
> > >
> > > ----- Original Message ----
> > > From: Tomasz Skorża<t.skorza@xxxxxxxxxxx>
> > > To: midrange-l@xxxxxxxxxxxx
> > > Sent: Fri, October 1, 2010 11:53:49 AM
> > > Subject: Which data warehouse use?
> > >
> > >     Hi guys
> > >
> > > I have one database on DB2-400, one on MS SQL Server and one on
> > PostrgreSQL.
> > > Which dedicated software for data warehouse you propose to use -
> > > best if
> > open
> > > source - and which
> > >
> > > database use for warehouse?
> > > Other DB2, MS SQL, Postgre or maybe mySQL?
> > >
> > > I will be gracefull for any ideas and prompting
> > >
> > > Regards
> > >
> > > Tomek
> > --
> > Jim Steil
> > VP of Information Technology
> > Quality Liquid Feeds, Inc.
> > 608.935.2345 office
> > 608.341.9896 cell
> >   --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> > mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To
> > subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
> > a moment to review the archives at
> > http://archive.midrange.com/midrange-l.

--
Jim Steil
VP of Information Technology
Quality Liquid Feeds, Inc.
608.935.2345 office
608.341.9896 cell
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.