Data model patterns identify common model structures and define how they should look and interact with other parts of the model. PowerDesigner can assist in this process by automating the setup of objects (tables, columns, mappings) based on the pattern to be applied and ensuring consistency through the use of custom model checks.
Stereotypes
Anyone who’s used PD for any length of time has wondered about the ubiquitous “stereotype” attribute of almost every object in the model. PD’s stereotype facility allows the modeler to specify a general classification or pattern for the object.
Stereotypes are defined in extended model definition (XEM) files and can have nearly anything attached to them – additional attributes, collections, methods, templates, model checks, etc. Stereotypes are hierarchical in nature, so generalization is possible. For instance, we use column stereotypes to designate metadata columns with the “Metadata” stereotype. Other stereotypes extend “Metadata” as shown.
Note that each object can have only one stereotype applied to it, so choose wisely. If what you’re after isn’t a design pattern (a decision made by the modeler) but rather is simply an observation based on a business or technical rule, use a “Criteria” instead. For instance we don’t use stereotypes to indicate materialized views (or MQTs in DB2) – we can add a “criteria” with most of the same functionality as a stereotype and leave the stereotype free for a pattern designation.
Ideas for Implementing Model Patterns
Once you’ve identified a common pattern rules can be implemented to speed the modeling process. Here are a few things you can do:
- Determine a set of required columns for the pattern. For instance, a Type2 SCD requires a pair of row dates. So, when the Type2SCD stereotype is selected for a table, our XEM adds these columns. We also add a pair of surrogate key columns if they’re not already available.
- Add/update required keys. Our Type 2 SCDs have a primary surrogate key and alternate keys on the business key + each of the row dates. The XEM ensures that these 3 keys are available and correct.
- Modify the join condition of a foreign key. For FKs that join to Type 2 SCDs, we don’t want to include the row dates in the join (and also don’t want those dates propagated to the child table). XEM logic removes these columns from the join automatically.
- Determine standard naming. If your standard says lookup tables are prefixed with “LU”, make it so as part of your stereotype. Note that the stereotype hierarchy allows object overriding as well, so you can add a “stdName” template to each level of the stereotype hierarchy and the most specific version will be applied.
- Generate dependent objects. All our Type 2 tables include 2 additional views – the current view and the historical view. The XEM ensures that these exist and stay up to date.
- Custom column ordering. The configuration of any standard can potentially be overridden by replacing the setup with sometime specific to the stereotype.
- Check all this. The XEM includes custom model checks to verify compliance with all the model pattern standard.
Now we’re cookin’ with gas!
Applying high level model functionality is one of the great values of using a capable modeling tool and can streamline the modeling process greatly. So, don’t settle for using your modeling tool as a glorified diagramming tool. Put it to work!
