DBMS, October 1996

Oracle's Impressive Designer/2000

I recently changed jobs, leaving a company that develops application software packages to become a member of a consulting firm. One of my first projects was to assemble our standard developers workbench. I wanted to work with fairly high-level modeling tools that we could use to capture business requirements and convert them into design forms. We wanted to map these directly to a runtime environment that would let us control how we deploy applications. Such a toolset would minimize the amount of code we had to write. It didn't take long before we reallocated my time to several revenue-generating projects, so I failed to complete the workbench, but I do have some interim findings.

In the decade since I started developing business applications and searching for good tools to support this work, the development product model has changed somewhat. In the mid to late 1980s, I worked with PC-based products that bundled a data file manager, programming language, form design tool, and report designer into a single package. Nowadays the database engine is separate from the application development product that includes those other capabilities, but in most cases a single-user version of a database engine is bundled with the development tool. The Internet puts a great deal of stress on this development tool model; that stress is impacting the communities that are using products such as Delphi and PowerBuilder, whose historically rigid two-tier approach to deployment is out of synch with the requirements of a distributed world.

More fundamentally, this product model fails to capture the upfront analysis and design work that needs to occur in the development of larger systems. The addition of third-party modeling tools has produced mixed results. As data modeling tools have matured, their vendors have tightly integrated the modeling environments with existing database and application development products. Such integration makes the external tool an effective member of the development workbench. Other tools that support analysis and design methodologies, such as Popkin's System Architect and Embarcadero's ER/1, have not matured to the point at which they integrate with popular development tools in a way that significantly increases developer productivity.

I have to admit that between the shifting paradigms of information engineering, object-oriented development, and rapid application development on the one hand and the evolution of host-based, LAN-based, client/server, and Internet architectures on the other, it's been pretty difficult for tool sets to keep pace. Development platforms evolve quickly, and third-party developers of analysis and design products have difficulty supporting more than one platform. They must pick a target platform into which they can get sufficient penetration to recoup their investment. On the other hand, development product vendors have completely filled their production schedules, improving the integration of their languages with their data and form definition tools, and enhancing application deployment options and performance. In the past I have not looked to them to improve the richness of their upstream tools.

Enter Oracle

Designer/2000 provides a collection of business and system modeling applications in which you can record business processes, application requirements, and business standards and conventions, convert these into specifications, and automate the generation of applications. (These are summarized in Table 1.) The Designer/2000 toolset is organized into four groups. The first, a group of just one application, consists of Process Modeler, which provides tools for diagramming complex business processes. The second group, the Systems Modeler, provides Entity Relationship, Function Hierarchy, and Data Flow diagramming tools, as well as a general-purpose cross-referencing tool called the Matrix Diagrammer. The third group, the Systems Designer, contains five diagrammers that target the design and implementation phases of development. These include the Data Diagrammer, the Module Data Diagrammer, the Module Structure Diagrammer, the Module Logic Diagrammer, and the Preference Navigator. Finally, Designer/2000 provides a set of generators for converting the design information into Oracle Forms screens and logic, Oracle Reports, DBMS Server objects, Visual Basic application components, Web applications, and C++ class definitions. The shared repository is the foundation for the tight integration between the components, and it supports a system-wide repository navigator that offers an integrated view of all of the entities, functions, and processes in the system. A collection of wizards further integrate the tools by automating the conversion of analysis models into design models.

The inclusion of a business process modeling tool appears to be driven by the increasing acceptance of structured management techniques that promise to improve business operations. The idea of drawing business process models looks pretty obvious from a systems engineering standpoint. Just as the engineer in me wants to model software applications before I build them, I also like the prospect of drawing models of businesses processes and trying out changes on the model before applying them to the organization. Oracle's Process Modeler presents a framework for considering business processes as the interaction of six elements:

• organization units, which represent individuals, workgroups, or departments that can take independent action
• process steps, or tasks, which may involve data entry, decision making, or generating outputs
• flows, which present the movement of information or material or the passage of time
• stores, which represent collections of information or materials
• triggers, events that cause process steps to occur
• outcomes, the results of a process or process steps

Process Modeler can present a property sheet for each of these diagram elements, allowing an analyst to capture time and cost information for each one.

Most businesses are relatively complex, so a model is likely to consist of a number of diagrams, each showing part of a process, with perhaps one diagram showing an overview of the whole process at the top level. A static diagram can illustrate a business process very well, but a diagram is much more effective if you can actually see information and materials moving through the process. Diagram animation is one of the major features provided by Process Modeler. You can create a richly animated process model by using Object Linking and Embedding to associate relevant audio and video clips with process steps. Running the animation highlights each of the flows and processes in turn, illustrating graphically how each works. For each process step, store, or flow, the icon is animated and plays the associated sound (you must start embedded video clips manually). Each item's animation continues for a time proportional to the time value specified in its property sheet. During animation, a process step flashes in a different color if it is waiting for previous processes or flows to complete before it can start. This feature is extremely useful in highlighting bottlenecks in a process.

System Modeler

The Function Hierarchy Diagrammer provides an organizational chart-style interface that lets you create hierarchies of all the functions that are performed by a business. You can start at a very high level, by defining very general functions or by reusing the analysis performed with the Process Modeler. From there you can drill down, decompose business functions, define elementary business functions, identify which can be automated, and indicate how they interact with data. Elementary business functions ultimately map to individual screens, forms, reports, and utilities in the application system. For each of these, the Function Hierarchy Diagrammer presents a property sheet that captures a full, detailed description of the function, the frequency of execution, the speed of response required, the event that acts as a trigger for the function, any events that are the outcome of the function, and the business rules and logic associated with the function.

With the entity-relationship model in hand, you can drill down on each function to specify the information that each function will retrieve, create, or change. Performing this analysis in conjunction with the ultimate users of the system may uncover shortcomings in the entity-relationship model that should be addressed before the system moves from analysis to design. Designer/2000 provides a series of matrices for entry of relationship information, including "CRUD" (create, retrieve, update, delete) rules. (See Figure 2.) The analyst can access the matrix through the Function Hierarchy Diagrammer or the Repository Object Manager. With CRUD matrices, an analyst can specify when a function will create, retrieve, update, delete, or archive an instance of an entity and can drill down to define CRUD rules for specific attributes. Reviewing these matrices provides a good check for entities that never get created or deleted and for functions that lack operations. Once complete, this information will drive the Application Design Wizard to determine how application modules use tables.

The final piece of the Systems Modeler is the Data Flow Diagrammer. It's not likely that you would use both the Data Flow Diagrammer and the Function Hierarchy Diagrammer heavily, because they both seek to define how the target organization's business processes interact with the entities that are the object of those processes. Data flow diagramming tools provide somewhat richer semantics, directly addressing the interaction of functions with the flow and storage of information. Most data flow diagramming tools suffer from a lack of integration with other development tools; Oracle's diagramming tool is unique in that it becomes an effective practitioner (and makes your investment worthwhile) by reusing the specifications you enter throughout the successive steps of the process.

From Analysis to Design

The Wizard uses a tabbed dialog with matrices for each type of mapping (entities to tables, IDs to keys, relationships to constraints, and so on) to give the designer substantial control over the scope of the conversion and the mappings employed during the conversion process. Most interesting is the flexibility with which you can map subtypes; the Wizard lets you resolve subtype scenarios into a single table with a discriminator column, child tables with only columns specific to the subtype (the Wizard calls this an "arc"), and separate tables with a parent table that retains common information about the subtypes.

To move effectively into the System Designer, you need to understand Designer/2000's concept of a module, which is actually a fairly common systems view in which a module represents a computer program that implements a discrete business function. A module network specifies the order in which the modules are called. System Designer classifies modules as Menu, Screen, Report, PL/SQL, or Utility modules and supports definition of several specific types of screen and report modules. Menu modules provide top-level application partitioning and control functions. Screen modules represent the user interface for entity maintenance and transaction entry procedures. PL/SQL and utility modules perform processing tasks in the database, which may or may not be synchronized with the work of a screen or report module.

The Application Design Wizard deduces which program modules are needed to support the functions captured in the function hierarchy, data flow, and process models, and what data these modules should access. Based on its analysis, the Application Design Wizard creates first-cut application designs in the formats used by the System Designer's Module Structure Diagrammer and Module Data Diagrammer. It converts functions into candidate modules, which can then be implemented as screens, reports, manual procedures, or utilities; it employs a set of configurable rules to determine how to represent a specific function. The Wizard also converts function data usages to module data usages, which define how a module uses the tables and columns in the database. The Application Design Wizard then uses the module-to-business-unit association to create candidate menu modules and menu structures. The resulting modules and their usages can be used for generating applications via the generators.

Systems Designer

To distinguish itself from the Systems Modeler's Entity Relationship Diagrammer, the Data Designer provides entry forms for specifying physical attributes of the table and defaults for use of a table or view by a module. Physical attributes include the database and tablespace in which the table should be defined and how much space to reserve for it, as well as whether a journal table should be created. If it should, the Forms Generator creates triggers in each of the forms that uses the table to record every insert, update, or delete operation in the journal table. Drilling down to column definitions, you can identify the default type of user interface (UI) control that should represent the column on forms, a default display size, and display sequence, as well as the usual data type and amount of storage to allocate. The Data Designer also permits specification of behavior that the Forms Generator should implement to enforce constraints, starting with whether to enforce them at the client, the server, or both, and the message to display when violations occur.

It's easy to get confused about the three tools whose names start with "Module." The Module Logic Navigator is a PL/SQL coding assistant that provides syntax checking and user-defined code formatting, and the ability to drag and drop valid PL/SQL constructs from a selection tree, import, and export. The Module Structure Diagrammer manages the hierarchy of functions in an application, whereas the Module Data Designer manages the detailed information required to generate a specific module. This is where the rubber really meets the road: Here the data structure and information about physical implementation recorded by the Data Designer merge with the functional hierarchy of the application.

There will be one Module Data Diagram per module. A Module Data Diagram looks like a portion of the data designer diagram, with boxes drawn around entities that will share windows or blocks within windows in the generated application. Using a tabbed dialog interface to collect these specifications, the Module Data Diagram describes how the module uses tables, views, snapshots, and columns. By specifying the mode in which tables or views should be linked, you can create master-detail, matrix, and LOV (List of Values) style entry forms and master-detail, matrix, control break, and label-style reports. Although the nomenclature of this application is arcane (it refers to detailed table usages, blocks, and placement items), the objective is to complete a series of property sheets that specify how the generated application will treat each data element.

After defining the data structures and mappings of data to functions through the other System Designer diagrammers, it's almost time to generate your application. One final, critical step requires entering the Preferences Navigator and setting preferences. Preferences serve as defaults for the values assigned to variable settings during the generation process; there are dozens of them, covering everything from coding style to the type of code to use for validation to the justification of prompts regarding items that themselves have specific styles. All of the preferences have default values, which Designer/2000 refers to as the "factory" settings. You can override the factory settings at any level so that your custom preferences will apply throughout the application, only to usage of specific data elements, or only to certain forms. Preference Navigator presents a Windows explorer-style interface, which exposes a property sheet for the currently selected preference item. Custom settings cascade down the preference hierarchy, and an icon next to the preference value indicates whether it represents a factory or custom setting.

It's Generation Time

Looking at Designer/2000 as a potential general-purpose application design suite, I'm more interested in its ability to generate Visual Basic forms, Windows Help, Oracle's WebServer applications, and (to a somewhat lesser extent) C++ class definitions. For instance, the VB generator creates a VB form for each module defined in the design system. It creates a separate region on the form, which Oracle calls a zone, for each "base" table usage in the module. It then creates a UI control for each of its column usages, plus the column usages of "lookup" tables; the choice of control depends on preference settings. Much of the generated interface is driven from the properties of these usages - for example, captions, types of control, and zone styles. Templates determine the look and feel of the forms, zones, dialogs, frames, and controls that make up the generated Visual Basic application. They are defined as a Visual Basic project and hence are created or modified using the Visual Basic design environment. Oracle includes a set of standard templates with the product; the designer can extend or replace these templates.

Of course, using Designer/2000 in conjunction with Visual Basic compromises one of the objectives I stated at the outset: the integration of implementation and application deployment into the toolset. Once Designer/2000 generates the VB forms and menu system, optimization and deployment is up to you. You would achieve much tighter analysis to deployment with Developer 2000, but issues beyond the scope of this column make VB developers unlikely to switch, even if they use Oracle tools to design their applications and manage the data. Even so, Designer/2000 brings a great deal of value to the design side of the process. Although the user interface for some of the diagrammers is clumsy, and the repetitive use of a grid for capturing details is two-dimensional and arguably not a particularly creative way to capture this information, sharing a comprehensive design repository makes these very effective tools. Overall, Designer/2000 comes the closest to converting a non-procedural specification into an application of any tools that I have investigated. I'm glad I checked it out; it will raise the standard I set for additional products of this nature.

* Oracle Corp., 500 Oracle Pkwy., Redwood Shores, CA 95065; 800-672-2537, 415-506-7000, or fax 415-506-7200; http://www.oracle.com.

FIGURE 1

FIGURE 2