Search This Blog

Friday, September 1, 2017

Macros in ER/Studio Data Architect is your data modeling lifebelt

[2017-Sep-01] There is a well-known phrase, "the rescue of a drowning man is the drowning man's own job" from one of the best books by Ilf and Petrov  "The Twelve Chairs". And if you have ever used macros with the ER/Studio during your data modeling, then you may see how they can be your lifebelt among a large volume of your database design tasks and operations.

ER/Studio Data Architect is a great software tool with extensive data modeling capabilities across multiple relational and big data platforms and macros could automate repetitive tasks and save your time.

My favorites macros are:

1) Import Columns From Excel
2) Import/Export Domains From Excel
3) Name Foreign Constraints
4) Name Primary Constraints

The first two macros are a great time saver for designing tables with a big number of columns and the other two could help you to keep your entity object names aligned with a single naming convention.

Let's briefly take a look at each of those macros.

Import Columns From Excel
Your Excel files with a list of entity attributes should comply with the following structure:
1. Entity Name
2. Table Name
3. Attribute Name
4. Column Name
5. Data type
6. Table Definition
7. Attribute Definition
8. Nullability - 'NULL' or 'NOT NULL'
9. Primary Key - 'True' or 'False

Then when you choose to execute this macro a dialog box appears for you to upload your file. Once you finish this task, ER Studio could add or update attributes to the existing entity of your data model or create a new entity.

Import/Export Domains From Excel
This macro exports the domains and their properties to Excel. Each record in the Excel spread sheet represents domains from the data dictionary for all or selected entities. The domain properties can be further updated and imported back into an existing model or a new model using the "Import Domains From Excel" macro.

Name Foreign Constraints
This macro will prompt the user with a dialog to specify the naming convention for Foreign Constraints.  It will then name all the constraints using the parent and child table names. It will also make sure the name is unique by adding an index for the last characters of duplicate names.

Name Primary Constraints
This macro will name all primary key constraints with the given naming conventions. The table name with either a prefix or suffix.

I hope you will find your lifebelt next time when you do your data modeling in ER/Studio Architect!