ERwin is a data modeling tool commonly used in warehousing projects that provides a centralized location to capture metadata. This metadata can be extracted from ERwin to create data dictionaries as mentioned by my colleague, Bob Lambert, in his blog "DIY Data Dictionary: ODBC Reporting from the ERwin Metamodel." ERwin has an API that not only enables users to run queries for reporting, but it can also be used to auto-populate your data models with all the metadata by which users want to report against.
I am going to introduce the components needed to auto-populate ERwin data models with metadata, then walk through the approach I used on a warehousing project. There is a bonus section at the end containing details on how to create ERwin data models from scratch with an excel spreadsheet via the ERwin API.
There are two key components needed.
- The Excel Macro template titled "Definition_Comment_DT_Import" provides the ability to update ERwin model properties with the click of a button. If you know visual basic, you will easily be able to tweak the template to fit your needs. The Excel Macro template can be downloaded from the ERwin Knowledge Base using the following link: http://erwin-knowledgebase.com/index.php?View=entry&EntryID=372
- ERwin Spy enables users to read the metadata captured by ERwin for each object in a given data model. It provides the object property names used within the ERwin API. This will be very useful if you want to add or remove properties that are set programmatically by the "Definition_Comment_DT_Import" excel template. ERwin Spy can either be downloaded and installed as a utility or enabled as an ERwin Add-In. To install the utility, locate the sample ERwin API application named ERwin Spy in the CA directory under Program Files on your local machine. For me, the ERwin Spy executable file was in the following path: C:\Program Files (x86)\CA\ERwin Data Modeler r8\BackupFiles\Samples\ERwin API\ERwin Spy. I did not use the Add-In version but directions on how to do so can be found using the following link: http://erwin-knowledgebase.com/index.php?View=entry&EntryID=3313
Now that we have the components needed, let's put all the pieces together and walk through the approach I took to streamline the process of populating and maintaining the metadata in my data model and creating a data dictionary. I start out by creating the logical and physical data model in ERwin with little regard for setting the properties of the entities and attributes. I then begin creating my source to target mappings which include entity and attribute data types and definitions along with Nullability, primary and alternate keys, business rules, transformation logic, etc.
When I am ready to update my data model with the proper metadata, I open up the "Definition_Comment_DT_Import" excel template and begin copying and pasting the data from my source to target mappings into the excel template. I am now ready to update my ERwin data model by setting the entity and attribute properties with the click of a button. First, I close the ERwin data model I want to update then click the 'Update Entity/Table Defs/Comments' button in the 'Update ERwin' tab of the "Definition_Comment_DT_Import" excel template. I then click the 'Update Attribute/Column Defs/Comments and Logical Datatypes' button. Voila, I open up my ERwin data model and see that all the metadata I want is now captured in the model itself. The ERwin data model is now ready to be queried to create the data dictionary that will be easy to consume for both technical and business users.
The "Definition_Comment_DT_Import" excel template may not be configured for all the ERwin data model object properties I wish to set, so I use ERwin Spy to fix that. I start ERwin Spy and select the ERwin data model I wish to populate with metadata. I double click on the ERwin model objects listed on the left side of the ERwin Spy window and the corresponding ERwin model properties for those objects appear on the right side. These are the property names I add to my "Definition_Comment_DT_Import" excel as new columns. Once I add the ERwin model object properties to the excel template, I update the macros accordingly. This is where visual basic programming knowledge is helpful.
The "Definition_Comment_DT_Import" excel template will actually create any entities and attributes that are not already in the ERwin data model. Be careful, because the ERwin API is case sensitive and there are a few gotchas you will want to avoid. The auto-generation of ERwin data models can be extremely useful when in an applicable situation. However, I do not recommend doing this as a best practice.
In order to successfully auto-generate entities and attributes in an ERwin data model from the "Definition_Comment_DT_Import" excel template, you must open the model after creating new entities via the 'UpdateEntity' macro in the "Definition_Comment_DT_Import" excel template. If you do not open the model after the entities have been created, a new instance of the entity will be created for each attribute that is going to be created via the 'UpdateAttribute' macro. For example, say you are creating a new entity called Customer and there are 10 attributes to be created in Customer. The 'UpdateAttribute' macro will create 10 individual Customer entities, one for each Customer attribute. The entity must first be established and registered in the ERwin model's metadata before you can auto-generate the attributes.