onBI

Business Intelligence cron service

What is onBI?

onBI is a service that acts like the popular Unix daemon Cron.

It's a .NET service that launches scripts defined in a list of crontab events.

So, if it walks like Cron and speaks like Cron, why do I need to use it?

When working on Business Intelligence solutions, sometimes you need a task that is recoverable if, for any reason, it wasn't possible to run it at the right time ( for example in case of a system restart after a failure). So, in order to accomplish this goals, onBI provides tasks recoverability.

This is the main feature that justify the existence of this project, but that's not all. Also has other features that make it particularly suitable to quickly write Business Intelligence tasks.

So, features?

Recoverability

Every task can be marked as recoverable. In this way, in case of any troubles that prevents the task to be launched at the right time, the same task will be launched as soon as possible.

Scripting

onBI execute scripts written in Boo (http://boo.codehaus.org/).

From the site: Boo is a new object oriented statically typed programming language for the Common Language Infrastructure with a python inspired syntax and a special focus on language and compiler extensibility.

Merging Python style coding with the power of the .NET framework results in an environment that let you easily write powerful BI tasks.

DBMS

onBI can read events list from a PostgreSQL table. The same connection, used to read events, is passed to every launched user's scripts so it's possible to dynamically reuse the same db connection in all of your scripts. Anyway it's always possible to easily create new connections using .NET DbConnection classes.

How it works?

onBI resides in memory as any other .NET service (working as a daemon in *nix systems).

Every 1 minute it re-reads the events list from the previously defined PostgreSQL table.

Then it checks if there are recoverable events that were not launched when it was supposed to. If something is found it's launched immediately.

Then it fires events that needs to be launched, according with the crontab string defined for each event.

Then, for each launched event, the next time that an event is previewed to be fired is written on the events table.

How to start

You can use the previously compiled binaries provided on SourceForge at http://sourceforge.net/projects/onbi.

You can compile the whole project using the MonoDevelop solution provided, or you can do the same thing directly with Mono. Maybe it could be easy to compile it with the MS compiler.

With Mono the project can be launched as a daemon using mono-service2. You can look the init.d scripts provided in the /sys/Linux/init.d folder.

Configuration

Configuration parameters are provided in xml files under the cfg folder: this files must be placed together with the cronbi.exe binaries.

Note: when compiling with MonoDevelop projects, the files are moved automatically in the right place.

[Email]

Email parameters are defined in a standard .NET configuration file. This file is called cronbi.exe.config.

Under <appSettings> the are two keys: “SmtpServer” and “SmtpFrom”; they contains string values for SMTP address and sender address.

[DBMS]

For now, only PostgreSQL is supported.

PostgreSQL parameters are defined in pgconnect.data. Open it with a text editor to understand how it's composed:

<DBASES> can contain several keys: every key define one database connection.

For example:

<DBASES>

<ONBI Server="arcopg.riva.griva" Port="5432" UserId="SIMONE" Password="SIMONE" Database="ONBI"/>

<WAREHOUSE Server="arcopg.riva.griva" Port="5432" UserId="SIMONE" Password="SIMPASSW" Database="MYDB"/>

</DBASES>

You can see that there are two databases defined: ONBI and WAREHOUSE.

ONBI is the primary database connection, used by onBI itself in order to read the events table. You must specify a valid connection where you will create the 'events' table (the structure of this table is specified later in this document). ONBI connection can also be used in user scripts, as in every other connection.

WAREHOUSE is a custom connection: in this example the PostgreSQL server address is arcopg.riva.griva, port is 5432, user used to connect to will be “SIMONE” with password “SIMPASSW”, database name is MYDB (note that WAREHOUSE is the connection name and MYDB is the name of the PostgreSQL database ).

To connect to WAREHOUSE in your script you can use:

using sqlconn = DbConn.GetConn("WAREHOUSE"):

Then you can access to the database using standard .NET DbConnection classes:

dbcmd = sqlconn.CreateCommand()

dbcmd.CommandText="select * from clothes.products"

reader = dbcmd.ExecuteReader()

( in examples/scipt1.boo you will find the complete script )

You can define new lines for every connection that you want to use within your scripts.

Events table

The events table is accessed using the ONBI connection as specified in the previous chapter.

In PostgreSQL you have to create one table in the 'public' schema, under the database specified in the connection parameters. This table will be called 'events' with the following DDL:

CREATE TABLE events

(

idevent serial NOT NULL,

crontab character varying NOT NULL,

nexttime timestamp without time zone,

module character varying NOT NULL,

recoverable boolean NOT NULL,

email character varying,

enabled boolean NOT NULL,

CONSTRAINT events_pk PRIMARY KEY (idevent)

)

WITHOUT OIDS;

any rows of the table defines an event, events properties are specified in the table's fields:

crontab: crontab standard string that defines when the events have to be launched (if you don't know crontab you can start reading http://en.wikipedia.org/wiki/Cron ). For example: “35 2 * * 1-5”
nexttime: you can leave it empty. onBI use this field to store the next time that a recoverable event need to be executed
module: name of the boo script that needs to be fired: the module must end with the .boo extension that will not be specified in this field. For example: “script1” will launch script1.boo
recoverable: if true, the event will be marked as recoverable and it will be launched as soon as possible if troubles has prevented it to be ran in the time defined in crontab
email: where to send email notifications of any unexpected error occurred during execution
enabled: with this boolean you can enable and disable events without having to remove anything from the table

Note: in examples/pg_events_tbl.sql is defined the sql postgresql script to create an events table that launches script1.boo

Scripting

For now, scripting is limited to Boo language.

Scripts are true .NET Boo sources compiled on the fly at execution time. They could use everything of the .NET libraries.

The easiest way to start is to read script1.boo, you can find it under the /examples folder.

Here is reported an example of code that implements the standards that every script have to respect:

import evalBI # the namespace of the common structures

class MainClass(IScriptBI): # MainClass is the name of the entry class that must inherit IScriptBI

[Property(DbConn)]

_dbconn as BaseConn = null # cronBI db connection. Provides the same connection manager used by cronBI

[Property(EvRet)]

__evret as ev_ret = null # cronBI db connection. Provides the same connection manager used by cronBI

def Execute(): # the entry point called by evalBI

The code above includes all the things that you have to respect:

You must define a main class, called 'MainClass', that inherit the IscriptBI interface. In this class you have to implement two properties: DbConn and EvRet. You must also implement the Execute() method.

Execute() is the main function used as entry point. It will be called by evalBI when it will launches the script. It doesn't receive parameters and it doesn't return anything.

DbConn is the connection container. It will be assigned before the execution by evalBI. You can use it to access the db via the same structure previously opened by onBI.

EvRet is the container of values readed by onBI when the script returns. For now it contain only one attribute: nextTime.

nextTime is a DateTime type value that you can use to customize the next time that you want that your script will be launched. nextTime will be used only in case scripts marked as recoverable. Not recoverable scripts will ignore this field.

For example you can be called again one hour later with the following code:

if EvRet:

# if I'm recoverable I want to be relaunched 1 hour later

EvRet.nextTime = DateTime.Now.AddHours(-1)

Debug

onBI provides a command line mode. Command line execution is useful to debug scripts. In this mode cronBI executes your script and then returns immediately, without any event table interaction.

For example you can test your script launching:

mono cronBI.exe script1.boo

from the command prompt

The are also the following parameters:

-e Let you define an email address where to send error messages. If -e is not specified error

messages will be written directly to the console.

cronBI.exe -e mymail@gmail.com script1.boo

-h Print inline help

The project is good, but I want to...

This project lacks of many important things such as: read an event list from a txt file or a MySQL table, launch C# or Python scripts...

I've wrote interfaces and classes in order to provide an easily implementation of additional capabilities; everything is open for future work and collaboration with anyone.

For comments and questions write me at simone.tregnago@fastwebnet.it

Credits

Cron timing/parser are based on an article written by Stefan Rieken

Mono is dual licensed by Novell

Linux ™ is of Linus Torvalds