Category Archives: Tools

Tools contain posts describing a certain tool from Grundsätzlich IT. A link to an external tool is usually a review.

PowerDesigner do’s and don’ts

Many people consider PowerDesigner to be the de facto standard datamodelling tool. Many people are right. However, that does not mean the tool is perfect. As many users can testify, the version 16 release has been quite buggy in the beginning, only stabilizing a bit more with 16.5. And this is not exceptional. The repository is still buggy, projects are a recipe for pain, and let’s not start a discussion on license prices – we’d still be here next year.

However, if you avoid some practices and adopt others, using PowerDesigner is a breeze. Here is my take on things.

Do Not:

  • Use the repository
    The repository is a major cause of bugs. It looks nice, like a venus flytrap, and then it sucks you in and eats you for breakfast. Avoid it like the plague. You are better off spending some money on creating an extension to generate a model export to your own repository. You can buy this from I-Refact or other parties. The other functionality can be done better, cheaper and with less frustration and bugs by just using standard version control software (TFS, git, etc.). If you must compare models, you can do that from within PowerDesigner with very little effort – without losing parts of your model on the check-in/check-out.
    There is only one part of the repository that is actually semi-useful, which is the indication whether your model is out of date versus the repository version. As this functionality does not cooperate with replication or extensions that use that, there is little point in it once you evolve beyond the basics. Also, it is much better to split up your models so as to avoid getting in a situation with 10 people working on the same model. Even potentially. If this is a risk, appoint a designated datamodeller for such a model. The rest can get a read-only version.

  • Hide attributes on entities by hiding them
    Unless you use an extension to automate setting/un-setting this and also indicate this visually, it can create no end of trouble when the model shows tables and columns but leaves out certain columns that then get deployed anyway. It takes ages to debug that. If you must do this, make sure it’s an all or nothing proposition: either hide all standard attributes, or none.

  • Create shortcuts to other models
    While PowerDesigner does this automatically once you start creating mappings, there is no need to refer to models outside the scope of the folder, as this will render the models almost impossible to deploy without heaps of pop-ups asking about other models that you have not yet stored in the right location (and don’t even know where they should be located). Only consider this if you have an agreed-upon folder structure and even then I recommend you don’t do this.

  • Create Projects
    Sure, they’re good for having a dependency graph view. But you can create those anyway. And projects are buggy, especially when interacting with the repository. Half the bugs I found in PowerDesigner went away when I stopped using projects and moved to workspaces. No more disappearing models, or graphics. No more models that are impossible to check out or check in.

  • Work for long periods without saving
    The PowerDesigner auto-save function is nonexistent. After you work with PowerDesigner for a while, you will learn to save often. It becomes a reflex. Because it hurts when you lose hours of work through a crash. It’s not as bad as it was when you were still using version 16.5.0, with repository and projects, but still.

  • Use auto-layout without a current back-up
    Your gorgeous, handcrafted model could use a minor improvement and you used auto-layout. And then you pressed “save” automatically, because by now it’s a reflex. And when the screams died down, you realized you didn’t have a current backup. Ouch. Backup often. If you use Git: commit often.

  • Model the entire Logical Data Model as a hierarchy of subtypes
    I have seen them, with their entity types derived from the Object supertype and each other, six hierarchical layers deep. I dare you to try it with a non-trivial model and then generate a working physical model out of it. Go ahead, make my day…

  • Create a unique data domain for each attribute
    This sort of misses the point of data domains. Because while they are rather limited in PowerDesigner (no entity types or attribute groups), they are most useful when they provide a single point to change definitions of common attributes. Use them freely, but let the data architect decide which ones are available for use. It’s best to create a single model for this, that you can use as a template for the other models you create.

But Do:

  • Add metadata to your models
    Especially metadata that describes the following items: Title, Subject Area, Author, Version, Data (Model) Owner, Modified Date, Modifications, Validation Status

  • Add domains
    Create a list of standard attribute domains, then create a template model containing them. People can either copy the model file directly and use it as a template (this creates havoc in a repository though, because the internal model ID will be the same as that of the template model), or copy the attribute definitions into your own model. The definitions should be controlled by the data architect(s).

  • Add attribute groups
    If you create attribute groups of commonly grouped attributes in keyless entities, you can then inherit from multiple of these entities in order to combine them. Most useful when you have things like “firstname/lastname” pairs of attributes that you do not want to separate out to their own entity, for some reason. Use with caution.

  • Tie models together with separate workspaces for each project
    Workspaces are small files with zero overhead that tie different models together. They have no impact on the repository check-in/check-out, they are files that can be under source control, and they are pretty much bug-free. You can even edit them when necessary. Much better than projects.

  • Store your models in version control systems
    Seriously, I should NOT have to say this, but I keep meeting people who don’t seem to realize that MODELS ARE CODE. And with a VCS I do not mean that abortion they call the repository. I mean TFS, Git or even Subversion. Anything that works, basically.

  • Save often
    If you don’t, you’ll regret it.

  • Store backups
    Having version control is not the same as having backups, unless you commit often.

  • Create a folder structure that is the same for everyone and make it mandatory
    If you don’t, you’ll create unending pop-ups whenever someone opens a model they did not create themselves. If they check it in, it’s your turn the next time you open it from the repository.

Collatz calculator

By Pokipsy76 - English wikipedia, Public Domain,

Grundsätzlich IT B.V. would like to publish a small javascript tool: the Collatz Calculator, on the occasion of the publication of a new proof of the Collatz Conjecture.

Two days ago Peter Schorer published a new proof of the famous mathematical problem named the Collatz Conjecture. This doesn’t mean it has been proven, though: new proofs for this particular problem appear with depressing regularity, only to be invalidated in a few weeks. But we can always hope for the best!

The problem itself can be stated in simple terms:

Take any positive integer n.

  • If n is even, divide it by 2 to get n / 2
  • If n is odd, multiply it by 3 and add 1 to obtain 3n + 1

Repeat the process (which has been called “Half Or Triple Plus One”, or HOTPO[7]) indefinitely.

The conjecture is that no matter what number you start with, you will always eventually reach 1. This seems easy to prove, but the Collatz mapping above exhibits chaotic, even fractal behaviour. Thus, a proof has long been sought but not yet been found. Due to the relationship to several other longstanding mathematical problems, this problem has occupied mathematicians for at least a century, possibly much longer than that. Leading to frustration in some quarters.

Used with permission.

To get some idea of the complexities of the mapping, feel free to play with this Collatz Calculator (javascript): Start the Collatz Calculator


Time Dimension Generator v1.41

In a data warehouse we often use a table containing information about dates. This is called a Time dimension (it should rather be called a Date Dimension, but let’s not quibble). This table is used to deal with calculations regarding time in SQL queries a bit easier. It enables us to attach meaning to dates as well, by letting us add attributes to them. Using time-shifted columns in the table we can then say things like “give me all the sales of the same period last year, for period X to Z”. Given the right query, of course.

This tool generates a table you can load into a data warehouse. For more information, please see (it needs improvement but it’s a start). Note that it is available in the Dutch language only, due to specific features that depend on the Dutch local calendar (like working days). I may (on request) rebuild it for international use, but please check the following alternatives first: or

Start the Time Dimension Generator (Dutch language version only).

Templator v1.0

This tool provides a template-replacement-service. You provide a template with variables, a signle line with the names of the variables and an number of lines (say, N) with replacements (CSV-format). The result will be N templates, with all the variables replaced by row 1..N from the replacements. Those templates will be present in a single download.

What do you use it for? Well… currently I use it as follows: create an ETL-workflow in BODS. Export the workflow to XML. Replace the items that are variable with variable-names. Save this as a template. Now, when a new workflow is necessary, it is just a matter of copying the variables, their replacements and the template into the Templator. Then, run it and use the download as an import (after removing superfluous begin and end-tags).

It works best if you have highly similar flows based on highly similar inputs that differ only by up to ten variables. Beyond that, it tends to become more cumbersome to gather the replacement values than to copy and paste the original workflow. Your mileage may vary, however.

Start the Templator or the Dutch language version.