Dickeson--Give Poor Roger's OLAP a Look

Most of us in printing use relational database technology for accounting and production files. Job-cost and general-ledger files we keep in relational tables; transaction processing is what we do.

That's cool, but industry is zipping by us, leveraging data to make critical decisions. Printers are living in the days of OLTP: Online Transaction Processing. But the world around us is moving into variants of OLAP: Online Analytical Processing technology.

Let me use a personal example to illustrate. I've maintained a database of web printers for nine years. It's a "relational" database consisting of four tables or files. Table One is for companies. Each company has an ID, a number that identifies it. Table Two describes the dozen or so products that presses produce. Table Three details the presses, their IDs and the IDs of the companies operating them, as well as product IDs. Lastly, there's a "transaction" file: Companies send me monthly operational data for their presses, and I enter a record for each press, including the ID numbers of the company and the press, as well as the data for time intervals and impressions.

It's a "rationalized relational database" set up for OLTP. Each transaction record is "related" to a company, a press and a product.

Know What to Know
It's easy to enter transaction data each month: I don't have to repeat a string of data for each company, press and product. Nor do I use up a lot of disk storage space or "read" time for the disk drive heads. I publish structured reports using special report-writing software that "joins" the tables using the ID number relationships.

That's also the way our general-ledger and cost-accounting systems work. They're designed to speed entry of transactions at different terminals feeding a network. They use "rationalized" tables that relate to transaction tables for joining by the computer in data processing. Fine and dandy, as long as you know what you want to know.

The trouble with us humans is that we think. We think we know what we must have when we design a system but, at the design stage, we don't know what we don't know. That's where that Latin phrase "ad hoc" enters the scene: We demand answers from a system for questions that occur to us as we ponder some decision or action. Ad hoc questions occur in this specific situation. We want to cross-examine those computer data tables, regardless of transaction entry ease at this point; we want analytic ease for decision support. Right now.

Here's where OLAP technology makes its entrance. In place of our now-outmoded relational databases, which are basically two-dimensional (columns and rows), we can have multi-dimensional data cubes or hexagons designed for analysis. OLAP databases enable you to cross-examine humongous tables with great speed.

Or there are OLAP tools for those not-so-speedy and incomplete answers from existing, smaller relational databases. Great stuff, but expensive and mucho installation time. Or even Poor Roger's "OLAP for Dummies," called Pivot Tables. It's nothing fancy or akin to Walmart's inventory stocking system, but it works to answer many ad hoc queries.

Pull your transaction table for whatever transactional database into MS Excel. Click on the data menu. Select Pivot Table Report. Now you're on your own to follow the Wizard and use Poor Roger's OLAP—PROLAP.

Pivot Points
In my web press database, I asked: "How long is it taking to makeready a press?" Zap. It searches 5,200 transaction records since October 1991 and shows me. I drill down in cross-examination. "How long does it take "fictitious press" to do that?" Zap again. "How long for each of their presses?" Bingo. "Hey, Ma, look at me. I'm OLAPping—no hands!"

Pivot Table capabilities are built into MS Excel and Access, Lotus Approach and 1-2-3, Seagate Crystal Reporter and others. Sometimes they're called cross-tabs. In the freebie StarOffice from Sun Systems, they're Pilot Tables. All of them enable redimensioning columns and rows in database tables, so that rows become columns and vice versa. They may vary in mathematical functionality. Redimensioning a table may lead to aggregating table data into sums, averages, variances, standard deviations, etc. It truly is PROLAP.

Go on, ask me how fast plants are running a given make of press or how much the average running waste is. I'll tell you and then ask you which model of the Heidelberg M-1000 you want to know about. Try it on your sales files and do some marketing analysis by customer, salesperson, region or product type. Ask me if catalogs take longer to makeready than books. Ask your inventory database how many butt rolls of 38-lb., 34˝ coated groundwood are available.

In place of transactional reports from OLAP, you're getting analytic answers to support decisions about stocking, core competence, sales, etc.—on an ad-hoc, drill-down, data- mining basis.

So why aren't you and other printers using pivot technology? We know that the economy is booming with Knowledge Worker capabilities, the decision-makers who need and use OLAP. But our computer tech people are so engrossed in "rationalizing" two-dimensional, relational databases for transaction efficiency, they can't spare the time for Excel or Lotus analytics, even though their tables are ODBC-enabled (ODBC is Open Data Base Connectivity).

Or they don't want you messing with their stuff. Or they haven't figured out how to Pivot. It does take a day or two. I would know!

So try Poor Roger's OLAP—PROLAP. You'll like it.

—Roger V. Dickeson

About the Author
Roger Dickeson is a printing productivity consultant based in Tucson, AZ. He can be reached by e-mail at Roger@prem-associates.com, by fax (520) 903-2295, or on the Web at http://www.prem-associates.com.