|
SmartCruddy! Official Web Site BI now, code later. |
|
IntroductionWhile 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 setsYour 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.ScreenshotsQuick-Start and Setup InstructionsPrerequisitesSmartCruddy! 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:
Get SmartCruddy!Download and unpack the latest SmartCruddy! distribution.
Install webserver resourcesYou'll find the following resources in the distribution.
Place smartcruddy.css in your webserver document root, e.g.
Quick-start (cgi-bin install)Place smartcruddy.pl in your cgi-bin path or otherwise allow it to be executed within your webserver, e.g. /var/www/cgi-bin/smartcruddy.plSet the following environment variables to connect to your RDBMS via DBI: DBI_DSN DBI_USER DBI_PASS Optionally set the following environment variables if your template path differs from above (where you put smartcruddy.tpl) CRUDDY_FAST_TEMPLATE_PATH SMARTCRUDDY_FAST_TEMPLATE_MAINE.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 # SetEnv SMARTCRUDDY_FAST_TEMPLATE_MAIN smartcruddy.tplFor Oracle you'll probably want the following as well (of course making sure they are set before starting apache): PassEnv LD_LIBRARY_PATH PassEnv ORACLE_HOME(Re)start your webserver and navigate the URL to execute the script, e.g.: http://localhost/cgi-bin/smartcruddy.pl Try going through the tutorial to get an idea of basic usage.
mod_perl setupFor 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.
PrerequisitesIn 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
Apache::Requestmod_perl v2 The perl XS libapreq2 Verify the following have been installed: Apache2::Request Apache2::RequestRec Apache2::RequestIO Apache2::RequestUtilRecommended: Apache::DBI You may need to run /sbin/ldconfig -v as root after installing libapreq(2). Install SmartCruddy.pm (found in the package distribution) to a place it can be found by mod_perl e.g. perl's INCLUDE path: /usr/lib/perl5/site_perl/5.8.8/DBIx/Knowledge/SmartCruddy.pm
ConfigurationYou'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 mod_perl_startup.pl # 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/mod_perl_startup.pl 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/cruddy_debug_params.pl Order deny,allow Allow from all </Location>NOTES: 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 mod_perl_debug.pl to debug your application (details). Do NOT use in a production environment. Add the following to the list of use'd modules in mod_perl_startup.pl (see Cruddy! docs). BEGIN { ... ... 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: http://localhost/smartcruddy Tutorial - Case Study for Server Activity LogsIf 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 setCreate 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).demo_tables.sql demo_populate_process_load_log.sql Data point definitionsCreate the table to store data point definitions and populate with data points for PROCESS_LOAD_LOG.dbix_knowledge.sql demo_populate_dbix_knowlege.sql Report builder UIPopulate UI_TABLE_COLUMN for the demo tables (DDL for UI_TABLE_COLUMN is included in Cruddy! and should have been executed on setup:demo_populate_ui_table_column.sql Create a reportNavigate 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):FORMAT.HEADER_REPEAT=0&__SDAT_TAB_ACTION.TABLE_NAME=process_load_log&FORMAT.SELECT_FIELD=DAY&FORMAT.SELECT_FIELD=HOSTNAME&FORMAT.SELECT_FIELD=LOAD_TIME&FORMAT.SELECT_FIELD=RECORD_CNT&FORMAT.SELECT_FIELD=SKIP_CNT&FORMAT.SELECT_FIELD=WARN_CNT&CRITERIA.HOSTNAME._WM=proc%25&CRITERIA.LOAD_STATUS=SUCCESS&FORMAT.SUBTOTAL_FIELD=DAY&FORMAT.SUBTOTAL_FIELD=REPORT_TOTAL&FORMAT.REPORT_TITLE=Load+History+By+Day+And+Process+Server&CRITERIA.LOAD_START_DATE._RE._DAY=27&CRITERIA.LOAD_START_DATE._RS._DAY=15&CRITERIA.LOAD_START_DATE._RE._YEAR=2008&CRITERIA.LOAD_START_DATE._RE._MONTH=04&CRITERIA.LOAD_START_DATE._RS._MONTH=01&CRITERIA.LOAD_START_DATE._RS._YEAR=2008&CRITERIA.LOAD_START_DATE._UR=1 e.g. http://localhost/cgi-bin/smartcruddy.pl?FORMAT.HEADER_REPEAT=0&__SDAT_TAB_ACTION.TABLE_NAME=process_load_log&FORMAT.SELECT_FIELD=DAY&FORMAT.SELECT_FIELD=HOSTNAME&FORMAT.SELECT_FIELD=LOAD_TIME&FORMAT.SELECT_FIELD=RECORD_CNT&FORMAT.SELECT_FIELD=SKIP_CNT&FORMAT.SELECT_FIELD=WARN_CNT&CRITERIA.HOSTNAME._WM=proc%25&CRITERIA.LOAD_STATUS=SUCCESS&FORMAT.SUBTOTAL_FIELD=DAY&FORMAT.SUBTOTAL_FIELD=REPORT_TOTAL&FORMAT.REPORT_TITLE=Load+History+By+Day+And+Process+Server&CRITERIA.LOAD_START_DATE._RE._DAY=27&CRITERIA.LOAD_START_DATE._RS._DAY=15&CRITERIA.LOAD_START_DATE._RE._YEAR=2008&CRITERIA.LOAD_START_DATE._RE._MONTH=04&CRITERIA.LOAD_START_DATE._RS._MONTH=01&CRITERIA.LOAD_START_DATE._RS._YEAR=2008&CRITERIA.LOAD_START_DATE._UR=1
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:
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 reportsAt the bottom of every report are two links:
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! DocumentationThe perl modules SmartCruddy! implements are fully documented. The following are a good place to start:DBIx::Knowledge::Report CGI::CRUD CGI::AutoForm DBIx::IO |
||
SmartCruddy! sponsored by The SMB Exchange, INC
Copyright © 2008 Reed Sandberg
|