Data Modeling Tips, Tricks, and Customizations

PDR605: Data Modeling Tips, Tricks,
and Customizations
Anthony Hill
Senior Product Support Engineer
[email protected]
August 15-19, 2004
PDF "pdfFactory" www.fineprint.cn

f
The Enterprise. Unwired.

f
Industry and Cross Platform Solutions
Manage Unwire Unwire

Information Information People
Adaptive Server Enterprise Unwired Accelerator SQL Anywhere Studio
Adaptive Server Anywhere Unwired Orchestrator M-Business Anywhere
Sybase IQ Unwired Toolkit Pylon Family (Mobile Email)
Dynamic Archive Enterprise Portal Mobile Sales
Dynamic ODS Real Time Data XcelleNet Frontline
Replication Server Services Solutions
OpenSwitch PocketBuilder
Mirror Activator PowerBuilder Family
PowerDesigner AvantGo
Connectivity Options
EAServer
Industry Warehouse Studio
Sybase Workspace

TT
Industry and Cross Platform Solutions
Manage Unwire Unwire

Information Information People
Adaptive Server Unwired Accelerator SQL Anywhere Studio*
Enterprise* Unwired Orchestrator* M-Business Anywhere
Adaptive Server Anywhere Unwired Toolkit* Pylon Family (Mobile Email)
Sybase IQ* Enterprise Portal Mobile Sales
Dynamic Archive Real Time Data XcelleNet Frontline
Dynamic ODS Services* Solutions
Replication Server* PocketBuilder*
OpenSwitch PowerBuilder Family*
Mirror Activator* AvantGo
PowerDesigner
Connectivity Options
EAServer*
Industry Warehouse Studio
Sybase Workspace
* = Products supported by PowerDesigner

T fT
Topics
PowerDesigner Options
Managing Diagrams
DBMS Definition File (XDB)
Extended Model Definitions (XEM)
Definition Files (All Definition Files)
Generation
Transformations
Profiles
Generation Template Language (GTL)
VB Script
PowerDesigner Metamodel
Miscellaneous Tips

Miscellaneous Tip Workspace loading
Restore the exact state of the model when you saved the
workspace
[HKEY_CURRENT_USER\Software\Sybase\PowerDesigner 10\General]
WorkspaceRestoredStates = Loaded, Expanded, OpenedReadOnly
Loaded = Loads models that were open when workspace last saved
By default expands workspace tree if a model is opened
Expanded = Expands workspace tree
OpenedReadOnly= Opens files in read-only mode that previously had this setting
Hot keys for expanding and collapsing nodes

Expand all nodes at once press numpadstar (*)
Expand individual nodes press numpadplus sign (+)
Collapse all nodes at once press numpadminus sign (-)

Miscellaneous Tip Cancel model open or save
Problem
You started to open a model, but you realized it was the wrong file. You would like to
cancel the open process but you cant.
Solution
A registry key that allows you to have a Cancel button appear onstatus bar during
Open and Save action
"CanLongActionsBeCanceled" = "Yes"
The default value is No for GA product
Valid in PowerDesigner 8 or greater

PowerDesigner Options
What
Method for controlling PowerDesigner behavior.
Display Preferences controls visual aspects of PowerDesigner
Model Options controls model specific aspects of PowerDesigner
General Options options not specific to any particular model
Why
Alter PowerDesigner behavior to best suit your needs
Changes can either be model specific or set as defaults for new models created
How
General Features Guide Chapter 3 -Using the PowerDesigner Interface Defining
Global Options
General Features Guide Chapter 7 Managing Models sections dealing with
Naming Conventions and Conversion
General Features Guide Chapter 14 Model Graphics Model Display Preferences

PowerDesigner Options -Managing Diagrams
Move Diagrams
Move Diagram Wizard Allows you to selectively move diagrams and its objects to a
package or different model of the same type
Drag and drop diagram
Problem:
All objects are currently in packages and the user would like tocreate a global
diagram of all objects.
Solution:
In main model diagram, select menu Model/List of object
Select all objects in list and drag as shortcuts to the diagram

PowerDesigner Options Generation Options
Inter model
Convert Names into Codes option must be selected during inter model generation
Object codes are generated from names using associated conversion script
DBCreateAfterStereotype
Used with stored procedures and views to control generation order

PowerDesigner Options Reverse Engineering
Reverse engineer into an existing model or a new model
Selection specify DBMS source to be reverse engineered. Either
an ODBC data source or SQL script file
reverse using administrators permission select in order to be able to select data in
the system tables that are reserved to a database admin
Options
After reverse engineering options that potentially can alter the reverse engineered
results
Script terminator terminator used to end the statement when reverse engineering via
script
Target Model includes or allows you to add target model(s) to
create external shortcuts

Miscellaneous Tips Disable Synonym creation
Problem:
When the auto-layout feature is executed, synonyms are sometimes created for
several object. This functionality is not always desired especially when reverse
engineering a database.
Solution:
Use the following registry key to deactivate the synonym creation
"SynonymsInAutoLayout"= "No" ("Yes" by default)
Autolayoutwill attempt to create a star schema by default
To remove unwanted graphical synonyms from existing models,

execute the following VBScript

Miscellaneous Tips Remove graphical
synonyms VBScript
' Delete unwanted graphical synonyms
option explicit
RemoveModelSynonymsActiveModel
sub RemoveModelSynonyms(M)
dim Dg
for each Dg in M.AllDiagrams
output " **** Searching in " + Dg.Name + " (" + Dg.Code + ")"
RemoveDiagramSynonymsDg
next
' manage sub packaegs
dim p
for each p in M.Packages
RemoveModelSynonymsp
next
end sub

Miscellaneous Tips Remove graphical
synonyms VBScript (cont)
sub RemoveDiagramSynonyms(D)
dim DSym, CloneCount
for each DSymin D.Symbols
if DSym.IsKindOf(cls_TableSymbol) then
CloneCount= DSym.ClonePredecessor.Count+ DSym.CloneSuccessor.Count
if (CloneCount> 0) then
dim DelSym
output "Deleting " & CStr(CloneCount) + " clone(s) for Table " + DSym.Object.Name+ " (" +
DSym.Object.Code+ ")"
for each DelSymin DSym.ClonePredecessor
DelSym.Delete
next
for each DelSymin DSym.CloneSuccessor
DelSym.Delete
next
end if
end if
next
end sub

What
Method used by PowerDesigner that contains specifications for a particular Database
Management System (DBMS). It contains the syntax templates for generation,
reverse engineering, data types and constants for a supported DBMS.
Why
Used by PowerDesigner engineering to support all the DBMS
Used by users to alter standard DBMS to better suit their needs
Used by users to add new DBMS that are not shipped with PowerDesigner
How
Advanced User Documentation Chapter 1 DBMS Reference Guide
Advanced User Documentation Chapter 2 Managing Profiles
Physical Data Model Users Guide Chapter 13 Variables in PowerDesigner
Physical Data Model Users Guide Chapter 14 DBMS-Specific Features
Review included XDB files for understanding and ideas

General DBMS Identification and general flags that apply for all
objects
Script -DBMS characteristics, command definition, and data type
translations for script generation and reverse engineering
SQL contains values that define the general syntax for the database
Syntax -Contains general parameters for SQL syntax
Format -Contains entries that define script formatting
File -Text entries used during the database generation
Keywords -List of reserved words and functions available in SQL
Objects details for each type of object available for generation to andreverse
engineering from the database

Script (cont)
DataType lists of valid data types including conversions to PowerDesigner
internal data types
AmcdDataType list of PowerDesigner internal data types mapped to
DBMS data types
OdbcPhysDataType-translation table from ODBC data types to target
DBMS data types
PhysDataType contains the translation table from target database data
types to internal data types
PhysDttpSize-table of storage sizes of target DBMS data type
PhysOdbcDataType contains data type translations from target DBMS
to ODBC
HostDataType translation from database data type to procedure data
type
Abstract translation files several files for abstraction data type
translation
Customize retrieved information from PowerDesigner 6 DEF files

ODBC same structure as Script category.Used for ODBC
generation when DBMS Script category does not suffice
Profile see Definition Files section
Trigger templates list of pre-defined trigger templates indicating
referential integrity
Trigger type insert, update, delete
Trigger template items list of pre-defined trigger template items to
support the PowerDesigner trigger templates

DBMS Definition File (XDB) -How does it all
work?
Generation -object statements are parsed and their variables are
evaluated and replaced. Script and ODBC use the same statements
Extension mechanism uses extension statements to generate additional code before
or after the Create, Drop, or Modify statements
Defined using PowerDesigner Generation Template Language (GTL)
Can contain references to extended attributes, variables, and macros
Modify statements -statements used to modify existing objects

during database modification and to generate DDL outside the
table create statement
ModifiableAttributes list of extended attributes to be evaluated during the Modify
database process
Database definition items
Statements that customize the PowerDesigner interface and behavior according the
current database features

DBMS Definition File -How does it all work?
Reverse engineering -Script

Each statement in the script file maps to a create statement in the DBMS file
ReversedStatements list of Extension statements used in database
generation

DBMS Definition File -How does it all work?
Reverse Engineering ODBC
Standard queries
SqlListQuery populate selection list
SqlAdditionalQuery gather addition information for each selected item in the
list
SqlAttrQuery additional attributes
SqlChckQuery check constraints
SqlOptsQuery physical options
SqlPermQuery permissions
And more right click the object and select Add New to see available
standard options
All of these are not necessary, if you can get enough information in your list result
set the others are not required.
If none of these queries exist, standard ODBC calls are attempted

DBMS Definition File How does it all work?
Reverse Engineering ODBC (cont)
ODBC Two methods of extending reverse logic beyond the standard queries when
the default ODBC function calls do not retrieve enough information
Extended Queries user defined queries and variables used when system table
information is stored in Long, Blob, or Text columns
Returned field allowing this is indicated in query header with the "EX"
indicator
When the "EX is found, and if the returned value contains a %% with query
name and field name, then it will execute the appropriate query and use the
returned value to replace the %% part.
ReversedQueries list of user-defined ODBC queries to be taken into account
via reverse engineering
Queries must be listed in ReveredQueriesitem to be executed
The result of a query is mapped to PowerDesigner internal variables via the query
header

Miscellaneous Tips List Tips
List tips
Customize Columns and Filters button in toolbar -add or remove displayed columns
Multi-select to make same change to many objects
Ctrl-Up -First line in list
Ctrl -Down-Last line in list
Ctrl -Left -First column on row
Ctrl -Right-Last column on row
Sort selected columns in customize filter

In Customize Columns and Filter dialog, use key combination <shift>+ click OK
Result
Selected columns are grouped at the top of the list

Miscellaneous Tips Change Modify SQL order
Problem:
When executing Modify Database, PowerDesigner generates the DDL based upon
statement type. All the drop statements, then the create statements, etc. However,
some customers do not like this format.
Solution:
Change the modify SQL order. To group all the SQL orders relative to a table in
Modify script add the following registry key (CR 270997)
[HKEY_CURRENT_USER\Software\Sybase\PowerDesigner
9\FolderOptions\Physical Objects]
"GenTablGroupStmt" = "Yes"

What
Mechanism for extending PowerDesigner object definitions
Contains graphical, functional and generation parameters
Why
Used by PowerDesigner engineering to support various functions
Can be used to complement object language generation Application servers
support
Can be used to generate totally new text files
Users can customize included behavior to better suit your requirements
Users can add totally new functionality
How
General Features Guide Chapter 8 Working with Extended Model
Definitions
Advanced User Documentation Chapter 2 Using Profiles
Advanced User Documentation Chapter 4 Extended Model Definitions
Reference Guide
Review included XEM files for understanding and ideas

XEM can be created for each of PowerDesignersmodel

types
Business Process Model
Object Oriented Model
Physical Data Model
Free Model
Conceptual Data Model
XML Model
XEMsare specific to each model type,
i.e. can not be shared between model types.

Extended Model Definition properties
Name and Code uniquely identify this definition
File Name where XEM is saved, blank if internal to the model
Family Classify an XEM, usually to associate it with an Object Language
Sub Family further classification of and XEM within the selected family
Auto attach determines whether the default is to include this XEM in new models
created targeting the family specified above
Category used to control generation options
Enable Trace Mode debugging/learning tool that lets you preview the templates
used
Complement Language generation determines whether this XEM definition will be
used in the language generation (Only available for OOM XEM files)

Generation section define optional tasks to be processed

after generation and options used during generation
Commands actual commands which will be executed post generation
Written in the Generation Template Language
Just writing the command does nothing, must be linked to a task and
selected at the time of generation
Tasks actual list presented during generation, these reference one or
more commands defined above
Options options user can specify as part of the generation process
Variables which are displayed and set at generation
Options values can be referenced in all templates
Option types allowed boolean, String or List

Transformation Profiles Group of transformations used during

model generation
Can target to model type, Family and Subfamily
Pre-generation transformations executed before generation in the source model
Post-generation transformation executed after generation in the target model
PowerDesignersbeginning stages of supporting Model Driven Architecture(MDA)
Design Patterns could be built using the transformation logic

Definition Files (All Definition Files) Profile
What
Extension mechanism for customizing the PowerDesigner Metamodel.
Categorizing objects, customizing graphics, adding metadata, adding object specific
generation options and more
Why
Used by PowerDesigner engineering in both XDB and XEM files
Allows customizing graphics based on object type and category
Customize context menus based on object type
Add additional metadata to specific object or categories of objects
How
Advanced User Documentation Chapter 2 Managing Profiles
Review supplied XDB and XEM files for examples of usage

Definition Files (All Definition Files) Profile
Shared contains extended attribute types or templates which can
be used by any object
Metaclasslevel extensions
Determines the level at which the extension applies
Extensions apply to all instances of the metaclass
The PowerDesigner metamodelis object oriented and thus supports inheritance
Tip when adding metaclasses, change the filter to show abstract classes
Example extended attributes applied to the following levels would applyto
NamedObject -applies to all objects
ConstrainedNamedObject -applies to Columns and Domains
Columns -applies to Columns only

Definition Files (All Definition Files) Profiles
Instance level extensions

Stereotype extension applies to instances of the object which have had
the stereotype set to match
Example Java Classes EJBEntity, EJBSession, WebAppListener
Criterion extension applies to instances of the object which match the
Criterions condition
Example Java Classes Bean Class, ServletClass, WebServicesClass

For Metaclasslevel extensions the following can be added

Stereotypes sub-classification on instances of the metaclass
Custom Tool allows the creation of a tool palleteobject to ease the creation
of objects
Criteria sub-classification on instances of the metaclassbased on a condition
Extended Attributes additional metadata which can be controlled by instance
and used to control generation
Custom Checks enhance the validation of instances when check model is
performed
Custom Symbol customize the visual display of objects to better identify the
instances

For Metaclasslevel extensions the following can be added (cont)

Templates and Generated files used to generate text files based on the model
Methods and Menus These two are used to add entries to context menus.
Methods hold the vbscriptto execute, menus are the text that show in the menu
Event Handler -Definition of a script to catch the occurrence of an event on an
object
Extended Collection -Definition of an additional link between a selected
metaclassor stereotype, and another metaclassor stereotyped metaclass
Transformations -Definition of a transformation script that defines a set of
actions to be executed during generation or upon request

For Instance level extensions the following can be added

Extended Attributes additional metadata which can be controlled by instance and
used to control generation
Custom Checks enhance the validation of instances when check model is
performed
Custom Symbol customize the visual display of objects to better identify the
instances
Templates and Generated files used to generate text files based on the model
Methods and Menus These two are used to add entries to context menus.
Methods hold the vbscriptto execute, menus are the text that show in the menu
Event Handler -Definition of a script to catch the occurrence of an event on an
object
Extended Collection -Definition of an additional link between a selected metaclass
or stereotype, and another metaclassor stereotyped metaclass
Transformations -Definition of a transformation script that defines a set of actions
to be executed during generation or upon request

Walk Thru
Profiles Walk Thru

Generation Template Language
What
PowerDesigner internal template based language used for text generation
Used in Definition files (XDB or XEM) to generate files based ona model
Why
Allows for all PowerDesigner generation to be flexible
Used by PowerDesigner engineering to generate text files
Can be used by users to alter files generated by PowerDesigner to suit your individual
needs
Can be used to generate entirely new files.
How
Review supplied database definitions and extended model definitions
Advanced User Documentation Chapter 5 Generation Reference Guide

Miscellaneous Tips -HTML Report page break
Problem:
PowerDesigner implicitly cut pages every 50 rows for list which can be a problem for
large model
Solution:
Define when you want PowerDesigner to generate a page break in list using the
following keys:
NbRowInHtmlList -allows to define the number of items you want on each HTML
pages generated for list items
NbSubPageInHtmlList -allows to define the number of links you want to have below
each pages of the list.
"NbRowInHtmlList" = "50" (default value)
"NbSubPageInHtmlList" = "20" (default value)

Miscellaneous TipsChanging colors in code
editor
It is possible to customize the colors used for keywords,
comments, etc. in the code editors (VB Script, Preview, SQL Editor,
...).
The change can be done in registry under the key
[HKEY_CURRENT_USER\Software\Sybase\PowerDesigner
10\Controls\Editor]
By default, the Controls\Editor is almost empty.
The keys are dynamically created once you have opened a syntax editor in
PowerDesigner (preview, script, VB Script, ...).
You can change the syntax coloring for each type of file/editor
(Java, SQL, C++, ...).
For each file type, there are several category of colors:
Comment, Keyword, Number, Operator, String, Text, Text Selection
For each category, you can change the foreground color and background color.

Miscellaneous Tips Changing colors (cont)
To change the text colors:
Open the registry
Open the node Software\Sybase\PowerDesigner 10\Controls\Editor
Open the node of the file type, for example, SQL Files
Open the Color Groups node
Select a category, for example, Comment
Create a new DWORD entry
Rename the entry to Foreground
Change the value of the entry to: BBGGRR in hexaformat.
Example: 0000FF (red), FF0000 (blue)

VBScript
What
Microsoft scripting language
Why
Automate repetitive tasks
Import / Export information
Extend PowerDesigner via additional model checks
Access PowerDesigner information from other applications
Used by PowerDesigner engineering to implement certain functionality
How
Review supplied sample scripts
PowerDesigner home directory\VBScripts
PowerDesigner home directory\OLE Automation
Get familiar with the PowerDesigner Metamodel
Experiment - *Backup your models first*

VBScript
What is supported
Object manipulation Full Create, Update or Delete control on all
PowerDesigner objects including graphical objects
Repository access connect, extract, consolidate and manage versions
Invoke reports
Generation
Generate new model
Regenerate into existing model
Generate files
What is not supported

Reverse engineering
Algorithms like
Rebuild indexes
Rebuild triggers

VBScript Walk Thru
VBScript Walk Thru

PowerDesigner Metamodel
What
A description of PowerDesigner objects, properties and methods
Why
Learn how to access PowerDesigner objects
Using VBScript
Using Generation Template Language
Using other languages that support COM
Understand PowerDesigner XML formatted models
How
PowerDesigner home directory\Examples\Metamodel.oom
Set of UML class diagrams graphically representing the metamodel
PowerDesigner home directory\pdvbs9.chm
Hyperlinked reference document of the PowerDesigner Metamodel

Conclusion
QUESTIONS ???

Miscellaneous Tips Test Data Generation
Change data format in Test data generation
In order to change the date format in test data generation, you
must create new entries in XDB file
\Script\Sql\Format\DateFormat
\Script\Sql\Format\TimeFormat
\Script\Sql\Format\DateTimeFormat
\Script\Sql\Format\OdbcDateFormat
\Script\Sql\Format\OdbcTimeFormat
\Script\Sql\Format\OdbcDateTimeFormat
For datetime(DT) or timestamp (TS) data types
"DateTimeFormat" is used generating by script
"ODBCDateTimeFormat" is used generating by ODBC.
For date (D) datatypes
"DateFormat" is used generating by script
"ODBCDateFormat" is used generating by ODBC

Miscellaneous TipsTest Data Generation(cont)
For time (T) datatypes
"TimeFormat" is used generating by script
"ODBCTimeFormat" is used generating by ODBC.
PowerDesigner looks at the conceptual correspondent data type of
the physical data type of the column to be generated to know
which DBMS entry to use.
The content of these entries will be the format for the date using
the following formatting strings
"yyyy" represents year on 4 digits
"yy" represents year on 2 digits
"mm" represents months
"dd" represents days
"HH" represents hours
"MM" represents minutes
"SS" represents seconds

Miscellaneous Tips-Copy Symbol Format
Copy Symbol Format
Applies object formatting information from one object to another(line styles, color, font,
)
Only accessible via format toolbar, or add to some other toolbar
two toolbar pieces
Get Format -Select source object and click
Apply Format -Select Target object and click

Miscellaneous Tips MouselessProp Nav
MouselessProperty Sheet Navigation
Ctrl -Tab -Next tab page
Ctrl-Shift-Tab -Previous tab page
Ctrl-PgUp -Next sub tab, Notes Description or Annotations, text area must have
focus
Ctrl-PgDn -Previous sub tab

Miscellaneous Tips Mouse Roller Support
Mouse Roller Support
roll up: scroll to top
roll down: scroll to bottom
shift+roll up: scroll left
shift+roll down: scroll right
ctrl+roll up: zoom-in and center the view to the position of the mouse
ctrl+roll down: zoom-out and center the view to the position of the mouse

Data Modeling Tips, Tricks, and Customizations

Uploaded by

Copyright:

Available Formats

Data Modeling Tips, Tricks, and Customizations

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Data Modeling Tips, Tricks, and Customizations

Uploaded by

Copyright:

Available Formats

What are some of the products supported by Sybase?

What are some of the products supported by Sybase?

What are some tips for changing data formats in test data generation?

What are some tips for changing data formats in test data generation?

PDR605: Data Modeling Tips, Tricks,

PDF "pdfFactory" www.fineprint.cn

PDF "pdfFactory" www.fineprint.cn

Industry and Cross Platform Solutions

Manage Unwire Unwire

PDF "pdfFactory" www.fineprint.cn

Industry and Cross Platform Solutions

Manage Unwire Unwire

* = Products supported by PowerDesigner

PDF "pdfFactory" www.fineprint.cn

PDF "pdfFactory" www.fineprint.cn

Hot keys for expanding and collapsing nodes

PDF "pdfFactory" www.fineprint.cn

PDF "pdfFactory" www.fineprint.cn

PDF "pdfFactory" www.fineprint.cn

PDF "pdfFactory" www.fineprint.cn

PDF "pdfFactory" www.fineprint.cn

PDF "pdfFactory" www.fineprint.cn

Autolayoutwill attempt to create a star schema by default

To remove unwanted graphical synonyms from existing models,

PDF "pdfFactory" www.fineprint.cn

PDF "pdfFactory" www.fineprint.cn

PDF "pdfFactory" www.fineprint.cn

PDF "pdfFactory" www.fineprint.cn

PDF "pdfFactory" www.fineprint.cn

PDF "pdfFactory" www.fineprint.cn

PDF "pdfFactory" www.fineprint.cn

Modify statements -statements used to modify existing objects

PDF "pdfFactory" www.fineprint.cn

Reverse engineering -Script

PDF "pdfFactory" www.fineprint.cn

If none of these queries exist, standard ODBC calls are attempted

PDF "pdfFactory" www.fineprint.cn

PDF "pdfFactory" www.fineprint.cn

Sort selected columns in customize filter

PDF "pdfFactory" www.fineprint.cn

PDF "pdfFactory" www.fineprint.cn

PDF "pdfFactory" www.fineprint.cn

XEM can be created for each of PowerDesignersmodel

PDF "pdfFactory" www.fineprint.cn

PDF "pdfFactory" www.fineprint.cn

Generation section define optional tasks to be processed

PDF "pdfFactory" www.fineprint.cn

Transformation Profiles Group of transformations used during

PDF "pdfFactory" www.fineprint.cn

PDF "pdfFactory" www.fineprint.cn

PDF "pdfFactory" www.fineprint.cn

Instance level extensions

PDF "pdfFactory" www.fineprint.cn

For Metaclasslevel extensions the following can be added

PDF "pdfFactory" www.fineprint.cn

For Metaclasslevel extensions the following can be added (cont)

PDF "pdfFactory" www.fineprint.cn

For Instance level extensions the following can be added