1 / 33

ERWin Template Overview

ERWin Template Overview. By: Dave Wentzel. Agenda. Overview of Templates/Macros Template editor Available templates Independent column browser Forward engineering process Trouble shooting. What is a template ?. ERWin contains a macro language Used for the creation of:

adem
Download Presentation

ERWin Template Overview

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ERWin Template Overview By: Dave Wentzel

  2. Agenda • Overview of Templates/Macros • Template editor • Available templates • Independent column browser • Forward engineering process • Trouble shooting

  3. What is a template ? • ERWin contains a macro language • Used for the creation of: • Stored Procedure Templates • Trigger Templates • Table script template (pre & post) • Based on type of template, will automatically generate SP, trigger, or script based on table/relationship information when the model is forward engineered

  4. Why are we using templates? • Provide turn-key approach to data model creation • Reduce the number of triggers and stored procedures which need to be written • Can more easily make model changes without having to ‘re-write’ triggers and SPs

  5. A little on the Macro Language... • ERWin contains a Template Toolbox which is used to create the templates

  6. Types of Macros • Entity • Applied to tables in the diagram • Can be used to act as a loop - ‘For Each Entity’ • Can be used to reference qualities of an entity - ‘For Each (Attribute/Index/Key…)’ • Relationship • Applied to relationship information between entities • Can reference PK, FK, Parent/Child entities

  7. Types of Macros Continued • Attribute • References ‘attributes’ of attributes such as length, field type, FK/PK, etc. • Constraint • References database constraint information • Miscellaneous • Arithmetic operators • Comparison operators • System Information

  8. Stored Procedure Templates • Generates a stored procedure for WRITING / UPDATING a record in a table • Based on primary key attributes • If the key values are equal to an existing record, the record will be updated • If no matching record is found, a new record will be inserted • To be used as low level write routine called from higher level SPs.

  9. Accessing the SP Templates • Right mouse click on a table • Select Table Editor / Stored Procedure option • Attach the appropriate template to the table • One must be attached for each table

  10. Available Templates • Write Template • Stand Alone Table Triggers • Write Dimension Template • Write Fact Template

  11. Write Template • Creates spWriteTableName • Assumes PK is an IDENTITY column • Assumes PK is FieldNameInst • Assumes columns: • Create Date / Create User • Modify Date / Modify User • If PK provided, updates record otherwise inserts new record

  12. Standalone Table Triggers • Generates the insert/update triggers for standalone tables • Standalone tables may occur if created as a reference table • Generated through the SP template instead of the Trigger template because the trigger template assumed relationships

  13. Write Dimension Template • Found in Datamart diagrams • Assumes a PK of identity • Assumes PK is FieldNameInst • Assumes no updates • If PK does not exist, will write new record

  14. Write Fact Template • Found in Datamart diagrams • Inserts new records into fact tables

  15. Trigger Templates • Generates Update/Delete/Insert triggers for a table and it associated tables • Helps maintain RI and date validations between related tables

  16. Triggers and Relationships • Triggers are generated based on relationships between two entities • Double click on a relationship line • Child Delete / Insert / Update • Parent Delete / Insert / Update • Options: • Restrict - prevent • Cascade - propagate change • None - no trigger relationship

  17. Accessing the Trigger Templates • Left mouse click on a table • Select SQL Server Trigger • Select SQL Server Trigger Template to attach Triggers

  18. Available Trigger Templates • Approach Child Delete Restrict • Approach Child Delete Cascade • Approach Child Insert Restrict • Approach Child Update Restrict • Approach Parent Delete Cascade • Approach Parent Delete Restrict • Approach Parent Insert Restrict • Approach Parent Update Restrict

  19. Available Trigger Templates • Approach Delete Footer • Approach Delete Header • Approach Insert Footer • Approach Insert Header • Approach Update Footer • Approach Update Header

  20. Parent / Child Templates • On restrict, provides Raise Error message when a parent / child relationship exists • On cascade, will delete all associated tables with the same primary key • Verifies records do not overlap based on from and thru dates • Requires fields either FromDate / ThruDate or BeginDate / EndDate • Assumes domain type of ID in some models

  21. Header / Footer Templates • Header creates the ‘Create Trigger’ code, parameters, and initial date checks against the table • Footer templates update the ModifyDate/User in the update triggers • Assume ModifyDate/ ModifyUser fields

  22. Standard selections for RI Actions

  23. Table Templates • Pre / post scripts generated during table creation • Used to generate print statement before table creation so implementation script is easier to read • Used for the OLAP fact tables to generate update triggers

  24. Accessing Table Templates • Left click on table • Select Table Editor / Pre & Post Script

  25. Independent Column Browser • Provides method of creating standard fields for use in current data model • Example: Used for standard fields applied to all tables • Domain type of ID exists which is referenced by some templates and must be assigned PK identity columns

  26. Accessing the Independent Column Browser • Under Window select Independent Column Browser Option

  27. Forward Engineering the Model • Triggers, stored procedures, and table scripts will be generated when you forward engineer the data model - based on the options you select • To forward engineer the model, select: Tasks \ Forward Engineer/Schema Generation

  28. Schema Generation

  29. Schema Options to Select • Table • Create Table • Drop Table • Pre-Script • Post-Script • Create Procedure • Drop Procedure

  30. Options continued • Column • Validation • Default • Index • Create Index • Alternate Key • Foreign Key • Inversion Entry • CLUSTERED • Physical Storage

  31. Options Continued • Referential Integrity • Primary Key • Alter (PK) • Foreign Key • Alter (FK) • Trigger • User Defined • RI Type Override • Relationship Override

  32. Forward Engineer • Select PREVIEW • Cut / paste into SQL query window to run • Do not directly forward engineer into the database • Could delete items you did not anticipate • May be connected to a production server

  33. Troubleshooting • Execute script • Review error statements • Review script code with error • Access the template to see what it is expecting • Verify correct relationships, triggers, SPs, etc. are associated with the table • Verify required fields are assigned to the table

More Related