The Cognos Transformer tool is extremely powerful and does a great job of producing cubes via the transformer interface. The problem I have with Transformer, and the Cognos 8 BI suite, is that it provides no easy way to automatically refresh cube data on a regular schedule. At every client I visit, they want to refresh cubes regularly for use in Analysis Studio. It sounds easy enough but when you start to dig deeper you realize that it is not included in the out of the box Cognos functionality. Surprisingly a custom solution is required to achieve this. After each release I continue to wonder why IBM does not include cube refresh functionality within the Cognos Connection scheduling tool. It seems like it would be the perfect place and relatively simple given how robust the scheduling options are. I know eventually it will happen but until then we will continue to have to write custom scripts to do our automated cube builds.
So what is the best way to handle building cubes in a production environment? In my experience, using a script and scheduling tool is most effective. In a Windows environment, the Windows scheduler and a batch file works well, and in Unix a shell script scheduled using Cron seems the best route.
What should your script do? The basic steps are as follows:
- Identify the cube you want to build including the name of the source model and the name of the target cube
- Build the new cube using RSSERVER, the Transformer command line tool. You will want to do this in a temporary location just in case something fails or the production cube is locked
- Move the refreshed cube from the temporary build location to the production location, overwriting the old version of the cube
When you write your script, be sure to consider the following:
- Cubes that are in use are locked and cannot be overwritten. To work around this you may need to run the process during off hours or stop the Cognos service prior to replacing the old cube
- If the cube refresh script fails you won't know it unless you have a notification process and error handling included. There are a number of ways to handle notifications and it depends on the environment you are working in. Most scheduling tools provide notification mechanisms and some scripting tools provide the ability to send an email. I have seen both of these used with success.
- Make sure the Cognos administrator is aware of the process and how to troubleshoot in the event of a failure. On many occasions I have seen cubes go for days and even weeks without a refresh because the administrator was not aware of the process.
A sample version of a basic Unix shell script is attached.