Data Modeling (Relational or Dimensional,) is a job that needs more thinking than really doing. It has more to do with gaining a better understanding of business processes and data that follows through those processes than knowing a bunch of technical terms or tools.
However, having a good technical handle on tools like CA ERwin makes the life of a data modeler much easier and the job so much more fun to do. In this post, I am going to share a few important aspects of data modeling that a lot of data modelers ignore and this struggle later. I am also going to share how you can do it correctly using the ERwin Data Modeler tool.
DBAs Aren’t Supposed To Be Your Data Modelers
I have been in interview rooms where a data modeling candidate was asked, “How did you create your data model for so and so project?” and the candidate answers, “DBAs designed and also deployed our data models.”
Now, this post isn’t for such data modelers/data architects. This is for the people who are the real deal, those who really do care about data modeling.
I have seen teams where those responsible for data modeling create their data models in excel sheets listing tables, columns and all their respective details in excel workbooks. They pass their excel to the DBA teams who actually use ERwin or Power Designer-like data modeling tools to really create the final deployable data model.
Now, that happens because either people in such environments do not understand that an ERwin-like tool is not for DBAs (although they may use it if they want to do data modeling,) but for the data modeler/data architect; the person who creates those actual data models. No wonder such projects end up without having a proper data model.
If you have an ERwin license and you are a data modeler with the intention to create real good data models that can be actually be used correctly and be appreciated by the DBAs, I hope this post is going to add value to you.
Plan To Derive Physical Data Models from Logical Data Models
Many times during the initial phase of data modeling you may not know all the details about all entities and their attributes. Even if you have an idea, you may not exactly know all their data types, standards to follow for naming conventions and data types, etc.
Therefore, I prefer to go ahead with a logical data model design first, knowing that I can always derive a physical data model from my logical data model when I am ready with all the details later on.
So, if you don’t have all the details about your design, start with logical model. Get the work started and move on. The logical model will help you crystallize your thought processes for the final design that will come later on during the project.
The logical model is based on entities and their relationship with one another. Your entities will have attributes, but you don’t have to have all the database level data types or syntaxes etc. This helps you put formulate your design.
Establish Some Design Themes Early On
You don’t have to do this because ERwin provides a default classic theme that is pretty clean to begin with.
However, if you are like me, and you like to put a little design around your data models for setting some standards across all data models you are going to create, you might want to create a theme which you will be able to use across all your data models now and in future.
Here are the steps you need to take to create a new theme:
- Open CA ERwin Data Modeler and then open a data model.
- Expand Model Explorer, right-click Themes, and click New. A new theme is created with a default setting. You can rename the name of the theme to whatever you want.
- Right-click the new theme and click Properties. The Theme Editor opens with this action.
- Edit the name of the theme if you like. You have the option to do so.
- Edit the properties in each column for the new theme.
- Click the Defaults tab and make your selections for the desired options, as appropriate, for your organization.
- Customize your theme by selecting the desired options on the other tabs, as appropriate, for your organization. Make the changes across all tabs to make sure all the settings you want to have in your theme are in place.
- Now, click Close and your new theme is ready to be used.
Now, you can apply your theme to your diagram objects depending on how you want to format it. For example, I ensure that the fact tables have a different color scheme than the normal dimension tables. I also like to assign a different colors to confirmed dimensions, junk dimensions, and so on.
Create Naming Standards for Your Model Objects
ERwin is designed to inherit the physical names from the logical names by default. So, if you are creating a logical model (and I like to do that as a first step,) when the times comes to derive a physical model from your logical model, it inherits the table, column and constraint names based on the names of these objects created in the logical model.
However, every organization has certain rules around naming database objects. You may encounter some length restrictions enforced by your DBA team or you might want to follow a certain prefix/suffix to be attached to your database objects.
Therefore, the best way to achieve your naming standard enforcement goal is to create a naming standard file glossary and load it up in your ERwin model. This way, you will make sure the database object names are derived based on your naming standard rules while deriving your physical data model.
If you ask me, the goal is to keep your logical names as descriptive as possible, although you can enforce standards here as well. Enforcing naming standards only to the physical data models makes logical sense unless you have some strict rules around logical names too.
Steps To Create and Use Naming Standards
As a Data Architect, you have three methods to choose from to work with naming standards in ERwin. Depending upon your preference and your project situation, you can choose any one of these and produce the same results.
The following picture depicts all three methods and how you choose which method to use to apply naming standards to your model in what circumstances:
As you can see from the picture, there are three distinct steps involved in this process:
1. Decide the method.
There are three options to choose from when deciding the method of applying naming standards:
1.1 Create Fresh and Apply
Usually we can choose this method when creating naming standards for the first time.
1.2 Import from a Previous Version of ERwin
In version 9 of ERwin, the options to create external .NSM file has been removed.
Therefore, you will not be able to create new .NSM files going forward. Instead, the release 9 of ERwin now allows you to create naming standard objects that are part of the model itself.
So, this option is for you if you already have your naming standards created using a previous version of CA ERwin Data Modeler and have your .NSM files handy. You can import your .NSM file and apply your naming standards to your models.
1.3 Bind a Model Template
This is the new way to create and reuse naming standards in release 9 of ERwin and beyond. This option is useful if you want to use the naming standards that you have already created and attached to a model or a model template so multiple data models can use the same naming standards.
Let’s assume for the moment that you have already created naming standard objects in your current model and that you are ready to save your current model as a template for future use.
Follow these steps to create a new model template, which you can bind to other models later, to apply naming standards:
- Click File —> Save As.
- Select the file name extension “.erwin_tmpl” to create your template file.
- Remove the entities and relationships from this model if this is not something you want to be part of your model. However, if you wish to keep naming standards, themes and other formatting related reusable items in your template, then save it. Now, the template is ready to be used.
Going forward, to apply this template to any new data model you will create, all you have to do is to follow these steps:
- Click Model Templates —> Bind Template on the File menu and the Bind Model Template dialog opens.
- Select the model template you want to bind to the current model using the drop-down control or browse for the template file.
As shown in the picture above, you will need to choose one of the following options to specify how to bind the template, and click OK.
Load the entire contents of the template
Specifies to load the entire contents of the template. This option binds all of the model template objects to the current model without any opportunity to review and select individual template objects.
Run the Template Wizard to select objects for synchronization
Specifies to open the Model Template Synchronization Wizard so that you can select the objects in the model template to synchronize with the data model. I prefer to use this just to have a better control on knowing what is being applied to my new model and what is not.
2. Verify Naming Standards Compliance
To know if the naming standards you have created are applied properly, use the Naming Standards Compliance using steps below:
- Click Tools —> Standards —> Check Naming Standards Compliance.
- The Check Name Standards Compliance dialog opens. Now, select the objects to check and select the Check Word Order and Position check box if you want and then click Start.
- All instances of non-compliance are displayed in the Check Name Standards Compliance dialog. You can replace the name, replace all instances of the name, or ignore the non-conforming name. This is a good way to know if everything is going to work as expected.
3. Apply the Naming Standards
After you define naming standards using the Naming Standard Option Editor, select individual model objects and apply the standards using following steps:
- Click Actions —> Model Naming Options.
- The Model Naming Options dialog opens. Now, click the Name Mapping tab.
- For each model object type that you want to use the glossary, select the Use Glossary checkbox and select the type of abbreviation. Click OK.
The naming standards abbreviations (or alternate abbreviations,) defined in the Naming Standard Option object that are active, are applied to the current model.
Derive Physical Data Model from Logical Data Model
Deriving a new physical model from a logical data model is simple if you already have accomplished setting up proper naming standards and you are happy with your logical design.
Here are the steps you follow to derive a new physical data model (or a logical/physical model,) from your existing logical data model:
- Click on Actions —> Design Layers —> Derive New Model menu item as shown in the image below:
- A new dialog box will appear allowing you to choose what kind of data model you want to select, for what target database, and other options that you may wish to choose before you click on the “Derive” button as shown in the image below:
- When you are done selecting all relevant options for your new model to be derived, click on the “Derive” button on the dialog box and your new derived data model will appear in a new model.
- Now Save the new model using File —> Save menu option.
ERwin is a powerful Data Modeling tool and if you are a Data Architect, you can use the tool from the beginning of a new project, right from the conceptualization of the data model all the way through the final design if you plan ahead, use the features wisely and avoid rework.
Setting a proper theme, deciding on naming standards and using these features from the beginning of the project can help you save numerous hours of re-work down the line.
Your Turn, Share Your Experience
I just shared with you some of the best practices I follow when I am working on a data architecture design using ERwin. I hope I added some value to you.
If you have some great tips to share, please use the comments below and share your experience with us. We would love to know what you have to share!
Also, if you have any questions, please use the comments section to ask your questions as well. We will be happy to answer them as soon as and as much as possible.