Garry Gramm, a long time member of the ERwin User Discussion Group recently put together a helpful response post on making the most out of ERwin Complete Compare feature when comparing to databases.
As some background, recent versions of CA ERwin Data Modeler have added a nearly-infinite number of options for comparing models, databases, and DDL. With those options, though, comes the added complexity of trying to figure out how to best use them for your purpose. Garry has agreed to share his recommendations for making the best use of complete compare.
Welcome to the world of advanced complete compare! (no, there is not beginner version - that was in a previous version! :-( the simplicity many of us miss)
I'll hope that there is a best solution to compare a model with the database and have the DB side clean from [calculated] object.
I cannot guarantee clean, but if you follow these steps you should be able to get reasonably close to what you want to accomplish.
1. When establishing the database model through the reverse engineering step, you will have to make some choices. I suggest that you:
- choose only the minimal things that you absolutely need to compare when you set up the type selection if you are concerned about speed.( e.g. tables, columns indexes, FK constraints to start)
- if the database is big, then you can
- bite the bullet and do a one time full load of all, then save the reverse engineered model to use in subsequent compare. If the DB is not changing a lot (i.e the production database) then I generally do this and reuse the results in many other compares over a period of time.
- Select demand load which will reverse engineer all the tables in the database, but only "fill in the blanks" for those tables specified during the complete compare object selection screen. In this case its probably not worth saving the demand loaded DB model at the end of the compare unless you are going to run the exact same compare in a later ERwin session.
- If the database is not overly big, then I always do a full load (not demand) and just tailor the object selection to the type of compare, then save this type selection for reuse at all times when doing the same kind of CC, using the same target database (DB2, SQL server etc.)
2. After Step 1 above completes, and you are now into specifying the condition of the compare itself (Type Selection), make sure you are doing a database compare and not a physical compare. This will cut down on the number of objects in the type selection you are presented with. When selecting these objects, start small. I generally just start with table names , column names , column datatype, and column null options .
3. Once resolved I then incrementally add other objects like column orders , relationships, physical properties, indexes or whatever is deemed necessary to be in sync with the database, and then run the compare again. When I’ve done one compare, I add more things in the type selection on successive passes and repeat till all necessary objects have been compared.
Using this approach cuts down on the number of things present in any one RD screen and makes it easier to reconcile overall.
4. For the Left /Right object selection (assume here model is left and reverse engineered DB is on the right); In the list of subject areas, choose only the subject areas you are interested in, in the left model . The default scope for the compare in versions below 7.3 defaults to all subject areas, so you will have to uncheck at the top level and select only the subject area you want to compare, which is usually the current active subject area. In the example below I want to limit the scope of the compare against the database to only Subject Area3. And further to that limit the scope within that subject area, as shown in following screen shot.
Note that when selecting a specific subject area, the pane on the right will show all tables in the model, but only those in the chosen SA will be checked on for the compare.
Enhancement request: to cut down on noise for this pane, OPTIONALLY, show only the tables in the selected SA not the model. If this is optional, then user has the flexibility to also include other tables not in the current SA; i.e. where you want to include a parent table mot shown.
In R7.3 .x there is new advanced option to select only the active subject area, which, when checked will persist across ERwin sessions until it is turned off , and give the same results as manually doing the unchecking in the left pane above. .
5. And finally in the advanced options, I generally only check the generated objects only and match on name as the only 2 options checked. If you choose any of the exclude unmatched objects in the right or left model, in versions below 7.2.7 (I believe), these exclusions will bypass anything not in the side chosen instead of just the high level object (Table). So if you have a column in the database that is not in the model for instance, and you exclude unmatched objects on the right, then tables will align,but the column will be excluded from showing on the compare and you will know about it. So if this were the only difference, the compare would come up clean when in fact it is not. This is fixed in later versions (7.2.7) and above I believe. Definitely fixed in 7.3.x.
As in 7.2.5
As in 7.3.x
You can save many of the components the compare including the complete session if you want to, in order to repeat the whole process at any time .
Doing the compare in steps rather than all at once may seem like more work, but in reality, it simplifies the understanding of what you see on the RD screen and makes it easier to handle and figure out what is going on.
Keeping in mind that [calculated} means that the value is inherited from somewhere, if you get differences with it showing and you cannot resolve them, then I would say just ignore them as it probably does not affect anything, unless you want to spend the extra time trying to determine the root cause of the difference.
For me, its not worth the time or effort and as CA is constantly trying to improve the CC process, and enough people have complained about this, that I am sure at some point , this level of noise on the CC RD screen will probably go away , or at least provided as a user controlled option to suppress it. Just my opinion and thoughts, and not based on anything stated by CA.
Hope this helps.
Garry is an Enterprise Information Architect at Worksafe BC and Owner of DataSmart Business Solutions Inc.