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.

 

I started working with IBM Cognos Transformer (the tool that builds multi-dimensional cubes) back in the day – the version 6.0 day, as a matter of fact, before the advent of SQL Server Analysis Services.  I believe that at that time, the binary model files had a file extension of .pyg, and the only way I knew to manipulate the model was inside the Transformer UI.

Ah, the innocent days of (relative) youth.

Well, innocent and really annoying, too.  I’ve always had issues with programs that were only accessible one way.  I like knowing about back doors and troubleshooting methods to invoke when the compiled UI lets you down or corrupts your files. 

Then Cognos released an update to Transformer that changed the file extension from .pyg to .pyh and I discovered the .mdl file format.

When you save your model in the .mdl format, it’s stored in MDL, the proprietary text-based modeling language that is the “universal translator” between versions of Transformer. 

In those early days, other than just using the .mdl file format as a conversion function, I also used it to compress model files.  The binary .py? files never release any space, since they store information about changes to the model.  Consequently, they continue to grow forever.  The only way to wrangle them back into a manageable size is to save them as .mdl files.  When you save the .mdl file back to .py? format, all that unseen space is removed and the binary file is restored to a reasonable size.

After a while, I wondered…why bother to go back to a .py? file anyway?  The only thing you really gain using the .py? file is a slight advantage in load time.  You can do anything with the .mdl formatted model that you can with a .py? file.  And I discovered that you could do even more. 

In addition to just being able to open and edit the model file in a text editor (a huge bonus for me, see reference to back doors above), you can actually use the MDL language to alter, manipulate and create all or part of a Transformer model.  Granted, creating an entire model from scratch using MDL would not be my idea of a good time, but luckily this is not an all-or-nothing proposition. 

My two favorite uses for MDL are using it to create model “building blocks” (reusable model components that can be standardized and used across multiple models), and using MDL to automate model maintenance and cube builds. 

In this series of blog posts, I’ll cover both these scenarios, but first, we need to do a little housekeeping. 

There are two types of MDL – Structured MDL and Verb MDL.  Structured MDL was the original version, but it’s more restrictive – you have to pay attention to the order of the commands in the file, for instance.  Verb MDL is easier to work with.  However, the default setting for the MDL version for Transformer out-of-the-box is Structured MDL, so we need to change a couple of settings in trnsfrmr.ini (for pre-series 8 Transformer) or cogtr.xml (for series 8).

The VerbOutput setting toggles between Structured MDL and Verb MDL.  VerbOutput = 0 sets Transformer to Structured MDL mode.  VerbOutput = 1 sets Transformer to Verb MDL mode.  The second setting (ObjectIDOutput) affects the way object values are presented in the MDL text file.  ObjectIDOutput = 1 presents the object identifiers as numeric values, which are not easy to evaluate in a human-readable way.  ObjectIDOutput = 0 presents the object identifiers using their text value, which is much easier on the eye (and brain).

• Series 7
  • Open …cer[2,3 or 4]/bin/trnsfrmr.ini
  • In the [PowerPlay Transformer] section, set the VerbOutput option to 1
  • In the same section, set the ObjectIDOutput option to 0

• Series 6.*
  • Open …cer1/bin/trnsfrmr.ini
  • At the end of the [PowerPlay Transformer] section, add a line and type in VerbOutput=1
  • Add another line and type in ObjectIDOutput=0
• Series 8
  • Open …cognos/c8/configuration/cogtr.xml
  • Add the following node under the <Section Name="Transformer"> node:: <Preference Name="VerbOutput" Value="1"/>
  • Add the following node under the <Section Name="Transformer"> node: <Preference Name="ObjectIDOutput" Value="0" />

The following examples show the same Transformer MDL model category definitions.  First, as Structured MDL (ObjectIDOutput is the default of 1):

Next, Verb MDL with ObjectIDOutput = 1:

Next, Verb MDL with ObjectIDOutput = 0:

As you can see, setting the ObjectIDOutput to 0 presents the .mdl file in a much friendlier way.  After you set your VerbOutput and ObjectIDOutput configuration options, open one of your models from the Transformer UI, then use the Save As option to save the model as an .mdl file with the new configuration options applied.  You can then open the .mdl file in your favorite text editor for your viewing pleasure.

I’m posting this in case someone else comes across this strange bug.

I recently had a Silverlight project start displaying a vertical scrollbar in the window in IE8.

It turns out I had ‘formatted’ the page that hosts the Silverlight object control and that since the div element was on a separate line from the iframe element, it was causing the scrollbar to appear.

 

I tried playing around with the CSS since I don’t think it should matter how I have the angle brackets in the page but I finally gave up and just put it back the way it was.  This still feels dirty to me.

So if you are having similar issues.  Here’s at least one solution.

One of the most valuable uses of good business information is to help navigate a business in times of uncertainty and change. Many of our clients are doing exactly that and seeing huge benefits from having a data mart of solid, reliable and up to date information about their business performance.

A little while ago I had lunch with one of our Retail clients and they provided a great example. Their bank had been hit hard by the financial services crash and decided that over a period of months they would scale back our client’s line of credit for working capital. Rather than panicking our client went back to a new multi-dimensional analysis that they’d been using to analyze their inventory levels. They realized they could more closely manage inventory now that they had more accurate and timely seasonal sales information. Over a period of about nine months they reduced their inventory levels and working capital by almost 50% and were able to easily accommodate a smaller line of credit. In fact, they are seeing other operational benefits from not storing and managing so much inventory. Since their customer’s economic situation has also been dramatically changed they are buying different products. Having less on-hand inventory has kept our client better positioned to respond to these product buying shifts and able to move quickly into new higher-margin products rather than having to heavily discount large inventories of older products.

Having good business information delivered in a means that facilitates analysis allows our clients to see leading indicators of change well in advance. It also allows them to explore multiple possible solutions and anticipate how these solutions will impact their business. These are essential capabilities in these times of economic change!

Recently, I inadvertently discovered a useful feature of Windows 7 Virtual PC that I'd like to share, just in case there are folks out there who haven't experienced this particular functionality.

On my host laptop (a Windows 7 box), I've installed SQL Server 2008 and Visual Studio 2008, although my previous XP box hosted SQL Server/Visual Studio 2005. I use Visio for Enterprise Architects as my data modeling tool and as I discovered during the move to the new Windows 7 laptop, Visio for Enterprise Architects requires Visual Studio 2005 in order to install properly - it won't play with VS2008.

Okay, fine.

This was annoying, but not catastrophic. Since most of our clients are still on SQL Server 2005, I have a Virtual PC with SQL Server/Visual Studio 2005 installed. I installed Visio on this VPC, grumbling since I knew I'd have to launch this VPC whenever I wanted to do any data modeling.

Or so I thought.

One day, I experienced a momentary lapse of consciousness and double-clicked a Visio (.vsd) file on my host machine. Just as I was cursing myself for doing something so dopey, the Virtual PC credentials login for my SQL Server/VS2005 VPC popped up. I entered my credentials, and the VPC started to launch, except instead of opening the VPC window, it launched Visio as a "virtual application" within the context of the host machine.

How cool is that?

I was able to access files from inside the VPC as well as the host machine from the Visio application menus.  The host machine treated it as a remote connection to the VPC through the local network.

I've poked around with this a little more since the first time it surprised me and have discovered a few more things:

• My VPC is a member of a domain.  If no network is available (even a network that is not the domain network), the remote application cannot launch.  For instance, I was in an airport recently with no internet/network connectivity and was unable to start the virtual application.
• For this domain-enabled VPC, it doesn’t seem to matter if Integration Features are enabled or not.  The remote application can still launch.
• Occasionally, Internet Explorer tries to take over and pretend that it’s the application of choice to host .vsd file.  You have to take a firm stand on this – right click the .vsd file and choose Open With.  On my host laptop, the option to open with the remote Visio program appears in the popup menu (SQLServer2005 is the name of my VPC):

I haven’t tried this with a VPC that isn’t a member of a domain, so I’m not sure what the behavior for that would be like.  I’ll have to give that a try for the next blog post!