Subject Areas – an art form for data modellers and data governance? #PowerDesigner #datagovernance #ERStudio

(this article was originally published in 2011 – I’ve made a few minor amendments today, nothing to change the message)

Time spent using colours, font styles etc. can definitely increase both the clarity and usefulness of a model.  If that use of colours and styles can be automated, even better.  Previously, I’ve used macros in a data modelling tool to define styles for entity symbols that depend on the business owner, and font styles that vary according to when the data (master data in this case) is expected to be available.  I also colour-coded relationships to denote the business area responsible for managing them, which is not always obvious. This information was all held as properties on the entity and relationship, making the macro pretty straight forward.

You can also use colours and styles to highlight entities or tables affected by a given release or change request; again, this is possibly metadata that is available for a macro to query.

One great use of colour-coding and styles is to categorise entities on a diagram, using colours to denote the owning subject area.

I recently examined a data model where all the entities were categorised into a number of subject areas, and there was a separate ERD for each subject area.  Unfortunately, the model suffered from a complete lack of artwork.  There was no colour coding, so I couldn’t tell which subject area any of the entities belonged to (though of course I could guess some of them), so I couldn’t be sure which ERD I needed to look at to see the full context of a given entity.  I had to use the main model ERD to be sure that I was seeing the full picture for an entity; this showed all the attributes for every entity, and made no use of styles or colour whatsoever; it was a difficult diagram to work with.  Thankfully, it did fit onto a single sheet of A3 paper, as the model was quite small.  In this data model, the subject areas were virtually unusable and irrelevant, because they weren’t being communicated at all.

When creating subject areas, think carefully about why you need them, and how users of the model should interpret  them.  You could break the model down into data-related subject areas, functional areas, system of record, etc.  With a really large model, you may be able to justify having multiple sets of subject areas for different purposes.

Take the example of subject areas based upon a higher-level data model; let’s say that the higher-level model includes 60 concepts, including:

‘Abstract Geography’ includes 15 entities, including ‘Company Location’, ‘Country’, and ‘City’. ‘Exploration’ includes 20 entities, including ‘Well’ and ‘Field’.  Both ‘Well and ‘Field’ will have relationships to entities within ‘Abstract Geography’.  Here’s part of the complete LDM:

Note that the colours tell you which subject area ‘owns’ each entity, and also which subject area is responsible for populating relationships.  The diagrams were created in SAP PowerDesigner, which uses the triangle symbol on the relationship line to indicate a dependent entity. here’s the same diagram in Idera ER/Studio Data Architect

Subject Areas as art - ERStudio

Assume I have separate subject area ERDs for my concepts; where can I be sure of seeing all the relationships between the ‘Abstract Geography’ and ‘Exploration’ entities?  I would expect to see all of them on BOTH subject area ERDs.  It would even be possible to code a macro that tells you which entities should be on a given subject area ERD; again based on the metadata in the model.

Is any of this art?  No, but it is a combination of graphic design, common sense, and ergonomics, with a tasty dash of automation to make it more palatable.


“#PowerDesigner Fundamentals for Data Architects” at Data Modelling Zone EU 2018 #DMZone

Are you thinking of attending Data Modelling Zone in Düsseldorf in September 2018?

  • Are you an experienced data architect, new to SAP PowerDesigner?
  • Have you used SAP PowerDesigner in the past, and need bringing up to date?
  • Are you currently using SAP PowerDesigner, but never had any training?
  • Are you evaluating SAP PowerDesigner?

– then –

This session is for you!

Join the author of Data Modelling Made Simple with PowerDesigner, and learn the Fundamental techniques you need to get the best out of SAP PowerDesigner.

The program hasn’t been announced yet, so watch the conference web site for announcements.



#PowerDesigner adds support for Amazon #Redshift

Late last year, SAP issued Service Pack 5 for PowerDesigner 16.6. From the list of new features, it’s obvious that SAP are putting a lot of focus on the web-editing capabilities of the tool. In this service pack, there are changes that affect process modellers, enterprise architects, requirements modellers, and data architects. I shan’t list them all here – see the note below for details of how to access them yourself.

I’m going to focus on what matters to data architects – the new support for Amazon Redshift. SAP have done the usual excellent job of enhancing the underlying database support in the Physical Data Model (PDM) in order to handle this DBMS. For example, they’ve added a new type of object, “External Schema”, and extended the properties available for Tables, Columns, Views, and Users. The latter includes recognising Schemas as a Stereotype of User.

For example, here’s the new ‘general’ tab for a column – I’ve highlighted some of the new properties for you.

Redshift column

To find out more about these new features, follow these two simple steps:

·        Search for PowerDesigner stuff on –

·        click on the “New Features Summary” link


click here for more

Work smarter with #PowerDesigner – Choosing your Conversion Table

In yesterday’s blog post, I described how to convert CamelCase object Codes into ‘Proper Case’ object Names, using a combination of GTL and VBScript in a model extension. This took advantage of the built-in conversion routines, which enable us to convert abbreviations into plain language, such as replacing “acct” with “account”.

I didn’t show you how to tell PowerDesigner where to look for those abbreviations, so that’s what I’m going to do now. The secret lies with the Naming Conventions in the Model Options. There are three ways to access the Model Options:

  • near the bottom of the Tools menu
  • right-click the model in the Browser
  • right-click a blank area of a diagram

Click on the “Naming Conventions” section, then on the “Code to name” sub-tab, as shown below.  You need to do two things:

  1. Select “Enable conversions”
  2. Choose from the drop-down list of conversion tables – in the example below, I’ve chosen one of my CSV files

PDM model options - conversion table

The drop-down list of conversion tables will include entries from the following sources:

  • if you have a repository, one entry for ‘glossary terms’ (these are the Terms in the PowerDesigner Glossary)
  • CSV files that have been checked into the ‘Library’ folder in the repository
  • CSV files in the target folder(s). Click on the folder icon to the right of the drop-down to change the target folders – the default folder is “C:\Program Files\SAP\PowerDesigner 16\Resource Files\Conversion Tables”, which contains a single sample, called “stdnames.csv”, so you’ll probably want to add at least one more folder to the list.

You can edit your conversion table directly, without using Excel – just click on the ‘Edit Selected Conversion Table’ button.

edit selected conversion table

Each time you run the menu options I showed you yesterday, it will use the current conversion table. If, for example, you haven’t defined ‘BBC’ as an abbreviation, the code ‘BBCNews’ will be converted to ‘BBC News’. If you decide that ‘BBC’ should be converted to “British Broadcasting Corporation”, just add the following entry to your conversion table, and run the menu options again.

British Broadcasting Corporation BBC

Lastly, it’s worth pointing out that the Conversion table that you select on the Naming Conventions tab is used for every type of object, unless you select a different Conversion table in one of the object-specific sections. In this example, I’ve chosen a different Conversion table for Columns:

naming for columns

So, you could use different conversion tables for different types of object, if you want to.

Work smarter with #PowerDesigner – Converting Camel Case Codes to Mixed Case Names

Every object in PowerDesigner actually has two labels, which we refer to as the Name and the Code. The Name is the ‘business’ or ‘human’ name for an object, and the Code is a technical name for the object. The Naming Conventions allow you to automatically convert the ‘business’ names into the ‘technical’ codes, like this set of LDM attributes:

att names and codesAs well as changing the case, and replacing spaces with underscores, we can replace words and phrases with their abbreviations, using a CSV file or the PowerDesigner glossary as the source. Here are the same attributes, after changing the standard for codes to UpperCamelCase, and applying abbreviations from a CSV file.

att names and codes 2

That’s fine if you’re forward-engineering, creating technical artefacts from your models, but what if you’re reverse-engineering, and those technical names (the PowerDesigner codes) are your starting point?

When you reverse-engineer a database in PowerDesigner, the only names available are the technical names, so the PowerDesigner Names and Codes are the same. Synchronisation is automatically turned off, so you can manually edit names without accidentally changing the codes (the technical names). For example, here’s part of the model created by reverse-engineering the Demo database that gets installed with SAP SQL Anywhere 17:


SQL Any 17 Demo DB

The table and column names are all in UpperCamelCase. If I want to create a Logical data Model, I will need to convert them into a more human-friendly format. Out of the box, I can easily convert some standards – for example, it’s trivial to convert “SALES_REPRESENTATIVE” to “Sales Representative”. However, converting Camel Case names is not trivial. After reading a Sandhill blog entry about how to do this in ERwin yesterday, I decided to finally figure out how to do it in PowerDesigner – I’ve thought about it on and off, but never took the time to work it out. I didn’t want to do it outside PowerDesigner, as I wanted to make use of PowerDesigner’s standard naming conventions as much as possible, especially the ability to replace abbreviations with the real thing. For example, here’s part of a CSV file I was using today, which I’ll use in the next example:

abbrevsI also wanted to come up with a mechanism that was as easy to use as possible – the result is a simple model extension, that adds menu options to the model, to tables, and to columns, so you can reset the names of:

  • all tables and columns in the models
  • a selected table
  • all the columns in a selected table
  • a selected column

For example, I have a column called “BBCOrderLn”, and want to set the Name to “BBC Order Line” – “Ln” is the abbreviation for “Line” in my CSV file. With my model extension, I just right-click the column on the diagram or in the browser, and select the option “set Proper Name”,

column Proper

and the result is

column Proper after

Here’s a made-up example for a whole table:

Proper Table

How does it work? It’s based on a single model extension that contains a number of GTL templates – GTL is PowerDesigner’s Generation Template Language, which is great at turning metadata into text. One of the templates contains embedded VBScript, which is the part that does the real work. I shan’t bore you with the whole model extension here, I’ll just show you the part that converts a single column.

It includes a menu, which allows you to run the Method called “set Proper Name”, which contains a little bit of VBScriptxem proper column.

Sub %Method%(obj)
Dim candidate
candidate = obj.evaluatetemplatefor("newName","PDM-ProperCase")
 if not candidate = obj.Name then
   reportChange "Column", obj.Table.Name & "." &, obj.Table.Name & "." & candidate
   obj.Name = candidate ' need to change it
 end if
End Sub

The key part here is “evaluatetemplatefor“, which runs a shared GTL template called “newName”. Because it’s shared, I only have to define it once, and then I can use it wherever I like.proper shared templates This is a very simple template, containing a single line of GTL, which calls the standard template (.convert_code) that PowerDesigner uses to convert codes into names, changing the case and reversing abbreviations. Instead of supplying the object code to be converted, it passes the result of the other template, “ProperCase”

.convert_code(%ProperCase%," ")

“ProperCase” does the real work here, with some embedded VBscript. If you find any problems with this code, please let me know.

Dim obj : set obj = activeselection.item(0)
Dim myString : myString = obj.Code
Dim ProperCase

' converts a string into Proper Case, one character at a time
' the first character is always upper case
' if an existing character is already upper case, it is not converted
' if an existing character is a space, it is output as is
' ignore underscores - convert_code will deal with them
' acronyms are left intact
' multi-word conversions only made if they're separated by a space
Dim i
Dim prevSpaceInd ' was previous character a space?
Dim prevUpperInd ' was previous character upper case?
Dim nextChar ' the next character in the string 
Dim myStringLength ' the length of myString
myStringLength = len(myString)

Select Case myStringLength
 ' If there are 0 or 1 characters, just return the string.
 Case 0
   ProperCase = myString
 Case 1
   ProperCase = Ucase(myString)
 Case else 
   ' Start with the first character - this will always be upper case
   ProperCase = Ucase(Left(myString,1))
   prevUpperInd = true ' remember this for the next character

  ' Add the remaining characters
   Dim myChar
   For i = 2 To len(myString)
      myChar = Mid(myString,i,1)

     If myChar = " " then
         prevSpaceInd = True ' remember this for the next character
         myChar = " "
     ElseIf myChar = "_" then ' ignore
         myChar = myChar
         prevSpaceInd = True ' force script to act as if it was a space
     ElseIf myChar = Ucase(myChar) then
         ' the current character is upper case
         If prevSpaceInd then ' previous character was a space
            myChar = myChar
            prevSpaceInd = False
         ElseIf prevUpperInd then ' previous character was also Upper Case
            	nextChar = Mid(myString,i+1,1)
         	If i = myStringLength then ' this is the last character in the string
            		myChar = myChar ' don't insert a space
         	ElseIf nextChar = Lcase(nextChar) then ' next char is lower case
            		' If the next character is not upper case, 
            		' assume the current letter is beginning of new word
            			myChar = " " & myChar ' make this 1st letter of new word
            			myChar = myChar ' continue an acronym
         		End If
        	 	myChar = " " & myChar
      		End If
      		prevUpperInd = true ' remember this for the next character
	Else ' must be lower case or perhaps a number, leave it alone
		prevUpperInd = False
		myChar = myChar
	End If
       	ProperCase = ProperCase & myChar
    Next ' i
End Select
ScriptResult = ProperCase

Finally, you need to add a simple Global Script, which reports actions to the Output window:


Thanks to everybody who visited my blog in 2017 – you came from 108 different countries #PowerDesigner #datamodeling #datamodelling

Blog visitors 2017