Using a PowerDesigner model to help me build my PowerDesigner scripts

[GeorgeMcGeachie]

Over the years, clients have asked me to write a fair number of scripts for them, automating various metadata and modelling tasks in both Idera ER/Studio and SAP PowerDesigner. As you might expect, some of these scripts can get quite complicated, with a hierarchy of functions and subroutines that the scripts rely on. I suspect that a few of these functions and subroutines were still included in scripts after I had decided they weren’t actually needed. Over time, I’ve built up a folder or three of useful scripts and code fragments, which it’s tricky to keep track of.
As someone with a lifetime of data and process modelling behind me, I’ve been looking for a simple way to visualise the calling hierarchy in a script, with no luck. Before you talk about Git Lab or Git Hub (just in case they can do that for me), please remember that I’m a visual modeller, not a seasoned programmer.

After I returned to consulting this year, I got my first scripting request from a new client – they wanted to import source-to-target mappings into a PowerDesigner Physical Data Model from Excel. This time, I decided to be proactive, and manage the calling hierarchy from the very beginning, using a PowerDesigner model.

For those of you who aren’t PowerDesigner aficionados, I’ll explain. The extension and customisation features of PowerDesigner enable users to change the way the tool handles models, adding extra types of objects, links between objects, additional generation capabilities (such as generating JSON from a Logical Data Model), model validation checks, and other such functions. I did think I could customise a UML model to do what I want, but then the model would have a lot of extra capabilities I wouldn’t be interested in using, plus (and this might be the real reason ?) I wouldn’t be able to sell on this capability to people who are only licenced for data modelling in PowerDesigner.

I decided to use a Free Model instead, which is available to every PowerDesigner user. The Free Model is a context-free modelling environment – you need to define the types of object, the properties you want them to have (other than the standard ones), and the connections you want to make between them.

I have simple requirements that I needed to build into the Free Model:

• Defining distinct chunks of code (Scripts, Functions, Subroutines, and useful Code Fragments)
  
  
  
  • I can edit directly in an object, using a custom property formatted as VBScript
  
  
  
  


• The calling hierarchy


• Diagrams showing the logic I need to build – a simplified form of flow chart, with Events and Actions


• Define the comments to be included in the code – to be included when the code is generated


• Simple metadata, such as “parameters”, “progress” and “version”


• Generating a complete script, which includes the Functions and Subroutines it relies on, and no others. This must include:
  
  
  
  • boiler-plate text, such as the version number for the code
  
  
  • the comments
  
  
  • standard ‘begin’ and ‘end’ comments, to make the script easier to read
  
  
  • Tasks, linked to affected code
  
  
  
  

After completing my customisations, I can create nine new types of object and one new type of link, indicated below by the orange circles – everything else is standard.



So, what’s it like using this new approach? I must say, it’s much easier to keep track of what I’m doing – I can edit a given chunk of code, then save the full script to try it out. Here’s one of my functions – the Comment and Script properties are combined with the calling hierarchy to generate a complete script file, which can be seen on the Preview tab.



Here’s a sample calling hierarchy – the connections are used to construct lists of the functions and subroutines that each chunk of code relies on, and these lists are used to generate the complete code (for example, if I generate ‘processExcelMappings’ the code will include everything else on the diagram).



Finally, for the geeky people who wonder how I did it, here’s a peek at the PowerDesigner Model Extension where I defined all these extra features: