Ingenuity And Integration Meet In IBM i Data Warehouse

September 12, 2016 Dan Burger

It’s not uncommon for people to overlook the IBM i Power Systems platform when it is data warehouse project planning time. Laura Hamway sees things differently. Hamway is a consultant who works primarily with manufacturing companies running Infor‘s LX ERP systems, which most people refer to as BPCS, the name of the software before the Infor acquisition. She helps these organizations consolidate data from multiple systems, which allows a single, summary view. More analysis. Less inefficiency. Better business.

There’s too much data for ease of use in transactional applications like BPCS, MAPICS, JD Edwards and other ERP software that provides very detailed data.

“Most of the frustration that I’ve been hearing over the past 20 years is that the reporting in the packaged ERP systems is not sufficient,” Hamway says. “Some have gotten better, but there are always some things the customers want that are not available in the ERP. Data collection is not usually part of the ERP software. So companies with a financial system or sales system or logistics system that’s different from the core ERP needs to have that data from the other systems married to the transactional data from BPCS. If you need a report on transactions for the past two weeks, for instance, that can be done very easily. But if you want to analyze trends or see where there are hiccups, there’s no reporting for that.”

Where to run a data warehouse is often a quick decision. Move all the data to SQL Server is, by far, the most common course of action. Keeping analytical workloads separate from production systems is a position that makes sense to some, while others believe whatever platform collects the operational data should house the data warehouse.

Integrate And Interface

Consolidating data from multiple sources is the best reason, but some would also note the typical transactional database architecture isn’t designed for analytical uses. It was designed for operational data. DB2 for i, however, has supported several OLAP functions–such as rank, dense rank, and row number–for some time. And with IBM i 7.3, new functions, such as cumulative distribution support and lag and lead capabilities were added. There are also data warehouse experts who believe whatever platform collects the operational data should house the data warehouse.

Like a lot of IT decisions, it should come down to the topic of skills. When there’s a knowledgeable IBM i staff, familiar with the tools and languages of the data coming from multiple sources, the warehouse belongs on the i. We just don’t see that happening all that often. What we do see is minimal staffing applied to IBM i and lots of staffing applied to Windows.

“The biggest problem is that IT staffs are overwhelmed with what they already have on their plates,” Hamway says. “There’s no time to research before making a move. So they do what they can by creating more queries and RPG reports, and that makes a few people happy. If they looked at a bigger picture, looked to the future, they would become proactive rather than reactive. As that happens, the value of the proactive projects will be more apparent.”

Because the existing workloads on IT staff are already pretty heavy, a consultant like Hamway ends up doing quite a bit of the work, which is integration oriented–getting systems to talk with one another–and automating steps.

“I’ve become pretty good at cutting projects into smaller sections so that it can be fit into the workday of the internal people. I don’t want this to be something they can’t understand or support in the future,” she says.

One of the cautions that Hamway emphasizes with regard to data warehousing projects is that they can easily become monsters.

Creating A Monster

“Often a project starts out with small requirements, but quickly grow into big requirements. They need to be managed,” she notes. “Companies with small IT staffs have trouble handling this.”

Her advice is to understand, in the early planning stages, what the business needs to get out of the project and avoid viewing the project entirely from a technical perspective.

“Some companies already have reports that duplicate efforts to some degree and they don’t clean this up as they move into new report writing technology. Adding selection criteria usually brings a reduction in the number of reports necessary. Creating unnecessary reports or duplicating results in several reports is time-consuming and costly in the long run. Determine the business reasons for having so many reports. That usually reduces the number,” she says.

“I haven’t seen an ERP product that can do the whole data collection aspect of what companies need. It comes down to the customization of what each customer wants and not being satisfied with only what the packaged software provides. To make it easy to customize and add other apps, the ERP software only needs to provide the hooks. The ERP software company can’t know all the possible add-ons and interfaces a company will want. Although most will happily provide the development services that build the bridges.”

Fighting The Current

There are a lot of IBM midrange shops that are not current with the software or the IBM i OS. They let it lapse and think they are saving money. Of course, they do save money in the short term. Highly customized BPCS shops have a lot to test when changes in BPCS are made. There can be hundreds of programs to test. It’s a project in and of itself, Hamway says. Eventually they turn to consultants to help them figure out problems.

“The majority of my customers are still doing green-screen DDS development using SEU. I try to raise the awareness of RDi, free-format RPG, and the use of change management software,” she says.

“In the manufacturing area there’s not a lot of shops building SQL databases. It’s a time and budget question. If you have the time and budget, do modernization as soon as possible. After it is completed, it will save a huge amount of time and allow IT to be more productive doing things other than putting out fires and concocting piecemeal solutions.”

When the data warehouse is built before modernization takes place, it will take some reworking of these data collection processes when modernization does take place. However, it won’t mean going back to square one if the planning is done right and a good data warehouse structure is built.

New IBM i Conference

Hamway has applied a lot of time and effort into organizing an IBM i technical conference, which will take place October 6 and 7 in Virginia Beach, Virginia. This two-day educational conference is being planned as the starting point for a new IBM i local user group in the greater Washington, D.C., area. A conference agenda, session schedule, list of speakers and list of participating vendors can be found online. The event is taking place at the Wyndham Hotel.

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search