 | | | | | | |
 | ||||||
 | ||||||
| Spring 1992 | ||||||
| ||||||
 | ||||||
| ||||||
| Data Dashboards For building applications that connect to industrial-strength servers, it's what's up front that counts by Daniel Miles Kehoe Part I: Front-end database tools Pick your buzzword: Client-server computing. Enterprise computing. Distributed databases. Rightsizing. At NeXT, the au courant phrase is "operational productivity." It is an article of faith today that the best way to manage information resources is with centralized databases residing on networked servers and client applications manipulating the data from desktop computers. Gone are the days of mainframes and drone terminals; equally passÝ is the notion of managing organizational data on stand-alone or networked personal computers. Client-server computing provides security, reliability, and flexibility by splitting applications into two parts: front-end client programs on locaworkstations, which interact with the user, and a back-end application server on a central computer that manages data, processing requests from clients and returning manageable sets of data across the network. Client-database applications make requests of data servers in Structured Query Language (SQL, often pronounced "sequel"). The lingua franca of client applications and servers from many vendors, SQL makes shared data possible. Servers may store data in peculiar file formats and know nothing of a client application, but they will respond to a SQL request by returning data to the client. NeXT endorses this model with support from SQL servers such as Oracle and Sybase. The central hardware platform may be a specialized database server like the Sequent Symmetry, Pyramid MIServer, or Sun SPARCserver 690MP ± or (in smaller companies) it may be a workstation like the Data General AViiON, the IBM RS/6000, or the NeXTstation. On the client side, built-in networking makes NeXTSTEP ideal for connections to shared databases. In Part I of this report on the NeXT database market, NeXTWORLD surveys client applications that speak SQL and connect to database servers. We will explore the world of database servers in Part II of this report in a future issue. Databases defined Computers may be data machines, but it takes database software to organize data. Flat-file database applications, such as Stone Design's DataPhile or Teledia's FileMaster, structure data in a single table, much like the rows and columns of a spreadsheet. The system is fine for simple lists and is easy to maintain, but as soon as one record has data that doesn't fit (a person with two phone numbers, for example, while all other records have only one), the single-table structure becomes cumbersome. A relational database management system (RDBMS) allows the use of multiple tables. Names and customer IDs may form one table; phone numbers and customer IDs a second; and shipping addresses, billing addresses, and customer IDs yet another. When data is needed, relational tables are joined by a common key (the customer ID in our example). Tables can be kept to a manageable size and more closely resemble the unevenly structured real world. Object-oriented database management systems (ODBMS) can model the real world even better. But there are no tables in an ODBMS; instead, containers hold objects comprised of data and behaviors. Objects are linked by chains of inheritance ± thus a request to an inventory object for a count of writing instruments yields both pens and pencils, and a request for pens yields fountain pens and blue ballpoints. Right now, relational-database applications are easier to implement, because ready-made RDBMS development environments are available, and perhaps because contemporary computer users find it easier to think of the world in terms of tables rather than taxonomies. For now, ODBMS clients must be custom-built by programmers. Overlapping categories of database software add to the confusion. A PC product like Borland's Paradox is colloquially a "database," though it is really a software package that is used to build database applications. It comes packaged with its own relational database engine, a simple database-programming language, and data-entry and reporting-forms-generation tools. In the world of SQL databases, each of these functions is often sold as a separate product: a server, a fourth-generation programming language (4GL), an application generator, a screen painter, and query or reporting tools. There is no application like Paradox for NeXTSTEP. Stone Design's DataPhile is a flat-file database application: It has an engine, plus forms-design tools, but no programming language (though one is promised for a future release). IP Design's Parabase uses a relational server (purchased separately) and includes forms-design tools but doesn't make use of a database-programming language. Only the combination of a third-party SQL server, NeXT's DBKit and third-party objects, and Objective-C can be compared to Paradox ± though the comparison is that of a construction project with a Lego set. The right tool for the right job As the "Database Clients" table shows, you'll need more than one application to round out a NeXTSTEP database management system (DBMS). In addition to SQL servers such as Sybase or Oracle, you'll need applications for data entry and reporting, database-administration tools, and perhaps, database-engineering aids. Also, users may ask for utilities to link analysis tools, such as spreadsheet applications, to databases. Stand-alone applications designed for your industry may connect to SQL databases. Or you may decide to build your own DBMS clients for data entry, reporting, and analysis, using a database-shell application or NeXT's DBKit and its add-ons. Unlike word-processing or page-layout applications, which vary little across industries, you'll likely find that your DBMS must be tailored to fit your particular business, if it is to deliver gains in operational productivity. Application design Many NeXT customers use DBKit to build applications for accessing DBMS records. Although novices can build simple applications, sophisticated database-client applications using DBKit require Objective-C. (If you're ready to tackle programming with the DBKit, see the sidebar, "Approaches for Programmers.") Don't abandon hope if C and SQL are beyond you. A NeXTSTEP package such as IP Design's Parabase or the forthcoming Proponent from Proponent (see the sidebar, "Proponent in the Wings") can get you up and running a custom DBMS with no programming. At their best, these programs can be as easy to use as a flat-file database, but they tap the shared data of industrial-strength SQL relational-database servers. Parabase, $695 from IP Design, provides everything, except for the SQL server, that you need to build a database application. The $695 version of Parabase is used to design forms; $349 run-time licenses can be deployed among users who do not need to modify designs. To build a database application, use Parabase to create an empty database on the SQL server and design a form, pulling fields off a palette and assigning a name and descriptive title to each field. As soon as the form is designed, you'll save it, storing a graphical description of the form in the UNIX file system and automatically creating the first table in your new database (each data entry field corresponds to a column in the table). Open the form and you are ready to enter data and query for specific records. You can output database records as reports by graphically building a form, using a report-layout panel to specify a sort order for records and summary data for aggregate groups. Parabase's strong suit lies in its flexible forms and its basic database system-administration tools. Fields in your database can contain data in the form of fixed-length text, variable-length text, numbers, time and date, yes/no check boxes, graphics, whole files (making document-archiving applications possible), and an "electronic signature" field type that is used to build applications that require password-protected authorization of documents. One of Parabase's sample databases uses a scanned Federal Express form for a freight-tracking system, combining invisible fields and a background image. Validation of user-entered data can be specific to each field, and field data can be calculated from other fields (this requires acquaintance with SQL "select" statements ± though the statements can be created from a point-and-click editor). Table-based pick lists, combined with scripts triggered by buttons or custom menu items, flesh out a custom app. Parabase handles enough database system administration to get any SQL-based application up and running. Point-and-click tools add and delete user accounts and modify permissions. Databases can be added or dropped, dedicated storage space can be increased, and disk mirroring or backup can be configured in one step. You'll need SQL skills or a stand-alone database-administration tool for more advanced operations. Parabase accommodates the relational-database model, albeit with some limitations. A single form can employ fields from more than one database table, as long as a field common to both tables is present. But Parabase 1.1 can't handle a common expression of a one-to-many relationship ± cases in which a single form shows a single record from one table and multiple records from another (sometimes called a "master detail" form). In the real world, you might need an invoice that accommodates a variable number of line items. IP Design plans to incorporate this feature as "multirecord blocks" in Parabase 2.0. In its favor, Parabase automatically maintains the referential integrity of the database. If you're using Parabase to build a NeXTSTEP shell around an existing SQL database, you'll easily drop pre-existing tables and fields onto database forms. In fact, it may take only ten minutes to duplicate a character-based data-entry screen used on another platform. Migration from a PC- or Macintosh-based DBMS, however, may not be as easy. The version we reviewed imported only comma-and-quote delimited text files, though IP Design has plans to improve the flexibility of importing and exporting. Despite some limits, Parabase will be a godsend for many users, especially those who want to quickly build a database application around a SQL server without learning programming. Spreadsheets as clients Instead of building your own custom application, you can use a standard spreadsheet as a database front end. After all, the basic component of a relational database is a table, and spreadsheets are fancy tables. Put the two together, and you've got an intuitive database application. There's just one missing ingredient ± a way to transfer data to a spreadsheet from a SQL server. Two utilities, OTProvide for Lotus Improv, and DataLink for Oracle and Wingz, forge the link. OTProvide, $850 for a single user with floating license, is a model NeXTSTEP application. Objective Technologies's product attaches to Improv and manifests as another set of Improv menus, allowing you to log onto a SQL server and open an inspector panel that displays all resident databases, tables in databases, and columns in tables. You won't see actual data until you click buttons to select columns and specify details of your database query, saving the instructions to a query file that you can recall or share with others. Execute the query and watch an empty Improv worksheet fill with the contents of the database you've selected. Improv users can manipulate models built on SQL data without learning the syntax of SQL, if an experienced user supplies a prebuilt query file. On your own, you may find that setting up a query requires confronting an unruly combination of file viewers and multipurpose inspectors, as well as a brush with SQL. We'd prefer to see a graphical representation of databases, with data only a double-click away. Nonetheless, we can't fault the functionality of OTProvide in combining the power of Improv and SQL databases. DataLink for Oracle, $500 from Fusion Systems Group, links Informix Wingz spreadsheets to Oracle databases. Wingz's strong suit is its HyperScript programming language. Unlike macro functions in PC spreadsheets like Lotus 1-2-3 or Microsoft Excel, HyperScript is a full-featured procedural programming language, powerful enough to build decision support or executive-information systems. DataLink is nothing more than a third-party HyperScript routine that adds database-access menus to Wingz and new functions to HyperScript. Placing the DataLink files in your Wingz folders creates a version of Wingz with menus for logging onto Oracle servers, selecting tables, specifying criteria for retrieving data, and copying columns of data into columns of the spreadsheet. By itself, it's convenient, but if you're handy with HyperScript, your imagination will soar. You could easily write your own HyperScript routine that logs onto a server, retrieves data from a table, and graphs the results. In fact, since HyperScript lets you create dialog boxes, a prompt could ask the user how recent the data should be and offer a choice of graph types (Wingz's other strong suit is more than 25 different charting choices). Fusion Systems has a version of DataLink for Sybase in beta; Wingz also directly supports Informix's own Informix-SE SQL server, which is an alternative to Sybase or Oracle under NeXTSTEP. Mesa, $499 ($99 educational) from Athena Design, is a spreadsheet application that includes rudimentary SQL-query capabilities. A database menu includes a Query Inspector, which has fill-in-the-blank fields to establish a connection to server and database. The user types in pieces of a SQL "select" statement and specifies a range of spreadsheet rows to fill with retrieved data. It's possible to save the query specifications for reuse. You'll need to know how to construct a SQL "select" statement, but SQL querying doesn't cost extra for Mesa users. (Mesa is reviewed elsewhere in this issue of NeXTWORLD). The Appsoft Solution spreadsheet, scheduled to have shipped in January, also supports SQL queries, according to the company. Database tools and utilities Although SQL databases can be designed and maintained entirely from the UNIX terminal window, we found a crowd of NeXTSTEP query tools and administrative utilities, as well as one engineering tool. If you're new to SQL, these products can be a substitute for help from a SQL expert, and, by confining SQL commands to menu items and alert panels, flatten the learning curve. DBCommander, $250 from DBSA, is a comprehensive database-administration utility. It implements almost all SQL administrative commands, including all their options, within a NeXTSTEP interface. There are eight main panels for administrative functions: security; configuration of server and database operational parameters; disk-device and database management; data import/export and Improv data transfer; database-consistency verification and error-log review; database backup; permission controls; server start-up/shutdown; and database-process control. Few database managers will use all functions often, but when administrative matters need attention it's easier to launch DBCommander than to hope one has entered an unfamiliar SQL command correctly on the command line. DBCommander will be particularly welcome if you have suddenly been assigned database-administration responsibility ± it offers a thorough, if daunting, introduction to administration tasks. DBInspector, $349 from Black Market Technologies, is a database-query-and-update tool. The interface is a file viewer that looks much like the NeXTSTEP File Viewer, but hierarchically displays servers, databases, tables, columns, and the data in columns. Double-clicking a table name opens the DBEdit panel, a matrix that displays the columns from the table, allowing entry of new data. Data can be modified by double-clicking a column name or data in the file viewer, opening a DBQuery panel in which one specifies the desired records. The records are sent to the DBEdit panel, where they can be edited (alternatively, data can be sent to a text window or an empty Improv worksheet). Both the DBEdit and DBQuery panels show the SQL statements that underlie queries or updates, a "training wheels" feature that helps the novice. Both novices and experienced SQL programmers will appreciate DBInspector as a utility for data peeking and tweaking. Objective DB Designer, free with the purchase of the $995 Objective DB Toolkit from Professional Software, is intended for database engineering and administration. Three windows serve as control panels for server administration, database administration, and documentation of database design. Server administration runs the gamut from basic (adding and deleting databases) to advanced (expanding data-storage allocation, setting backup and disk mirroring, and configuring parameters in the Sybase master database). The structure of databases can be displayed and edited using an Inspector-like panel, allowing changes to table structure (including column data types and size), stored procedures, data-entry defaults and validation rules, and more. After one has made extensive engineering changes to a database, Objective DB Designer handily generates documentation either as easily readable reports or as SQL source code that can be used to recreate a database. The Objective DB Designer interface is a combination of inspectors and scrollable lists, and while we would have preferred a diagrammatic graphical interface, the interface is more polished than DBSA's DBCommander. The DBSA product has a few more functions (data transfer to text files or Improv, for example), but Objective DB Designer generates engineering documentation. SchemaE, $2500 from Schema Research Corporation, is billed as a database-engineering tool, a NeXTSTEP version of the pencil-and-paper planning aids or computer-aided software and engineering (CASE) tools of salaried database managers. The rationale for tools such as these is irreproachable. Corporate databases may contain hundreds of tables and thousands of columns. In order to maintain relational efficiency and referential integrity, data must be normalized. Key columns must match, data must not be duplicated, and relationships between data entities must be explicit. Until now, descriptions of database organization were kept in three-ring binders or in stand-alone CASE applications, not in the SQL servers themselves. SchemaE maps the inner territory of a SQL server, allows an administrator to reorganize and document the landscape, and saves the description back to the server for on-going reference and maintenance. We liked it because its click-and-drag graphical representation of SQL databases showed us the structure of our DBMS better than any other product in this roundup. We were able to quickly add and drop tables; add and drop columns; move columns; and copy columns, preserving data types and other attributes. We assigned primary keys and drew lines representing data relationships. Unfortunately, that's all we could do in the current version of SchemaE. It would be more useful if it also contained the administrative functionality of DBCommander or the querying power of DBInspector. SqlBuddy, $499 for a single user with floating license, is another Objective Technologies product and solely a database-query tool ± allowing peeking but not tweaking. A Build Query panel is SqlBuddy's main window, allowing an unschooled user to select tables in databases. A click of the Execute button retrieves data, opening a text window that has the contents of the database table neatly displayed. The results can be saved in a text or RTF file. You can narrow a query in the Build Query panel, defining SQL "where" clauses by pointing and clicking, specifying sort orders, requesting unique results, aggregating data for summaries, and retrieving data from multiple tables (SQL "joins," which the on-line manual handily demystifies). A Query Inspector shows the SQL commands generated by the Build Query panel and allows editing of queries, including common administrative and query operations provided by SqlBuddy as prebuilt templates. We liked the interface but judged the overall utility of SqlBuddy limited, since it does not connect to analysis applications or allow data updating. SqlBuddy is a compact and handy can opener for databases, but at heart it's no more than a tool that eliminates the typing of SQL commands. Stand-alone applications Would you love to put SQL data to work without the expense of building a custom application? There is a wealth of NeXTSTEP applications that connect to SQL databases (the table "Speaking SQL" provides a sample). Some applications can be used in any industry, like Megatel GmbH's FLIG, a system for combining geographic data with maps. Other applications are more specialized, like Leading Market Technologies's EXPO worksheet, for analyses of financial time-series data. Accounting packages are particularly suited to use of shared data ± Datrix is shipping accounts-receivable and general-ledger software, with more applications coming from other vendors. Indeed, with the NeXT's DBKit simplifying connections to SQL servers, there may soon be more NeXTSTEP applications sharing data than not. A workbench for data It's inescapable that databases under NeXTSTEP require more work and more tools than the typical database applications on PCs or Macs. NeXTSTEP's advantage is the pooling of SQL-accessible data throughout an organization, with no limits to database growth ± in a phrase, industrial-strength applications. If your company's operational productivity requires client-server databases, you'll likely assign a team to program one or more custom front ends. For the person who oversees your databases, we'd recommend DBCommander or Objective DB Designer as administrative tools. Skilled users are likely to appreciate DBInspector for ad hoc querying and data updates. And Parabase is great for quick, uncomplicated database applications users can build themselves. Though none of the reviewed products is fully mature, they all show promise, and the time is ripe for building a database-development workbench. Daniel Miles Kehoe is a NeXTWORLD contributing editor. He can be reached at dkehoe@nextworld.com. "Speaking SQL" (Stand-alone database applications) A sampling of applications that use shared data. Datrix5 A conventional accounting package with a NeXTSTEP interface. List Price: $695 per module (up to four Sybase connections); $1695 (up to 255 users); $2695 (source-code version) Contact: Datrix, 33 W. Boxelder #104, Chandler, AZ 85224. 602/545-7421, 602/545-7419 fax; e-mail: bobby@datrix.com EXPO A graphical worksheet for analyzing time-series data, using historical or real-time sources, intended for financial analysts, traders, and investment managers. List Price: $3000 (historical data only); $5000 (real-time system) Contact: Leading Market Technologies, One Kendall Sq., Cambridge, MA 02139. 617/494-4747, 617/577-8211 fax FLIG A geographical information system (GIS) for NeXTSTEP. Geographical objects appear against the backdrop of a scanned map. The user accesses database records (text, graphics) by clicking the objects or data lists. Simple demo available from ftp archives. List Price: Free (via ftp only) Contact: Megatel GmbH, Wiener Straûe 3, D-W2800 Bremen 33, Germany. 49/421/220.95.16 fax; e-mail: mhi@megatel.de SIDEBAR: Approaches for Programmers Your most productive database application will be the one you build yourself. You'll have full access to the entire NeXTSTEP environment unlike working on another platform with a 4GL, a high-level language designed for databases but which restricts access to the rest of your computer. NeXT's DBKit provides three essential ingredients: Adapters that link the DBKit to external servers; an Access Layer that presents a uniform application programming interface (API) for all Adapters, allowing programmers to maintain portability of applications among servers from various vendors; and a palette of Interface Builder objects, such as a database file viewer and an image-displaying object. Although you'll likely write to the Access Layer API, you'll probably supplement NeXT's own DBKit palette with objects from third-party vendors or develop your own interface objects. Frontier's DBKit Companion Objects and spreadsheet matrices from Black Market Technologies are examples of third-party objects that can be used for database-application development in Interface Builder, though you'll use plenty of your own Objective-C in your application. Alternatively, if you know SQL but aren't comfortable with a lot of C, check out Professional Software's Objective DB Toolkit. It's a C-free substitute for NeXT's DBKit for the experienced SQL programmer. If you've caught the object-oriented bug, VNP Software's AccessKit layers on top of NeXT's DBKit, mapping the traits of your own Objective-C classes directly to relational-database columns and tables. The product lets you build object-oriented applications directly on top of an existing RDBMS. Or you may want to start developing applications based on ODBMS technology from Versant Object Technology, Object Design, BKS Software, or Persistent Data Systems (NeXTWORLD will cover ODBMS development in a future issue). Whatever your approach, you'll get there faster with NeXTSTEP. Database Clients Shell application Parabase 1.1 Servers: Sybase and Oracle 3.5 cubes (beta) The only NeXTSTEP software that builds SQL database applications without programming. Despite a few missing pieces, it's easy to use and constructs quite elaborate applications. List Price: $695 IP Design, 201 Wilshire Blvd., Santa Monica, CA 90401. 310/394-1820, 310/393-7245 fax; e-mail info@ipdesign.com Spreadsheet link OTProvide 1.2 Servers: Sybase 3 cubes Turns Improv models into powerful SQL applications, though OTProvide doesn't allow data entry or updating. List Price: $499 (one machine); $850 (one user, floating license) Objective Technologies, 7 Dey St. #1502, New York, NY 10007. 212/227-6767, 800/362-5328; 212/227-3567 fax; e-mail info@object.com Spreadsheet link DataLink for Oracle Version 1.3 Servers: Oracle 3 cubes Turns Wingz spreadsheets into powerful SQL applications. $500 Fusion Systems Group, 225 Broadway, 24th Fl., New York, NY 10007. 212/285-8001, 212/285-8705 fax; e-mail info@fsg.com Spreadsheet Mesa 1.1 Servers: Sybase (obtain cube rating from Lauriston review) Mesa connects directly to databases through a primitive interface but requires familiarity with SQL. (The cube rating here applies to the entire application, as reviewed elsewhere in this issue.) $499 Athena Design, 17 Saint Mary's Ct., Boston, MA 02146. 617/734-6372, 617/734-1130 fax; e-mail info@athena.com Administration/Engineering Utility DBCommander 1.01 Servers: Sybase 3 cubes Distinguished by a reasonable price and comprehensive administrative panels, DBCommander needs a graphical make-over before it'll be an attractive NeXTSTEP product. $250 Contact: DBSA, 1525 Los Pueblos Rd., Los Alamos, NM 87544. 505/662-5619, 505/662-9944 fax; email: info@dbsa.com Administration/Engineering Utility DBInspector 2.0 Servers: Sybase 3 cubes A point-and-click query builder with a less-than-intuitive interface, DBInspector satisfactorily retrieves and updates data. $349 Black Market Technologies, 501 Hicks St. #307, Brooklyn, NY 11231. 718/522-5090; 415/474-7896 fax; e-mail: info@bmt.gun.com Administration/Engineering Utility Objective DB Designer 1.0 Servers: Sybase 3 cubes Intended for database administration and documentation, this utility is cluttered but useful. Free with Objective DB Toolkit Professional Software, 599 North Ave. #7, Wakefield, MA 01880. 617/246-2425, 617/246-1443 fax; e-mail: psi@logibec.com Administration/Engineering Utility SchemaE 1.0 Servers: Sybase 3 cubes (beta) It does little but does it well. The interface is elegant and the price may be acceptable when there's large databases to design. $2500 Schema Research Corporation, 460 Seaport Ct. #202, Redwood City, CA 94063. 415/368-8477; 206/783-4762; 415/368-8479 fax; e-mail: products@LSC.com Administration/Engineering Utility SqlBuddy 1.0.1 Servers: Sybase 2.5 cubes Without an ability to update data on its own, SqlBuddy is graced by an attractive interface and limited utility. $179 (one machine); $499 (one user, floating license) Objective Technologies, 7 Dey St. #1502, New York, NY 10007. 212/227-6767, 800/362-5328, 212/227-3567 fax; e-mail: info@object.com SIDEBAR: Proponent in the Wings At Swiss Bank Corporation/O'Connor Associates (SBC/OC), the database-application development environment of choice is neither NeXT's DBKit nor IP Design's Parabase. SBC/OC is using Proponent, an application from a company of the same name. "Parabase makes a good forms generator," says Jeff Kvam, head of SBC/OC's NeXTSTEP office-automation project. "Proponent can better handle the full life cycle of defining and building our databases." Proponent extends the familiar metaphor of the spreadsheet to represent databases. In comparison, the form is the interface metaphor for Parabase; query tools such as DBInspector or SqlBuddy use a combination of NeXTSTEP browsers and inspectors; and SchemaE represents databases diagrammatically. Arguably, Proponent's spreadsheet approach offers no inherent advantage, but in the prerelease version that SBC/OC uses, Kvam says Proponent offers genuine advantages over Parabase. "We found that Proponent can handle more complex problem sets. Our applications use multiple joins, a variety of tables, and tons of one-to-many, or even one-to-many-to-many relationships," says Kvam. He claims that Proponent easily accommodates relational complexities, including the prototypical example of an invoice with a varying number of line items. More importantly, says Kvam, design of a Proponent application begins with legacy data, not a blank form. "We're working with real data from the start, so we talk with users about ways to organize the data, not make pretty forms. It helps that using Proponent is a quick, iterative process. A manager who understands a business problem can sit down with our database expert and together they'll design an application, often in an afternoon. That's very different from typical database programming, where a programmer who doesn't know anything about inventory tracking, for example, comes back three months later with an application that doesn't fit the user's needs." Kvam says, adding that Proponent's architecture simplifies adapting applications as needs change. Although you might be as intrigued as NeXTWORLD about this product's promise, Proponent is only available to select customers. Bob Beth, president of Proponent, explains that only a handful of large NeXTSTEP customers are using the product. "We'd love to see everyone using it, but we're going to take the time to get it right. Industrial-strength databases are a complex problem space and the software can require a lot of support, so we want to be sure we're ready when the time comes." Beth declined to state when the product would be generally available. | ||||||