PowerDesigner: Generate automatic incrementing index name and code

Would you like to have index names and code generated with something other than Index_1, Index_2, etc.?

Here's how to do that. (Thanks for guidance from Robert Adlington, SAP/Sybase Technical Publications Manager).

1. Open the XDB file (Oracle used in this case)
2. Navigate to root:Profile\Index.
3. Right click Index in the navigation column and select New->Event Handler.
4. Select Initialize from the drop-down list
5. In the Event Handler Script tab, enter your custom code:  I use:
   obj.SetNameAndCode obj.parent.name + " Index", Left(obj.parent.code, 24) + "_IDX"

After doing the above, in a table's property sheet on the Indexes tab, create a new index.  It should use the text from the table's Name attribute plus the word "Index" for the index's name and the first 24 characters of the table's Code attribute plus the _IDX extension for the index's code value.

The first index will have suffix _IDX and the second will have _IDX2, with the number incrementing for each additional index.  

The SetNameAndCode method applies any naming conventions and also verifies uniqueness - hence the incrementing suffix. I can’t make the first index end in 1, but you may be able to enhance the script to do so if it is important to you. [Rober Adlington]

If you use name to code conversions, the abbreviations from your conversion table will be picked up automatically in the index's code.  However, if you use a different abbreviation for Index than IDX, you will need to change the code entered into the Event Handler Script tab to use that instead.

Unfortunately, I don't know how to get the conversion table's abbreviations to be picked up automatically in that script (yet!)  If someone knows how to do that, please let all of us know with your added comment.

How to Import Permissions into PowerDesigner.

Import Permissions into PowerDesigner

Have you ever had to create permissions for users to many objects?  And did you want to maintain these permissions in your PowerDesigner Physical Data Model (PDM)?

I recently had to add permissions to an Oracle database that I had reverse-engineered into PowerDesigner, but PD did not import the permissions!  I haven't figured out why not yet.

But with the help of Richard Kier and Cristy Russell who responded to my post on LinkedIn's Sybase PowerDesigner Users group, I was able to identify all objects that needed permissions added and then create an Excel file to import them using the Excel Imports extension.

By the way, permissions are also known as "grants" since that is the DDL command to give permissions to users on objects:

GRANT action ON object TO user;

Excel Imports

Before you can import into PD from an Excel file, you must add the "Excel Import" extension to your model.  The Comment section for this extension says:

"This extension allows you to create PowerDesigner model objects from tables of data imported from an Microsoft Excel file."

I won't go into the details of how to attach this extension to your model in this post.  But if you need help doing that, just email me.

Excel File Template

Before you can create an Excel Imports object, you must create the Excel source file.  This is where I ran into problems because PD documentation does not provide detailed guidance for this.  There is a sample Excel import file located at C:\Program Files\Sybase\PowerDesigner 16\Examples (assuming you use the default installation folders) named excel_import.xls.  It contains very brief examples for Table, Table.Column, Reference, Reference.Reference Join, Table.Key, and Reference2.

So, creating an Excel file to import for permissions requires a lot of guesswork.  I started by creating a list report for permissions to find the column headings to use.  You could use any heading you want, but then you have to know which attribute to map that column heading to.  If you use column headings that PD already knows about, it will provide suggestions when you manually map the columns. (I don't use the "Auto-map columns to properties" import option.)

My permissions list report included the following columns:

• Parent
• In the List Report, this column is populated like this:
  Table 'RPTUSERN'.'ACCOUNT_CODE_CATEGORIES'
• When populating your Excel Import file, only put the table's Name or Code value.  So the above would become:
  ACCOUNT_CODE_CATEGORIES
• Required column in Excel file
• Object
• This is the name or code (depending on how you have your model configured) of the table, view, or procedure.  Since the object could be a table, view, or procedure, I added the "Object.Object Type" column to let me know.  If you only want tables, you could filter the report for Table types and not display the column.
• Required column in Excel file
• User
• The User shown is not the owner of the object, but the user the permission is to be granted to.  Make sure you have this user created in the PDM even if it doesn't own any objects.
• Required column in Excel file
• Grant
• This is the permission(s) you want to give to the user.  I only granted SELECT, but if you have granted multiple permissions to a user for the object, they would be displayed in the list report as a comma-separated list.  I don't know if a comma-separated list of permissions can be imported this way; someone should try that and let me know (or I'll have to try that later myself).
• Required column in Excel file
• Grant With Option
• If you want the user to be able to grant other users this permission, enter the permission in this column.
• Optional column in Excel file
• Revoke
• While PD provides this column for the list report and they do show up, but grayed out, on the Permissions tab of an object, I haven't figured out how they would be used since you can't grant a permission and revoke it at the same time.  And PD doesn't seem to provide an option to mark an existing permission with a "to be revoked" option.
• You don't have to include this column.
• Revoke With Cascade
• While PD provides this column for the list report and they do show up, but grayed out, on the Permissions tab of an object, I haven't figured out how they would be used since you can't grant a permission and revoke it at the same time. And PD doesn't seem to provide an option to mark an existing permission with a "to be revoked" option.
• You don't have to include this column.
• Object.Object Type
• I included this column in the list report only to show me what type object was being reported.  I could have filtered the rows by this column to only show table, view, or procedure objects.
• You don't have to include this column.

From this list report, I created an Excel file with same column names; only the first four are required.  The fifth is optional and only needed if you want to allow the user to be able to grant the permission to other users.

Determine Objects Without Permissions

To reduce the number of entries needed in the Excel file, I needed to know which objects had missing permissions.  The list report I created above only showed those objects that had permissions.

Richard Kier suggested I run the Check Model function to determine which objects would fail the 'Existence of Permissions' check.  I did this and got an ugly list I could retreive from the Output window, but it requires a lot of editing to get just a list of the name/code value for the tables, views, or procedures.  These name/code values would be the ones you would paste into the Parent and Object (or any other name you wanted for this column) of the Excel import file.

Cristy Russell suggest I try a dependency matrix to get this list.  I hadn't used a dependency matrix before, so this was (is) a learning experience for me.  The defininition settings I used for my dependency matrix are:

• Object types
• Rows = Table
• Columns = User
• Matrix cells
• Dependency = Permissions
• Object type = Permission
• Object attribute = Grant
• While "Grant With Option" is also available, I couldn't find any way to have both attributes selected.  So I'll have to create a second dependency matrix for "Grant With Option" attribute.

A separate dependency matrix will need to be created for View and Procedure object.

This dependency matrix is displayed in the browser the same way as a diagram.  There is an icon at the top that will allow you to "Display only empty rows" that can be copied and pasted into the Excel import file.  The objects in the left colum will be placed in both the Parent and Object columns of the import file.

Import Excel File

Now that you have 1) added the Excel Import extension, 2) determined which objects and users need permissions added, and 3) populated your Excel import file, you are ready to actually import the file into your physical data model.

I won't detail how to do that since that is fairly intuitive.  Start the import by right-clicking in an open diagram for the model and selecting "Import Excel File...".

KPI, metric, measure: Huh?

KPI, metric, measure: Huh?

I recently read an interesting discussion on LinkedIn asking "What is the difference between KPI, metric, and measure?"  When I was first notified about the discussion, I thought "That's easy to answer."

Then I started reading all the answers; some thoughtful, some insightful, some trite, some silly, ... you get the idea.  But it did point out that terms we in the IT world use frequently may not be universally defined.

So, here are my definitions, take 'em or leave 'em.

KPI

A Key Performance Indicator (KPI) is the use of a metric that the business has defined to provide management with a quick analysis of how the business is doing in the area being measured that is considered critical.

Notice how I used metric and measure in that definition?

Metric

A Metric is a relationship of a measure to something that will indicate some degree of good or bad, success or failure, improvement or degradation such as time, distance, repetitions, events, etc..  For example, miles per hour, gallons per minute, kilowatts per hour; each of these can be given some rating or ranking in a hierarchy.

100 miles per hour might be an "Unsafely Fast" metric on a two-lane road, but be "Very Slow" on a NASCAR race track.

Notice how metrics are made up from measures?

Measure

A measure is fixed indicator of something that can be quantified such as count, size, quantity, dimension.  Examples are 2 apples, 15 inches, 8 ounces, 10 acres.

Notice that measures are at the bottom of this hierarchy.  Measures are used to define metrics which are used to define Performance Indicators which may become Key Performance Indicators.

Notice that I snuck in a level between metric and KPI?  There can be many performance indicators, but there should be few Key Performace Indicators.

PowerDesigner: How to determine "create" and "modify" timestamps in XDB files

Have you ever wondered who made a change to a PowerDesigner XDB file and when the change was made?  Here's how to figure that out.

Open an XDB file in a text editor.  Search for CreationDate or ModificationDate.  Following these XML tags you will find Creator or Modifier tags; these will give the logon id (OS user) of the person who created or modified the attribute.

To get the date of creation or modification, create an Excel spreadsheet similar to the one shown below.  In the "Seconds" column, insert the number found in the XDB file such as:

<a:ModificationDate>1348840976</a:ModificationDate> (Column A4.)

Divide this number by 86,400, the number of seconds in a day ("Days", B column).

• B column formula: =A4/86400

PowerDesigner counts the number of seconds from Midnight on January 1, 1970 UTC.  In Excel, add the number of fractional days to this date to get the calculated date in the UTC time zone.

To calculate the local time, add or subtract your time zone offset.  For me in the U.S. Central time zone, during daylight savings time, I need to subtract 5 hours, or 5/24 of a day.  The formula I use is below.

• D column formula: =(C4+B4)-(5/24)

Of course, the formulas above would have their rows changed in each row, but you should get the idea.  You would also have to use a "custom" cell format to get the dates to display as shown below.  I use "m/d/yyyy h:mm:ss" as a custom format for dates in columns C and D.

	A	B	C	D
1	Seconds	Days	Starting Date UTC	CDT
2	1325765647	15344.5098	1/1/1970 0:00:00	1/5/2012 7:14:07
3	1330036842	15393.9449	1/1/1970 0:00:00	2/23/2012 17:40:42
4	1348840976	15611.5854	1/1/1970 0:00:00	9/28/2012 9:02:56
5	1348842034	15611.5976	1/1/1970 0:00:00	9/28/2012 9:20:34

Hope you find this useful.

PowerDesigner:: How to reroute links

Have you tried to manually reroute a link in PowerDesigner (PD) and been frustrated that, no matter what you try, you can't get the link to go the route you want it to go?

After much irritation at not being able to find a way to do this in the help documents, I emailed pubs@sybase.com to see if they could tell me where to find help topics on this.  They responded by giving me not only the help pages to review, but giving me step-by-step instructions on how to do it.

My problem involved routing lines around other tables.  If the most direct route was through another table and I couldn't move any of the tables involved, I wanted to go from top-to-top, bottom-to-bottom, or side-to-side around the blocking table.

It turns out that I needed to add "elbow joints" to the line and then drag the line between the two new elbow joints.  How far to drag this section was not apparent, but it appears that one must drag the line far away from the actual route desired so PD will actually create the two new right angle turns in the line.  If you don't go far enough, PD will either return to where you started or create a new route not what you want.

Once you drag the line far enough away to actually get the route to do what you want, you can drag the center portion (between the two new elbows) to where you really want the line to go.

The help page to look at is:
Core Features Guide > Modeling with PowerDesigner > Diagrams, Matrices, and Symbols > Symbols > Manipulating Link Symbols

To add additional "elbow" points, ctrl-click on the link where you want to add the new elbows.  By the way, these are temporary elbows and if you don't get the results you wanted after dragging the link, you will have to recreate them.

Once you have added two new elbow points, left-click on the space between the two elbows and drag it to where you want the route to go (a little farther is better).  Once you get the link to have the route you want, you can move it desired position by dragging between the two new right angles.

PowerDesigner 16.1 Repair

After installing an EBF, do you quite often find PowerDesigner does not work properly?  A very common occurrence I have is the receipt of the following message (multiple times, I might add):

After Googling this error and trying some of the suggested fixes, I finally called PD support and this is what worked:

1. Close PD.
2. Start - Control Panel
3. Programs and Features
4. Right-click Sybase PowerDesigner ...
5. Click "Change"
6. Click "Repair"
7. Click "Next"
8. Click "Finish"
9. Open PD - hopefully it works.
10. If not fixed, uninstall PD, reinstall base PD and last EBF (they are cumulative.)

PowerDesigner 16.0 CDM entity properties displays TRUE instead of actual value

Product: PowerDesigner Enterprise Architect Version 16.0.0.3576 EBF5

OS: Windows 7 Professional

 In CDM entity property window, clicking in property column displays TRUE instead of the actual text in the cell.

Closing and reopening the entity property window temporarily fixes it, but TRUE will be displayed eventually.  If press TAB to step through all columns, when it wraps to next row, problem is temporarily fixed.

Bug reported: Case 11738269 created successfully.