Monarch

Turn printed reports into data sources

Have you ever run a report and created a print spool file, only tofind out later that you deleted your database and now need to repopulate it fromthe spool file? Do you routinely find yourself running dozens of reports thatcontain 95 percent identical information, with minor fields omitted here andthere depending on the recipient?

Monarch 3.0 from Datawatch is a data access and analysis tool that lets youcollect data from spooled report files. With Monarch, you can take a commonreport such as a payroll listing and either convert the information into anotheroutput report or import the report data into a file format such as a generic DBF(xBase) or CSV (comma-separated) file. Datawatch refers to this process asreport mining.

Prospecting
Suppose you have a large healthcare installation and run a nightlypatient-billing job to print invoices for a portion of your patients. If thefacility has a comprehensive billing system, a typical report might take hoursto complete. Running several reports to allow for minor variations in output canbe costly because of the time and processor resources it requires. With Monarch,you can instead run one billing report and feed the spooled output file fromthat report into Monarch to create additional customized reports.

Monarch also lets you convert other databases to SQL Server. For example,converting a UNIX FilePro, AS/400 DB2/400, or VAX/VMS Oracle database toSQL Server is not easy. Monarch lets you write a small application program toprint the contents of each record in your database and convert the printedreport into a database format you can easily load into a SQL Server database.

Reducing your report distribution costs is also an issue. Rather thandistribute hundreds or thousands of pages of output to your employees, why notdistribute reports to them electronically? With Monarch, you can convert yourprinted report into a compact porTable report format (.PRF) file that you canattach to an email message. Monarch registers the .PRF file type in the WindowsNT Registry; when you double-click the attachment in your mail reader, itlaunches the appropriate report viewer so you can review the report.

Of course, these benefits do not come without a price. Although simple inconcept, Monarch does have a moderate learning curve, especially if your reportsare complex.

Staking a Claim
Installation of Monarch components is standard and uneventful. The basesoftware package includes three components. The first component is the Monarchapplication program. This program lets you write the templates you will use onvarious input files to perform redistribution and repackaging functions. Thiscomponent arrives on three 3.5" floppy disks and consumes less than 10MB ofdisk space when fully installed.

The other two components are two versions of the Monarch Report Explorer.This module lets users view and print the contents of .PRF files you createusing the main Monarch application. The Report Explorer program arrives in astandalone version and as a Netscape Navigator plugin. The standalone programlets users work from any Intel-based Windows machine, and the plugin lets usersview .PRFs from the Intel-version of Netscape Navigator and Microsoft InternetExplorer and see .PRF files across the Web.

Panning for Gold
Suppose you want to create a .PRF file, or export the data from a spooledreport to another data format. You must use Monarch with a three-step process.

First, after running the main Monarch application, select the input fileyou want to process. You accomplish this task through a series of file anddirectory selection dialogs.

In the second step, you create a data extraction template, which is ablueprint for how Monarch will interpret your report's data. At the simplestlevel, you have a detail template, which extracts data from detail lines on areport.

To create a detail template, select a sample detail line in your report.Screen 1 shows a sample template. To define a detail template, press [ALT]-[T]to select the template menu-bar, and [D] to chose the Detail option on the menubar. This process initiates a Template Definition Dialog. Here, you create aseries of traps, or points where Monarch will collect data. Traps helpMonarch differentiate detail data from other data (such as header or footerdata) in your output file.

Output files are rarely formatted simply. You're more likely to havereports with multiple section breaks, such as detail reports sorted on variouscriteria, where reprinting the common data from each detail record is redundant.To properly extract data from these reports, you use Monarch's "1st-append"and "2nd-append" templates. With these template definitions, you tellMonarch which data on the report is acting as a control-break for the printout,and have Monarch automatically recapture that data for you.

Once you've defined your data template, in the final step you can view theMonarch Table Window to examine how Monarch will interpret your report. AsScreen 2 shows, from the Table Window, you can examine the data from the reportin a tabular format, similar to a data sheet view in a Microsoft Accessdatabase. For each column in the Table, Monarch extracts data values from youroutput report and stores them in the database. You can manipulate the order ofthe fields in the Table, provide names for the individual columns, and establisha data type (character, date, numeric) for each column.

Once you conFigure your Table, you can print your output or export the datato another application. Monarch can export the data it collects to xBase DBFfile format; Excel spreadsheet XLS format; Lotus spreadsheet WKS, WK1, and WK3formats; and delimited and non-delimited ASCII text files (CSV).

Before exporting your data, you can massage it. For example, you can filterout certain records or key fields, or you can change the output's sort order. Tofilter certain records, you need to create a filter expression, aBoolean operation that uses the names of each column with different operatorsand conditional statements. You can create simple filters, such as "order_amt> 0," or complex filters with AND, OR, and NOT conditions.

To sort your Table's data, you create a sort definition with thissimple process, you select fields in the order in which you want to sort theTable's data and select each field's sort order, either ascending or descending.For example, if you want to sort a Table of customer orders by customer numberand most recent order date, you might select the customer number (in ascendingsequence) as the first sort field and the order date (in descending order) asthe second sort field. To restore the original order, delete the sortdefinition.

Monarch includes several advanced features that you can use to manipulateyour Table's data and presentation. For example, you can add subtotals and blanklines to a summary printout. For this process, you define the key field and thesummary field. When the key value in the key field changes, Monarch prints asubtotal of the summary field for all the records with the same key value. Youdon't need complex COUNT or SUM functions.

You can also create drill-down reports that let you view a summary recordin a Table. When you select an individual record, all the supporting detailassociated with that record expands on the screen.

Finally, Monarch lets you create charts and graphs. With your detail orsummary data, you can instruct Monarch to build line, bar, and pie chartsshowing various relationships among components of your data.

For instance, if your company sells different categories of items, you canuse Monarch to create a bar chart that details your company's yearly sales ofeach category. You don't need to export the data to Excel to create the report.

Finding a Nugget
Monarch is an unusual program that lets you realize additional value fromthe investment you've made in printed reports or legacy databases. In these daysof data mining and data warehousing, companies are looking for every possiblesource of information to analyze business practices. Monarch lets you extendyour search for meaningful business information to the printed reports sittingon your desktops.