Cognos Scheduling – Part I

By Elwood Philbrick - Envisn, Inc.
In this article we will cover the basics of scheduling in a Cognos 8 BI environment. The basic challenge is getting the report or cube you want, where and when you want it. In a typical Cognos 8 environment scheduling a report, query of PowerPlay cube usually starts with something that looks like this:

Process Overview

An assumption that is often made in scheduling a recurring report is that the data are refreshed by the time the report is run. But generally the reports you want to schedule will come after your data source(s) has been updated via the ETL (extract, transform & load) process. See the diagram. Once this has been completed successfully you can begin the report creation process. In many environments there will be two parts to this: reports, queries, jobs, etc. and PowerPlay cubes. When this process completes successfully the results are available for the customer.

With the ETL process you have to be sure that it completed successfully. Because many environments may have multiple data sources verifying this is an important step before the Cognos report or cube build process begins, otherwise you can be creating incomplete and/or inaccurate reports and cubes. When this occurs the rollback process can often be painful and time consuming. What is often done here is to create flags or files whose presence can be used as a signal that ETL completed successfully. These are often used as an event or trigger to kick off a report or query build.

Scheduling

Scheduling reports with Cognos scheduler in C8 gives you the option of running a report based upon a fixed date and time; by day, week, month, etc. You also have the option of scheduling it based upon an event or trigger. For many users this may be more than adequate for their needs. Some environments may require more options to meet broader needs. This may include:

• More flexible scheduling options including what you would get with a calendar that allowed you to make custom schedules. Once you get beyond standard day, week and month scheduling you often see the need for highly specialized scheduling options to accommodate unique requirements (eg, fiscal calendars, etc.).
• The ability to deal with multiple triggers. The use of a single trigger may not be sufficient to kick off a job that has a need for multiple conditions to be met. These are sometimes referred to as cascading triggers.
• The reports scheduled this way are all independent events. Each one is going to get done base on its own scheduling parameters eliminating the possibility of building a job stream(s).

Scheduling Cognos PowerPlay cubes is a whole different matter. Here there really isn’t anything that comes with Cognos 8 that you can use right out of the box. What most people have done is to build their own routines for scheduling Cognos PowerPlay. This usually done with batch files or scripts that spell out all of the details of what needs to be executed to build the cube. These can become very detailed and involved (read long) since they may spell out unique file names, task sequences, etc. Where multiple cubes need to be created these scripts can often be somewhat overwhelming.

Scheduling batch files may be done with the Windows scheduler or something similar. There are many mid to large environments that are running today in this manner and getting along. But some of the issues they often run into include the following:

• Managing change can be difficult. Since each script is unique it has to be changed whenever the parameters around that specific cube build change. This can become very tedious where there are a large number of batch files. An example here is going from one version of Cognos to another.
• Personnel changes over time make the process of managing change difficult. When the person that originally wrote the batch file is long gone it can be hard for someone else to update it. Thus, this gets even more difficult over time.
• There is little or no possibility for error detection with batch files. They either work or they don’t and the failure of one can be the failure of all.
• No performance tracking (thru put, success-failure rate, etc.). Since the processing time for many cubes grows over time this can be an issue.
• No possibility of load balancing and/or doing mixed builds over multiple servers where there is a large number of cubes.

We do not want to give the impression here that it’s a lost cause. There are alternatives to using the Cognos scheduler and rolling your own cube build process. In the next blog, Cognos Scheduler Options, we will discuss alternatives and what you need to consider as you take a long term look at your needs.