Comparative Review: Sizing Up Data Modeling Software

Editor's note: At press time, Embarcadero Technologies released ER/Studio XE, the successor to ER/Studio Enterprise 8.5. Although there wasn't enough time to review ER/Studio XE for this comparison, it'll be reviewed in a future issue.

Data modeling software can help you get a picture of what your database environment looks like and what it contains. It can also help you design databases and generate code. Despite the sometimes hefty price tags of data modeling packages, they're true productivity tools because they significantly shorten the time it takes to perform tasks. Tasks that took weeks take only days, and tasks that took days take only hours.

Data modeling software typically requires a long learning curve because of the complex capabilities built into it. But a good data modeling software package should have a relatively straightforward "getting started" path. It should also provide most, if not all, of the following features:

• Ability to capture and define business terms (i.e., a glossary)
• Ability to capture and document business requirements
• Ability to capture and document business processes
• Support for multiple database platforms (not just SQL Server)
• Support for two levels (minimum) or three levels (optimum) of data modeling (i.e., conceptual, logical, and physical)
• Error checking on all model levels, based on industry standards for modeling best practices
• Ability to modify best practices to fit individual corporate standards
• Ability to customize and modify model templates to fit corporate presentation standards
• Ability to customize the file management system to fit corporate or personal needs
• Ability to reverse engineer, document, and inventory existing databases
• Ability to verify a model by comparing it to stored model standards, prior to generating SQL Data Definition Language (DDL) code
• Ability to forward engineer a database (i.e., create a new database or refactor an existing one), either by generating SQL DDL code or by creating a live database using ANSI-standard SQL code or the appropriate SQL dialect for the target database platform (user's choice).
• Ability to customize the structure of generated DDL code, such as defining keys and constraints that are internal or external to a table definition
• Ability to define and code file groups, partitions, users, roles, and other infrastructure artifacts
• Ability to define and code tables, columns, defaults, constraints, and other database artifacts
• Ability to define and code indexes, stored procedures, triggers, user-defined functions (UDFs), and other code-based artifacts
• Ability to compare database models at the physical level to map the differences between them
• Ability to share models in a team environment
• Ability to generate reports in multiple formats for publication and sharing
• Documentation in different types of media (e.g., print, online, video)

Using these features as criteria, I recently tested six popular data modeling packages:
 

• Quest Software's Toad Data Modeler for SQL Server 3.5, Xpert Edition
• Altova's DatabaseSpy 2011, Enterprise Edition
• Datanamic Solutions' DeZign for Databases 6.2, Expert Edition
• Embarcadero Technologies' ER/Studio Enterprise 8.5
• CA's ERwin Data Modeler (DM) 7.3
• Sybase's PowerDesigner Studio Enterprise 15.2

Most of the data modeling packages I evaluated have the core features I just mentioned. If a product doesn't have a feature or if it has a special extended feature, I note it in the individual reviews that follow. I also created a chart that compares the six products' features. Table 1 contains that chart.

Note that each vendor has its own nomenclature when referring to the components in their software. To simplify the product evaluations, I use generic terms (e.g., tree view, workspace) in the individual reviews and the product comparison chart.

Toad Data Modeler for SQL Server 3.5, Xpert Edition

Installing Toad was incredibly easy and straightforward. However, the Toad installation dumps everything on the C drive. It would be better if the user could decide on the default file location.

Toad uses the conventional data modeling layout—that is, a tree view on the left, a message area at the bottom, and a workspace that takes up most of the screen's real estate. In Toad, the tree view contains multiple tabs, with each tab containing a project that you've brought into your workspace, plus the various artifacts that belong to the project (e.g., file groups, partition schemes, entities and attributes, relationships, views). The tabbed workspace is where the data model is displayed and edited. The message area contains two small windows: Application View and Message Explorer. Application View is a brief replicate of the tree view. It lets you quickly switch from one view of the model to another by means of a tab. Message Explorer is the log file. It displays errors, warnings, and any other messages that Toad needs to relay to the user.

Toad has some neat features, such as a place for notes in the tree view. You can literally build a punch list of things to do, which is necessary because of the numerous steps involved when creating a new database or refactoring an old one. Toad has a lot of right-click menus that are context-sensitive. Those menus and keyboard shortcuts make repetitive actions fast and easy.

Toad doesn't have a centralized repository for team development, but it does have version control. You need to be in Expert Mode to use versioning, and only projects can take part in the version control system. Shifting Toad into Expert Mode also lets you write and edit scripts, customize package features, and do other advanced tasks.

One productivity feature I really like is the rollover highlighting of primary-to-foreign-key relationships. When you place the cursor on a relationship line, the parent and child columns in the related tables are highlighted—no guessing or searching needed. Managing the model in the workspace is easy and straightforward. For example, getting relationship lines untangled is as simple as right-clicking, selecting Straighten All Lines, right-clicking again, and choosing Change Lines to Right Angle. No data modeling package that I know of produces a compact auto-layout, but Toad's screen management makes re-organizing and re-orienting artifacts easy.

Color-coding of the keys is another feature that I really like. A red key indicates a primary identifier/key. (There's no problem with identity properties, as the property window is fully compliant with SQL Server.) A green key indicates a foreign key.

Toad isn't perfect, though:

• File placement and path control are a struggle, especially once you're under version control. If you deviate from the default location, file management is nearly impossible, even after modifying the Paths entries in the Options page on the Settings menu.
• Placing relationship lines can be troublesome when creating a model. For clarity, you need to have absolute control over where the relationship lines are positioned in the model, and Toad doesn't offer that.
• The Help files aren't inclusive. While working with the model, I encountered unfamiliar terms, but I couldn't find any explanation of those terms in the local Help files.
• Right-clicking to locate the artifact in the tree view doesn't always locate it in the workspace view. When you're dealing with a data model of any size, being able to quickly find a specific object in the model view is a necessity and not just a "nice to have" feature.
• The print facility seemed to be crippled. I couldn't print in landscape mode.

Toad is available in the Professional, Xpert, and Development Suite editions. It's also offered as a freeware package, which is limited to a maximum of five users within an organization.

Quest Software Toad Data Modeler for SQL Server 3.5, Xpert Edition PROS: Intuitive to use; designed to appeal to developers and DBAs; version control for managing models CONS: No way to map to requirements; no support for conceptual data modeling; file placement can be a struggle RATING: 3 diamonds out of 5 PRICE: $1,045 RECOMMENDATION: This fun-to-use tool will keep DBAs and developers on their toes and help them do more with less. It's a good entry-level tool for aspiring data architects. CONTACT: Quest Software · 949-754-8000 or 800-306-9329 · www.quest.com

DatabaseSpy 2011, Enterprise Edition

Installing DatabaseSpy was straightforward, but I had to connect to a live SQL Server machine as part of the startup process. For this review, I was connecting to a remote server with a SQL Server logon. I was prompted for both local and remote credentials, which got a little confusing until I figured out what DatabaseSpy wanted.

DatabaseSpy suffers from an identity crisis. It's like SQL Server Management Studio's (SSMS's) Database Diagrams tool (aka the SQL Diagrammer) combined with Red Gate Software's SQL Compare and SQL Data Compare, plus a report builder. In DatabaseSpy's UI, there are various tree views that resemble SSMS and Visual Studio. The workspace, which is used for editing the database tables, resembles the SQL Diagrammer. There's a separate overview window, which lets you pan the workspace, and a query window, which you can use to query live data. Like the SQL Diagrammer, you're connected to a live database, so changes made to the schema are immediately reflected in reality.

The entire DatabaseSpy model is loaded into memory, which results in speedy editing and display. However, this can be problematic if you have large databases (large in the sense of having many objects in the schema) or a client computer with limited memory. While the load wizard is working, you can't access the Help files or any other DatabaseSpy feature because the load wizard is modal.

DatabaseSpy supports only the physical model, so you can only edit existing objects or add new objects to the database that you're connected to. Because you're connected to a live database, you can inadvertently modify tables and columns and cause chaos (or worse) in a development or production system. I found other problems as well:

• The metadata isn't readily revealed, and there's no lineage tracing capability.
• You can't create new databases.
• In the workspace, scrolling and object resizing is neither efficient nor effective.
• The software package itself was fairly unstable. It appeared to hang multiple times, even though I was using the 64-bit version of the product, which should have enhanced memory management.
• Aborting the Help file aborted the entire software package, which resulted in a loss of work.

DatabaseSpy comes in two editions: Enterprise and Professional. Both are available in 64-bit versions at no extra cost. You can purchase DatabaseSpy separately or as part of a package called the MissionKit 2011 for Software Architects.

Altova DatabaseSpy 2011, Enterprise Edition PROS: SQL Diagrammer-style layout; in-memory modeling provides speedy editing and display CONS: Must be connected to a live database; in-memory modeling limits size of database; supports only the physical model; no way to create new databases RATING: 1 diamond out of 5 PRICE: Starts at $189 (if purchased separately) or $1,739 (if purchased as part of the MissionKit 2011 for Software Architects) RECOMMENDATION: It's SQL Diagrammer on steroids but not a true data modeling tool. CONTACT: Altova · 978-816-1600 or 43-1-545-5155 · www.altova.com

 

DeZign for Databases 6.2, Expert Edition

Installing DeZign for Databases was very easy and seamless. After you plug in the license code and key, the DeZign application opens with a sample entity relationship diagram (ERD) in the workspace, giving the experienced user a good first impression and the new user a sense of place and purpose. DeZign is set up in the conventional data modeling layout.

I was struck by the high comfort level I encountered with DeZign. The visual query builder for views makes creating views a snap, and the ability to set object naming standards (and other options) by project is wonderful. Generated code is arranged in correct hierarchy order. For example, the code deletes any constraints before deleting the tables to which they apply.

There's also a high level of ease in managing models. For instance, there's a function that lets you search for and replace data types across a model, which makes data-type management a snap. And a drag-screen feature makes it easy to move around in a large model.

Switching platforms (e.g., SQL Server to Oracle) is very easy, but it comes with a caveat: The code that's going to be generated will be in the dialect of the original platform. DeZign has a few other drawbacks as well:

• It lacks F1 context-sensitive Help support, and the existing Help options are too limited. DeZign could use more extensive Help menus and video tutorials.
• There's no format painter, so you can't copy and paste formats from one artifact to another.
• During model validation, the schema checker failed to note data inconsistencies between artifacts, which subsequently caused the execution of a generated script to fail.
• You can't create a new folder when relocating data files.
• There's no function (F) key or menu option for showing the entire model, which is essential for navigating very large models.
• You can't set standards for the enterprise, such as the seed and increment values for an identity property

DeZign comes in three editions: Standard, Professional, and Expert. The Expert Edition is the version for both data architects and enterprise architects. (Want to know how these two positions differ? See the web-exclusive sidebar "What's the Difference Between a Data Architect and an Enterprise Architect?") You can purchase DeZign separately or as part of a package called the DBTools Bundle Expert Edition.

Datanamic Solutions DeZign for Databases 6.2, Expert Edition PROS: Extremely easy to use and manage, which translates into high productivity CONS: No way to modify built-in standards for model or reports; no way to set enterprise defaults; not enough choices on how to generate code for supertype-subtype entities RATING: 3.5 diamonds out of 5 PRICE: Starts at $799 (if purchased separately) or $899 (if purchased as part of DBTools Bundle Expert Edition) RECOMMENDATION: If you're already comfortable with data modeling software, if you have your own method of collecting and documenting requirements, and if you don't need to customize standards, then this is a best buy. CONTACT: Datanamic Solutions · 31-0-71-5140901 · datanamic.com

ER/Studio Enterprise 8.5

ER/Studio Enterprise is a big download file, so make sure you have a lot of room on your local disk before starting the process. During the install, you have to set up a user account on the Embarcadero Technologies website.

ER/Studio has so much flexibility and so many features that it'll take years for anyone to grow into it. Data architects who need to maintain strong standards will love the domain control built into it. When you create an entity, you can choose from a prepopulated data dictionary of domains instead of creating every attribute from scratch. The data dictionary contains standard, re-useable attributes similar to user-defined data types, which have the same data type/length, definition/meaning, rules, and constraints, no matter where they're used in the model.

The software uses the conventional data modeling layout. There are multiple ways to navigate large models. The Overview screen gives you a birds-eye view of the entire model. The Pan feature lets you pan the model directly. The Zoom window enlarges whichever artifact your cursor is passing over.

ER/Studio generates both Data Manipulation Language (DML) code, which creates the database schema, and DDL code for selects, inserts, updates and deletes, with triggers to handle updates and deletes of a parent with children and children without a parent. ER/Studio produces the cleanest generated SQL code of all the products I reviewed. In my test, the DDL code executed without a single change required.

There are a few things about ER/Studio that bug me, but not many:

• Many-to-many relationships at the logical level are treated as unresolvable, which isn't a true statement. When you migrate a logical model to its physical counterpart in any relational database, the many-to-many relationship resolves to a join table (also called an intersection entity) with foreign key attributes inherited from the two parents. Therefore, the unresolvable assumption that ER/Studio makes at the logical level is erroneous.
• I couldn't connect to a remote SQL Server machine until I had it registered with my local copy of SSMS. This might be problematic for shops in which data modelers aren't allowed or choose not to use SSMS.
• The Zoom In and Zoom Out options don't work correctly. I had to use the Zoom Window to zoom in on artifacts.

ER/Studio Enterprise is a productivity suite that contains five ER/Studio software packages: Data Architect, Business Architect, Software Architect, Portal, and Repository. Data Architect and Business Architect can be purchased as standalone products, as can several ER/Studio add-ons. The evaluation copy I received shipped with a central repository and a user portal, which I didn't review.

Embarcadero Technologies ER/Studio Enterprise 8.5 PROS: Strong support for standards, domains, data lineage, and metadata tracking; excellent screen management tools CONS: High cost; long learning curve; having to register a remote SQL Server machine with the locally installed version of SSMS RATING: 4 diamonds out of 5 PRICE: $5,100, plus $1,260 for annual maintenance (required) RECOMMENDATION: Although pricey, its awesome model management features make it highly suitable for data architects. CONTACT: Embarcadero Technologies · 415-834-3131 · www.embarcadero.com

 

ERwin DM 7.3

Installing and setting up ERwin DM was easy, although the download took a long time. ERwin employs the conventional data modeling layout. 

Features such as the data-type standards editor and the report template builder add much value to this package. For the busy data modeler, being able to view all the instances of a single type of artifact (e.g., all tables, all columns) in a single list and being able to check or edit their common properties is a terrific timesaver. Coupling ERwin DM with its Saphir option can help anyone struggling with extracting metadata from popular third-party ERP packages.

When creating logical models, you can use the conventional logical data types (e.g., BLOB, string) or more detailed, platform-specific data types (e.g., tinyint, int). You would use logical data types if you planned to push your logical model to more than one type of physical platform, such as pushing it to SQL Server and Oracle. The SQL query tool is handy if you have to view the data in a connected database. Rather than using SSMS, you can view it directly in Erwin DM. The model comparison wizard is one of the cleanest and easiest to work with that I've found.

ERwin DM doesn't support any version of SQL Server prior to SQL Server 2000. The workaround is to script out the database into a DDL file, then reverse engineer from that file. I couldn't dispose of models without first saving them to disk, then using WinExplorer to delete the file, which seemed unnecessarily convoluted. Other annoyances and problems include:

• The workspace is tabbed, but if you have multiple models open at the same time, you can't simply click from tab to tab. You have to choose which model to display from the top menu, which is a little counter-intuitive, given the multiple tab presentation in the workspace.
• The product lacks a direct zoom capability. You can't click the zoom tool and apply it to a single artifact. Instead, the entire screen zooms in or out. This is a tremendous oversight, especially since I couldn't find a way to center the object that I wanted to see close-up.
• The Domain Dictionary window can't be resized, necessitating you to scroll back and forth for attribute/column properties, which is annoying.
• Child windows, such as the Index Manager, aren't readily identified in the window banner, leading to confusion.
• Model validation features aren't integrated into ERwin DM, which I found very disconcerting. To validate a model, you need the ERwin Data Model Validator package, which you must purchase separately.
• When I created a new logical model, I had difficulty finding the forward engineering option in the menus. (It's labeled as derive model.)
• When I pushed the logical model to a physical model, I couldn't find the Check Model/Validate SQL option or the Forward Engineer option mentioned in the online Help file.
• I failed to figure out how to generate SQL code.

CA offers the Erwin DM Community Edition, a freeware version of ERwin DM. CA also offers several other modeling products (e.g., ERwin Data Model Validator, ERwin Model Manager) that you can purchase.

CA ERwin Data Modeler 7.3 PROS: Artifacts easy to use and manage; strong standards editors; has SQL query tool CONS: Doesn't contain all the operations critical to basic data modeling; non-standard option names; non-intuitive modeling behaviors RATING: 3 diamonds out of 5 PRICE: $4,794 RECOMMENDATION: The functionality necessary for data modeling is too fragmented among separate packages. If you were to purchase the other packages in addition to Erwin DM, you'd have a good data architect toolkit. CONTACT: CA · 631-342-6000 or 800 225-5224 · erwin.com

PowerDesigner Studio Enterprise 15.2

The PowerDesigner installation DVD comes with a set of video tutorials that really help the new user get started. (Even existing customers who upgrade will find the tutorials helpful because there are so many new features in version 15.) Installation was a non-event. PowerDesigner uses the conventional data modeling layout.

The newest version of PowerDesigner offers enterprise architecture modeling, with support for multiple enterprise architecture frameworks such as the Zachman Framework, The Open Group Architecture Framework (TOGAF), and Federal Enterprise Architecture Framework (FEAF). Plus, you can build your own custom framework. Enterprise architecture models let you map all organizational systems and processes, aligning stakeholders from all parts of the business (and not just technology) with strategies and goals. You can organize models by project, create shortcuts for documents, and relocate documents all from the PowerDesigner UI. You can link models within a project.

In addition to all the conventional modeling notations, PowerDesigner strongly supports the object-oriented (OO) world. For example, the OO model supports class, object, use case, collaboration, sequence, statechart, activity, component, deployment, and composite structure diagrams.

PowerDesigner has strong large-model management capabilities as well. For example, you can divide model components (e.g., table artifacts, relationships) into packages, which are a form of submodel. Packages can be nested. File and artifact management is extremely easy. You can use a form for creating and editing tables and their associated artifacts (columns, keys, indexes), or you can use the grid-like list layout, which is extremely customizable. There is a centralized repository, with check-in and check-out capabilities. The model's find operation automatically presents the target artifact in close-up mode, so it's easy to manage.

Individual or organizational preferences, such as display preferences for the model, are easily modifiable, with a large library of options. There's strong support for domain data typing. You can create data domains and use them across all model levels. There's equally strong support for modeling standards, which are set by default to industry best practices, but they're completely customizable. The impact analysis capabilities (both cross-model and lineage) are amazing. After you've modeled the enterprise, you can run an impact analysis to display downstream and upstream impacts of a change across the enterprise.

The report wizard makes report generation quick and straightforward. The generated reports are hyperlinked—the metadata links to the relevant artifact and vice versa. Reports are saved with the model, after which you can edit them in the Report Editor. Report templates are customizable.

In the requirement model, artifacts can be linked to other model artifacts for traceability. The optional repository, which is a central storage location for all models, is secure. A login is required, and individuals or groups can be assigned specific permissions. You can browse through the artifacts in the repository and edit them after extraction. Conflict resolution management upon check-in lets you reject, overwrite, or merge changes from multiple modelers. There is a web-based user portal for the repository and a search engine.

Even this most elegant of data modeling software has faults:

• Relationship line management can be awkward, even difficult.
• It doesn't have an option that lets you view all the artifacts for models, which are split into packages.
• The trial period is only 15 days. Due to the complexity of the software, 15 days isn't enough time to get acquainted with the core capabilities, much less the extraordinary set of features that support enterprise architecture.
• Until you've used this product long enough so that you recognize its power, it's hard to develop a viable argument for the purchase price.
   

PowerDesigner is the only product of those reviewed that goes beyond data analysis and modeling. It reaches out to the enterprise and lets you model the organization. In addition, you can tie documents (static and otherwise) and databases into the organizational framework model. PowerDesigner is by far the most complete organizational modeling package on the market. Right out of the box, you can model business communications, city planning, organization charts, process maps, application architecture, service-oriented designs, and the technology infrastructure in your shop, with no need to purchase additional products.

Sybase PowerDesigner Studio Enterprise 15.2 PROS: Supports the Zachman Framework; supports the entire Unified Modeling Language (UML) spectrum; integrates database and application schema and logic; maps requirements to database artifacts; full standards modification and enforcement capabilities CONS: High cost; long learning curve RATING: 5 diamonds out of 5 PRICE: $7,495 RECOMMENDATION: If you need an extremely high level of capability and functionality that will enable you to model the enterprise, this is the software for you. Contact: Sybase (an SAP company) · 925-236-5000 or 800-792-2735 · www.sybase.com

Editor's Choice

All the data modeling packages I reviewed aim to ease your workload and help you create a better work product deliverable. Some achieve these goals better than others. The product that will work best for you depends on your job duties and your budget.

The reviewed software falls into two general categories:

• A classic package that's meant for serious data or enterprise architects who are responsible for all seven steps of the database lifecycle (i.e., discover, design, develop, deploy, day-to-day, defend, and decommission).
• A lighter-weight product that's targeted at developers and DBAs who might need to only inventory or refactor existing databases.

PowerDesigner Studio Enterprise, DeZign for Databases, ER/Studio Enterprise, and ERwin Data Modeler are classic packages, whereas Toad Data Modeler for SQL Server and DatabaseSpy are lighter-weight products.

For DBAs and developers, I recommend Toad Data Modeler, despite my struggles with file management. The package is intuitive, so it's easy to learn and use. It's also built to enforce modeling best practices, so it'll help grow your modeling skills. Besides, it's just plain fun to use.

For data architects, I recommend one of two products. If your primary driver for choice is budget, go with DeZign for Databases. It has tons of capabilities with a high comfort and productivity factor, and is clearly a best buy. However, if your primary driver is standards enforcement, go with ER/Studio Enterprise. This is the most powerful software package for data architects that I evaluated.

For the enterprise architect, I recommend PowerDesigner Studio Enterprise. It's an elegant product with numerous capabilities, which is why it deserves the title of "Editor's Choice." It'll probably be a few years before you learn how to take full advantage of it, but during that time, it'll guide you into using industry-standard best practices and help you hone your enterprise architect skill set.