Fight Truth Decay

For the price and early stage in its life, ERwin Examiner is a decent tool for discovering data structure problems

by Rajan Chandras     

The second law of thermodynamics states that all systems tend toward entropy - that is, disorder and decay. As IT practitioners will vouch, this law applies as well to the quality of data in our information systems. Left to itself, data quality deteriorates almost monotonically, and reversing that tendency requires significant commitment, time, money, and expertise.

Broadly speaking, data quality has two aspects: design and content. Bad data design is much worse to recover from than bad data content, because the design (the structure) of the data is the application's foundation and therefore affects all its components. Recognition of this principal has prompted a recent surge of interest in data quality and in "total data quality management"; a number of tools have emerged in response to help organizations detect and correct deficiencies in data quality.

Tools at the high end include Quality Manager (from Ardent Software Inc., www.ardent.com, which was acquired by Informix) and Evoke Software's Axio (www.evokesoft.com). These tools trawl through your existing data sources and examine the data structure as well as content, construct a quality profile of the data, and propose corrective action or even suggest a normalized data model. The high-end tools offer a wide array of functions - and demand a high-end price to match. At the lower end, there's a new kid on the block from a well-known family: ERwin Examiner 1.0 from Computer Associates (CA).

ERwin Examiner gets its name from the popular ERwin ER/X data modeling tool that came to CA with the acquisition of Platinum - which itself acquired the product by taking over the erstwhile Logic Works Inc. CA didn't create ERwin Examiner in-house either; DBE Software (www.dbesoftware.com) developed the product, also known as DB-Examiner, for CA. CA provides first-level support; DBE does the second-level support, and provides fixes and new versions to CA.

The product's marketing material states that it is "an expert system that validates your database structures, evaluating integrity and performance issues. It examines your data models, makes suggestions for improvement, and in many situations generates the scripts to change them." Letting slide the 'expert system' bit, this blurb describes the product's abilities exactly.

ERwin Examiner is a client-side standalone product, and is aimed at data architects, data modelers, and database administrators. ERwin Examiner's major features are its abilities to reverse engineer a data model, perform structural diagnostics on the model, provide corrective measures to the data model based on the diagnosis, and compare two data models.

Reverse Engineering

ERwin Examiner lets you reverse engineer the data model in three ways: directly from the database catalog, from an ERwin ER/X file, or from a SQL script file. Current database support includes Oracle, DB2 UDB, DB2 Mainframe versions 4 through 5.1, Microsoft SQL Server, and Sybase. I could satisfactorily read from all three source types: an ERwin ER/X file, a local SQL Server database, and SQL scripts generated by ERwin ER/X as well as by SQL Server. The reverse engineering was quick and painless.

Diagnostics

The product's diagnostic component is its most important part - its raison d'etre. ERwin Examiner produces four groups of diagnostic measures: 10 diagnostics for columns, 21 for indexes and constraints, four for normalization, and nine for relationships. For each diagnosis, ERwin Examiner offers an explanation as well as the means to learn more. This last feature is nice for the aspiring data modeler learning the intricacies of data modeling through practice.

The product designers chose the diagnostic measures well, but the same cannot be said for the explanations of these measures. For example, the software offers the exact same explanation for the Second Normal Form as for the Third Normal Form, which is misleading, at best, to someone who's learning on the go.

ERwin Examiner also identifies the severity of each diagnostic measure as Severe Error, Error, Performance, Caution, or Warning. (See Figure 1.)

Generating the diagnostics for a model is easy enough: You click on a tab at the bottom of the left panel, click OK on a message that warns you that it could take a little time, and you're on to the fairway. The first thing that hits you about the resultant diagnosis is the bewildering variety of icons. The designers of ERwin Examiner seem to have gone overboard in using icons, perhaps forgetting the axiom that colors should be used only to the extent that they attract and organize; any more than that, and they distract and annoy. Also, the ERwin Examiner graphical interface does not share its motif with the ERwin ER/X interface, which is a pity because the simple but elegant and uncluttered GUI is a strength of the ER/X flagship product. This difference is because, as I mentioned earlier, the two products come originally from distinct software houses, and hence are not related in design or ancestry.

To test the software, I created a simple, fully normalized, complete data model and ran diagnostics on it. I then denormalized the model, put in several inconsistencies and again fed it to the software. The results were interesting. The tool picked up most of the inconsistencies and explained them correctly. However, it did miss some inconsistencies: For example, it failed to identify synonyms (slightly different column names with the same meaning) although it found homonyms (same column name, different meanings). Also, it seemed buggy in the way it treated indexes on foreign keys, and it failed to caution me about an improper use of an Oracle bitmap index.

Comparison of Models

ERwin Examiner compares two data models by examining their table and column level information. Over time, the data modeler may end up creating dozens of versions of the data model, many of which vary only slightly from others. It is undeniably useful to be able to determine what changes were made between two given versions, but the comparison feature has limitations. For example, it was unable to identify a script that I attached in ERwin ER/X to one of two identical tables.

Other Considerations

CA provides plenty of context-sensitive online documentation, with appropriate graphics supporting detailed textual directions. However, as I mentioned previously, CA needs to improve the documentation's explanations of structural deficiencies.

ERwin Examiner is simple to install and is available as a fully featured but limited-time trial version on the Web at www.cai.com/evaluate/download/erwin_standard.htm. The downloaded software carries a serial number and requires the corresponding key code from CA.

Rate This Article Rating: 1 (best) 2 3 4 5 (worst) Comments: Optional e-mail address:

A note of caution: An essential requirement of real-life data modeling is the denormalization of the data model, as well as some "leeways" built into the model for flexibility and changing business requirements. ERwin Examiner diagnostics will diagnose all such tweaks as errors of some kind. Such errors are meant tobe, and the experienced data modeler knows to acknowledge them - and then selectively ignore them. I would strongly caution IT management against using ERwin Examiner's severity analysis as a basis for accepting or mandating changes to the data model. This determination cannot be automated; it must be made by someone experienced in the techniques and nuances of data modeling.

ERwin Examiner has significant scope for improvement, especially in the areas of user interface and technical explanations. I would also like to see it provide stronger diagnostics in the area of normalization. That said, ERwin Examiner offers a valuable tool for IT organizations and experienced data analysts in determining potentially costly deficiencies in the quality of the database design. The tool is easy to set up, easy to use, and generally well documented.

Rajan Chandras (rchandra@csc.com) is a senior consultant with the New York Metro consulting offices of Computer Sciences Corporation where he creates e-commerce solutions for businesses.