IBM i Integration Elevates Operational Query and Analytics

May 16, 2022 Bill Langston

Modernization, SQL, RPG, open source, cybersecurity, resilience, compliance, clouds, and AI are the trending topics in enterprise technology. As a result, IT professionals can easily overlook and undervalue software that enables real-time, operational query 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 data access and often wish their IT team could do more to help. Collaboration is the key, because your company isn’t realizing the potential of operational query and analytics if you have narrowly defined it as a dashboard and ad hoc file transfers to Excel.

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

We differentiate ourselves in our market by processing claims accurately and collecting payments fast. We run lots of one-off queries and produce tons of detailed reports. We strive to arm our clients with the exact level of information that helps them make their key business decisions. Within our business, we know that Insurance companies will reject a claim for the smallest reason. We use queries to present our billing specialists with the exact claims that require their attention in order to ensure that claims are paid quickly and accurately.

Often doctors don’t have time to analyze spreadsheets, but they do want to see if they are being paid correctly. We customize our data output to match the desires of that practice. Some want great levels of detail that they can ingest in the way that they are comfortable with; others want just one page with the totals. We frequently help practices look into the productivity of their own doctors. Most services a doctor performs have a relative value unit (RVU) set by the Center for Medicare Services (CMS). We use queries to apply the RVU to a conversion factor based on a complex set of rules to calculate the doctor’s pay. We deliver the result in Microsoft Excel with the totals and supporting detail the doctors expect. It’s important for us to control and secure those queries on IBM i to ensure the data integrity. Some doctors like to see their data visually, so we use Microsoft Power BI to create dashboards from the data our queries deliver to Excel.

We also use queries to develop multidimensional models that monitor the productivity of remote staff members. Multiple parties can look at the same dataset, and each can customize various views into their work, including hour of day, the client they are doing work for, etc. The best part is when we receive questions about the data, our team can drill down and see the detail behind the numbers. By working directly over our IBM i database, any additional data we need is easy to add.

Juan Cavallo, a long-time IBM i banking industry executive now serving as a senior technical specialist at Focal Point Solutions Group, explains how the bank he used to work at used operational query and analytics:

The NGS-IQ query and reporting software enables a bank I worked for and now support, to automatically produce client notifications and statements and send them to secure mailboxes. Their overnight processes use queries to generate and distribute 200 reports to different departments in Adobe PDF and CSV format. As a bank, they’re required to send specific reports to their Banking Authority. One of them is the quarterly Call Report which documents the bank’s financial position for the Federal Deposit Insurance Corporation (FDIC). The bank I work with added a menu option to its application software to run the queries that produce this report.

Having a query tool with advanced functions reduces how many queries are run. The bank can even use the query software with CL programs and IBM i job scheduling to monitor the QSYSOPR and QSYS message queues and send email 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 closely regulated, due to the use of radioactive materials in its products. Jack Haney, business systems programmer, is QSA’s primary query developer. He supports users at multiple locations in materials, finance, customer service, sales, and other departments, and this is the scope of operational querying at the company:

We use queries in a variety of ways. For example, instead of looking at several screens, a query for the materials department provides all the information they need to make scheduling and purchasing decisions. Customer service relies on queries that output to Excel to view customer purchases and sales trends. We use queries and job scheduling on IBM i 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 multidimensional model (developed with our IBM i query and analytics software) to monitor lots accepted and rejected by the number of lots, instead of 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 analytics software should align with your users’ skills and technical support expectations. For companies like the ones above, who rely on in-house staff to develop and maintain their query and analytics processes, the highest return on investment may depend more on how their chosen provider supports that staff than on which toolset has the most features.

Why? Most IBM i users who need to extract and share custom datasets, review reports, and visualize operational data aren’t data scientists, full-time business analysts, or trained database managers. Many of these users may not even be interested in writing or running queries, but they do need timely data served to them to make operational decisions that impact company performance. A solution that leverages IBM i automation features is critical when your goal is to integrate operational query and analytics into your applications and daily processing.

Many query and analytics software vendors build their business model around cultivating a partner channel of consulting firms 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 in-house staff can spare. This problem causes many companies to struggle with query and analytics software – presenting a steep learning curve for their in-house staff or a significant cost barrier due to ongoing consulting requirements.

Further complicating software selection for IBM i customers, IBM i isn’t 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 problem solving. In the query and analytics consulting niche, that is often a solution that is optimized for Microsoft SQL Server and non-IBM databases. These solutions have their own strengths, but they aren’t designed to integrate with your IBM i applications and operational processes.

Time-Tested, But Under-Utilized

IBM, New Generation Software (NGS), and others have offered query and analytics tools for IBM i and its predecessors for decades. A few Google searches can turn up other vendors. The topic isn’t new, but that doesn’t mean most companies have optimized their methods or that operational query and analytics has lost its relevance. In fact, companies concerned about a shortage of IBM i talent 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 business software, and they have continued to improve over time.

Integration: The Secret Sauce of Operational Query and Analytics

IBM i is designed to help companies run with a lean IT staff focused on business. Nonetheless, responsibility for query and analytics often falls into a no man’s land among operations management, application development, and supporting IT infrastructure. Effective operational query and analytics requires a collaborative effort across departments. The value of an IBM i-based solution rises when you integrate operational query and analytics into your IBM i applications, workflow and daily operations along as well as simplify end-user data access and visualization.

Bill Langston is the director of marketing at New Generation Software.

This content was sponsored by New Generation Software.

This Issue Sponsored By

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search