wvanbergen edited this page Sep 12, 2010 · 36 revisions

Active OLAP is a Rails plugin that eases performing OLAP (online analytical processing) queries on your tables. An introduction to OLAP can be found on Wikipedia, with explains some of the basic concepts like dimensions and fact tables.

In Active OLAP, the table corresponding to your ActiveRecord-derived class acts as a fact table. You can define dimensions that categorizes the records (facts) in the table. A dimension can cluster the records in any number of categories. A combination of these dimensions can be used to perform an OLAP query that returns an OLAP cube.

A simple example: For the fact table people, the dimension gender will categorize the records into two categories: male (m) and female (f). The age dimension might categorize the records in three categories: youth (y), adults (a) and the elderly (e). An OLAP query on these to dimensions will return a [2×3]-cube with 2×3 = 6 different values: m&y, f&y, m&a, f&a, m&e and f&e.

By default, Active OLAP will return the number of records that match the category definitions (using COUNT(DISTINCT id)). It is possible to “summarize” the results of a set of records using other SQL aggregate functions as well, for example using SUM(amount) to get the total amount in a category. These summarizing functions are called aggregates in Active OLAP. It is possible to use multiple aggregates in a single OLAP query at the same time.


To install the gem on your system, run:

gem install active_olap -s http://gemcutter.org

Alternatively, you can include them gem into your Rails project by adding it to
the configuration in your environment.rb file:

gem.config 'active_olap', :source => 'http://gemcutter.org'

Performing OLAP queries with Active OLAP

Using Active OLAP for your model basically consists of the following steps:

  1. Enable Active OLAP for your model with enable_active_olap
  2. Define dimensions and aggregates for you model
  3. Perform a query with a call to olap_query and use the returned cube to display the result in a table, chart, etc.
  4. Find the actual records in a category using olap_drilldown

Presenting results

Active OLAP comes with several helper modules to make it as easy as possible to include Active OLAP functionality to your Rails application:

  • ActiveRecord::Olap::FormHelper contains functions to generate forms to build OLAP queries.
  • ActiveRecord::Olap::TableHelper contains functions to display result cubes as tables.
  • ActiveRecord::Olap::ChartHelper contains functions to display result cubes as charts. Google Chart is used for creating charts; the gchartrb gem is required for accessing the Google Chart API. The gchartrb gem will be loaded automatically when you include this module.
  • ActiveRecord::Olap::DisplayHelper contains functions for displaying category names, values, etc. This module is required for the other modules and will be included automatically

If you want to use any of these helper modules, include them in your controller as a helper:

class OlapController < ActionController::Base
  helper ActiveRecord::Olap::TableHelper
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.