blog – Metadata Matters

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

February 29, 2020George McGeachieLeave a comment

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, Replications, Traceability Links, Related Diagrams, Extended Collections (extensions to the out-of-the-box capabilities)

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 sub-tabs, if the entity has other types of dependencies.

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 that already exists – the Relationships collection.

Here’s what you need to do:

On the Model menu, select Extensions
Create a new entry in the list – just type the name – then click on OK

The new extension will appear in the 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 the Relationships collection to the Form, then close the extension editor

Here’s the new tab

I’ve used this technique in other places as well, such as adding a Sub-Requirements tab to Requirements in a Requirements Model.

The key features of SAP #PowerDesigner – last year’s webinar – here’s what I covered (and some more), with screen shots

June 10, 2019September 3, 2020George McGeachieLeave a comment

If you attended my DAMA UK webinar last year, thank you. If you didn’t, where were you? <grin>

The webinar description was :

“To be successful at Data Architecture, organisations have always needed to understand what data they have, why they have it, how they use it, where it is, how it got there, and what happened to it along the way – forming a ‘pipeline’ of information. Very often, this pipeline is managed and documented using a mish-mash of tools and methodologies, often resulting in conflicting and contradictory information, making it difficult for Data Governance to be effective.
In this webcast, George McGeachie will demonstrate the key features of SAP PowerDesigner that support the pipeline. A set of requirements and business rules, and an existing Conceptual Data Model, will be used to create Logical and Physical Data Models. This will include the generation of JSON structures from both Logical and Physical Data Models.
Some of what you will see is not out-of-the-box – it has been built using PowerDesigner’s powerful customisation features.”

The webinar was recorded – take a look – https://www.brighttalk.com/webcast/12405/311023

Well, I think I demonstrated that pipeline okay, though I did make one slight mess-up on the JSON PDM (see below). That’s the way it goes with one-off demos, the old memory plays you tricks. Here’s a quick reminder of the steps I followed

Import Requirements from a Mind Map – Via OPML and a Word document – then create a Conceptual Data Model (CDM) from the Requirements. The OPML import was built by me using the PowerDesigner XML Import feature, and the Word import is part of the product.

Merge this CDM into the existing Hotel Loyalty CDM

Normally I would be more selective about the changes that I carry over from one model to another, today I carried over every change that didn’t delete something in the target model.

My objective was to show that the links back to the original requirements were still maintained. Here is the link from one of the Hotel Loyalty CDM Entities, back to the original requirement:

I also showed the additional Entity properties that I added via a model extension:

This included a link to a new type of object I’ve added to the model – the Data Steward. Here we can see the list of entities that one of the Data stewards is responsible for:I didn’t show you this, but the model also contains a Dependency Matrix, which can be used to edit the links between entities and data stewards. It can also be used for simple validation, such as showing only those entities that do not yet have a Data Steward.

There is also a matrix that allows you to edit the links between Data Items and Domains.

Generate LDM from the CDM

I generated a Logical Data Model (LDM) from the CDM, making sure not to run a model check, as errors would prevent the new model being generated. I didn’t show you the model options, where the option to allow many-to-many relationships was disabled.

There is a many-to-many relationship in the CDM, between Facility and Reservation – I’ve called this “Facility Reservation”:

In the LDM, an entity was automatically created to replace the relationship:

It’s worth mentioning here that I can do this in the CDM or LDM at any time – any relationship can be converted into an entity, preserving the overall optionalities and cardinalities.

The LDM has a couple of model extensions attached – one of them generates JSON and CSV, and the other generates facts about the model as text. Both involved writing some Generation Template Language (GTL), which allows me to mix boilerplate text and information from the model, in much the same way as when we generate Data Definition Language (as SQL) from a Physical Data Model.

Here’s a JSON sample:

Some CSV:

An entity summary:

Generate JSON PDM from LDM, then generate SQL Server PDM from LDM

I could have used a subset of the LDM as my source for generating the two PDMs, but I chose to generate the whole model.

In the LDM, I could now see (though I didn’t show this at the time) the links from the LDM to the objects in the PDM. Here’s an example what happened to one of the entities:

I didn’t look at the two PDMs in any detail, as the purpose of the webinar was to show the pipeline of artefacts and models. I did look at the Preview tab for one of the tables in the JSON model, which was where I made my mistake. A JSON model represents a complete JSON structure, possibly a message, so I should have looked at the Preview tab on the model. Here it is:

Use the Mapping editor to create mappings from the JSON model to the SQL Server model

In the interest of saving time, I allowed PowerDesigner to populate the default mappings, which mapped everything in each model to the equivalent in the other model.

Load the JSON-SQL mappings into a Data Movement Model

The Data Movement Model already contains some detailed data movements for MDM and a Data Warehouse:

I used the built-in wizard to import the mappings from the SQL Server PDM, and create a single transformation task in the model. This links back to the tables and columns in the two PDMs, and allows me to describe the transformation actions in detail:

I also showed an alternative view of the Data Warehouse Data Architecture, in an Enterprise Architecture Model, which is linked to the Data Movement Model. As an architecture model, it doesn’t contain the level of detail that the Data Movement Model does; what it adds is the ability to connect this data architecture to business functions, servers, locations, and other enterprise architecture artefacts. The Enterprise Architecture model also links to the Physical Data Models, ensuring traceability at the model level.

Finally, I created a Project, and added all the models to it, so we can visualise the links between models. each symbol on this diagram represents one of the models. I can open a model by double-clicking the symbol.

One thing left, which I’m sure I showed somewhere near the end. I showed the Impact and Lineage analysis for one of the tables in the JSON PDM. This followed all the links from object to object, to the entity it was generated from, to the source-to-target mappings, to the mappings in the Data Movement Model, and kept on going. For example, we can see that the table was generated from an entity in the LDM, and the LDM entity was generated from a relationship in the CDM (remember the many-to-many relationship we saw earlier), which is connected to the Facility and Reservation entities in the CDM. I haven’t expanded far enough in the diagram above to show the link to one of the requirements we started with, but it’s there.

I clicked on the ‘Generate Diagram’ button, which created an Impact Analysis Model from the above analysis. As this is a model, I could save it for future reference, and refresh the contents in the future. In this diagram, changes to the objects on the left could affect the table. each of the symbols is a link to the actual object, so I can access the CDM and LDM entities, the Data Transformation, and the related tables.

Again, thanks for attending, or reading this far if you didn’t attend the webinar. Please use the ‘Contact me’ option on the blog to send me a message, or find me on Twitter , Xing or Linked In.

Thanks to every one who joined my #PowerDesigner webinar today – here’s what I covered (and some more), with screen shots

March 22, 2018September 3, 2020George McGeachieLeave a comment

If you attended my DAMA UK webinar today, thank you. If you didn’t, where were you? <grin>

The webinar description was :

The webinar was recorded – take a look – https://www.brighttalk.com/webcast/12405/311023

Import Requirements from a Mind Map – Via OPML and a Word document – then create a Conceptual Data Model (CDM) from the Requirements. The OPML import was built by me using the PowerDesigner XML Import feature, and the Word import is part of the product.

Merge this CDM into the existing Hotel Loyalty CDM

Normally I would be more selective about the changes that I carry over from one model to another, today I carried over every change that didn’t delete something in the target model.

My objective was to show that the links back to the original requirements were still maintained. Here is the link from one the Hotel Loyalty CDM Entities, back to the original requirement:

I also showed the additional Entity properties that I added via a model extension:

This included a link to a new type of object I’d added to the model – the Data Steward. Here we can see the list of entities that one of the Data stewards is responsible for:I didn’t show you this, but the model also contains a Dependency Matrix, which can be used to edit the links between entities and data stewards. It can also be used for simple validation, such as showing only those entities that do not yet have a Data Steward.

There is also a matrix that allows you to edit the links between Data Items and Domains.

Generate LDM from the CDM

There is a many-to-many relationship in the CDM, between Facility and Reservation – I’ve called this “Facility Reservation”:

In the LDM, an entity was automatically created to replace the relationship:

It’s worth mentioning here that I can do this at any time – any relationship can be converted into an entity, preserving the overall optionalities and cardinalities.

The LDM has a couple of model extensions attached – one of them generates JSON and CSV, and the other generates facts about the model as text. Both involved writing some Generation Template Language, which allows me to mix boilerplate text and information from the model, in much the same way as when we generate Data Definition Language (as SQL) from a Physical Data Model.

Here’s a JSON sample:

Some CSV:

An entity summary:

Generate JSON PDM from LDM, then generate SQL Server PDM from LDM

I could have used a subset of the LDM as my source for generating the two PDMs, but I chose to generate the whole model.

In the LDM, I could now see (though I didn’t show this at the time) the links from the LDM to the objects in the PDM. Here’s an example what happened to one of the entities:

Use the Mapping editor to create mappings from the JSON model to the SQL Server model

In the interest of saving time, I allowed PowerDesigner to populate the default mappings, which mapped everything in each model to the equivalent in the other model.

Load the JSON-SQL mappings into a Data Movement Model

The Data Movement Model already contains some detailed data movements for MDM and a Data Warehouse:

Work smarter with #PowerDesigner – Choosing your Conversion Table

February 2, 2018September 3, 2020George McGeachieLeave a comment

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:

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

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 FilesSAPPowerDesigner 16Resource FilesConversion 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.

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:

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

February 1, 2018September 3, 2020George McGeachie9 Comments

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:

As 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.

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:

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:

abbrevs I 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”,

and the result is

Here’s a made-up example for a whole 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 VBScript.

Sub %Method%(obj)
Dim candidate
candidate = obj.evaluatetemplatefor("newName","PDM-ProperCase")
 if not candidate = obj.Name then
   reportChange "Column", obj.Table.Name & "." & obj.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. 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.

.vbscript(%1%)
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
         		Else
            			myChar = myChar ' continue an acronym
         		End If
      		Else
        	 	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
.endvbscript

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