ERwin modeller has a reverse engineering functionality implemented. Reverse engineering is nothing else than creating a model of existing database. If you already have some tables in the database but you have not modelled them first you might want to use reverse engineer to have to model automatically created for you. The fact is that many business simply do not model and create the tables directly in the database. This is obviously malicious practice because when creating a model first you have enough time to thing about the design (you see the desing in WYSIWIG editor) which obviously leads to better DB design. If you create tables directly in the DB you don’t see how it actually look and effectively bypass the design phase. Model is also form of documentation and having documentation is always a good thing. Documentation can be easily shared and other developers can see what is already in the database and maybe reuse the data which leads to lower data redundancy. Lastly when using ERwin you can create logical model which is DB independent and then you create different physical models for different DBs. This is particularly useful when you migrate from one DB vendor to another (preventing vendor lock in) or if you have heterogeneous DB environment.
So how does the reverse engineer works? You simply tell ERwin how to connect to the database you want to reverse engineer and select the type of objects you want to reverse engineer. Erwin then does everything for you and creates automatically a logical/physical model. So the first step is to select Reverse Engineer from Tools. In the following diagram you select if you require Physical or Logical/Physical model. Better practise is to have Logical model as well. Also select the DB engine you reverse engineer from.
The next thing to do is to select objects you want to reverse engineer. It depends on your modelling strategy but I recommend to select as few objects as possible. You don’t want your model to be overly complicated. You should however tick following: tables and views. Click next and in the last step you just need to fill in all the credentials – tell Erwin to where and how is he supposed to create the connection. Just select the type of authentication Windows or SQL Authenticaiton, fill in the user (in case of SQL Authentication) and fill the server and database name. Once this is done click connect and ERwin will reverse engineer database you have selected and create model like the one below.
Reverse engineering is an effective way of creating a model from an existing database. Since some developer might bypass the design phase (this should not be encouraged!) the reverse engineering tool might be useful. The only limitation is that you cannot select the tables you want to reverse engineer – it will always pull meta data about all the tables in the database you have selected. This is particularly annoying if you connect to a massive database like (example: datawarehouse) – it will take ages to reverse engineering all the tables.
Complete compare
Another useful tool is called complete compare. It allows you to compare different models you have created and detect changes. It is particularly useful tool when you make some change to the model for which physical implementation in DB already exists. You can then clearly see what the differences are and do some impact analysis before you actually deploy the changes to the DB. To do complete compare just select Complete Compare from the Tools menu. Then you just select left and right model (two models to be compared), the types of objects (tables, views etc.) you want to compare and finally the objects you want to compare (you can select just some tables to be part of the comparison etc.). Then you compare the model and a comparison dialog containing all the detected differences will pop up and you can decided which differences should be carried over and which not. This whole process is fairly simple and straightforward.
However there is another use of complete compare – to merge meta data from two different sources. Just imagine a situation that you have a model of a database (tables) but some meta data is stored somewhere else – in some other modelling tool and you would like to have all the meta data in one model in ERwin. So what to do? Simply use the import wizard of ERwin which allows you to import models from different tools and compare the models you have imported. Using complete compare you can merge meta data from one model with meta data from other model. Real world example can be from area of business intelligence. One of my customers had a datawarehouses without keys defined (they had reasons for that) and Business Objects universes containing joins and keys. They wanted to have a data model containing both the meta data about the tables form the data warehouse as well as the joins and some other meta data from the Business Objects universes. The process of merging is fairly simple. First step is to reverse engineer the database and get a create a model; after create a model import the model from the Business Objects universe (or any other data source you might have). When you have both models just complete compare them and migrate the changes from one to other. In our case we have migrated the joins from the Business Objects universe into our DW model.
In this chapter we have discussed the reverse engineer and complete compare tool. When you have a database but you have not used ERwin to design it you might want to reverse engineer the model from the database. Or if you made any changes to the database without changing the model you can use complete compare tool to find the differences. Complete compare is also useful for merging meta data from different sources.
No comments:
Post a Comment