One more DIY article. This time we’ll talk about:

• You have more than a dozen Power Cubes (20?50?100?)?
• And since you can use only 2 CPUs per cube on Windows platform and “update-windows” are only narrowing, you have a couple servers (3?5?)?
• And so you get the task of carefully splitting cube creation tasks on those servers, adjusting build schedules to fit the gaps, manually setting start times on windows scheduler?

Time to get an automatic egg-clock load-balancing!

Those of you, who already have a sophisticated load-balancing software tuned and humming can skip the next lines.

For those, who haven’t got the branded engineering masterpiece to do the job, or just can’t wait for support to configure it, we’ll see how to use what cognos gave us )

Cognos has a built-in balancer, used to distribute report creation and other jobs between cognos cluster. We want to utilize it to -cook eggs- to load-balance transformer cube build, so that we could schedule cube build starts in Event Studio and forget about what server will do the job. And, moreover, to add servers with minimum pain (not to update schedules).

To achieve this we need to make cube build look like a report task. To build a cube, we need to run an OS level command.

How to combine these?

Let’s make a parametrized datasource that will run an OS command on access and then create a report with param = cube_to_build_name. So we’ll run the report = start cube build and normal cognos report balancing will apply to cube building.

To create such a datasource we can:

• use a database stored procedure (like xpcmdshell in ms sql)
• write a tiny webservice that will run the command

But we need to guarantee that execution will start on server, which took the cube creation task. For ms sql servers, you set datasource to (local) and stored procedure is started on each server. For web service call “localhost” in datasource will do the job. Just ensure that model paths are the same for all servers (network share, SAS).

To sum up:

1. You create a parametrized datasource, that triggers cube creation (and holds focus until cube is created)
2. You create reports with param = cube name, using this datasource
3. You schedule report execution using Event Manager and voila, Cognos does the rest, free servers pick cube creation. And you get audit reporting of cube builds as a small bonus )

PS. Do not forget to lower affinity for cube reports, not to get 4 cubes started in a row. You can use Cognos advanced routing to separate “cube reports” from other kinds of reports and fully tune your new “transformer cluster”