Using “copy-to” for Data Warehousing

Introduction

Until now there has been no good means for data warehousing data from Cricket. RRD provides a great means for graphing data but sometimes there is a need to have the numeric values in a more accessible form. That's where the SQL method for “copy-to” comes in.

Setup

This is still in it's early stages of development and has only been tested under “SQLServer 7.0”. This should definitely work with other versions of “SQLServer”, and maybe “Sybase”.

You need to create a database called “cricket”. In this database, set up a table called “CricketData”. There is a file in the docs directory called “cricket.sql” which can be used either to create the table, or as a reference to manually create the tables if it won't work with your database.

Using

In a target definition create a “copy-to”:

target myImportantDatasource copy-to = "sql:dbi:Sybase:NMSDB,mylogin,mypassword"

As you can see, it's pretty simple to define a copy-to. The “dbi:Sybase:NMSDB” can be customized to use whatever special definitions your DBI driver requires. This is designed to be as flexible as possible to allow for working with all DBI drivers.

When the collection happens, it inserts into the database whatever numbers were retrieved from the target.

That's it! You now have a crude form of data warehousing set up for Cricket data.

Caveats

Yes, there are many.

This isn't guaranteed to work with all DBI platforms.

While it's designed to be as flexible and generic as possible, it may not work with your platform. If it doesn't, we're accepting patches. :-)

Cooked numbers aren't inserted.

This inserts every number into the database as-is. This means that all of the cool things rrd can do like averaging, interpolation, and computations are gone. This copy-to happens before anything is done with RRD.

Datasources aren't named.

This is a big limitation right now. It means that the user has to figure out what datasource number co-responds to which counter. Luckily, it goes in the order collected in the “Defaults” file for the particular target. Until Cricket can name datasources, it can't insert named datasources into the database.

Note: Cricket 1.0.4 supports named datasources, but this support is pretty new and untested. Until more experience is developed with named datasources, this limitation stands.

Cricket doesn't supply any tools for using these numbers.

It's up to you to provide tools for extracting and using the data once it goes to a database. Cricket is simply an agent for passing the numbers over.

Still in early stages of development.

Don't expect perfection. As more people use this, we'll see greater improvements, though.