er/studio 2016 vs. erwin 9.64 comparison...

ER/Studio 2016 vs. ERwin 9.64 Comparison Guide Copyright @ 2016

Enterprise Architecture . Modeling

4900 Hopyard Road . Suite 100 Pleasanton . CA 94588

T 925 . 736 . 3400 www.emodelers.com

ER/Studio 2016 vs. ERwin 9.64

Comparison Guide

Dr. Nicholas Khabbaz

François Cartier

e-Modelers, Inc.


!  TOOL USABILITY

!  DATA MODEL ORGANIZATION

!  OBJECT DEFINITION AND NAMING

!  RULES AND CONSTRAINTS

!  DATA MODEL EXTENSIONS

!  FORWARD ENGINEERING

!  ENTERPRISE MODEL DEVELOPMENT

!  MODEL MANAGEMENT

!  MODELING AUTOMATION

!  DATA TO BUSINESS PROCESS ALIGNMENT

!  DATA TO APPLICATION ALIGNMENT

!  TEAM COORDINATION

USABILITY)

Introduction

Key Semantic Differences

Key Capability Differences

Tool Comparison Process

Conclusion

Authors

Appendices

3

3

5

5 7 8

10 11 12 13 15 16 17 18 19

20

21

22

23

Content


In this paper, we propose a guide for comparing the ERwin 9.64 and ER/Studio 2016 modeling tools. The comparison covers only a limited set of key features that relate to key differences between the two modeling tools. Many features that are more or less similar are excluded in this document. First we highlight key semantic differences between ER/Studio and ERwin in order to facilitate reading the rest of this document. Next, we look at key capability differences. Finally, we use the results of the capability differences to come up with a proposed tool comparison process. Both tools have a rich set of features. This guide may be refined and expanded to create a comprehensive comparison of ERwin 9.64 and ER/Studio 2016 based on a set of tool and project requirements. Over the past twenty years, we have been delivering ER/Studio and ERwin professional services to over 80 corporations and government institutions in the U.S. and Canada. Our opinion relies extensively on our experience from projects delivered in both tools, as well as extensive feedback received from our clients. In some instances, ER/Studio and ERwin use different terms for similar features and capabilities, and in other cases they use the same term for different features or capabilities. The following are key basic semantic differences. ! DIAGRAM

! In ER/Studio Data Architect, a diagram is a container of a logical model, one or more physical models, a local data dictionary, a pointer to an enterprise data dictionary, and a data lineage model. The diagram is implemented as a dm1 file.

! In ERwin, a diagram is a distinct display area for selected model objects that have been included in a given subject area. A subject area can include

Introduction

Key Semantic Differences


multiple diagrams, and a diagram belongs to only one subject area. The auto-populate feature in a diagram synchronizes the diagram to the subject area.

! MODEL

! In ER/Studio Data Architect, a model is a container of model objects, such as entities, attributes and relationships at the logical level, and tables, columns and relationships at the physical level.

! In ERwin, a model is a file that contains model objects such as subject areas,

entities, tables, relationships, domains, validation rules and user defined properties. The model may be Logical Only, Physical Only, or Logical/Physical. Regular ERwin models are saved as .erwin files, and ERwin template models as .erwin_tmpl files.

! SUBMODEL VS. SUBJECT AREA

! In ER/Studio Data Architect, a Submodel is a subset of logical or physical model objects representing a specific perspective of the main model. One such perspective could be a subject area model. Submodels may also represent a variety of other perspectives. They may be nested. Submodels and nested submodels are automatically synchronized with the main model.

! In ERwin, a Subject Area is used to designate a set of model objects relevant to

a business topic, and a set of diagrams. Some of the displayable members of the model object set may not be included in any of the members of the diagram set. Subject areas are independent of each other and not synchronized. However, an autopopulate feature in a subject area synchronizes the subject area with its model.

! USER DEFINED PROPERTY (UDP) VS. ATTACHMENT

! In ERwin, a user may define additional properties to model objects of a given type through an editor. Unlike regular properties such as validation rules, a User Defined Property (or UDP) is created and used as either a Logical property or a Physical property but not both. When the user creates a UDP on a column, then all the columns assume the UDP as a default but only in a Logical model or a Physical model.


! In ER/Studio, an Attachment is defined then bound to one or more logical and physical model objects. There are different categories of attachments such as files, lists and URLs. Attachment Types are groupings of attachments. A user may specify model object types that only certain Attachment Types may be associated with.

! MACROS VS. SCRIPTS

! In ER/Studio, a Macro is a set of statements written in the WinWrap Basic language (similar to Visual Basic). It can be developed and executed from a Macro Library, or embedded in a model. Macros may be grouped in user defined folders that are labeled according to their common purpose. ER/Studio Data Architect comes with a number of useful macros.

! In ERwin, a Macro is a function from one of several ERwin Macro languages. It is

used either in model Scripts and Script-like templates, forward engineering templates, reporting templates, or in APIs. An ERwin Script is a named set of ERwin macro-based statements to be executed during forward engineering, either before the generated DDL (Pre-Creation) or after it (Post-Creation). Scripts are typically used to generate RI triggers. They can be used outside of DDL to generate simple reports, like model object counts and data business rules lists.

! TOOL USABILITY

! Underlying Tool Structure

! ER/Studio provides an underlying object-oriented structure around model object types, model objects, object properties, and binding between objects. This structure is reflected in the user interface to make it quite consistent and easy to use.

! ERwin provides an internally consistent underlying structure around

model object types, model objects, object properties, and links

Key Capability Differences


between objects. However, even though all the object type editors in ERwin have a consistent look and feel, they are in our opinion not as visually compelling (e.g. lack of color variety). Furthermore, the complexity of the internal structure is not well reflected in the documentation.

The consistent user interface in ER/Studio and the consistency of the binding operations between objects across the tool, provide substantial usability benefits, including reducing the tool learning curve and enhancing user productivity.

! Model Display Levels and Options ! ER/Studio has a versatile Model Display Options capability that enables

the selection and display of a wide variety of model object types including domains, keys, definitions, notes, attachments, data security, and indexes.

! ERwin can display a selected set of entities/tables at different levels

(entity or table, PK, attribute or column, definition or icon for Logical or Physical display) in a diagram. The scope is limited to some of the object types including entity/table, attribute/column and view. User Defined Properties (UDPs) for instance cannot be displayed in the diagrams.

The ER/Studio Diagram and Object Display Options capability has a wider selection and variety of displayable object types and metadata.

! Colors & Fonts

! ERwin supports themes, which are named sets of font and color default specifications for displayable model object types.

! ER/Studio Data Architect has a flexible method for setting up fonts and colors for model object types. These settings could be different for each model and submodel. In addition, fonts and colors can be set for specific objects such as an attribute in an entity. However, the set of font and color specifications cannot be saved or reused as an independent set.

The ability of ERwin’s themes to save colors and fonts and re-use them across multiple models provides a consistency of colors and fonts across multiple models while reducing model development time.


! Spell Checking

! ERwin provides multiple dictionaries for spell checking definitions, notes, extended notes, and comments in all model objects. It also provides various spell check options, including suggested replacement words. From within ERwin, users can add, modify and delete entries from any dictionary or create their own. A dictionary for acronyms is also included.

! In ER/Studio, there is no spelling checker. However, definitions and

comments are typically entered in a spreadsheet along with other model objects then imported into a model. The spell checking is done in the spreadsheet.

The ERwin internal spell checking of textual properties of model objects improves textual consistency and saves time.

! DATA MODEL ORGANIZATION

! Model / Submodel Organization

! In ER/Studio Data Architect, a logical model and multiple corresponding physical models, each corresponding to a different DBMS can be contained in the same diagram. A model may contain a hierarchy of submodels and nested submodels. There is no limitation to the number of submodels or the nesting level of submodels. The synchronization of all the submodels with the main model is maintained at all times by default. However, specific submodels may be set to not synchronize with the main model.

! In ERwin, subject areas are all independent of each other and not

synchronized. However, a subject area may be marked as “auto populate”, becoming the “main subject area”, where other subject areas are synchronized to it. Similarly, diagrams within a subject area are independent of each other and not synchronized except with a diagram marked as “auto populate”. The “auto populate” mode of diagrams is common, as each diagram is used to display the same entities/tables and relationships that are part of a subject area at various levels of modeling; e.g., high level, conceptual, logical or physical.


In ER/Studio Data Architect, the diagram organization that contains one logical model and multiple physical models is compelling. This is especially the case in projects where a model (or its various submodels) are deployed on multiple databases, or when multiple dimensional models share the same logical model as in the case of data warehousing projects. Submodel nesting may be leveraged to break down a data model into different perspectives. This capability becomes very useful when developing large models such as enterprise or data warehouse models. In ER/Studio Data Architect, a large model may actually be developed and managed from the nested submodels while the main model is used primarily for model object synchronization. This capability substantially improves model development. In an ERwin model, there is no nesting of subject areas.

! Object Type Grouping ! In ERwin, object model types are all combined in a single list. Any

model object in a logical model can be promoted to the enterprise level. However, on the physical level, an intermediate enterprise-wide model template for each DBMS version would have to be created.

! In ER/Studio Data Architect, object model types used in developing a

model are organized into 2 groups: basic modeling group such as entities, attributes and relationships, and a separate data dictionary that encapsulates the remaining object types such as attachments, domains, rules and naming standards templates. The data dictionary applies to both the logical and physical models. Each of the data dictionary model object types can be promoted to an enterprise data dictionary that can be shared across models.

The data dictionary organization in ER/Studio Data Architect is compelling. Both an enterprise data dictionary and a local data dictionary containing enterprise and local data model objects respectively are maintained in the same model.

! OBJECT DEFINITION AND NAMING

! Terms and Definitions ! In ER/Studio Team Server, multi-level business glossaries may be

created. For example, an enterprise level glossary may be created and project level glossaries may inherit from it and extend it. Terms may be created and associated with glossaries. Terms can then be


pulled from the glossaries to be used in model objects. Conversely, terms can be pushed from model objects to the glossaries. Glossaries and terms are made available to a wide variety of model stakeholders helping to improve the consistency of terms and definitions across the enterprise.

! In ERwin Web Portal, a glossary of terms and their definitions may be

created from models. In addition, the ERwin Web Portal can maintain an enterprise glossary of business terminology and object definitions from template models. As in the case of ER/Studio, glossaries and terms are made available to a wide variety of model stakeholders.

! Naming Standards Templates ! In ER/Studio Data Architect, a Naming Standards Template enables

the definition and enforcement of naming standards in logical models, physical models, and the transformation from logical to physical models or from physical to logical models.

! ERwin has a similar capability named “Naming Standards” which

incorporates a glossary for each available Naming Standard, only one of which can be active at a time. A Naming Standard transforms names only from logical to physical models, with a choice between two sets of abbreviations, a long (e.g., for Oracle) and a short abbreviation (e.g., for DB2). ERwin has also a Naming Standards Compliance checker that reports on naming irregularities, such as an attribute or domain name whereby the last word is not a classword in the glossary.

In ER/Studio Data Architect, the enforcement of naming standards in a logical model or in a physical model is quite useful, as it ensures that the model continues to be conforming to standards as it is changed or merged.

! Domains

! In ER/Studio Data Architect, dictionary objects such as defaults, rules, data security information and attachments may be bound to a domain. These bindings are inherited by all attributes the domain is associated with. Subdomains can be defined and synchronized with the higher level domains. In addition, ER/Studio supports domain folders that are key in organizing large numbers of domains that typically emerge in large data models.


! In ERwin, object types such as UDP, default and validation rules may be bound to a domain. As in ER/Studio, these bindings are inherited by all attributes the domain is associated with. However, a large number of domains may be organized not with a domain folder structure, but by creating additional layers of subdomains.

The ER/Studio domain folder feature where domains can be organized in a multi-level folder structure is most compelling. If such folder structure is well designed, domains can be easily found and re-used.

! RULES AND CONSTRAINTS

! Column Level Rules

! In ER/Studio Data Architect, column level rules are created in the data dictionary and bound to other objects such as columns and domains. These rules are expressed as DBMS-specific constraints. These rules can also be attached to entities, columns and domains, and can be generated in XML but not in DDL. Another type of rule, Reference Values, can also be created in the data dictionary. They are expressed in logical terms (min/max and list of values).

! In ERwin, validation rules (min/max, list of values or user defined) may

be created on an attribute, column or UDP. However, validation rules utilizing min/max or list of values are expressed in terms identical to ER/Studio Reference Values.

In ERwin, rules around min/max and list of values can be generated as DBMS-specific check constraints. In both tools, rules can be promoted to an enterprise level.

! Table Level Constraints ! In ER/Studio Data Architect, one can create entity/table level

constraints, such as a constraint involving two attributes/columns in the same entity/table.

! In an ERwin physical or logical/physical model, one can create a table

level user-defined validation rule that automatically generates a DBMS-specific table check constraint.


ER/Studio Data Architect has the ability to capture rules at an early stage of the forward engineering process. This enables a better alignment with an enterprise data model where enterprise level rules are defined. However, these constraints are DBMS specific and not expressed in logical terms.

! DATA MODEL EXTENSIONS

! Attachments and UDPs ! In ER/Studio, attachments can be associated with model objects of

different model object types in both the logical and physical models. Attachments are grouped into attachment types. Attachments and attachment types may be constrained to certain objects and object types respectively.

! In ERwin, a UDP can be associated with only one model object type,

either logical or physical, but not both. For example, in order to support two object types with the same property such as list of values, two UDPs with the same name will have to be created, but their list of values has to be kept in sync manually. Once created, a UDP is automatically bound to every instance of the associated object type and the default value is automatically assumed, unless overridden.

Attachments and UDPs are used to cross-reference model objects with internal information and documents such as business requirements and use cases, as well as external documents such as market and regulatory information. Resulting benefits are many including improved traceability from business requirements to logical to physical models, alignment with industry standards, improved data administration and improved communication among the model stakeholders, to name a few. ER/Studio Data Architect has a more flexible attachment capability, enabling an attachment to be bound to multiple model object types at the same time in both the logical and physical models.

! Targeted Mappings

! In ER/Studio Data Architect, the Data Security Information enables

targeted mappings such as security and compliance. For example, different types of compliances can be defined such as regulatory compliance or corporate policies. Regulatory compliance can be broken down into Patriot Act and Sarbanes-Oxley, which in turn could be assigned to model objects such as entities, tables and columns.


Audit reports are then easily produced. Data Security Information can be displayed on the model separately from attachments. In that way, displaying attachments does not reveal data security information.

! In ERwin, UDPs can be used to define and report on targeted

mappings such as security and compliance. However, since a UDP is automatically bound to every instance of the associated object type, there is no segregation of UDPs by security or compliance as mentioned above.

The Data Security Information in ER/Studio Data Architect facilitates targeted mappings such as ones related to security, confidentiality and compliance. These mappings enable quick and comprehensive audit reports that can demonstrate compliance to data governance or regulatory requirements.

! FORWARD ENGINEERING ! Denormalization Mappings

! In ER/Studio Data Architect, denormalization mappings keep track of

the mapping of roll downs, roll ups and table splits. These mappings are saved and shown under the “Where Used” tab in participating model objects such as entities and tables.

! ERwin has the same capabilities as ER/Studio Data Architect, but does

not preserve the mappings when transforms are applied within the same model.

ER/Studio denormalization mappings is a very useful capability in forward engineering as it provides the means to document and maintain the traceability between a normalized logical model and a denormalized physical model within the same diagram. Such traceability is essential to support business agility; i.e., translating changes in business requirements to a logical model then having the ability to quickly map those changes to a denormalized physical model.

! Comparing and Merging Models

! The ER/Studio Compare and Merge utility is quite versatile. It is used in

multiple different ways such as to compare models at different stages of a forward engineering process; i.e., conceptual to logical, logical to physical, physical to database schema.


! The ERwin Complete Compare has a similar capability. It compares all selected model objects within selected model object types, their associations, and their properties within selected property types, whether natural or derived.

Both tools have a rich set of functionality around comparing and merging models, which is used ubiquitously in data model development. However, ERwin Complete Compare provides the extra capability to compare and merge object types such as domains, validation rules, defaults and themes. ER/Studio Data Architect, on the other hand, supports Alter SQL statements when comparing a model with a schema that includes complex views.

! ENTERPRISE MODEL DEVELOPMENT

! Enterprise Objects ! In ER/Studio Data Architect, all data dictionary objects can be

promoted to an enterprise data dictionary (EDD) that becomes part of the ER/Studio Repository. The EDD is made available to diagrams in the repository and can therefore be applied uniformly to data model objects across all selected data models in the repository. The EDD is shown below the local data dictionary in a diagram, so both dictionaries can be used in the same model.

! ERwin maintains within and outside of Model Mart “Model Templates

Models” that include enterprise definitions for shared entities, domains, validation rules, default rules, macro scripts, default diagram properties and color legends. Synchronization of a model with a template ensures that the model has all enterprise model objects associated with it.

ERwin provides a wider selection of enterprise object types including shareable entities, re-useable color schemes and tables such as used in conformed dimensions. Support for shared entities can be very useful in certain projects such as enterprise data modeling and MDM. On the other hand, macros are leveraged in ER/Studio Data Architect to provide an efficient way to transition objects to the enterprise, such as switching domain bindings from a local data dictionary to an enterprise data dictionary.


! Reverse Engineering ! In ER/Studio Data Architect, a database schema or DDL may be

reverse engineered into a physical model and a logical model simultaneously. A Naming Standards Template can then be used to map physical to logical names and enforce logical naming standards.

! In ERwin, a database schema or DDL can be reverse engineered into

a Logical/Physical model. Entity and attribute names are automatically copied from table and column names. In order to generate desired logical names from the physical counterparts, physical names would have to be downloaded to a spreadsheet using Bulk Editor, a reverse translation (from an abbreviation to a word) would have to be performed using the Glossary copied from a csv file, and the resulting expanded logical names uploaded back to the model using Bulk Editor.

The ER/Studio Naming Standards Template provides a more streamlined way of mapping physical names back to logical names in reverse engineering, including a capability to resolve mapping the same physical abbreviated names to different logical names. Both tools provide different layout options to facilitate the analysis and organization of the reverse engineered models.

! Mappings between Model Objects ! ER/Studio Data Architect supports a “universal mappings” capability

whereby model objects such as tables or columns can be linked together. This can be used to link tables or columns in reverse engineered models that are semantically equivalent. The Compare and Merge utility is used to establish a mapping between them. Such mapping can then be saved and displayed in the “Where Used” tab of the respective tables or columns.

! In ERwin, an attribute/column can be linked to another

attribute/column, based on selected properties, such as a datatype. The link can be bidirectional, in which case the selected property of both attributes/columns is kept synchronized. The resulting links are show under the “Where Used” tabs. ERwin manual synchronizations (equivalent to ER/Studio’s universal mappings) cannot be saved, and are lost upon exiting Complete Compare.

In developing an enterprise data model, it is important to map the enterprise entities to the corresponding tables in the existing databases. During that process, the intermediary mappings between tables and columns across


reverse engineered models become an important step. Such mappings, if saved and leveraged such as in ER/Studio universal mappings, provide substantial time savings benefits in enterprise model development.

! MODEL MANAGEMENT

! Model Change History ! ERwin Model Mart keeps track of major events around model object

history such as object creation, linkages, transformations, splits and imports. The events can be tracked for various model object types including model, entity/table, attribute/column and view.

! ER/Studio Repository keeps track of object creation, but not of object

history. ER/Studio Repository Change Management also keeps track of a log of changes in model development entered by users, upon their model check out or check in. The log is traced at different levels, such as diagram, model, submodel or entity.

The ERwin Model Mart object event tracking is comprehensive and quite effective for certain uses such as data governance, data stewardship and audit. The ER/Studio Change Management, on the other hand, provides the required traceability of model changes by one or more users, thus reducing the time to resolve model change conflicts, if and when they occur.

! Model Versioning

! ER/Studio Repository incorporates a “Named Releases” feature, which

enables users to save a new release or a new version of the model. Named releases are read only and can be given a description by the user creating the release. A model may be rolled back to a particular named release. ER/Studio Repository also supports a branch and merge capability whereas separate branches, such as for model development, UAT and production can be created. A version of a model can then be merged from one branch to another.

! Unlike ER/Studio Repository, Model Mart automatically increments the version number when the model is saved or checked in. A user may enter descriptive text related to changes in a model version. When a model is checked in, only changes in the model are saved. In


addition, past versions of a model can be “marked” (hardened) to prevent accidental deletion.

! MODELING AUTOMATION

! Macros and Scripts ! ER/Studio macros use a Visual Basic like language that acts on data

models. Macros are used to extend the functionality of ER/Studio. ER/Studio comes with a number of pre-installed macros that users can change or extend. Alternatively, users can build their own custom macros. Macros are used primarily for automating repetitive tasks, import/export to Excel, identify exceptions, and provide customized reports. The Macro language is very well documented.

! In ERwin, simple macro scripts can be written, e.g. to generate INSERT

statements for reference tables based on validation rule valid values. Referential Integrity Trigger Template macro scripts come with the product. A Bulk Editor is used to import and export to csv files. In addition, an API set for custom add-in utilities and external applications can be programmed in any COM supported language. This enables a set of models to be accessed and modified simultaneously. Finally, the Visual Basic code of the ERwinSpy program is provided as an example of ERwin API use.

ER/Studio macros enable new compelling capabilities while saving time. These macros are wide in scope and, unlike the case of ERwin, provide a consistent way to automate many tasks. The ERwin Bulk Editor, even though limited in scope, is useful for import/export to csv files, as it exposes most ERwin model object types and their properties.

! Extending internal events ! ER/Studio provides a set of “Handlers” that extend the internal

operations of ER/Studio events including object creation, deletion and update. Handlers may be programmed to provide an immediate action based on an event. For example: When an attribute is deleted, check if that attribute was using a domain. If so, check if that domain is still used by other attributes or child domains. If not, delete that domain. In this example, a domain is therefore deleted


immediately when no attribute is bound to it, satisfying a model validation rule.

! ERwin does not have an equivalent capability. All handlers are

internal to the ERwin tool and not accessible by the user.

Handlers, even though not frequently used, can in some instances be useful in improving the quality of the model or the agility of the model development process.

! DATA TO BUSINESS PROCESS ALIGNMENT

! Business Process Construction

! ER/Studio Business Architect provides a rich BPMN-compliant business

process modeling capability. Process model construction is iterative and well documented. Key model object types include tasks, sub-processes, gateways and events. Model objects such as sub-processes can be shared enabling the discovery of process improvement and optimization. In addition, the set of business process model objects can be shared in Team Server providing a way to propagate the knowledge to a wide range of architecture and modeling stakeholders.

! Impact Analysis ! Data Model Objects including entities/tables and attributes/columns

can be imported into Business Architect and associated with business process tasks. The association includes a CRUD matrix. Once the associations are made, the impact of a change of the process model on the data model and vice-versa can be easily viewed. For example, one can view the impact of a change in an activity or event in the process model on a set of tables. A data steward can have an integrated view of data and process model components and perform a wide variety of impact analyses.

Business process modeling is important to construct and organize standards-compliant business processes, which provide many benefits. In data modeling projects related to transactional systems or data migration, the association of business process and data models is important in aligning data and business processes. Such association enables a comprehensive set of impact analyses


that would be invaluable for data and business architecture development and governance. BPwin, the companion product of ERwin is unfortunately no longer supported.

! DATA TO APPLICATION ALIGNMENT

! Views

• ER/Studio Data Architect supports both Logical and Physical Views. Even though logical views include DBMS-specific constraints and SQL validations, they can be leveraged for proper Data Requirements Modeling, thus aligning application and data requirements.

• ERwin supports only physical views, but has improved support of

Materialized Views. For instance, the display of Materialized Views is similar to the display of regular views, and the handling of Materialized Views provides the required consistency and alignment of a physical model with an operational database.

Views are useful to map application requirements to data. Each of a set of application requirements may, for example, be mapped to a logical view in ER/Studio Data Architect, which in turn is implemented as a physical or materialized view.

! Model Object Grouping

• ER/Studio Data Architect supports a new “Business Data Object” capability (or BDO for short) that consists of a grouping of model objects around a key object called “anchor”. BDOs are created within a data model or submodel by selecting model objects and including them in the group. The BDO can be shown as expanded or collapsed. There is no similar capability in ERwin.

BDOs are useful to communicate mappings from data model objects to business objects. For instance, when designing a set of data services, one can assemble the required data model objects that are required by each data service into a corresponding BDO, or a set of BDOs. The data structure in each BDO could then be mapped to a class structure that provides a basis for developing the data service. BDOs facilitate the communication between data and application stakeholders, and therefore the alignment of data and


application architectures. Such alignment provides substantial benefits in terms of faster and agile development, as well as improvement in application design.

! TEAM COORDINATION

! Shared Portal Capabilities

• The ER/Studio Team Server capabilities include browsing and

visualization of models and data lineage, model object drill down, versatile advanced searching and customized reporting that include all model objects. Furthermore, Team Server integrates both data and process model objects.

• ERwin Web Portal supports similar browsing, model visualization and

model object drill down capabilities as ER/Studio. However, it supports only basic search and reporting capabilities. In addition, ERwin Web Portal provides “Semantic Mapper” and “Data Mapper” capabilities.

ERwin Semantic Mapper capability can be useful for various types of impact analysis in applications such as data governance, while the Data Mapper can be used for mapping source to target metadata in applications such as data warehousing. ER/Studio Team Server enables sophisticated searching and reporting across both data and business process model objects, effectively supporting enterprise team coordination.

! Social Media Type Coordination

• ER/Studio Team Server provides a social media type interface where a team member may “follow” model objects and glossaries as well as other team members, and view who and what team members are following. A team member may also view streams of events around resources such as a selected glossary or term, or a specific model object. Finally, team members can post messages to each other and be notified when messages are received. ERwin does not have a similar capability.

This capability, if managed properly, can substantially improve team coordination, as each team member can focus on resources of interest to them while tracking these resources in an effective and timely manner.


! Sharing External Data Sources

• ERwin Web Portal provides a new capability of connecting to existing data sources and exposing them as additional data models. This is performed through the reverse engineering of data stores from both relational databases, using JDBC, and from non-relational databases such as HADOOP. The reverse engineered models are then imported into the Web Portal. One can then communicate comments with a desired importance level on given model objects or glossary terms. A graphical overview of various tier layers is provided through an Architecture Diagram where each layer can be expanded and drilled down. ER/Studio does not have a similar capability.

This capability, if managed properly, can enrich the set of data sources such as Big Data sources in the Web Portal providing a more comprehensive analysis and reporting capability.

We propose the following tool comparison process: A. Specify the set of required projects that the tool will be used for, such as enterprise

architecture, data warehousing and MDM.

B. Specify the set of modeling objectives that need to be achieved. For example:

! Ensure high data quality in the downstream databases. ! Ensure that data, application and process models are all well aligned. ! Ensure that changes in business requirements will be handled speedily and effectively. ! Ensure a high level of productivity in model development.

C. Ensure that the tool capabilities meet the modeling objectives.

! Start with the example in Appendix 1 as an initial mapping between the tool

capabilities and the modeling objectives. Each capability, if properly used may have a “High” or “Medium” impact.

! Adjust the set of tool capabilities and mapping as you see fit. ! Check whether the set of tool capabilities covers all modeling objectives.

Tool Comparison Process


D. Use a scoring method to compare ERwin and ER/Studio in terms of meeting the required tool objectives. ! Provide a relative weight to each tool capability as shown in Appendix 1. ! For each tool, provide a score (0-10) for each of the selected capabilities. ! For each tool, compute the total capability score (TCS) by multiplying the

capability scores by their relative weights and summing them up.

E. Ensure that the tool capabilities effectively meet all project and DBMS requirements.

! For each tool, map the tool capabilities that were selected in C above to the required projects as shown in the example in Appendix 2.

! Check whether the set of tool capabilities covers all project requirements. ! Check whether the tool supports all key required DBMS. Appendix 3 shows the list of

DBMS supported by each tool. F. Make the tool selection.

! Drop a tool that does not fully cover project requirements or support for key DBMS. ! If both tools fully support project and DBMS requirements, select the tool with the

highest TCS as computed in D above. Both ERwin 9.64 and ER/Studio 2016 are well developed, enterprise-grade modeling tools. We have delivered successful complex projects in both tools. In our comparison, we covered at a high level only a limited set of features that highlight key differences between these tools. Both tools have a wide array of other features and capabilities that are more or less equivalent in how they are used in different projects. In selecting the best tool, we strongly recommend to first consider high level modeling objectives as shown in Appendix 1. Take Enterprise Alignment for example. Mis-alignments between process and data, or application and data are eventually too costly and could lead to failed projects. We have seen it too many times! The same is true for Data Quality which involves a variety of modeling concepts, standards, capabilities and processes, some (but not all) of which can be supported by a tool. High agility and productivity in data model development have become the norm, as planned timeframes for complex model and system development have shrunk.

CONCLUSION


After considering high level modeling objectives, take the time to go through the tool comparison process as outlined above. Change it and extend it as you need to. We believe that going through such a process will be quite effective in selecting the right tool that best meets a set of modeling requirements.

This white paper was developed by Dr. Nicholas Khabbaz and François Cartier of e-Modelers, Inc. Dr. Nicholas Khabbaz is a Senior Enterprise Architecture Consultant who has delivered ER/Studio training and consulting for more than 10 years. François Cartier is a Senior Data Modeling Consultant who has been an ERwin consultant for over 15 years. e-Modelers, Inc. is a professional services company specializing in enterprise architecture and modeling. We are located in Pleasanton, California. Please send us your comments to [email protected].

AUTHORS


Tool Capability WeightModel Quality

Enterprise Alignment

Agility Productivity

Tool Usability 7

Data Model Organization 5

Object Definition and Naming 10

Rules and Constraints 5

Data Model Extensions 7

Forward Engineering 10

Enterprise Model Development 10

Model Management 5

Modeling Automation 5

Data to Business Process Alignment 8

Data to Application Alignment 8

Team Coordination 20

100

High Impact

Medium or Low Impact

Appendix 1: Mapping of Tool Capability to Modeling Objectives


Tool CapabilityEnterprise

Data Modeling

Enterprise Data

GovernanceMDM

Tool Usability

Data Model Organization

Object Definition and Naming

Rules and Constraints

Data Model Extensions

Forward Engineering

Enterprise Model Development

Model Management

Modeling Automation

Data to Business Process Alignment

Data to Application Alignment

Team Coordination

High Impact

Medium or Low Impact

Appendix 2: Mapping of Tool Capability to Project Requirements


DBMS supported

ERwin DM 9.64 ER/Studio DA 2016

IBM DB2for I (5.x to 7.x), for LUW (9.5, 9.7, 10.x), for z/OS 8.1, 9.1, 10, 11)

AS/400: 4.x, 5.x (FE-native/RE-ODBC), for UDB (5.x to 10.x), for z/OS (5.x to 10.x), for Common Server (FE-native/RE-ODBC)

Firebird 1.5, 2x (FE-native/RE-ODBC)

Greenplum 4.x (FE-native/RE-ODBC)

Hitatchi HiRDBsupported, which version is unknown (FE-native/RE-ODBC)

Hadoop Hive 0.12, 0.13

Informix 10.x, 11.x, 12.x ONLINE, 9.x, SE (FE-native/RE-ODBC)

Interbase2007, 2009, generic, XE, XE3 (FE-native/RE-ODBC)

MongoDB 2.4, 2.6, 3.0

MS Access (FE-ODBC/RE-ODBC)2.0, 95, 97, 2000, generic (FE-ODBC/ RE-ODBC)

MS SQL Server 2008, 2012, 2014 4.x, 6.x, 7.x, 2000, 2005, 2008, 2012, 2014

MS Visual FoxPro 2.x, 3.x, 5.x (FE-native/RE-ODBC)

MySQL 5.x only 3.x to 5.x (FE-native/RE-ODBC)

Generic ODBC 2.x, 3.x supported, which version is unknown

Netezza 4.6, 5.0, 6.0, 7.0 (FE-native/RE-ODBC)

Oracle 10g, 11g, 12c 7.x to 12c

PostgreSQL 8.0, 9.x (FE-native/RE-ODBC)

Progress 9.x, 10.x

SAS supported, which version is unknown

Microsoft Azure (with extension)Microsoft Azure SQL Database; Microsoft SQL Server on Azure

Sybase ASE 15.x, 16; Sybase IQ 15.x, 16.xASE 11.9.2, 12.x, 12.5, 15.0; Watcom SQL (FE-native/RE-ODBC); ASA 5.0 to 10.0; Sybase IQ 12.x, 15.x, 16.x

Teradata 13.x, 14.x, 15.xNCR 2.4 to 2.6, 12.0, 13.x, 14.x, 15.10 (FE-native/RE-ODBC)

FE: Forward Engineering - RE: Reverse Engineering

Both tools support native FE and RE unless otherwise indicated

Appendix 3: Supported Database Management Systems

er/studio 2016 vs. erwin 9.64 comparison...

Documents