ER/Studio conversion service for the FIB-DM Model is nearly ready #FIBO

The free version of the Financial Industry Business Data Model has been downloaded many times – it’s managed using the SAP PowerDesigner modelling tool, and users of other data modelling tools need to import the PowerDesigner Logical Data Model that is supplied. There are two concerns here:

  • FIB-DM is a very big model with some deep super/subtype hierarchies
  • even if your tool can import a PowerDesigner model, the chances are that the import doesn’t work perfectly

If your tool of choice is Idera ER/Studio Data Architect, you can use the built-in metadata import feature to import the PowerDesigner model, but I know that you won’t be happy with the result, so I’m building a new service to help you make the transition a happy one! In addition I’ll be providing some useful features for ER/Studio, similar to those in The CDM LDM Productivity model extension.

The left-hand side of the following image is the model created when you import the FIB-DM PowerDesigner model into ER/Studio Data Architect; the right-hand side is the model after it has been improved. Use the slider to see the difference!

You can see a couple of key differences here.

  • the PowerDesigner package hierarchy is flattened by the ER/Studio import, and all the package names have been changed. This is a big deal in a model with over 260 packages.
  • the fonts and line styles in the diagram need to be changed to make the diagram more readable. This is also a big deal, as there are over 260 diagrams.

There’s much more below the surface that needs to be fixed, such as:

  • the attachment names in the Data Dictionary
  • incorrect conversion of data types

I suspect that I have an advantage over most organisations that need an ER/Studio version of the FIB-DM model – I also have access to PowerDesigner. I can make changes to the FIB-DM model before importing it, such as renaming the packages using a bespoke model extension, then use a macro in ER/Studio to rename the resulting submodels. I can export details of the Attributes from PowerDesigner, and use that information to correct the data types in ER/Studio.

If you’re interested in finding out more about this new conversion service, please go to the contact page and get in touch.

An informal introduction to PowerDesigner Web – 30th of September, 2020

I’m keeping the numbers low for this session, partly to keep it informal, and partly due to platform limitations. I’m pleased to say that it’s now fully booked. I’m sure I’ll repeat it sometime in the near future.

PowerDesigner Web has been around for some time now, exposing the content of the PowerDesigner repository to a wider audience and giving users the ability to create and edit some types of model. With the introduction of PowerDesigner 16.7, the types of model that can be created and edited has grown, and has started to get George interested.

In this session, George  will take you through the background to PD Web and show you some of the interesting features he’s found, focusing mainly on the things you can do in PD Web that you can’t do in the PowerDesigner client application:

  • Reverse-engineering JSON documents and Schemas
  • Productivity aids when editing Conceptual and Physical Data Models
  • Structured data types in the Conceptual Data Model
  • Exporting a diagram to PowerPoint as symbols that can be individually edited
  • A new diagram in the Requirements Model
  • and more

Watch this space for news of the ER/Studio conversion service for the FIB-DM Model #FIBO

The free version of the Financial Industry Business Data Model has been downloaded many times – it’s managed using the SAP PowerDesigner modelling tool, and users of other data modelling tools need to import the PowerDesigner Logical Data Model that is supplied. There are two concerns here:

  • it’ s a very big model with some deep super/subtype hierarchies
  • even if your tool can import a PowerDesigner model, the chances are that the import doesn’t work perfectly

If your tool of choice is Idera ER/Studio Data Architect, you may be able to use the built-in metadata import feature to import the PowerDesigner model, but I know (because I’ve tried it) that you won’t be happy with the result, so I’m building a new service to help you make the transition a happy one! In addition I’ll be providing some useful features for ER/Studio, similar to those in The CDM LDM Productivity model extension.

Adding existing relationships to a diagram in #PowerDesigner

This was one of my first blog posts and has always proved popular, so I’ve brought it up to date.

I remember the first time I came across SAP PowerDesigner. It was version 11, re-badged by Quest Software as QDesigner. This was before Quest started selling their own modelling tool.

I had multiple ERDs in my Conceptual Data Model, and I wanted to include existing relationships on a new ERD I was putting together. I was used to a command in Oracle Designer that would add all missing relationships (including inheritances) to the diagram – every existing relationship that connects two entities that already appear on the diagram would be included.

After a search, I found the equivalent command in PowerDesigner (there’s a ‘complete links’ tool on the Diagram toolbar – see below).

However, I often just wanted to include one or two particular relationships on the diagram, so I tried dragging them onto the diagram from the browser. I was really annoyed at the result, and took a while to understand what was going on. I don’t think it’s a problem with PowerDesigner really, but a symptom of the fact that it doesn’t always work in the same way as other tools.

The confusion was later evidenced by a thread on the old Info Advisors PowerDesigner group – “Even when dragging a relationship / reference from the browser into a diagram where the participating entities have already been included as symbols, PD creates new symbols for them, rather than connect the relationship’s symbol to the existing entities’ symbols. One has to manually delete the extra ones; a nuisance...”.

When you drag a relationship onto a diagram from the browser, PowerDesigner will automatically include the two entity symbols as well, even if the entities already appear on the diagram. These new entity symbols are called graphical synonyms, and can actually be very useful. If you do create unwanted graphical synonyms, all you need to do is delete the new entity symbols, and the relationship symbol will snap to the original entity symbols. This could be a real pain in the neck to do if you drag a lot of relationships at the same time.

Here’s how to prevent this happening

Prevention is better than cure, so remember this simple trick

  • hold down the <Shift> key when you drag the relationship(s) from the browser to the diagram – the relationship will be drawn between the existing entity symbols and no new graphical synonyms will be added.

Which relationship?

One problem with dragging relationships from the browser is ‘How do I know which relationship is which?‘ Here’s the list of relationships in one of the supplied demo models:

How well do you know your model? In this model, the relationship names are short, and we have some duplicate names as well. If you want to, you can make it more difficult to create duplicate names, using the ‘unique code’ setting in the model options.  As long as you synchronise your relationship names and codes, you won’t get duplicates.

In anything but the simplest model, or one you know exceptionally well, that still won’t help you know which relationship you need to drag onto your diagram. The names are just too short.

In this model, the two ‘Belongs to’ relationships could be referred to as “Employee Belongs to Division” and “Task Belongs to Project“. Now, you could change the relationship names to include one or both of the entity names which would certainly make the browser list easier to use:

That gives you a re-work overhead you don’t really want – if (sorry, when) you need to move a relationship end from one entity to another, you’ll have to remember to rename the relationship. By the way, don’t worry about displaying the extra-long name on diagrams, because you can choose to display one or both role names instead of the relationship name (you DO use role names don’t you?), like this:

I suggest you come up with a standard for relationship names so you can make sense of the browser – meaningful relationship names may also be useful in reports.

Alternatively, you could customise PowerDesigner by adding an Event Handler or a custom model check to derive relationship names, such as “Division Employs Employee” or “Project Requires Task”. If you do create such an Event Handler, remember to check for duplicate names.

I’ve actually done this so you don’t have to – the CDM LDM Productivity model extension applies a naming standard based on the entity names, one of the role names, and the relationship type.

Is there a better way to choose what to include?

If I want to include missing relationships in an ERD, there are better ways. If I want to see a list of my relationships, there’s a better way of doing that as well – look further down this blog post for that one.

OK, let’s get back to adding missing relationships. Here’s the first easy way – select the Show / Hide Symbols command on the Symbol menu, and choose the relationships you want to include:

Notice that one of the relationships is listed as ‘Hidden’. It was previously displayed on the ERD, but I decided to hide it. I’ve selected it for inclusion, and it will re-appear in the diagram in the same position and style as it appeared before (assuming the entity symbols haven’t moved, of course). Including missing relationships this way will not result in graphical synonyms being added (unless, perhaps – I haven’t tested this – they were previously attached to graphical synonyms that are still present).

Notice also that one relationship is listed as ‘None’, meaning that the symbol just isn’t known to the diagram, hidden or otherwise. perhaps it used to be there and I chose to delete the symbol.

Don’t worry if your Show / Hide Symbols dialogue doesn’t look like the one above. I tailored mine using the customisation tool , so you can replicate yourself.

The second easy way to add missing relationships is the “Add missing link” tool Missing Links tool  on the Diagram Toolbar. If you have nothing selected, this will add every link symbol that is missing from the diagram, which may give you a few surprises. If you want to include all relationships for, say, the entity Team, here’s what you do:

  • select the symbol for the “Team” entity
  • click on the “Add missing link” tool

PowerDesigner presents a choice for you:

Missing Links options
  • select the first options to add all missing links for the selected entity. This will include relationships and traceability links connecting the entity other symbols that er already on the diagram.

Now for my final point, listing relationships without using the browser. Which would you rather work with, the list of names in the browser, or a list that you have control over? In the list of relationships shown below (via Relationships on the Model menu), I can choose the properties I want to see, the order in which to show them, sort the listing, and filter according to property contents:

Again, this is a customised list, so it may take you a few minutes to achieve, but the results will stay with you – I carried out this customisation several months ago. I can click on the column headings to sort the list if I want to. I can also edit any of the properties that are displayed, as long as they aren’t shown in grey. By adding new rows to the list, I can create new relationships, which will automatically be added to my current diagram.

By the way, the ‘S’ column tells me whether or not the relationship has a symbol on a diagram – the first one on the list does not have a symbol.

If you want to include this list in reports, or take snapshots of the content from time to time, you can re-create it in a List Report.

There is another way

The CDM LDM Productivity model extension includes a ‘completeness’ feature, which allows you to select an entity on the diagram or in the browser, and then choose the relationships you want to include (as well as inheritance hierarchies and traceability links).

Work smarter with #PowerDesigner – Importing and drawing a complete LDM from Excel

Do you have documentation in Excel, that needs to be imported into a data modelling tool? Also, you’re not a programmer, so you want the tool to do all the work for you? Okay, you need to use the Excel Import Wizard in SAP PowerDesigner – no programming needed. Tell the wizard what to do with the sheets and columns in your Excel file, and let the tool do the work for you.

This silent video shows how a complete Logical Data Model can be imported from Excel, walking you through the steps involved to instruct the wizard, then you can watch as the model is imported and appears before your very eyes.

This video is part of a training course offered by Metadata Matters Limited – see Online Computer-based PowerDesigner training.