Simple Business Intelligence: Fact Or Phantom?
January 28, 2013 Dan Burger
With the requests for getting more information from existing data piling up like snow drifts in downtown Erie, Pennsylvania, IBM midrange shops are studying their query, reporting, and so-called business intelligence software options in hopes of finding the shovel that can dig them out. In doing so, discoveries are made that show that IBM i and its DB2 for i database has both tremendous capabilities and the potential for some disappointing outcomes.
For all of its limitations, the primordial Query/400 continues to be the favorite tool in shops that have otherwise regularly upgraded their hardware and software since the early days of the AS/400 great-great grandfather to the current generation of Power Systems running IBM i. One of the reasons Query/400 remains popular is that it is so dang easy to use and programmers just keep finding more ways to make the old dog do new tricks. That is great for those who are rewarded for making soup out of what is left over in the refrigerator, but it is mostly broth in a world where meat and potatoes should be on the table.
Executives and other power users want dashboards. Describing Query/400 as business intelligence is really stretching the terminology. Ask the end users about their satisfaction or their expectations . . . and don’t edit out the vulgarities. Despite the blood-thirsty calls for something better, companies–particularly those in the small to midsize category–still balk at deployments based on the cost of the projects and the lack of IT staff with which to accomplish them. It is the same old one-two punch that has been knocking out projects since cars ran on steam. (Well, trains anyway.)
Lack of IT staff and a resistance to investing in a staff–education and training is what I’m talking about–has put many companies in the position of hiring the experts who can onboard new technology while the existing staff handles the day-to-day duties. You may have noticed that IBM and many of its business partners have expanded their services departments to fill the need.
Some would say it is the higher degree of IT complexity–not just relative to business intelligence and analytics, but in every nook and cranny of IT–that scares companies from hiring and training their own staffs. IBM’s DB2 Web Query has been labeled as too complex and too costly since it was introduced in 2008. Is that an unfair categorization and a widely held misconception or is it just a reflection of the restrictive budgets that are strangling corporate investments overall?
For insight into BI on i, I turned to Doug Mack, a DB2 for i business intelligence consultant within the DB2 Center for Excellence team (part of IBM’s Lab Services organization). DB2 Web Query implementations and education are a big part of his work day. In March, he will be one of the speakers at the RPG & DB2 Summit, which is including a focus on DB2 for i capabilities, tools and self-management techniques. That event is scheduled for March 19 through 21 in Atlanta, Georgia.
Mack has been involved with analytics on this platform since the mid-1990s, when IBM started considering what the OS/400 community needed beyond the boundary of Query/400. The result of that was the delivery of DB2 Web Query near the end of 2007. At that time, he was product marketing manager for DB2 and business intelligence.
The topic of complexity was at the top of my list of questions.
“I don’t believe it is necessary to have consultants in order to use Web Query,” Mack explains. “Most customers can get a pretty good knowledge through our tutorials. It’s really the jump start that a little hand-holding provides, along with best practices that make things go more smoothly. I would say that 10 to 20 percent of companies using Web Query are contracting for services. It is, however, significantly different from a Query/400 environment.”
“A couple of things stand out. For one, Web Query was designed to incorporate a metadata layer, which is a fairly common practice in enterprise business intelligence tooling. That’s a foreign idea to most IBM i customers that have been running Query/400 reports for 20 years.”
“Some people think the metadata is where things get overly complex,” Mack says. “But it’s actually just the opposite. The metadata provides a description of the database. It takes a manual process that must be defined in every Query/400 report–like one that defines how two tables are joined–and allows that relationship to be defined only once. The advantage in the long run is huge in time savings.”
The importance of metadata brings Web Query closer to the concept of database modernization. Rules can be built into the metadata that provide shortcuts when doing future database development, and documenting the database with the metadata is another benefit that dovetails nicely with modern database management techniques.
“Web Query is not doing database modernization, per se, but it is providing value through a modernization approach,” Mack says. “It is not, however, touching the underlying database structure.”
“There is overlap in terms of a DB2 modernization context that could begin with Web Query, but in some cases the budget is only big enough to build some dashboards. As part of doing that, we can talk about database modernization and Query/400 modernization, or building best practices around improved databases performance. It all interrelates.”
“Sometimes we work more with the application development team to help them move to an SQL environment. That often leads to a discussion about query modernization and they go hand in hand. We have seen this happen a lot actually.”
From Mack’s perspective, the use of metadata is not terribly complex. Yes, he is an expert in Web Query and DB2, but he also works with and teaches people with average-size programmer brains.
Web Query, Mack says, is “robust” and, as an example, has many ways to build a dashboard. Some say that capability is over-engineering and that is another reason the product gets tagged with the complexity label. Mack deflects that criticism by saying it is designed to provide options for a variety of customer needs.
For instance, when customers require a dashboard that works well with mobile devices such as smartphones and tablets, Web Query includes a technology that maximizes the format rather than using a technology that attempts to fit a round peg in a rectangular hole.
“There may be different definitions of dashboards within a single project,” Mack notes. “But most often the dashboard is providing a single view of multiple reports. The reports can be built independent of the dashboard and the actual building of a dashboard could be fairly simple if done with a limited number of controls (radio buttons, are a common example) to manipulate the data.”
But adding multiple tabs and more complex reporting and functionality can make them more complex. There are projects he has worked on that are complex because customer requirements are complex and their expectations are high. For instance, embedding a dashboard into a line of business application like a Web-based ERP system built in-house. Getting a dashboard or report to execute within an ERP system can be a bit more difficult first project.
The majority of Web Query projects are not complex deployments, he reiterates. Some that he has witnessed are easily completed after a prototype application is created and the basic skills transfer takes place. Outside of Lab Services, it should be noted, there are also collections of individual consultants and some IBM i business partners that have acquired skills built around Web Query and are helping organizations with the start-up of business intelligence and analytics projects.
Again, is that so because of the complexity of Web Query or due to the skeleton staffing practices that make new project deployments unattainable unless outside help is brought in? Mack believes that any BI implementation would benefit from some assistance.
“You don’t always know what you don’t know,” he says. “Performance is a good example. The single biggest thing you can do to make a BI project fail is not pay attention to the performance of the queries.”
Bringing in the experts does have some benefits that come with experience. Query optimization is one of the advantages. In addition, pitfalls and wheel spinning can be limited and implementation and integration times can be shortened. But if that is done without a skills transfer to the IT staff, that seems to be a lost opportunity to upgrade IT talent internally, which pays off over time.
For a deeper dive into Web Query, see the IBM Redbook titled DB2 Web Query Tutorials Advanced Functions.