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.

Set-Up

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.
• 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.