IBM i integration improves operational queries and analysis

May 16, 2022

Bill Langston

Modernization, SQL, RPG, open source, cybersecurity, resiliency, compliance, clouds and AI are trending topics in enterprise technology. As a result, IT pros can easily overlook and undervalue software that enables real-time operational queries and analytics.

In contrast, managers and mid-level staff in accounting, logistics, inventory, sales, customer service, and other departments still place a high value on access to data and often want their team IT can do more to help them. Collaboration is key, because your business isn’t realizing the potential of operational queries and analytics if you’ve narrowly defined them as a dashboard and ad hoc file transfers to Excel.

Shawn Amick is the CIO of Ebix, a medical billing service provider based in Greendale, Wisconsin. Ebix uses query and analysis software on IBM i to optimize workflow, produce client payroll reports and analyze staff productivity. Its clients include group medical practices, in a variety of specialties, including emergency services, multi-specialty practices, ambulance services and specialty pharmacies. Here is how Amick describes the use cases:

We differentiate ourselves in our market by handling claims accurately and collecting payments quickly. We perform many ad hoc queries and produce tons of detailed reports. We strive to provide our clients with the exact level of information that helps them make their key business decisions. Within our company, we know that insurance companies will reject a claim for the slightest reason. We use queries to present our billing specialists with the exact claims that need their attention to ensure that claims are paid quickly and accurately.

Doctors often don’t have time to analyze spreadsheets, but they want to see if they’re being paid properly. We customize our data output to match the desires of this practice. Some want great levels of detail that they can ingest in a way that suits them; others just want a page with the totals. We frequently help practices review the productivity of their own physicians. Most services rendered by a physician have a Relative Value Unit (RVU) defined by the Center for Medicare Services (CMS). We use queries to apply RVU to a conversion factor based on a complex set of rules to calculate physician compensation. We deliver the result in Microsoft Excel with the totals and supporting details expected by physicians. It is important for us to control and secure these requests on IBM i to ensure data integrity. Some doctors like to see their data visually, so we use Microsoft Power BI to create dashboards from the data our queries feed into Excel.

We also use queries to develop multidimensional models that monitor the productivity of remote staff members. Multiple parties can view the same set of data, and each can customize different views of their work, including time of day, client they work for, and more. The best part is that when we get questions about the data, our team can drill down and see the detail behind the numbers. By working directly on our IBM i database, any additional data we need is easy to add.

Juan Cavallo, a longtime IBM i banking executive and now senior technical specialist at Focal Point Solutions Group, explains how the bank he worked at used operational queries and analytics:

NGS-IQ query and reporting software allows a bank I worked for and now support to automatically generate customer notifications and statements and send them to secure mailboxes. Their nightly processes use queries to generate and distribute 200 reports to various departments in Adobe PDF and CSV format. As a bank, they are required to send specific reports to their banking authority. One of them is the quarterly call report that documents the bank’s financial condition for the Federal Deposit Insurance Corporation (FDIC). The bank I work with has added a menu option to their application software to run the queries that produce this report.

Having a query tool with advanced functions reduces the number of queries executed. The bank can even use the query software with CL programs and IBM i job scheduling to monitor QSYSOPR and QSYS message queues and send emails to our IT operations staff.

QSA Global manufactures non-destructive testing equipment for pipelines and other industrial applications. The company runs the Infor XA system on IBM i. QSA’s operations are tightly regulated, due to the use of radioactive materials in its products. Enterprise systems programmer Jack Haney is QSA’s lead query developer. It supports users in multiple places in materials, finance, customer service, sales and other departments, and it is the scope of business operational queries:

We use queries in different ways. For example, instead of looking at multiple screens, a query for the materials department provides all the information they need to make planning and purchasing decisions. Customer service relies on queries sent to Excel to display customer purchases and sales trends. We use IBM i job queries and scheduling to produce and upload a daily report required by the US Nuclear Regulatory Commission (NRC). We also use queries to create and upload Automated Clearing House (ACH) files for our bank.

Our quality control manager uses a multi-dimensional model (developed with our IBM i query and analysis software) to monitor accepted and rejected lots by lot number, rather than by item. Some users rely on scheduled queries to deliver files as email attachments, and others run their own queries to pull data into Excel.

Match software to user skills

Your query and analysis software should match your users’ skills and technical support expectations. For companies like the ones above that rely on in-house staff to develop and maintain their query and analytics processes, the highest return on investment may depend more on how well the chosen vendor supports that staff. than the set of tools that has the most functionality.

Why? Most IBM i users who need to extract and share custom datasets, examine reports, and visualize operational data are not data scientists, full-time business analysts, or managers. of trained databases. Many of these users may not even be interested in writing or running queries, but they need timely data to make operational decisions that impact business performance. A solution that leverages the automation capabilities of IBM i is essential when your goal is to integrate operational queries and analytics into your applications and day-to-day processing.

Many query and analytics vendors build their business model around cultivating a network of consulting firm partners who recommend and implement the solution. With this go-to-market strategy, the channel’s needs strongly influence development priorities. A solution designed to satisfy domain experts may require more time to master than your internal staff can devote. This problem causes many companies to struggle with query and analytics software, which presents a steep learning curve for their internal staff or a significant cost barrier due to ongoing consulting requirements.

To further complicate the selection of software for IBM i customers, IBM i is not in the wheelhouse of many analytics consultants. Even though Db2 on i is a powerful relational database, most of us recommend what we know best when it comes to troubleshooting. In the query and analytics consulting niche, this is often a solution optimized for Microsoft SQL Server and non-IBM databases. These solutions have their own strengths, but they are not designed to integrate with your IBM i applications and business processes.

Time-tested, but underused

IBM, New Generation Software (NGS), and others have provided query and analysis tools for IBM i and its predecessors for decades. A few Google searches may bring up other providers. The subject is not new, but that does not mean that most companies have optimized their methods or that operational queries and analyzes have lost their relevance. In fact, companies concerned about an IBM i talent shortage should take a fresh look at IBM i-based query and analytics solutions like NGS-IQ. This class of tools arguably originated the concept of using no-code development to extend legacy enterprise software, and they have continued to improve over time.

Integration: the secret sauce of operational query and analysis

IBM i is designed to help businesses operate with a lean, business-focused IT workforce. However, responsibility for queries and analytics often falls into a no-man’s land between operations management, application development, and supporting IT infrastructure. Effective operational queries and analytics require a collaborative effort across departments. The value of an IBM i-based solution increases when you embed operational queries and analytics into your IBM i applications, workflows and day-to-day operations, while simplifying data access and visualization for end users.

Bill Langston is Director of Marketing at Next generation software.

This content has been sponsored by New Generation Software.

RELATED STORIES

NGS Breaks the Scalability Barrier with the OLAP Client

NGS and LightEdge offer a new entry into the cloud

Data Analytics and Business Intelligence: too little, too much, too late?

NGS clings to Query/400 to protect BI investments

NGS makes DB2/400 data accessible from MS Office

NGS boosts BI offerings for System i

NGS provides a quick “dashboard” view of business performance

Comments are closed.