Work smarter with #PowerDesigner – use a Dependency Matrix to assign Domains to Attributes

PowerDesigner’s dependency matrices are really powerful, and I don’t ever remember seeing anything similar in a data modelling tool. They allow me to visualise and even edit links between objects.

In a Conceptual, Logical, or Physical Data Model, or in a UML Object Model, Domains are a useful object, allowing you to manage the ways in which your data is represented. Take this simple data model, for instance.

1. initial model

I’ve reached the point where I need to assign a Domain to each attribute. I can edit each attribute one at a time, and select a Domain from the drop-down list, like this:

3, Assigning a Domain

In a large model, that could take some time. There are a couple of ways we could speed up the process:

  • edit multiple attributes at once using a list of attributes
  • use a Dependency Matrix

In this blog post, I’ll cover the second option. A Dependency Matrix is a model object, so  like any other model object there are several ways of creating one. The simplest way is to right-click the model name in the Browser, then select “New”, and “Dependency Matrix”. The first thing we have to do is choose the types of objects to display in the rows and columns.

4. Create matrix

I want to use this matrix for editing attributes, so I have to make sure that the rows contain Entity Attributes, and the columns contain Domains. The matrix cell will show the “Domain” property of the Entity Attribute. When I click on <OK>, the matrix is created, and appears in the Browser

4a. browser

Now I can double-click the matrix to show the content

5. Matrix Content - initial

Three attributes already have the domain assigned – two of those are foreign keys for Building.Building Name, so I only had to set one of them, PowerDesigner set the other two automatically. Now, if I click inside one of the cells, such as the intersection of Elephant.Elephant Name and Animal Name, I can assign the domain to the attribute with one press of the keyboard – I use the Spacebar.

6. instructions

Now all I have to do is use the cursor keys to move around the matrix, and press the Spacebar every time I want to assign a Domain. It doesn’t take long to finish them all. Here’s the final matrix:

7. Matrix Content - final

Here’s the model:

8. final

The toolbar allows me to use the matrix in flexible ways, such as choosing which attributes or domains to include, hiding ’empty’ or populated rows, and exporting to Excel. Press <F1> to find out more.

9. Toolbar

Advertisements

Work smarter with #PowerDesigner – adding a sub-Requirements tab in the RQM

The PowerDesigner Requirements Model (RQM) is a powerful tool for managing requirements, or anything else you want to keep track of that has a hierarchical structure but doesn’t fit well with any of the other PowerDesigner Models.

For example, take a look at a sample RQM supplied by SAP, in the WebLibrary project, which is usually installed at “C:\Program Files\SAP\PowerDesigner 16\Examples\WebLibrary\WebLibrary.prj“. Here we can see a simple hierarchy of Requirements in the Browser:01 WebLibrary RQM - browser

The same hierarchy is also visible in a Requirements Document view, like this one:02 WebLibrary RQM - requirements document view

However, when I’m editing a Requirement via the properties dialogue, I can’t see a list of sub-Requirements. That means I have to use the Browser or a Requirements Document View to work on the sub-Requirements. Most of the time, I’d like to work in a spreadsheet-like view, the same as in any other type of model in PowerDesigner, like this list of Columns in a Table:

List of Columns

I can’t access a list of Requirements via the Model menu, so it doesn’t look like I can use my favourite editing approach – or can I?

The answer is that I can work on such a list – with a very simple model extension, I can add a ‘Sub-Requirements’ tab to the Requirement editor, like this one:

03 WebLibrary RQM - sub-reqs tab

Like any other list of objects in PowerDesigner, I can filter this list, edit single entries, edit multiple entries, change the sequence, and create new entries.

So, how is this possible? Simple, I exposed the existing collection of sub-Requirements on the new tab, by creating a new Form in a model extension.

04 Model Extension

This isn’t the place for detailed instructions for creating an extension, so I’ll limit this to a few pointers for you:

  • “Requirement” is a Metaclass
  • You can call the Form anything you like
  • “Requirements” is a Collection, not an Attribute

There are probably other areas in PowerDesigner where this technique is useful, let me know if you find one.

Work smarter with #PowerDesigner – How to display a ‘Relationships’ tab for entities

Data modelling tools vary in the kinds of dependencies that you can create between objects. For example, they all recognise that entities in a Conceptual or Logical Data Model (CDM or LDM) can participate in Relationships – when you view or edit the properties of an entity most (perhaps all) tools will show you a list of relationships as part of the dialogue. They might also show you other dependencies, such as a list of diagrams the entity appears on, a list of related tables in Physical Data Models, or some Data Lineage or other mappings. With most tools, that’s the limit. PowerDesigner goes beyond these basic modelling and development connections, allowing you to create several other different types of dependencies:

  • shortcuts – the entity is used in another model, but cannot be amended
  • replications – the entity is used in another model, and can be amended, subject to limitations
  • traceability links – the entity can be connected to virtually anything else in any type of model, if it makes sense to you
  • related diagrams – the entity can be connected to a diagram in any type of model, if it makes sense to you
  • extended collections – using a model extension, you can create your own links between entities and other objects

One side-effect of this power and flexibility is the impact on the entity properties dialogue – most of these dependencies are all shown on the same tab. In the example below, the Contribution entity participates in four relationships, listed on a sub-tab; I can see that the entity also appears on at least one Diagram (indicated by the presence of the Diagrams sub-tab). There could be more tabs, if the entity has other types of dependencies.

dependencies

If you’re used to other data modelling tools, you might prefer to see relationships listed in a tab of their own. Well, with a little work, you can add that tab for yourself; you just need a simple model extension.

You create or use Model Extensions to change the way that PowerDesigner works, usually by adding additional metadata, or additional features such as imports, exports, and new object properties. In this example, we’re not adding a new feature, merely exposing some metadata – the Relationships collection – that already exists.

You may already have one or more extensions attached to your model, such as the Excel Import extension, but I’ll assume that you don’t.

  • On the Model menu, select Extensions
  • Create a new entry in the list – just type the name – then click on OKadd extension
  • The new extension will appear in the Browser

extension in Browser

  • Double-click the extension to open it, then right-click Profile, and add the Entity metaclass to the extension

 

  • Add a new Form to the metaclass, call it Relationships

add form

  • Add the Relationships collection to the Form, then close the extension editor
  • Here’s the new tab

the new tab

It’s possible to add more than one collection to a Form, plus lots of other things; I’ll cover these in future tips.

Work smarter with #PowerDesigner – How to display ‘missing’ toolbars

Like many other modelling tools, PowerDesigner provides tools to help you control the layout of the symbols on your diagrams. For example, I would probably want to align these three sub-type entities in my diagram using the top edge:

misaligned entities

By the way, these are not standard entity symbols, I’ve customised the display to show the name of the super-type entity, and the Generate property. I’ll probably show you how to display these properties in another blog post.

The alignment commands are available from the Symbol menu. All I need to do is select the three symbols, then click on the Align Top tool on the menu:

Symbol menu

I prefer to use the toolbar to do things like this, probably because it needs fewer clicks. Unfortunately, the required toolbar (the ‘Layout’ toolbar) isn’t always visible, it’s often hidden. Exactly how well hidden it is depends on your version of PowerDesigner. I’ll start with the current situation, which probably applies to most PowerDesigner users.

the Layout Toolbar

Here are the toolbars you’re most likely to see when you start PowerDesigner (they’re usually in a long strip – I’ve moved them around for this image).

standard toolbars

The layout toolbar is not visible, here’s how to change that:

  • right-click on a blank area to the right of the visible toolbars
  • choose a toolbar to show or hide

choose toolbar

That’s it, nice and simple.

Some of you will be saying to yourselves, “Hang on, the toolbar I’m looking for isn’t listed!”. That’ll be because you’re using an older version of PowerDesigner than the one I’m using today. In PowerDesigner 16.1, Sybase decided to make several toolbars optional. By default, the Layout, Check, Format, Window and Repository toolbars were not visible; they did not appear in the list of available toolbars. You needed to use one of the customisation features of PowerDesigner to enable the missing toolbars.

On the Tools menu, select Customize Menus and Tools, select the Toolbars tab, click on the word Toolbars, then click on Add Command and select the toolbar you want to use. Repeat this action for each toolbar you want to enable.

#PowerDesigner Tips and tricks – showing both the Name and the Code on a symbol

I answer a lot of questions on the SAP Community Network; a couple of years ago, a user wanted to know how to display the object codes on her diagrams instead of the object names. In PowerDesigner every object has both a Code and a Name – the Name is what it’s known as inside PowerDesigner, and the Code is what it’s known as outside PowerDesigner. In a Physical Data Model, for example, the Name of a table might be “Customer Account”, and the Code might be “CUST_ACCT” – in this case the table name is also the name of the Logical Data Model entity that the table was generated from. PowerDesigner has derived the Code from the model’s naming standards.

By default, PowerDesigner diagrams (and the Browser window) always show the object names; for a Physical Data Model (PDM), this could include:

  • Tables and Columns
  • Indexes
  • Views and View Columns
  • Dimensions
  • Facts

etc.

For example, here’s a sample PDM diagram showing the object Names, and the corresponding list of objects in the Browser:

The advantage of this view from my perspective, is that the names on show are descriptive, unencumbered by any restrictions imposed by the DBMS or local naming standards – I know what every column name means. The name of the bottom reference in the diagram was copied across from the LDM, the two called “Inheritance_1” were generated from an inheritance (super/sub-type) hierarchy.

The disadvantage of this view is that it doesn’t show the names used for the objects in the database. Those names are actally there, in the object definitions, in the Code property:

Column Name and Code

I’m sure that the vast majority of people who work with Physical Data Models would prefer to show the object codes on the Diagram, like this:

PDM - showing Codes

It’s really easy to change the view from Names to Codes; exactly how you do it depends on your version of PowerDesigner. The original method is by selecting ‘Code’ in the Naming Conventions in Model Options:

Showing codes - model options

The setting applies to every diagram in the model, and also affects the Browser content.

PDM - showing Codes - browser

In PowerDesigner 16.6, the setting was moved to the Display Preferences for the Diagram, though it still affects the whole model, including the Browser. Also, a new tool was introduced on the Standard toolbar  – Showing codes - toolbar – now you can toggle between displaying names or codes with a single click.

If you want to show both Names and Codes on diagrams, you can do this for individual diagrams via Display Preferences. The ability to customise the content of symbols is one of the features that helps PowerDesigner to stand out in comparison to ERwin Data Modeller and ER/Studio Data Architect. For example, here’s a table symbol that focuses on the table and column codes, but also shows the names for those objects:

Table with Name and Code

The Code is not available for display by default, so you’ll have to use the Advanced features to make it available. Right-click the diagram background, select Display Preferences, and select ‘Table’ on the left.

Display Prefs - default for Tables

Here’s where you choose what to display on Tables, based on the properties that SAP have made available. You’ll see that you can choose ‘Name’ or ‘Owner and Name’, but not ‘Code’. All is not lost, however, as we can make other properties available to display – just click on the Advanced button at the bottom right.

Now you can select what you want the user to be able to display on table symbols. You can move things around, add and remove things, and change the way they’re organised. Here’s the default view:

customise content

In this case, you want to make the Code visible. To do this, click on the ‘Add Attribute’ button, select ‘Code’ from the list of Attributes, and press <OK>. You will see more attributes than my clip shows, I’ve cropped the image.

my customisation - 3

Here’s my customised view, in which I’ve removed and added content, and amended the layout – notice the prefix and suffix for the Name:

my customisation - 1

I’ve changed the displayed content (a.k.a. ‘List columns’) for the Columns in each Table, making sure that it shows the code and then the name of each column:

my customisation - 2

I won’t show all the steps needed to reproduce my table symbol, due to lack of space; besides, I have to let you work out something for yourself, I can’t take away all the challenge for you.
You can apply these settings to other diagrams as well, just click on the ‘Apply To’ button in Display Preferences. Beware, this will apply all the current Display Preferences to the selected diagrams.

One last point – you can change the content of individual symbols – just right-click the symbol, slect ‘Format’, then click on the ‘Content’ tab:symbol format

In this version of my diagram, every table symbol has different content:

customised diagram

#PowerDesigner Tips and tricks – a brain dump from years of experience starts today

Over the years that I’ve been using PowerDesigner, I’ve stashed away a large number of useful nuggets of information, most of which I wish could have been downloaded into my brain when I worked on that first Conceptual Data Model (this was before Sybase added the Logical Data Model to the tool). Anybody who knows me well will not be surpised at all when I say that I’m using a PowerDesigner  Requirements Model to document my tips. Here’s a snippet:

RQM - snippet of Tips

Watch this space for a steady trickle of tips and tricks for PowerDesigner users.

I might sneak in a few for ERwin or ER/Studio as well.

%d bloggers like this: