Components:ERwin Data Modeler: 7.3/8/9
Title: Populating Entity and Attribute Definitions and Datatypes using the API.
Often users have models that do not contain any comments or definitions. Some reasons why that may occur may be that the database that was Reverse Engineered did not contain any comments or the database itself does not support comments. Whatever the reason, there are many cases in which users would like to add comments and definitions to their existing models.
The most common way to add comments and definitions to a model is to do that manually using the editors in CA ERwin Data Modeler, which can be time-consuming. Another way of adding comments and definitions to a model is to use the API. However the difficulty in using the API is that the user also has to write a program. The functionality of automatically adding comments and definitions to a model has now been captured in an easy to use utility and the purpose of this document is to explain what that utility does, and how you can use it.
- Create a simple Logical/Physical model, 'Test' and add Entities and Attributes as follows. Make sure there are no definitions for those objects.
- Save the model and close it.
- Open the attached Excel file (Definition and Comment Import with datatypes.xls). There are three worksheet tabs. Go to the second tab, 'Entity Definition'. As shown below, that tab contains the columns Entity Name, Entity Definition, and Table Comment.
- Go to the third tab, 'Attribute Definition'.
As shown below, that tab contains the columns Entity Name, Attribute Name, Attribute Datatype, Attribute Definition, and Column Comment.
- Click on the first tab, 'Update ERwin'. That brings up the screen that is shown below.
- Press the button for 'Update Entity/Table Defs/Comments' and you will be asked to select the file that is to be updated. Select the file 'test.erwin' that was created during Step 1 above. That completes the import of the entity definitions to the model.
- Next, if you would like to also import the definitions for the attributes into the model, click the box for 'Update Attribute/Column Defs/Comments'. You can import both entity and attribute definitions by clicking both boxes, one at a time.
Functionalities and Limitations:
- The utility will work in a Logical only or a Logical/Physical model, but not in a Physical only model because the API code was written for logical definitions only.
- The utility creates entities and their attributes when they exist in the Excel file but not in the model. After running the utility, note that an extra entity "Customer" and its attributes along with its definitions appears in the model. That is because that entity and its attributes are defined in the spreadsheet but did not pre-exist in the model.
- If the entity and attribute definitions do not appear in the physical model, there may not be an existing 'link' between the Logical and Physical model. You can get the definitions to appear in the physical model by using the Model Naming Options Reset Names button. To do that, go to Tools | Names | Model Naming Options, select the Name Mapping tab, and then click the Reset Names button.
Figure 6. If the definitions do not appear in the physical model, use the Reset Names button.
In the Globally Reset DBMS Property window, select the checkbox Comment that appears under Select Column Properties to Reset. Doing so will migrate the definitions imported into the Entity and Attributes into the Tables and Columns.
As you can see, using this utility is much quicker and more efficient than the manual method of 'copy and pasting' definitions from Excel to the model in CA ERwin Data Modeler. Moreover, it will also create entities and their attributes if they do not pre-exist in the model while automatically adding their data types, definitions, and comments. This utility uses the CA ERwin Data Modeler API code which is embedded into the Excel macros. You can review that code to see how it functions.
Please click the attachment to download the utility Definition and Comment Import with Datatypes.
For macros to work with a COM-based application, both (MS Excel and erwin DM) applications need to be on the same 32/64-bit size. Note: If you are using MS EXCEL 32bit, you will need to install erwin Data Modeler 32bit to use this API program. if you need a download erwin 32bit application, Please visit http://erwinhelp.zendesk.com, then Click 'erwin Data Modeler Solutions, Downloads and Compatibility' Button. Click the link 'All erwin Data Modeler Solutions and Patches'. On the next page, scroll down until you see the build you need.
If you are running erwin Data Modeler 64-bit, you must run MS Excel 64-bit.
Definition_Comment_DT_Import_r9_12 for r9.x and r12.x CA ERwin Data Modeler
The r9_12 API worksheet includes sample data in the Entity Definition and Attribute Definition tabs as per the screenshots above. When you go to use the API spreadsheet with your own data, you must remove the sample data first.