Automating Cube Build in IBM Cognos Transformer 10

IBM Cognos PowerPlay cubes are one of the most widely used OLAP data sources in the IBM Cognos environment. Typically, a PowerCube contains calculated and aggregated data that is organized as dimensions and measures, which can be viewed and analyzed in PowerPlay or Cognos BI (versions 8 or 10). Easy to use and fast access to pre-calculated summary data provides users with the ability to perform quick analysis. Since data stored in an IBM Cognos PowerCube is static, building a PowerCube naturally becomes a repetitive process. Often times cube data is refreshed on a daily, weekly, or monthly basis. Depending on data volume and the number of dimensions and measures in the cube, the build process can sometimes take hours to complete. In order to ensure the most benefit from the cube, having a scheduled and automated cube build is essential.

As many of you know, automated cube building can be easily achieved using Transformer command line options along with a scripting language of your choice. However, prior to the IBM Cognos 8.4 release, deploying the newly refreshed cube file wasn’t an easy process. It relied on an external utility to update your IBM Cognos data source connections to use the newly built cube. Today, I will show you a much easier approach that leverages new features in Transformer 10 to enable automated cube building and deployment with just a few easy steps.

Automated Cube Building and Deployment

Introducing Transformer from the Command Line

The Cognos Transformer command line is capable of performing certain modeling and cube-building tasks on the Windows, UNIX or Linux platforms. You must follow the syntax of the command line to run the desired tasks, such as creating a cube. You may use either an MDL or PJY model file, however best practice dictates the use of the MDL file format whenever possible.

The general syntax for using command line options with optional arguments is as follows:

cogtr-option filename.py?|filename.mdl

A few things to consider:

– On Windows, you can use -n with some options to run Cognos Transformer in batch mode.

– The option in the command line always starts with a dash (-).

– Command line options are case-insensitive. Arguments are case-sensitive.

– You must enclose the argument in double quotation marks if there are spaces inside any argument, for example:

cogtr -n -m”c:cube model Field3.mdl”

– For .py? files, the question mark (?) is replaced by the character that is used in the version of Cognos Transformer, i.e. .pyj or pyi file.

– You can use more than one option in a command line. If an option that is used in a command line is incompatible with an option that appears earlier in the command line, the earlier option is ignored.

– When entering command line options, you must specify the name of the .mdl or .py? file at the end of the command line after all the other parameters, for example:

cogtr -n -s -mmodel.mdl

cogtr -n -s -pmodel.py?

For more information about command line options, please review the Transformer User Guide .

Transformer Deployment and Activation Strategy

Deployment and Activation is a new feature introduced in version 8.4 that allows the cube file to be automatically copied over to a specified local or network location, activated and/or archived depending on the requirements.

Many developers have hesitated to switch to this new approach from the older CubeSwap method. It should be noted that CubeSwap has been deprecated since Cognos 8.4 and as such, no longer works or is supported in Cognos 10.1+. Anyone with PowerPlay cubes who intends to upgrade to Cognos 10 from a prior version should take this into careful consideration.

Step by Step Demo Instructions

The following demo is designed for use with IBM Cognos 10.1.1 on the Windows platform.

1. Login to IBM Cognos Transformer (assuming you have created a valid model and successfully tested it at least on time to create a PowerCube in Transformer). In this demo, the cube is named “Product Sales” and model file is saved as “ProductSales.mdl” file.

2. Create the data source and publish the package for your cube.

3. Open the cube properties, click on the Deployment tab and configure the following settings:

– Deployment Strategy: select the “Copy to available locations, then activate” option. This will copy the cube .mdc file to target server location.

– After building the cube: select “Automatically copy and active”.

– Deployment location: add a path for production and/or a place that .mdc file should be deployed too. You can select multiple locations. For instance, the same cube may be deployed to both the Development and Production servers.

– Check “Enable automatic PowerCubes deletion” and set the maximum number of cubes to be archived ( i.e. 10 as shown here).

 

 

 

 

 

 

 

 

4. Save the model, then exit Transformer.

5. Open command prompt.

6. Navigate to the Transformer installation/bin folder.

7. Type in the following command:

cogtr.exe -n2 -c -g”ProductSales” -s -m C:TransformerModelsProductSales.mdl”

Note: Replace the cube name with the actual cube name and location of your .mdl file.

Notice that after the cube build completes, Transformer has automatically deployed and activated the newly generated cube. It doesn’t require any changes to the data source connection. The live cube swap is effective immediately.

That’s it! With just a few more steps (depending on your environment), you can now schedule the cube build via a job scheduler application, such as Windows Scheduled Tasks. You will not need to manually rebuild or deploy the cube any more as everything will be done automatically on schedule.

If you have any questions about automating cube build in your environment, please contact the Ironside Group for more information. We offer packaged solutions for Transformer Automation coupled with our proven Transformer deployment methodology that includes an intelligent scripting suite with the following features and capabilities:

-Support for multi-server IBM Cognos environments.

-Change management without modifying Transformer models within your lifecycle.

-Custom e-mail notification for both users and administrators.

-Detailed logging to file and database for reporting on cube builds from within Cognos.

-Trigger builds from ETL processes on other servers.

-Trigger report execution when cube builds complete.

-Source code is provided for easy customization to meet your organization’s specific requirements and infrastructure.