Quickly Secure PowerPlay Cubes with Custom Group Security by Using MDL Scripting

Introduction

When faced with the challenge of creating and securing custom views in Transformer models (and their resultant PowerPlay cubes), one can easily perform the necessary steps via the Transformer GUI. However, when the number of custom views to be created and secured is large, it often makes sense to perform this task by using Model Definition Language (MDL) scripting – to both accelerate the time to deployment and reduce the errors that can occur with such a manual process.

What is MDL Scripting?

MDL is the proprietary scripting language for Transformer. You can use MDL-based scripts to create, manipulate, update, and store models without accessing the user interface.

Getting Started

In the following example, it’s assumed we have created an unsecured (no security views) Transformer model with multiple dimensions (including a Product dimension) and measures in version 10.2 of IBM Cognos Transformer, saved in .mdl format (not .pyj), and that we have run Generate Categories for all the categories we wish to secure.

It is also assumed that we have integrated security into our Cognos environment and have created a set of security groups (e.g. in the Cognos namespace) that align to the security views we want to create. Our goal is to permission the resulting PowerPlay cube such that members of groups see only certain categories along the Product dimension (and their children).

The specific task in this article will be to create a group that can see only the Video product family and its children. Understand that you’ll need to create code for each of the product families you want to secure.

To assist with any troubleshooting, it is a good practice to set your Transformer preferences to save the MDL language in verb form instead of structured form, as shown below:

 

 

For additional information about using MDL, please consult the IBM Cognos Transformer Developer Guide (http://pic.dhe.ibm.com/infocenter/cbi/v10r1m0/topic/com.ibm.swg.im.cognos.dg_cogtr.10.1.0.doc/dg_cogtr.html)

The process involves creating blocks of code – manually, or with an outside process (e.g. Excel, MS Access, SQL, etc), then using a text editor (e.g. Notepad) to paste this code into the .mdl file for your Transformer model. You then open the updated .mdl file in Transformer and the code you added is interpreted and processed.

Note: that you’ll need to be sure that your MDL code uses straight and not curly quotes. Failure to do so will generate syntax errors.

Creating the MDL Code

ViewMake

ViewMake is the MDL “verb” that creates a view in Transformer. We will use it to construct views of the dimensions, and set the “focus” appropriately. To set the “focus” we are using the APEX option, which allows the user consuming this view to see that specific category, and all its children.

The example below secures the Product dimension:

ViewMake “V_Video~User View” Dimension “Product” ViewCustomView “CV_Video” Apex “Video”

The first argument applies a name to the view – note the use of the ~user view suffix.

The second argument is the dimension you are trying to create a view against. In this case, it’s the Product dimension.

The third argument is the custom view you want to associate this view to. In this case, it’s ViewCustomView <custom view name>.

Last, we are specifying the APEX security option and setting it against the Video category.

CustomViewMake

CustomViewMake is the MDL “verb” that associates a CustomView with a set of views for the dimensions in your model, and sets access to the measures in your model.

CustomViewMake “CV_Video” DimensionView “Product” “V_Video~User View” MeasureInclude “QTY” Yes MeasureInclude “Bookings at CFX” Yes MeasureInclude “List Bk CFX” Yes MeasureInclude “Discount Bk CFX” Yes MeasureInclude “Net Bk CFX” Yes MeasureInclude “Bookings at AFX” Yes MeasureInclude “List Bk AFX” Yes MeasureInclude “Discount Bk AFX” Yes MeasureInclude “Net Bk AFX” Yes

The CustomViewMake syntax gives the custom view a name (this needs to match the ViewCustomView name in the ViewMake command above).

This is then followed by one or more DimensionView arguments that call out the views we want to associate to this custom view. In the example above, the custom view CV_Video is being associated with the V_Video~User View view of the Product dimension.

Following the DimensionView statements are a listing of all the measures we wish to include/exclude from this custom view. This is done through the use of the MeasureInclude verb.

Note: Not specifying a measure in the MeasureInclude statements means that it will be included by default.

SecurityNamespaceMake

The SecurityNamespaceMake verb defines the security namespace from which the security groups are retrieved. In this case, we are connecting to the namespace named Cognos. The SecurityNamespaceCAMID argument does not need to be changed and should be entered as is.

SecurityNamespaceMake “Cognos” SecurityNameSpaceCAMID “CAMID(“”:””)”

SecurityObjectMake

The SecurityObjectMake verb is used to associate a security group (in this case the V_Video group that resides in the Cognos namespace (Cognos > Security > Dimension Groups) with the CustomView called CV_Video.

SecurityObjectMake “CAMID(“”:Security:Dimension Groups:V_Video””)” SecurityNameSpace “Cognos” SecurityObjectDisplayName “V_Video” SecurityObjectType SecurityType_Group CustomViewList “CV_Video” EndList

PowerCubeCustomViewListUpdate

This MDL verb is a bit different than the others in terms of where and when it gets applied. It is to be applied after all the previous changes have been made and it is placed not at the end of the file (as all the others are), but just above the Allocations section, which is the last section of the file.

The syntax is as follows:

PowerCubeCustomViewListUpdate Cube “<cube name>” StartList <a space separated list of all the CustomViews you have created above> EndList

For example, if we were creating only 4 custom views for the Bookings Cube, our syntax would look as follows:

PowerCubeCustomViewListUpdate Cube “Bookings Cube” StartList “CV_Video” “CV_Audio_Speakers” “CV_Servers” “CV_Audio_Consoles” EndList

Implementing your MDL Code and Building the Secured Cube

Once we have our code created, we can simply open the model file (.mdl), and paste it in.

I tend to approach this in 2 steps. First, I paste in all the code relating to ViewMake, CustomViewMake, SecurityNamespaceMake, and SecurityObjectMake, then save the .mdl and open it in Transformer.

Once I establish that all has been created as intended, I save again, then paste in the PowerCubeCustomViewListUpdate code, save, and then open in Transformer to validate this last step.

After this is done, I open the model in Transformer, build the cube and test the security.