Managing IBM Cognos Transformer using MDL, Part 2: Model Building Blocks

In the first post in this series, http://blogs.perkinsconsulting.com/post/Managing-IBM-Cognos-Transformer-Using-MDL-Part-1.aspx, I described what I consider the optimum configuration for working with IBM Cognos Transformer using the MDL language.  In this post, I’ll talk about one of my favorite uses for MDL – creating a library of reusable model component “building blocks”.

Note:  For the purposes of this post, I’m using IBM Cognos series 7 with delimited text file data sources.   I’m also using the recommended model design protocol - “structural” queries to define the dimensions and “transactional” queries to define the measures and numerical accumulations. 

In our standard data mart development, we use the Kimball model of a constellation of dimensions surrounding a fact – the “star schema”.  Since we always try to deploy conformed dimensions, any given dimension may relate to multiple facts, yet the structure of the dimension and its internal hierarchies remain constant across the reporting environment.  This being the case, it’s really annoying to have to define the same dimensions in every Transformer model you create.  Furthermore, if your company has more than one Transformer designer, it might be possible (even with the best data mart design) for individual designers to configure their Transformer model differently, albeit inadvertently, and consequently introduce inconsistencies across reports.

MDL to the rescue!

Using MDL, you can develop a centrally maintained library of model object definitions which can then be agglomerated into a new model (also using MDL).  This is particularly effective with dimensions.  Measures, since they are dependent on the “transactional” or fact data sources are likely to vary more than dimensions.  This doesn’t mean you can’t make a measure building block – just that it might not be as universal as a dimension.

In order for this methodology to be effective, the Transformer object (we’ll refer to this object as a “dimension” from now on) should have a discrete data source, preferably that describes the single dimension only.  Each building block will contain the dimension and its data source – but nothing else.

As I mentioned in the first post in the series, it’s possible to create your MDL model file completely from scratch by typing commands into a text editor, but I have better things to do with my time.  Instead, I prefer to take an existing model with which I’m satisfied and break it apart into component blocks.

Make sure VerbOutput is set to 1 and ObjectIDOutput is set to 0 in your configuration file, as outlined in the first series post.

1. Open the source model with the Transformer UI.
2. Save the model as another name (say, for the dimension MDL file you’re creating), being sure to use the .mdl file type.
3. Remove all the dimensions except the one you’re creating.
4. Remove all the data sources except the ones that support the dimension you’re creating
5. Since you’re creating a generic template for the dimension, you don’t want to store any existing category values, so perform a Clean House on the model to remove all category values.
6. Save the model.
7. Open the model using a text editor (not Transformer).
8. Remove the “New Model” node at the top of the file. (Note: Be sure and do this each time you save your template from Transformer, since the New Model node will start a new model definition each time it’s encountered).
9. Save the file again. You now have a dimension template.

Even though you don’t want the “NewModel” command in all your dimension templates, in order to put together a new model using MDL, you have to have at least one “NewModel” command in the MDL code.  So, we’re going to make a “New Model” template that’s nothing but a NewModel command node.

1. Create a new model in Transformer (don’t put anything in it).
2. Save it as “NewModelTemplate.mdl” or some similar exciting name.

This is your template for new models.

Okay, now for the fun part.  We’re going to put our building blocks together to create a new model.

In addition to the item_dimension and NewModelTemplate, I’ve also got a date_dimension template.  To pull all these together, I’m going to write a small MDL code file.

1. Open a text editor such as Notepad.
2. The first line of the code file should reference the new model template, using the MDL command “OpenMDL”.
3. On each succeeding line, specify another MDL template to open using OpenMDL. Transformer treats each successive file cumulatively, so it will add any code it finds in each subsequent file to the code it’s already read.
4. Save the file with the .mdl file extension.
5. Open the file with the Transformer UI.

Transformer combines all the code in the template files to create a new model using all the building blocks you specified.  Pretty nifty, eh?

For more information on the MDL language itself, check out the documentation that ships with Transformer.  The Transformer MDL Reference guide is pretty well hidden in versions prior to series 8:

In series 8, it’s one of the selections on the Transformer Documentation page and consequently a little easier to find, although it’s now called the Transformer Developer Guide.