I’m proficient with ERwin and recently had the opportunity to try ER/Studio. I must say that I was impressed. ER/Studio is the best data modeling tool that I’ve seen to date. My experiments did not cover every aspect. But I did look at enough features to form a clear opinion.
I’ve coded my comments as follows.
A ‘+’ means advantage of ER/Studio. A ‘o’ means advantage of ERwin. A ‘~’ means they are comparable.
~ I had a huge ERwin model from a past data warehouse project. I readily opened the model with ER/Studio and saved it. ER/Studio carried forward everything I checked, including domains, physical data types, nullability, relationships, subject areas, and definitions.
+ ER/Studio can generate schema for more database platforms than ERwin.
o ERwin has an internal metamodel that is readable through SQL. This is a very nice feature. ER/Studio can read internal metadata via macros, but this is less convenient. Macros involve Visual Basic-like programming.
+ However, ER/Studio macros are excellent for updating a data model. For example, a data warehouse application could use one macro to create system attributes for dimensions and another macro to define different system attributes for facts.
+ A severe ERwin flaw is that it is effectively a single user tool. We had a project with a very large data warehouse model that only one developer could update at a time. The ERwin solution is to have separate models and use “complete compare” to combine work. Unfortunately complete compare is buggy and loses relationships. Therefore we had to use ERwin as a single-user tool. ER/Studio has check in and check out from a repository. I didn’t test this but it seems to be perfect for supporting parallel developers on a large data model.
+ ER/Studio has support for data lineage. A developer can reverse engineer a source data model into ER/Studio. In addition a developer can create a target data model. ER/Studio can then relate individual source columns to target columns. It can generate a report with the source-to-target mappings. This all appears to be there in ER/Studio but I didn’t fully test it. ERwin doesn’t have lineage support.
+ ER/Studio keeps schema names consistent with references in stored procedures. For example, the renaming of a column ripples through the affected stored procedures. This is nice. However, updates (such as deletions) are not propagated to stored procedures. It would be helpful if ER/Studio could at least flag affected SPs as inconsistent. ERwin does not support SPs.
+ ER/Studio has support for both entity nesting and entity reference. This is needed for MongoDB. ERwin has no counterpart.
+ I reverse engineered a SQL Server schema with ER/Studio and it was much better at deducing relationships than ERwin.
o I had trouble reverse engineering a MySQL schema with ER/Studio. This worked better with ERwin.
~ Auto layout is similar in ER/Studio and ERwin. It is helpful in both tools.
+ ER/Studio has XSD generation capability. I just took a quick look at this. XSD generation could be helpful in using data models for SOA.