One of the key added features of the ERwin R8 is the Where Used feature. What this feature shows you (not surprisingly) is where the particular object... is used. Ok, so we got that out of the way. Let's take a look at where this is available and what opportunities it provides us.
With all the current buzz around data governance, the question becomes how to begin to define a true data dictionary. While there is yet to be any tool (or set of tools) that can handle all that is required to define and maintain a true enterprise level data dictionary, the reality is that the data modeling tools that your organization already owns can provide some key features.
You can define your data systems using the reverse engineering features. You can create impose naming and datatype standards. You can define user defined metadata to assign tasks and responsibilities to separate systems and objects. You can maintain design history and plan the impact analysis of system changes. You can identify common metadata across many systems and link models to define relationships in your data. You can create reusable domains. You can create templates with all these standards in place so that new systems inherit them by default. This would also allow for central reporting on all of these features. You can even export your metadata information to other MDM, BI, and ETL tools to save time and ensure your standards.
But a key necessity to any of these implementations would be the ability of seeing exactly where a particular object is used in the design. Here are a few examples:
In this first example, we look at the table editor. Scroll to the right and note the Where Used tab.
The results listed show us a full list of the objects that relate to our table. This can include relationships and diagrams in which the tables exist. Perhaps the most useful usage is to validate that the table exists in the appropriate subject areas.
Another nice feature is that the editor for that particular object can be invoked, simply by selecting the object from the list and clicking on the button "Edit the Selected Object". This is valid, regardless of the object type. So a subject area will open a subject area editor while a relationship will open the object in the relationship editor. This allows for much faster navigation and editing of the model.
Let's take a look at a different property. In this case, we look at one of the new ERwin features; annotations.
Firstly, notice the similar editor and layout. This consistency in layout and editor features in the new version makes using these features easier to learn since the behavior is the same, across the board. Once again, we can quickly identify that we have assigned the annotation to all the diagrams on which they are needed.
Here is an even more powerful usage, default values.
In the above example, we can quickly identify that the default is correctly assigned to a column. A nearly identical process would be used to identify that validation rules are assigned to the correct columns and tables.
The ability to validate these properties are all key to any data governance initiative. But, perhaps, none is more useful than tracking domain to column assignment.
In the screenshot above, we have used the filter field in our domain editor to filter the available domains down to only the "address" related domains. Now, we can conveniently click on any of the domains (in this case, address_2) and by accessing the Where Used tab, we can quickly check to see that the domain is correctly assigned to the columns on which it should be.
So would there be a convenient way to run a report across all of the User Defined domains in the model into an Excel spreadsheet that included the table and column specifications for each domain. Actually, there is, the catch is that you would have to do a little coding using the ERwin API to do so.
The good news is that I have built such a project file. To request a copy, feel free to E-mail Me.
With all the current buzz around data governance, the question becomes how to begin to define a true data dictionary. While there is yet to be any tool (or set of tools) that can handle all that is required to define and maintain a true enterprise level data dictionary, the reality is that the data modeling tools that your organization already owns can provide some key features.
You can define your data systems using the reverse engineering features. You can create impose naming and datatype standards. You can define user defined metadata to assign tasks and responsibilities to separate systems and objects. You can maintain design history and plan the impact analysis of system changes. You can identify common metadata across many systems and link models to define relationships in your data. You can create reusable domains. You can create templates with all these standards in place so that new systems inherit them by default. This would also allow for central reporting on all of these features. You can even export your metadata information to other MDM, BI, and ETL tools to save time and ensure your standards.
But a key necessity to any of these implementations would be the ability of seeing exactly where a particular object is used in the design. Here are a few examples:
In this first example, we look at the table editor. Scroll to the right and note the Where Used tab.
The results listed show us a full list of the objects that relate to our table. This can include relationships and diagrams in which the tables exist. Perhaps the most useful usage is to validate that the table exists in the appropriate subject areas.
Another nice feature is that the editor for that particular object can be invoked, simply by selecting the object from the list and clicking on the button "Edit the Selected Object". This is valid, regardless of the object type. So a subject area will open a subject area editor while a relationship will open the object in the relationship editor. This allows for much faster navigation and editing of the model.
Let's take a look at a different property. In this case, we look at one of the new ERwin features; annotations.
Firstly, notice the similar editor and layout. This consistency in layout and editor features in the new version makes using these features easier to learn since the behavior is the same, across the board. Once again, we can quickly identify that we have assigned the annotation to all the diagrams on which they are needed.
Here is an even more powerful usage, default values.
In the above example, we can quickly identify that the default is correctly assigned to a column. A nearly identical process would be used to identify that validation rules are assigned to the correct columns and tables.
The ability to validate these properties are all key to any data governance initiative. But, perhaps, none is more useful than tracking domain to column assignment.
In the screenshot above, we have used the filter field in our domain editor to filter the available domains down to only the "address" related domains. Now, we can conveniently click on any of the domains (in this case, address_2) and by accessing the Where Used tab, we can quickly check to see that the domain is correctly assigned to the columns on which it should be.
So would there be a convenient way to run a report across all of the User Defined domains in the model into an Excel spreadsheet that included the table and column specifications for each domain. Actually, there is, the catch is that you would have to do a little coding using the ERwin API to do so.
The good news is that I have built such a project file. To request a copy, feel free to E-mail Me.
