KCI Updates Excel-Based Business Intelligence Tool

KCI Computing recently shipped a new release of CONTROL, a business intelligence tool that runs on the iSeries and uses Excel as its primary user interface. By pulling data from DB2/400 and other back-end data stores in real time, CONTROL enables analysts to use Excel to create up-to-the-minute forecasts and budgets. Version 8.7 adds new features in the areas of security, scheduling, and customization of the interface.

CONTROL is a suite of client and server components that, technically, is a relational online analytical processing (ROLAP) tool, according to a company representative. The tool’s main forte is providing financial analysts and other users with a centrally managed tool for creating models, forecasts, financial reports, and budgets, and providing real-time decision support. Once the data mapping between CONTROL and the backend data store has been defined, users are free to explore and read from the database, and write back to it, from within the friendly and familiar confines of Excel.

Providing an Excel front end to a ROLAP server enables non-technical users to slice and dice data, to build colorful graphs, and to drill down to any level of detail, without having to worry about periodically synchronizing their spreadsheet with the latest data or manually re-typing data into Excel, which can introduce errors. If a real-time connection is not needed, CONTROL can be set to connect to the back-end data store periodically.

Keeping data and reports centrally located is beneficial for a couple of reasons. First, it bolsters security by enforcing user and group access to data. Secondly, it prevents the problem of having multiple and disparate copies of spreadsheets stored on local machines. Having a single version of the truth is particularly important in this age of increased regulatory oversight into companies’ financial reporting practices, according to Naida Shaw, vice president of operations and administration at KCI, which is based in Torrance, California.

“Companies that are publicly traded, and doing forecasting, need to know who changed what and when,” she says. “By keeping a centralized database, if they ever have to go back and rationalize, why was that forecast basis changed,” they have a log of that activity. “So you never have an occasion where somebody says, ‘I’m going to just hide these six rows, and do some computation.’ Those types of thing could never happen when you have a centralized repository,” she says.

With CONTROL version 8.7, the company has added several new features. On occasion, users will need access to the tool for a short period of time to build one-off reports, and this is now possible through new ad-hoc reporting capabilities in CONTROL. New “sheet refinements” in version 8.7 should make it easier for users to customize their Excel interfaces with new buttons that do things like open a book or a view, navigate to a particular report or entry form, run a transform or a mapping, or navigate to a home menu, the company says.

Administrators can make better use of their server’s time with the new “remote asynchronous” job module, which lets administrators set CONTROL tasks to run on different servers, or at odd hours. Lastly, the computational overhead of accessing commonly accessed reports from relatively static data can be minimized with the new “view snapshots” feature.

Works with OS/400

KCI has customers using CONTROL with OS/400 servers and DB2/400 data. One of these customers is PMI Dental Health Plan, a subsidiary of Delta Dental Plan of California that provides DeltaCare coverage to approximately 1.3 million people in nine states and the District of Colombia. According to a case study available on KCI’s Web site, PMI Dental looked to CONTROL to help streamline a convoluted reporting process that involved running numerous manual queries to extract data, such as enrollment, subscriber, revenues, claims, and cost data, from its legacy data warehouse and Lawson Software‘s general ledger, and load it into Excel.

Once PMI Dental’s data was in Excel, the company would generate necessary reports, and then re-key the data into a budgeting program called SMART. In addition to SMART not having the level of forecasting capabilities that the company needed, the multi-step process made it too hard to formulate support plans to support the company’s strategic objectives. PMI needed a simpler solution, and found one in CONTROL, which the company installed in the summer of 2001. “KCI bent over backwards and proved that CONTROL works fine on an AS/400 platform,” says Mel Aou, PMI’s manager of financial planning and analysis, according to the case study.

Depending on scalability and user interface requirements, there are several options when it comes to deploying CONTROL, including Web and thick-client interfaces, and two-tier and three-tier architectures. With the two-tier architectures, the CONTROL Application Engine, which houses the metadata and handles communication with the back-end data source via SQL, runs directly on the PC where Excel is installed, or, alternatively, it runs on the Microsoft IIS Web server that’s serving HTML to the Web browser client. With the three-tier architecture, the CONTROL Application Engine and IIS Server run on their own servers, or they can be run on the same server (a Windows server with 3GB of memory is recommended).

While the product literature indicates CONTROL runs on Windows servers, it also runs natively on the iSeries, Shaw says. In fact, KCI views the iSeries as a potentially fertile market for business intelligence tools such as CONTROL. “We’re working on getting more iSeries customers. We would like to be bigger in that market,” Shaw says. “It’s powerful, with a very good operating system and database. There are not a lot of [business intelligence] tools that can directly work in the iSeries.”

KCI prices its software based on the number and type of users. An administrator’s license, which is needed for managing security and defining the roll-ups, costs $4,500 each. A license for a power user, or “information navigator,” who access CONTROL through an Excel spreadsheet, costs $800 each. Web licenses, which are used by casual users, cost $400 each. There is also a server fee. All told, the average installation cost for 100 users is around $100,000.

CONTROL version 8.7 is available now. For more information, visit KCIs’ Web site at www.kcicorp.com.