SmartCruddy! Official Web Site
BI now, code later.



While your technical teams battle over which Business Intelligence system to implement ( Jasper, Pentaho, BIRT, Oracle, SAS, Business Objects, etc), your systems administrator can get SmartCruddy! up and running today. Face it, there will be days (weeks?) of review, evaluation and (don't kid yourself) mud-slinging back and forth between your blood-thirsty developers, DBA and sysadmins, then weeks (months?) of implementation and development. With SmartCruddy! and a little organization of your database, (which you've probably done already ;) you can start making better informed decisions by the end of the day. While SmartCruddy! is no match for some of the features of the aforementioned epic projects, it will tide you over with negligible implementation resources until the "enterprise" solution is ready.

SmartCruddy! is a Business Intelligence (BI) application of the DBIx::Knowledge reporting framework that provides drill down and report creation capabilities for convergent data sets. Currently only Oracle RDBMS is supported. SmartCruddy! is stable and used in several production sites around the world including ValueClick, Inc.

With SmartCruddy!, you can get a basic BI tool up and running today without coding. At the same time, it allows a great deal of flexibility for customization by engineers (application functionality/business logic) and non-engineers (presentation and style) alike.

BI now, code later.

Linear convergent data sets

Your data set must be in a form that can be grouped by a set of fields. An example may be as simple as a table populated with webserver log records. This table can be aggregated by the timestamp field of the webserver request (say you want to report on the number of hits to /index.html from 9am to 10am) and will fit nicely into a model that can readily be used by this abstraction. This may be accomplished by creating views or semi-aggregated tables (materialized views) usually along an axis of time (temporal linear convergence). Semi-aggregated tables are useful for reducing the size of very large data sets aggregated by key fields analysts might be interested in so they can be queried efficiently in real-time.


Quick-Start and Setup Instructions


SmartCruddy! is an implementation of the all perl DBIx::Knowledge module (available from CPAN). It currently only supports Oracle RDBMS. So besides perl and a webserver:
  1. RDBMS: Presumably you have such a database setup with tables already, otherwise why would you be here? A schema is provided in the tutorial below if you want to test the waters on some sample data.
  2. I highly recommend getting Cruddy! working on your system first.
  3. Install DBIx::Knowledge.
  4. Make sure the DBD driver for your RDBMS is installed and working properly ( DBD::Oracle).

Get SmartCruddy!

Download and unpack the latest SmartCruddy! distribution.

Install webserver resources

You'll find the following resources in the distribution.

Place smartcruddy.css in your webserver document root, e.g.
cruddy.css should be here as well (from Cruddy!).

Place smartcruddy.tpl close to your webserver root path, e.g.

Quick-start (cgi-bin install)

Place in your cgi-bin path or otherwise allow it to be executed within your webserver, e.g. /var/www/cgi-bin/

Set the following environment variables to connect to your RDBMS via DBI:


Optionally set the following environment variables if your template path differs from above (where you put smartcruddy.tpl)

E.g. for apache httpd.conf:
SetEnv DBI_DSN dbi:Oracle:pdev
SetEnv DBI_USER smartcruddy
SetEnv DBI_PASS smartcruddy

# SetEnv CRUDDY_FAST_TEMPLATE_PATH /usr/local/apache/tpl
For Oracle you'll probably want the following as well (of course making sure they are set before starting apache):
(Re)start your webserver and navigate the URL to execute the script, e.g.:

Try going through the tutorial to get an idea of basic usage.

mod_perl setup

For a busy production environment, you'll probably want (already have) mod_perl. SmartCruddy! supports both mod_perl v1 and v2 with minor differences when noted below.


In addition to mod_perl, you'll need the following perl modules (available from CPAN) depending on the version of mod_perl you're using (most likely v2 for current systems). If you use RPM's (RedHat, etc) this will be easier if you install both mod_perl and mod_perl-devel packages.

mod_perl v1 The perl XS libapreq
Verify the following have been installed:

mod_perl v2 The perl XS libapreq2
Verify the following have been installed:

You may need to run /sbin/ldconfig -v as root after installing libapreq(2).

Install (found in the package distribution) to a place it can be found by mod_perl e.g. perl's INCLUDE path:



You'll need the following directives added to your httpd.conf. Typically, these exist in a separate file Include'd by httpd.conf e.g. /etc/httpd/conf.d/perl.conf for RedHat or similar systems.
# Define here if using Apache::DBI->connect_on_init() in
# Otherwise, or if there are conflicts, can set inside /smartcruddy Location directive below
PerlSetEnv DBI_DSN dbi:Oracle:smartcruddy
PerlSetEnv DBI_USER smartcruddy
PerlSetEnv DBI_PASS smartcruddy

PerlRequire conf/

PerlModule DBIx::Knowledge::SmartCruddy
<Location /smartcruddy>
    # For mod_perl v1 use the latter directive
    SetHandler modperl
    #SetHandler perl-script

    # For mod_perl v1 use the latter directive
    PerlResponseHandler DBIx::Knowledge::SmartCruddy
    #PerlHandler DBIx::Knowledge::SmartCruddy

    # Optional if these differ from the defaults
    # PerlSetEnv CRUDDY_FAST_TEMPLATE_PATH /usr/local/apache/tpl
    # PerlSetEnv SMARTCRUDDY_FAST_TEMPLATE_MAIN smartcruddy.tpl

    # Don't set in production environments, only useful for mod_perl (not cgi-bin)
    # PerlSetEnv CRUDDY_PARADUMP_DEBUG /tmp/

    Order deny,allow
    Allow from all

As explained in the cgi-bin install, you'll need the environment set (DBI_DSN, etc), I recommend PerlSetEnv for mod_perl.

CRUDDY_PARADUMP_DEBUG will enable you to use to debug your application (details). Do NOT use in a production environment.

Add the following to the list of use'd modules in (see Cruddy! docs).


    use POSIX ();
    use HTML::Entities ();

    use DBIx::Knowledge;
    use DBIx::Knowledge::Output;
    use DBIx::Knowledge::Report;
    use DBIx::Knowledge::OracleSQL;
    use DBIx::Knowledge::SQL;
    use DBIx::Knowledge::SmartCruddy;


Navigate to the Location URL you created:


Tutorial - Case Study for Server Activity Logs

If you're now thinking to yourself, "What just happened? Where am I?" then you probably haven't read the docs very carefully. OK, I'll spare you the derision - follow along the case study below for a quick tutorial and then read the docs!

Look in the "demo/" and "contrib/" directories for the SQL files.

Convergent data set

Create the demo and related tables PROCESS_LOAD_LOG, and populate with sample data. This table is organized in such a way to demonstrate some of the features of SmartCruddy!. It collects activity from an ETL process, stores raw data, and can be used for reporting as-is (no aggregation needed).

Data point definitions

Create the table to store data point definitions and populate with data points for PROCESS_LOAD_LOG.

Report builder UI

Populate UI_TABLE_COLUMN for the demo tables (DDL for UI_TABLE_COLUMN is included in Cruddy! and should have been executed on setup:

Create a report

Navigate to your SmartCruddy! URL and enter "PROCESS_LOAD_LOG" for the Table/view Name. The next page will present a form to create a report. For this demo, append the following query string to your SmartCruddy! URL to pre-define an example report (yes, it's a long one, all on one line):



The first section (Data Points), allows you to select the data points to include in your report and some formatting and other options. The form fields for this section are active links that give details - use them if you get lost in the future but one-by-one:

  • Report title
    Give a title for this report.
  • Report on
    Choose the data points to include in this report.
  • Summary totals on
    Choose which data points will have subtotal summary lines. Will always have the option of a grand total. In this example, we're grouping by activity day and including a grand total.
  • Repeat header on subtotals
    Repeat field headers for every subtotal row?
  • Auxiliary info fields
    For advanced use only.
  • Order by
    For each data point selected, there is an abbreviation of the field in parenthesis. Use those abbreviations here separated by commas to override the default ordering of this report. The word "desc" may be appended to each abbreviation to specify order polarity. The default ordering is to order by all data points in the sequence they appear in the select list.
  • Limit result set
    Limit the result set to this number of rows.

The next section (Result Criteria) allows you to select the rows in the table to include in the report. In this example we're filtering by the process server hostname, date range of activity and process job status.

Click the Report button - you know what to do, and it's... magic!

Saving and editing reports

At the bottom of every report are two links:
Save report URL for later
If you right click this link in most browsers you should have the option to copy the URL link location. You can then past it in an HTML document and anchor it, etc.
Further customize this report
This link will take you to the report builder with all parameters saved and pre-selected.

To fully understand what's going on, the substance of this demo is in the data point definitions; you'll want to inspect demo_populate_dbix_knowlege.sql and have a look at the DBIx::Knowledge::Report manpage.

You may want to take some time to integrate SmartCruddy! into your existing look and feel. The best place to start for "skinning" the UI is modifying smartcruddy.css, cruddy.css and perhaps smartcruddy.tpl.

You are now prepared to tame your data and rule your business!


The perl modules SmartCruddy! implements are fully documented. The following are a good place to start:

SmartCruddy! sponsored by The SMB Exchange, INC
Copyright © 2008 Reed Sandberg