CA's AllFusion ERwin Data Modeler r7

CA's AllFusion ERwin Data Modeler r7 offers a near-complete database-development package with capabilities and features that move it to the head of the class. There are many fine database-modeling tools on the market, including Embarcadero Technologies' ER/Studio, fabForce's DBDesigner (open-source), Quest Software's QDesigner, Sybase's PowerDesigner, and Microsoft's design-everything software, Visio. IT managers are demanding such products become an integral part of the entire database-development process, and AllFusion ERwin DM, with its completely rebuilt data-modeling engine, delivers.

AllFusion ERwin DM lets database developers become productive immediately, while providing an environment for saving business rules, data, metadata, database designs, database objects and shop standards. CA has built this release around its redesigned Generalized Data Modeling (GDM) engine, with new features such as "Complete Compare" and improved undo/redo capabilities. The comparison feature lets database designers compare any two versions of a model or the resulting production database; the undo/redo feature lets a user make changes anywhere in the model to understand the impact of those changes throughout the database.

The BasicsData models can be created in several ways. You may need to create a data model from objects that exist in your database schema, for example. AllFusion ERwin DM lets database designers integrate database objects between the design phase and the implementation phase of the database-development lifecycle. The product supports more than a dozen popular DBMSs.

Testing AllFusion ERwin DM with SQL Server 2000 and Oracle 9i proved the integration was seamless. After we ran the simple setup procedure, AllFusion ERwin DM established the connection with the database server and moved database objects seamlessly between the two environments.

You can just as easily generate SQL of the database objects and build a database with just a few clicks of the mouse. You also can save the generated SQL as a .sql file and modify it to suit your shop's needs. The generated SQL isn't pretty, but it's functional. The sample SQL we generated worked flawlessly, even with recursive relationships, check constraints and composite primary keys. CA says its product also supports all Oracle 10g syntax and database objects.

AllFusion ERwin DM's logical, clean layout makes it easy to build a database model from scratch. Entity Relationship Diagrams (data models that represent a logical view of a database and identify relationships among entities) can be created without referencing the product's extensive documentation. Just open a new document, determine the model type--logical, physical, or both--drag the entity icon from the tool bar and place it in the work area. Right click on the entity to see a list of choices. Select the attributes option and a neat dialog box appears, with options to add, change and delete attributes, as well as establish your primary key. The software also comes with templates that you can use to create your model, or you may derive a new data model from an existing one.

Problem SolvedData modelers often waste time performing extra steps to accomplish routine tasks because of the inconsistent implementation of model-manipulation features in many products. In creating entity relationships, AllFusion ERwin DM draws the relationship line and establishes the foreign key automatically. By comparison, Microsoft's Visio makes you draw the relationship line, pushing the end of that line into the center of the entity. One can only hope the line somehow connects.

When you click inside an entity, you can grab attributes and move them freely within an entity, or from entity to entity. Entities can also be resized.

Performance and Quality

We tested AllFusion ERwin DM on an anemic Compaq R4000 running a 1.8-GHz AMD Sempron processor with 512 MB of memory (CA recommends at least 2.0 GHz and 1 GB) running Window XP Professional. Most Windows apps running in this environment execute reasonably well, with the exception of those that require extensive graphics or I/O. AllFusion ERwin DM ran surprisingly well, with many functions achieving subsecond response time. Some of the more resource-taxing functions, such as forward and reverse engineering, took slightly longer, but overall, the product was remarkably fast, considering our environment.

Managers in most large IT shops still wrestle with quality issues, especially in the area of database standards and naming conventions. AllFusion's Naming Standards Editor helps address these issues.The editor enables defining entity and attribute structures by using the logical or physical tabs; the entity and attribute names can be entered using the glossary. Organizations that have naming conventions may import the names from existing sources.CA has a few things to work on with AllFusion ERwin DM: forward engineering support for MySQL, for example. MySQL 5.0 is a breakthrough product release, has become increasingly popular and is therefore a real contender in the DBMS arena. Also, the product's process for establishing a recursive relationship--that is, an entity with a relationship with itself--is not as intuitive as establishing a relationship between two different entities. Database designers don't think of recursive relationships as being different from nonrecursive ones, so the process shouldn't be implemented differently. Last, the creation of supertype and subtype relationships is clumsy. Conversations with CA support personnel confirm that many of these issues are being addressed and slated for future releases.

Dave Dischiave is on the faculty of Syracuse University's School of Information Studies and is a senior consultant with CADE IT. Write to him at [email protected].