|
Today, more than ever, the application development scene is continually changing. New systems are both Internet- and client/server-based and are written in everything from pure Java to C++, and the visual development tools heavyweights like Delphi and PowerBuilder are being rolled out. However, one thing that hasn't changed is the need for database architects to document the islands of information spread throughout their company in a logical design that they can then turn around and use to create a physical database. That database may serve only a few customers or may be at the beck and call of the entire Internet community, but one thing is for sure: Get the design of your database wrong and the echoes of pain and suffering heard from your developers and customers will be heard for a long time to come.
This is just one reason it's so important to have the right tools to model your corporate data environment. Logic Works Inc.'s ERwin/ERX modeling tool and accompanying suite of products continue to find their way into the hands of many data/database administrators. I, along with many other database professionals, have successfully used ERwin to build numerous heavy-duty production databases, and I was interested to see what new features the folks at Logic Works have put into their core offering.
I tested ERwin 3.0, along with its connectivity into Logic Works' ModelMart repository, on a 120 MHz Pentium machine loaded with 64MB of RAM running Windows NT 4.0 as well as a 200 MHz Pentium machine running Windows 95 with an equal amount of memory. The CD containing the ERwin software allows for both 16- and 32-bit Windows installs, and my installation of the 32-bit version on both platforms went without any problems. No other setup work was required. When all was completed, the product took up about 30MB of disk space.
Users of ModelMart, Logic Work's repository that allows designers to work on models in a team environment, will have to jump through a few more hoops (consisting mainly of invoking the SQL needed to build the physical ModelMart database) to set up the repository, but the process is painless and simple. I was able to successfully build a ModelMart repository on an Oracle7 release 7.3 database without any incident. My current repository that I use for day-to-day work sits happily on a Microsoft SQL Server 6.5 database and has worked nearly flawlessly for me since the day I created it about a year ago.
If you frequent most of the top systems publications, you will find that ERwin is a perennial favorite and top choice in many of the "reader's choice" awards (including five consecutive DBMS Reader's Choice awards) -- so much so that I almost feel like a heretic when I say that I really didn't like earlier offerings of ERwin compared to other database design tools that I've used. While I found the tool did pack a lot of power, I didn't think it to be very intuitive to use and, in some places, I thought it was downright difficult. The trigger editors were difficult to master, reporting was prehistoric, printing was a pain, and the differences in the logical and physical order of columns in tables caused me to botch a number of database builds. I'm now pleased to report that I've changed my tune since sampling version 3.0. The engineers at Logic Works have given a nice facelift to the tool in many places that make it much friendlier and more straightforward to use. In prior versions, the entity/table editors weren't the easiest to maneuver with, but I don't find that to be the case with 3.0. The new editors sport a tabbed interface that is navigated easily.
The new release of ERwin also assists designers in distinguishing between conceptual and physical database designs a little better. A single option from the menu now more clearly displays a data model in either a logical or physical view. New to 3.0 is the ability to declare attributes that are "physical only" and only appear in the physical model.
When building attributes for entities, I never did like entering my attributes for an entity on one window and then invoking a second window to lay out their physical properties in older versions of ERwin. That's now history with the new all-in-one column editor, which allows you to do it all from one place. Pure data administrators (DAs) can enter their attributes and specify base datatypes such as strings and numbers, allowing DBAs to then take over and make table-specific designations for their database of choice.
Speaking of databases, the tool supports a plethora of engines. The list of databases for which designers can build models include popular engines such as Oracle7, Sybase, Microsoft SQL Server, DB2, Informix, along with some of the more boutique databases like Red Brick, Teradata, Progress, and others. There's also full support for a number of desktop databases like Microsoft Access, FoxPro, Borland's Paradox, and Clipper. ERwin comes complete with a full supply of native database drivers for the larger relational databases (excluding Informix, which must be accessed through ODBC) and I've been pleased with the performance when doing any client-to-server database work.
In addition to support for many databases, ERwin has the ability to generate extended attributes for GUI front-end tools like PowerBuilder, Visual Basic, and Delphi. Physical entity definitions can be imported into the toolsets to allow faster creation of database objects once a physical database is in place.
In addition to the ease-of-use improvements and strong logical-modeling abilities in ERwin, the product continues to excel in the areas of physical database generation and reverse engineering. Once a model is complete and ready to be placed on a database server, the designer can choose the forward-engineer option in the product and build a set of DDL commands that will create everything a physical database could need, including tables, indexes, and stored procedures. I was pleased to see that version 3.0 of the tool can now build views, which are used heavily by DBAs. Options are made available to generate a model into the target database or the user may preview the DDL syntax ahead of time and save it to a file for later execution.
The reverse-engineering abilities of the tool are a blessing to those engineers who are given the task of documenting and/or modifying existing complex database systems. Designers start out with a new model and can pick and choose what objects to reverse engineer, or may decide to bring in everything for a particular database. I've always been leery about trusting the end results of a reverse-engineering process with other tools, but I'm pretty comfortable with the final product that ERwin builds. Naturally, you won't get exact relationships built from the tool unless declarative referential integrity has been used in the underlying database, but pretty much everything else is there. The only problem I've had is that ERwin will not get stored procedures for my legacy Microsoft SQL Server 4.x databases, and it sometimes balks at version 6.5 procedures. New features in the reverse-engineering portion of the tool include the ability to only reverse engineer objects that are in certain tablespaces and the ability to list the owners of objects in which you're interested.
ERwin also offers powerful database functionality with its bidirectional synchronization and compare abilities. As many administrators know, it's very easy for a physical database to get out of sync with its logical model. Fortunately, ERwin lets a designer take a logical model, connect to its existing data source, and update the model with any changes it finds in the physical database. Taking the opposite approach, modelers who make changes to a logical design may connect to the database and have ERwin determine how the new model changes must be handled in the physical database. A set of DDL (complete with alter tables, and so forth) may then be generated to assist the DBA in making the modifications to the existing physical database structure.
The compare facility in ERwin also comes in handy. The tool allows a designer to take a logical model, connect to a physical database, and then identify what's different. I recently used this feature to help our developers with a sticky upgrade they were doing with a packaged application. The team had gotten a large upgrade to an existing PeopleSoft system and needed to know what database changes they had to anticipate. I was able to help by first reverse engineering the legacy database and then taking the model and comparing it to a test server where we had the new version of PeopleSoft running. ERwin generated a very detailed report for the developers that they were thrilled to get.
Being completely blunt, I have to say that I always considered the reports produced by ERwin to be, well, ugly. But, now I'm happy to say that the ERwin folks have completely overhauled the reporting facility of the tool with designers being able to produce reports that stand head and shoulders above what prior versions of the product offered. Using the Report Browser (see Figure 1), modelers can choose from a wide array of canned reports that detail much more information about a model than past reports did.
In addition, users have much greater control over what's displayed and how the report is formatted. Designers may also create their own custom reports from the ground up and save them for later use. One way I use the reporting facility in ERwin is to communicate database designs over our corporate intranet to the rest of my organization. I first create nicely formatted reports with the tool and then bring them into Microsoft Word where I transform them into HTML documents. I then upload them to my company's intranet server and allow anyone with access to view the designs (excluding, of course, anything sensitive or proprietary that can't be shown). The only bad thing I can say about the new report browser is that it ought to handle the formatting of code better than putting it all on one line.
I have to be honest and say that I didn't expect to find a lot of differences in version 3.0 of ERwin, but I was wrong. Those who are fans of the tool already should be excited over the new bells and whistles Logic Works has put into its flagship product. And for anyone (like me) who hasn't exactly been a cheerleader for the tool, you owe it to yourself to take a look at 3.0 -- you may just change your mind.
