Sample Macros Installed with ER/Studio Data Architect

From ER/Studio Data Architect
Jump to: navigation, search

Go Up to Automating ER/Studio Data Architect

Sample macros are included with the installation of ER/Studio Data Architect. An extensive set of working sample code is also included as part of the Automation Interface Reference Code Reference chapter in the on-line Automated Interface Reference.

The following provides brief descriptions of the functionality of the sample macros. The header of each macro includes more extensive usage details. Most installed macros are also in the help file in the Sample Code section for ease of cutting and pasting and for comparison purposes.

Meta Data Management Macros

  • Attachment Binding Export to Excel: This macro can be used to export entity and attribute attachment binding information to Excel.
  • Attachment Bindings Import from Excel: This macro can be used to import submodel, entity and attribute attachment bindings from Excel.
  • Attachment Example: This macro exports attachment data for each bound attachment for every selected table in the active model. The file name and path is "C:\Attachment Data.txt".
  • Batch Add Repository Diagrams: This macro can add diagrams in the specified directory to repository in batch.
  • Batch Convert Erwin Models: This macro can be used to batch import erwin diagrams.
  • Batch Delete Repository Diagrams: This macro will read names of diagrams specified in a file and it will delete those diagrams from repository. You can use the "Export Repo Diagram Names" macro to create the file.
  • Convert Erwin Glossary CSV to NST CSV: This macro is used to import value references from an ERwin glossary csv exported file to a ERStudio Naming Standards Template abbreviation compatible csv file.
  • Data Lineage Export to Excel: Outputs the data lineage information to excel. If the format of the spreadsheet is unchanged, you can update the mappings and import them back into the model.
  • Data Lineage Import from Excel: Imports data lineage from Excel.
  • Domain Bindings Export to Excel: Exports domain bindings for all the attributes in the current model.
  • Domain Bindings Import From Excel: Imports domain bindings from Excel.
  • Drop Tables: This macro will generate a SQL file with DROP TABLE statements in dependency order.
  • Export Domain Info to Excel: Exports the domains and their properties to excel.
  • Export Index Column Info to Excel: Generates an index column report for the active model in ER/Studio Data Architect.
  • Export Meta Data to Excel Version X.X: This macro generates a report for the active model in ER/Studio Data Architect. Each record in the Excel spread sheet represents an index.
  • Export Model Meta Data to Excel: Exports model meta data for the active model to Excel.
  • Export Model Meta Data to Word: Generates a mini report for the selected entities in the active model.
  • Export Object Definitions and Notes to Excel: Exports definitions and notes for tables, views, relationships, and attributes to Excel.
  • Export Reference Value Info to Excel: Exports the reference values and their properties to Excel.
  • Export Relationship Info to Excel: Generates a foreign key column report for the active model in ER/Studio Data Architect.
  • Export Repo Diagram Names: This macro will export the names of all the diagrams in the Repository to a text file. You must first log in to the Repository before running the macro.
  • Export User DataTypes to Excel: This macro can be used to export User Datatypes from the selected dictionary to Excel.
  • Import Columns From Excel: This macro will import entities and columns using information provided in Excel spreadsheet.
  • Import Domains from Excel: Imports domains from the specified Excel spreadsheet.
  • Import Index Names from Excel: This macro is used to import index names from Microsoft Excel. A sample spreadsheet can be printed when executing the macro.
  • Import Object Definitions and Notes from Excel: Imports definition and notes for entities, attributes, views, and relationships from an Excel spreadsheet.
  • Import Reference Values from Excel: Imports reference values from Microsoft Excel.
  • Import Relationships From Excel: This macro will import relationships using information provided in Excel spreadsheet.
  • Import Relationship Names from Excel: Imports relationship names from Microsoft Excel.
  • Submodel Report: Generates a list of entities and their submodels for the active model. The output is an Excel spread sheet.
  • Visual Data Lineage Export to Excel: This macro will output the visual data lineage information to excel. If the format of the spreadsheet is unchanged, you can update the mappings and import them back into the model.
  • Visual Data Lineage Import from Excel: This macro imports visual data lineage from Excel. Each record in the Excel sheet should reference a tranformation in a data flow.

Model Layout Macros

The Model Layout macros all demonstrate how you can automate changing a model's appearance. You can use these macros to change a model's appearance by aligning entities, changing the background or text color of entities and attributes, or combining several entities into one new entity.

  • Auto-Align Selected Entities Left: Aligns all selected entities by their left-hand edges. This macro simulated the User selecting two or more entities in a model then clicking the Align Left button in the Alignment toolbar. Please note that you must select at least two entities before running this macro.
  • Auto-Color All Entities with FKs: Illustrates how to use entity objects, entity display objects, and selected objects - and how to distinguish between them. The macro changes the background color of all entities with foreign keys to purple. First, all entities in the active submodel are selected. Next, the background color of all selected entities that contain foreign keys is changed to purple. Finally, all selected (highlighted) entities are deselected.
  • Auto-Combine Selected Entities: Replaces all selected entities with one new entity that contains the non-foreign-key attributes from the selected entities. The new entity is placed in the diagram at the averages of the x and y coordinates of the selected entities. The new entity's name is a combination of the names of the selected entities, each separated by an underscore. Each attribute name is a combination of the originating entity name and the attribute name, separated by an underscore; attributes are renamed in this manner in order to ensure uniqueness in the new entity.
  • Change Submodel Display Properties: Changes several display properties in the active submodel. In particular, it changes the font and color of non-key, non-inherited attributes. The font size and style for the attributes are also modified.

Modeling Productivity Macros

  • Add Base Attributes To Person Entity: Adds base attributes to selected entities, which represent people. It will also introduce a primary key based upon the entity's name.
  • Add Definition to Type Entities: Adds a definition to selected entities. The definition applied to the selected entity will also include the object's name automatically, as in "My definition text + entity name +(s)."
  • Add Parent Name Prefix to Propagating Key: Adds the entity name as a prefix to all attribute role names. This macro demonstrates how to use the FKColumnPair object.
  • Add Table Name Prefix Globally: Lets the user add a prefix string to the names of all entities in the active model. A dialog box prompts the User for the prefix. If the active model is a logical model, the prefix is assigned to all entity names. If the active model is a physical model, the prefix is assigned to all table names.
  • Add Table Name Prefix Selectively: Lets the user add a prefix string to the names of the selected entities in the model. A dialog box prompts the User for the prefix. If the active model is a logical model, the prefix is assigned to the selected entities' names. If the active model is a physical model, the prefix is assigned to the selected tables' names.
  • Add Table Owner Globally: For physical models, updates the owner field in the table editor for all tables in the active model.
  • Add Table Owner Selectively: For physical models, updates the owner field in the table editor for all selected entities.
  • Add View Owner Globally: For physical models, updates the owner field in the view editor for all tables in the active model.
  • Add View Owner Selectively: For physical models, updates the owner field in the view editor for all selected entities.
  • Attribute Binding Macro: Lists all the unbound attributes/columns of all the models (logical and all physicals) and domains. The attributes can then be bounded to any of listed domains by pressing the "Bind" button.
  • Auto-Convert Datatype: Iterates through all selected entities to determine which attributes use the VARCHAR datatype, then changes the datatype to TEXT.
  • Auto-Create Data Dictionary and Bound Domain: This macro follows several steps during its execution. First, it creates a new diagram and adds a rule, default, and domain to the Data Dictionary. Next, it binds the rule and the default to the domain. Then it adds an entity to the diagram, and adds an attribute to the entity. Finally, it binds the domain to the attribute.
  • Auto-Create Data Dictionary: Creates a user-defined data dictionary quickly. The macro can be used as a template to create user-defined or business-specific data dictionaries. This macro can be inserted into ER/Studio Data Architect's ERSBasicHandlers system (specifically in the '"CreateDiagramHandler(CurDiagram As Object)" section of ERSBasicHandlers) and if the Create Handlers option is checked on in ER/Studio Data Architect's Automation Interface Options, this Data Dictionary will be created and populated any time a user creates a new diagram.
  • Auto-Create New Diagram: Creates a new diagram that contains six entities in the logical model, then generates a physical model that uses the IBM DB/2 database platform.
  • Generate Constraints and Indexes: Generates constraints and indexes.
  • Constraint Generator: This macro generates constraints from the defined reference values. The reference values must be defined in the data dictionary of ER/Studio. The macro can select from bound attributes, all attributes, bound domains, or all domains. This is chosen with the radio buttons at the bottom of the dialog. The constraint will be inserted in the check constraint/rule tab of the domain or attribute.
  • Convert Name Case: Converts the names of the selected tables or entities in the active model to all upper- or lower-case letters. A dialog prompts the User to decide if the names should be in upper- or lower-case letters.
  • Create Index For Each FK Column: This macro creates index on foreign key columns, provided such an index does not exist.
  • Create Index on FK Columns: This macro creates index on foreign key columns, provided such an index does not exist.
  • Definition Editor: Lists all the tables and allow the user to update the definition field for the table, by pressing the "update" button. There will also be a list of columns for the respective table that you can use to update the definitions for each column.
  • Domain Bindings: Opens a dialog that shows all domains in the diagram's Data Dictionary, and all attributes that are bound to a selected domain. The User can view which attributes are bound to a selected domain; the User can also unbind specific attributes from the selected domain.
  • Example macro-Loop Through Current Submodel: Demonstrates how to loop through the objects of the current submodel.
  • Get Related Entities: This macro selects the related parents and/or child of the selected tables. To use the macro lasso a group of entities on the diagram or select them in the diagram tree, then right click on the macro to execute. Parents and children will be selected depending if the option is checked. This macro can be used to assist in submodel creation.
  • Import Data Dictionary: Imports Data Dictionary objects from a specially formatted text file. The macro contains guidelines in its comments that outline and describe the file format that must be used.
  • Import SQL Files: This macro can batch import SQL files located in specified directory.
  • Index Naming: Applies naming conventions to all types of indexes. It provides an option to use the selected tables or all tables.
  • Infer: This macro attempts to replicate the Infer operation that is available in ER/Studio during reverse engineering and importing of SQL files.
  • Move-Up Key Columns: This macro can be used to move primary or foreign key columns to the top of column list.
  • Name Foreign Constraints: Prompts 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: Names all primary key constraints with the given naming conventions. The table name with either a prefix or suffix.
  • Notes Editor: Lists all the tables and allow the user to update the notes field for the table, by pressing the "update" button. There will also be a list of columns for the respective table, that the user can use to update the notes for each column.
  • Selectively Output PK and FK DDL: Outputs DDL for primary and foreign constraints for all selected tables. To operate, selected the desired tables, then right-click on the macro to execute. The DDL can be previewed or written to a file.
  • Sort Columns: This macro will sort attributes/columns alphabetically.
  • Spell Checker: This script can be used to check spelling in the documentation fields (i.e., Definition, Note, Description, Text) of Entities, attributes, views, packages, procedures, functions, triggers, shapes in ER/Studio Models. This script makes use of the SpellCheck functionality that is present in Microsoft Word. Microsoft Word will need to be installed on your machine to run this script successfully.
  • Switch Domain Bindings: Scans all the columns and attributes in the active model or all models and switches the domain bindings from the source domain to the target domain. The information for each bound column will be updated with the target domain. Any domain overrides will be preserved.

Physical Modeling Macros

The physical modeling macros are divided into folders for specific database platforms. The following describes the Physical Modeling Macros:

IBM DB2

  • Generate Partitions for OS390 Clustered Indexes: Provides an interface to add multiple partitions to clustered indexes. A list of tables is provided. Selecting a table will load the clustered indexes to be partitioned. The storage parameters are uniform across partitions.
  • Selectively Add DB2 Permissions to PostSQL: Adds permissions to the PostSQL of any selected table in a DB2 physical model.
  • Selectively Update OS390 Index Storage: Provides a list of tables from the active physical model. The tables in the right box will be updated with the specified storage parameters. Tables can be added and removed from the updated list.
  • Selectively Update OS390 Table Storage: Provides a list of tables from the active physical model. The tables in the right box will be updated with the specified storage parameters. Tables can be added and removed from the updated list.
  • Update DB2 OS390 Index Storage Parameters: Provides a list of tables from the active physical model. The tables in the right box will be updated with the specified storage parameters. Tables can be added and removed from the updated list.
  • Update DB2 OS390 Table Storage Parameters: Provides a list of tables from the active physical model. The tables in the right box will be updated with the specified storage parameters. Tables can be added and removed from the updated list.

MS SQL Server

  • Selectively Add MS SQL Server to PostSQL: Adds permissions to the PostSQL of any selected table in an SQL Server physical model.
  • SQL Server Storage Update: Opens a dialog that lets you update table parameters for a user-specified file group, or update index parameters for a user-specified file group and fill factor.

Oracle

  • Insert Synonym in PostSQL: Opens a dialog that prompts the user to specify a synonym for a specific table or view. The synonym code is inserted into the PostSQL of the specified table or view. The active model must be an Oracle physical model in order to run this macro.
  • Selectively Add Oracle Permissions to PostSQL: Adds permissions to the PostSQL of any selected table in an Oracle physical model.
  • Selectively Add Oracle Sequence: Presents a dialog that allows the user to specify the naming conventions and options for an Oracle sequence. The sequence is added to the PreSQL of the selected tables.
  • Selectively Update Index Storage Parameters: Updates the Oracle index storage parameters for the selected tables.
  • Selectively Update Oracle Storage: Opens a dialog that lets you set Oracle-specific properties to selected entities in a model. Some of the properties that you can set are the tablespace, initial extent size, and next extent size.

Sybase

  • Selectively Add Sybase Permissions to PostSQL: Adds permissions to the PostSQL of any selected table in a Sybase physical model.

See Also

Retrieved from "https://docwiki.embarcadero.com/ERStudioDA/203/e/index.php?title=Sample_Macros_Installed_with_ER/Studio_Data_Architect&oldid=5321"