I love PowerDesigner. It’s the Cadillac (Mercedes, BMW, etc.) of the modeling tools. I love that I can do conceptual, logical, and physical data modeling. I can do business process modeling with direct linkage to my data models. And I can model mappings between everything for both data flow relationships (ETL mappings) and design flow relationships (entities become tables become dimensions, etc.).
But my favorite feature is the ability to set standards and enforce them directly in the tool using extended model definitions (XEMs).
The Future of Big Data
With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.
PowerDesigner is built upon a COM based object model. Everything is an object, derived from the aptly named “BaseObject” class and can be read and manipulated via any COM-aware tool or language. This allows us to script things in VBScript right in the tool, or use VB.NET or even Groovy anywhere in the environment.
Here’s just a few of the things that we’re doing in our standards XEM:
- All tables will have a set of metadata columns appropriate for the model type (OLAP or OLTP).
- Column/Attribute names must use classwords, and the data types must align with those classwords.
- Columns are ordered by group and then alphabetically: primary keys first, then alternate keys, then other columns, then row date columns (begin, end, current flag), and finally metadata (Last Update Date).
- Foreign keys are named based on the tables they connect: FK_parent_child. Multiple keys between the same tables require the addition of a role name.
- FKs to type 2 dimensions are based only on the surrogate keys (since the row dates are range matches rather than value matches).
- All sorts of things are required (sometimes conditionally), and model checks point our missing or insufficient definitions, rules, naming, etc.
- Virtual data marts are created by automatically generating views based on the dimensional tables.
All of these rules are checked by custom model checks and automatic fixes are available for many of them.
Think of the time saved by never deploying a model with one of the above (minor) issues. You never have to choose between living with a little non-standard column ordering or redeploying the entire model!
Stay tuned for some tips and tricks posts about how we made these work!
