dbFunnel Provides Native ETL For IBM i
June 17, 2015 Alex Woodie
One of the software startups showcasing new wares at the recent COMMON conference was Hamway Software Solutions, which develops an extract, transform, and load (ETL) application called dbFunnel that runs natively on IBM i. As CEO Laura Hamway explains to IT Jungle, the software fills a need for lightweight data extraction and reporting involving DB2 for i.
Hamway’s journey to becoming an ISV begins, as so many do, with a consulting gig. The programmer was helping Del Monte Foods with an implementation of an automated data collection system for its packing and warehouse operations. The longtime BPCS shop was in the process of implementing the IBM i-based Viewpoint package from Nutech Systems just before the busy summer packing season, when they realized they had overlooked reporting.
“They were trying to find a solution to extract their data and move it over to a data warehouse and do their reporting for the entire warehouse,” Hamway says in an interview. “That’s when they found out I was writing this tool. They took a look at it and we implemented it within 30 days and ran their entire warehouse off it for the next five-and-a-half years.”
Unfortunately, the $1.8 billion food processing giant eventually moved off dbFunnel, along with every other IBM i application, as part of an SAP migration. But before Del Monte turned off its IBM i servers for good earlier this year, the company had used dbFunnel to extract more than 800 million records and transactions from its production system. During the peak of each Del Monte packing season from 2007 to 2014, dbFunnel moved 200,000 records per day to a secondary IBM i server equipped with the open source reporting package from Jaspersoft (since acquired by TIBCO).
“We have used dbFunnel (formerly known as ERP Explorer) to populate our hosted Data Warehouse for the last five years,” Del Monte’s Inventory Control Manager Joseph Akpovi says in a customer testimonial on Hamway’s website. “dbFunnel runs every 5 minutes and loads data from our ERP and WMS. We have been very pleased with the performance and the reliability of the application, and the support has also been great.”
Del Monte remains the biggest implementation for dbFunnel, and demonstrated to Hamway that there was a market for a lightweight and inexpensive ETL tool that runs natively on IBM i. Over a 25-year consulting career on the AS/400-iSeries-IBM i platform, Hamway has written her share of data extraction programs.
“I’ve spent a lot of time during my years as a consultant helping people extract data and move it somewhere else, or syncing data between multiple systems,” Hamway says. “A lot of customers don’t want to buy a big package. They don’t have the staff to support another package or more servers, or they don’t want to deal with the complexity that comes with a lot of these tools. They simply want to move data from A to B and have it done reliably.”
For many IBM i shops, the cost and complexity of a tool like Informatica‘s PowerCenter is overkill for their budget and modest needs. With a starting price around $20,000, dbFunnel is within the budgets of many IBM i shops.
There’s nothing stopping an IBM i shop from developing its own ETL scripts, Hamway says. “But every time there’s a change–especially if you’re doing ETL, the process and requirements are always changing–you have to go in and change files and programs. If you’re going to be doing it all the time, then you a need a programmer to do it, and then you’re back at the staff requirements to do that.”
Hamway says the simplicity and durability of dbFunnel are its top assets. The deployment basically involves setting up process groups that will copy one or more groups of database files from the production database to another location, either on the IBM i server or another location on the network. Once the mapping process is complete, the process groups will run in the background for as long as needed.
While the focus is on dbFunnel these days, Hamway has other irons in the IBM i fire, including a reporting solution called dbDecision. This product, which is designed to run on the server that contains the data warehouse or data mart, basically does the opposite of dbFunnel: instead of extracting data, flattening it, and sending it across the wire, dbDecision takes data off the wire, rebuilds it into some kind of shape, and then loads it into the warehouse.
Del Monte also ran dbDecision for two-and-a-half years after the failure of a PHP-based Windows solution.
Hamway has several other customers for dbFunnel, including PGI, a manufacturer and BPCS shop that makes cloth wipes for companies including Johnson & Johnson. Since then, she’s worked with other clients who need to move data out of other IBM i-based ERP systems, such as M3 and MAPICS.
With a lot of the ERP systems, it’s hard to get to the data you actually want. BPCS is notorious for that, she says while claiming Hamway software lets users extract what’s needed and then users can massage it.
Hamway is still consulting, but hopes that her packaged ETL tool will find a receptive audience. The recent COMMON conference in Anaheim, California, was her first major foray into marketing the solution and was a success, she says.
“Once people know it’s there and they have the need, it’s pretty simple to sell it,” she says. “As soon as they know there’s something that can get them to the end result faster and it’s not a bunch of programming, they’re very happy.”