|
I began using S-Designor (now renamed PowerDesigner) from Powersoft Corp. a number of years ago when I had to act as both a data administrator (DA) and physical DBA for the company I was employed with at the time. Shortly after making the product purchase, my team was charged with building a complex, 2437 system that would perform critical functions for the entire organization. Working with representatives from "the trenches" who would be using the system every day, we used S-Designor on a daily basis to construct and manage the logical design that mushroomed with each passing day. Once the logical design was completed, we made the transition to a physical model complete with the physical table and index specifications, complex referential integrity, and more. From there, we forward engineered the model into a complete Oracle7 database that remains intact and functioning as I write this.
Those days made me a big fan of S-Designor and gave me an appreciation for modeling tools that I didn't always have. Tools such as PowerDesigner perform a wide range of functions in addition to capturing the logical design of an upcoming application, and they definitely deserve a spot on the database administrator's and developer's shelf in addition to the DA's.
I tested PowerDesigner 6.0 on a Windows 95 Pentium 200MHz PC with 64MB of RAM, as well as on a Windows NT 4.0 Pentium 120MHz machine loaded with an equal amount of memory. I installed all the components of PowerDesigner and, all told, the products consumed about 60MB on my machine and installed without incident.
Powersoft breaks up the PowerDesigner product into a family of components that may be bundled together or purchased separately, depending on the need. For those interested in capturing the flow of data in business processes, the ProcessAnalyst module is the tool of choice. Hard-core data modelers will want to quickly become familiar with DataArchitect, which handles the logical and physical designs of databases. For those wanting special features for data warehouse projects, Powersoft brings a newcomer into the PowerDesigner family --WarehouseArchitect, which has been created to fill that special niche. Developers can use the AppModeler component to help with the construction of client/server front-end applications. Finally, for large projects that employ more than one designer, the MetaWorks member of PowerDesigner comes into play to handle version control and other team-oriented design requirements.
No real setup is necessary to start using PowerDesigner, unless you'll be using MetaWorks. After installing the product, you're required to build the repository tables that will be used to hold the various models created by the users. I only hit one snag when building the repository: Rather than using the bundled Oracle ODBC driver for the data dictionary creation, I used Oracle's native driver. I received an error near the end of the process that said the driver was incapable of performing a function. When I used PowerDesigner's bundled ODBC driver, I was able to create an Oracle7 release 7.3 repository with no problems.
Unlike Logic Work's ERwin, PowerDesigner still uses ODBC for database connectivity instead of native drivers, which is surprising because the product's big brother development tool, PowerBuilder, has used native drivers for its database work for some time. There are both proponents and opponents of ODBC, so depending on the camp you fall into, this area of PowerDesigner could be either good or bad. One nice thing is that the tool bundles most, if not all, of the ODBC drivers you'll ever need to connect to your database of choice.
Once the tool is up and running, the database designer can begin work in a number of different ways. Unlike ERwin, which really starts most engineers off at the physical level, PowerDesigner (with DataArchitect) lets a modeler begin work with either a conceptual model (which the tool calls a CDM for conceptual data model) that has its own specific set of options within PowerDesigner to perform "logical only" modeling or with a physical model (called a PDM) whose work environment contains all of the bells and whistles for creating a fully operational physical database. When I began using the tool, I built CDMs first where I laid out all the entities, relationships, and attributes from a logical standpoint. Once I was satisfied with the model from a logical view, I used a function within the tool to build a PDM from the conceptual model. PowerDesigner does a nice job here and provides real functionality for organizations that have a clear line between the logical and physical database duties. DAs can create their logical designs (CDMs), which can then be turned over to DBAs who can use PowerDesigner to get things started from a physical perspective. The product handles the transformation between the logical and physical quite well. It creates intersection entities in a PDM from the many-to-many relationships contained in a CDM, builds indexes based on primary and foreign keys in a CDM, and constructs referential integrity constraints (either declarative for databases that support such RI or triggers for those that don't) based on a CDM's relationship definitions.
Another way a modeler can work with the tool is by reverse engineering an existing database structure into a logical or physical design. I've used this function a lot and have been mostly pleased with how well the product does in building the model. The only problems I've seen with the tool is that is sometimes misses defaults, validation rules, or other table-level constraints defined at the database level. Referential integrity that has been set in a declarative form comes through fine; in the absence of this type of RI, PowerDesigner will take its best shot at mapping relationships.
The components of PowerDesigner are (naturally) designed to complement one another. For example, users of ProcessAnalyst can export any business rules and data stores captured within the tool to DataArchitect for getting a head start on database development. Within DataArchitect, the modeler can build designs that can be forward engineered into nearly every database you can think of, including Oracle, Microsoft SQL Server, Sybase, Informix, and DB2.
There are a couple of unique features within PowerDesigner that I, as a DBA, really find helpful. First, the tool lets you perform a check on the model to ensure that all relationships are accurate and no modeling blunders have been made. The second distinction I find helpful in the product is the sizing estimation utility. When building entities, modelers can supply DataArchitect or WarehouseArchitect with an estimation of the number of rows expected for each underlying table. Once the model is complete, the designer can click an option to produce a sizing report, which is a listing that details how large each table and its accompanying indexes will be. Are the sizes perfect? No. But they come a lot closer than other utilities or home-grown procedures I've seen for estimating database space.
New on the Powersoft scene is WarehouseArchitect, which is sure to be a hit for those charged with the task of building data warehouses or marts. The software sports special features, such as support for dimensional modeling (instead of regular entities, users can build small dimension objects which describe components of a business that, in turn, point to larger fact objects which contain detailed information relating to the many dimensions), which is becoming popular among designers (see figure 1) and special partitioning wizards for doing horizontal partitioning of tables and aggregations, both of which are used heavily in warehouses. (We'll see how WarehouseArchitect handles the new Oracle8 partitioning features in a later release, I'm sure). WarehouseArchitect targets all the normal databases you would expect, plus it allows designers to construct physical databases for special "warehouse-only" RDBMS engines like Sybase IQ and Red Brick.
For developers interested in stamping out templates for work inside, PowerBuilder, Delphi, Power++, and Visual Basic, AppModeler can be used. Working as somewhat of an application generator, the tool creates extended attributes that apply to database objects such as tables and indexes within the development product of choice. These attributes can be used to construct, for example, DataWindows within PowerBuilder that can help speed a development effort along.
As projects become large and teams of designers are required to keep up with modeling activities, the need for a central repository becomes apparent rather quickly. PowerDesigner provides MetaWorks, which allows for version control, security, and the normal repository features. The MetaWorks database can be built on most any RDBMS you choose, including the bundled Sybase SQL Anywhere.
Of all the components of PowerDesigner, MetaWorks was the product that always gave me fits in previous releases. Either models wouldn't store properly in the repository, or when I finally did get them into the database, extracting them resulted in model corruption at times, especially if I used column defaults and validation rules. I'm happy to report I didn't find this to be the case with the new version of the tool. I put MetaWorks through its paces and all went fine with the Oracle7 repository I was using.
Documentation is a big deal these days, what with the many data warehouses, data marts, and legacy systems that wind their way through most corporations. People who don't want to walk through a giant maze of entities and relationships that adorn every ER diagram need text-based reports that detail the inner workings of a logical model. In my opinion, PowerDesigner sets the standard in this area. The reporting facility in the tool creates what I believe are the easiest to read and digest reports of any other CASE product (although in its latest release of ERwin, Logic Works comes close); also the documents are professional and polished looking.
Designers can work with the reporting facility and use canned reports that build fine looking documents, customize existing reports, or build new reports to produce exactly what they have in mind. Reports are saved in rich text format and can easily be read by popular word processing products, such as Microsoft Word, to add any finishing touches. For the modeling sessions I used to conduct, we would produce PowerDesigner reports with DataArchitect that would be distributed to everyone involved with the session. It was a nice way to wrap up and gave everyone something to look over and think about before the next meeting.
For those interested in PowerDesigner, get ready to open your checkbook --the prices for the modules can set you back a fair amount. ProcessAnalyst lists for $1,495; DataArchitect weighs in at $2,495; AppModeler, $995; WarehouseArchitect, $4,995; and MetaWorks, $995. Discounts are available for bundles, an example being the DataArchitect suite, which will give you all the modules except WarehouseArchitect for $4,995. Big spenders can get the whole shooting match for $9,295.
Overall, I found PowerDesigner to be an excellent companion for any serious database engineer. Powersoft has a definite winner on its hands with the wide range of functionality offered by the different components that make up the product. If you want to take it for a test spin, visit the Powersoft Web site at www.powersoft.com and download a trial to see all that PowerDesigner offers for yourself.
View a table comparing ERwin 3.0 and PowerDesigner 6.0