One of the key IT systems for any insurance broker will be the Quotations system, where they record details of all of the insurance companies they represent, the types of risk they might cover, all their customers, all the risks the customers would like to have covered, all the possible questions they could ask them to assess risk, all the answers they received, and the resulting quotes. In a commercial insurance broker, those quotations could be very large and complicated.
About 15 years ago, one such insurance broker’s quotations database had been designed to handle any product, any type of client, and any risk they might want to cover, without requiring any structural changes to the database or much change to the user interface. It was what we call a ‘metadata-driven’ database.
This was good for the business because they focused their attention on defining their requirements, and it was good for some of the developers because all they had to do was turn those requirements into data to store in tables – there were a lot of code tables, “question” tables, and “answer” tables. This knowledge was good for job retention.
However, the database design was not good for anybody who had to get data out of the database, perhaps to print out a quotation – to extract the right data you had to look up all sorts of code values to find the right words to use for labels, and the right data to extract.
So, they decided to extract the data from that database into another database that would make it easier for someone to look at or report on the data. Their data modelling tool was PowerDesigner (then owned by Sybase). For the nerds among you it was version 11 – SAP have recently issued version 16.7; under the numbering system Sybase used we’d probably be up to 26 or so.
Modelling and building the new database was not the biggest concern they had, their biggest headache was describing how to convert the quotations data into the new format in such a way that the developers could build it and the testers could test it. They also need to be able to migrate some of that data to their Policy Management system, and develop messages for their messaging infrastructure but I won’t talk about that, this tale is long enough already.
One day, the project team were surprised to see the head of the company’s Information Architecture team, who most of them knew at least by sight, come into their office and sit down at a PC in the corner. He sat there for two weeks beavering away on, well, something. Nobody would tell the project team what he was doing, so they assumed it was something hush-hush that needed to be done away from the rest of his team. All very mysterious 😊.
At the end of the two weeks, the outcome of his sweat and toil was revealed – it was an Access database. Please, don’t groan, especially if you can guess what’s coming next.
The Access database was, admittedly, an amazing thing – it was designed to hold the Physical Data Model of the Quotations database, as well as the Conceptual Data Model representing the business data, the Physical Data Model for the new database, and the links between those models. It could import the data models initially, but all modelling changes had to be entered manually. The database generated the specifications to tell the developers the rules for moving the data between the two databases.
Applying the changes was a bit tedious, as the team had to make the model changes in PowerDesigner first, then make them again in the Access database, and there was nothing visual about that database, no ERDs to look at, just lots of metadata. The modelling work was slowed down considerably.
I can hear your thoughts already, you’re wondering “What’s the point of this long ramble, is it another dig at Access databases?”.
I’ll leave you to figure that one for yourself, just let me tell you one interesting fact.
PowerDesigner version 11 already supported everything the Access database did – nobody had investigated the capabilities of their existing tool.
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.
If you want to improve your productivity even more (and who doesn’t want to do that?), a more comprehensive model extension will be available soon for the CDM and LDM. This will include the “Ultimate Parents” capabilities, plus features to help you with
diagram content and page layout, including
set page size and orientation
add all possible objects
add things linked to an entity
add all inheritance links for an inheritance
removing Packages cleanly
showing and hiding foreign key or inherited attributes
The first online PowerDesigner training course was released recently, covering the Excel Import Wizard that is supplied with PowerDesigner. This allows you to import practically anything from Excel into any PowerDesigner model without writing a single line of code – if you want to import content into a type of model object or property that does not exist yet, it can even define them for you.
A Canadian customer was kind enough to say that the course easily justifies the cost:
“I found it extremely useful and became aware of a lot more functionality that I was not aware of around how Excel can be utilised within PowerDesigner. This will apply directly to the work that we are doing as it will allow us to get stakeholders to populate Excel templates that our data modeling team can then develop into actual PowerDesigner data models.“
In my first post and second post on the topic of Entity Inheritance hierarchies I showed ways in which the customisation features of PowerDesigner are useful when dealing with them.
If managing complex hierarchies is one of your modelling concerns (for example, if you’re using the FIBO CDM or LDM), help is at hand. A model extension is available from Metadata Matters that provides a set of useful new properties (known in PowerDesigner as Extended Attributes) to help you manage your hierarchies, as well as the menu options to ensure that they stay up to date.
Here’s part of an inheritance hierarchy in the FIBO Conceptual Data Model (thanks to Jurgen Ziemer for allowing me to use the model for illustration). Let’s focus on the entity Deposit Account:
The Deposit Account entity in context
The model extension provides a new Parentage tab that provides information about how an entity fits into inheritance hierarchies. Deposit Account has two parents and two grandparents – notice that one of the parents (and the hierarchy above it) are not visible on my diagram:
Presenting information about the parentage of Deposit Account in a custom form
Most of the information on this form is up-to-date and reliable, because it’s computed every time it’s viewed; only the Ultimate Parents property has to be manually refreshed (because of the potential processing overhead from automatically refreshing it every time it is displayed, which could be noticeable in a model with many entities and many inheritance levels).
Everything on this form (apart from the list of parent entities) is available in a List Report. Here’s a fragment of a List Report that has been filtered to only include entities whose name ends with “Account”:
The extension provides menu options to:
refresh a single entity
refresh an entity and all its dependent entities
refresh every parent and child entity in the model
in the FIBO CDM it takes about 30 seconds to update this information for over 1000 entities and 406 inheritances
For example, the contextual menu for the model now includes an option to refresh the list of Ultimate Parent entities for every parent and child entity in the model
You can access this menu by right-clicking the model in the Browser or right-clicking a diagram background.
The first online PowerDesigner training course is available from Metadata Matters. The course is called Using the Excel Import Wizard. The course is based on a workshop I ran at a conference a few years ago – it’s about the Excel Import wizard, a feature that I know a lot of people use, and some people have problems with.
Black Friday offer
Get FREE access to the full course material, a glossary of terms, and all the downloadable files for a month – book your place now – contact firstname.lastname@example.org.
If you would like to see a preview of the content, a cut-down version of the course is available for free –