NGS Brings Modern DB2/400 Query Development to BI Suite

August 28, 2007 Dan Burger

At the heart of business intelligence solutions is the capability to easily and quickly take care of query and report-writing tasks. In the IBM native System i, iSeries, and AS/400 environment, such tasks have been the primarily confined to green-screen development. Well, guess what? It’s time for graphical point-and-click, drag-and-drop query and report writing courtesy of New Generation Software, which earlier this month introduced IQ Client, a new query development module within the NGS business intelligence product suite.

IQ Client will likely surprise many veteran green-screen developers and power uses with its query-building functionality. This is not a watered-down version of what they have grown accustomed to in terms of capability. Even hard-core green-screen advocates should find much of this Windows-based functionality to their liking. NGS officials predict productivity gains for all but the most averse to trying new methods.

It’s also going to appeal to casual users who want to build or revise queries, but were hesitant to do so in the past because it required them to make a trip outside of the Windows environment. In addition to IQ Client, NGS offers a product called Qport Builder, which uses screen-scraper technology to provide a Web interface for those who prefer that environment.

In designing IQ Client, NGS made certain that it retained the complete functionality of IQ Server, the native i5/OS (OS/400)-based query engine for the company’s business intelligence offerings that have been rooting out and piecing together financial, sales, customer, vendor, and inventory performance data for more than 15 years.

Queries, from brief ad hoc requests to large production reports, can be created and revised and then run from NGS-IQ’s Web browser and 5250 user interfaces. Not only are there no query translation or conversion requirements, but for those of you experienced with DB2/400 (DB2 for i5/OS), you’ll appreciate the benefits of native, batch query processing, and security that the platform provides. Those are more than mere conveniences when compared to the hazards of query building done outside the platform. IQ Client also allows users to work with or without a live System i or Internet connection, which is a convenience factor that many remote workers will appreciate.

Another convenience feature is that queries developed by staff with advanced green screen skills can be downloaded by non-technical users and used within the Windows interface. Some of these may contain certain data fields that are not intended for all eyes to see. Security should always be a concern when query and reporting is the topic. Access to sensitive files and objects is controlled through the System i (highly secure for those who know how to make use of it) and a security management module (IQ SeQure) that is sold as an add-on to NGS-IQ. The NGS module restricts access by file, field, record, and output.

Yet another convenience is IQ Client’s one-click connection to a variety of integrated functions through Qport Access, the client module of NGS-IQ that provides direct output to Microsoft Excel, Access, and Word; NGS-IQ’s multi-dimensional OLAP module; display mode; to print mode; and output defined on the server, such as dynamic Web NGS dashboards, PDF, HTML, DB2 UDB (summary or detail), XML, CSV, and TXT. The result is that queries can be easily built, saved, and output by using icons on a screen.

For the pros at query development, IQ Client supports simple and conditionally calculated fields; multi-file joins; run-time prompts for selection criteria and/or calculations; date arithmetic; report break summaries and calculations, rankings, running totals, averages, and percentages; extensive formatting; e-mail and FTP delivery.

New Generation Software has been actively building out its business intelligence suite in 2007. In March it introduced Web-based dashboard applications and a new release of its flagship IQ Server. Its focus has been on delivering key performance indicators (KPIs) graphically in a Web browser and being able to continually update and refresh that information from DB2/400 (via hooks into the NGS-IQ server), without additional work or input from the user.

IQ Client pricing is based on the P group rather than by named user. The reason, NGS officials say, is so customers have the freedom to install this software on as many PCs as they like without worrying about the number of users they have licensed. Because IQ Client supports offline development, the queries users develop are ultimately processed in batch by the integrated NGS-IQ software on the System i, where the customer controls their workload by concurrent user. Any number of people can simultaneously develop queries with IQ Client, without consuming any i5/OS cycles, but the user decides how many of those queries can be run simultaneously. From the NGS point of view, this is more practical than a named user pricing model that forces customers to enroll each user and keep track of who has the software as personnel changes occur.