Techblog

Tech Blog

Our latest geek adventures!

29 July Active OLAP released

Remember my post about easy OLAP queries in Rails? I rewrote it almost completely and published is as a Rails plugin for anyone to use on github! It is now called: Active OLAP.

Although it is a complete rewrite, the API I demoed in my previous post should still work with some small changes. The most important: you have to enable it for every class you want to use it on with the enable_active_olap method. You can provide a block to this method with dimension definitions, but is not mandatory:

class User < ActiveRecord::Base
 
  enable_active_olap do |olap|
 
    # create a simple dimension on the account_type field
    olap.dimension :account_type
 
    # create a dimension with custom categories
    # the order of the categories will be kept in the results 
    # if you use an array to define the categories.
    olap.dimension :nationality, :categories => [
      [:usa, { :country => 'US' }],
      [:china, { :country => 'CN' }]
      # other is automatically added
    ]
 
    # Easily create a trend dimension
    olap.dimension :created_daily, :trend => {
      :timestamp_field => :created_at,
      :period_length => 1.day, 
      :period_count => 20
    }
  end
end

Now, we can use these dimensions for our OLAP queries. Multiple dimensions are supported too!

# simple query
@result = User.olap_query(:nationality)
# @result[:usa] == 123, @result[:china] == 456, @result[:other] = 789
 
# do drilldown using will_paginate to paginate the results
# olap_drilldown is implemented as a named_scope
@users = User.olap_drilldown(:nationality => :china).paginate(:page => 1)
 
# multiple dimensions!
@result = User.olap_query(:nationality, :created_daily)
@users = User.olap_drilldown(:nationality => :china, 
                        :created_daily => :period_19)

I am working on a generic controller that can easily be added to your Rails project. Just define dimensions for your models and the controller will let you execute OLAP queries and display the results as a table or a graph.

Keep an eye on this weblog or the github project if you want to stay up-to-date! Or, contact me if you have questions, suggestions or want to help out.

Tags: , , ,

5 Responses to “Active OLAP released”

  1. Floorplanner Tech Blog » Blog Archive » Easy OLAP queries in ActiveRecord Says:

    [...] I rewrote it and released this project on [...]

  2. Stanislav O. Pogrebnyak Says:

    Hi!

    How are things going on that project? Is it still alive. And I can’t find any words about performance. OLAP assumes that all queries should be very fast. Have u tested on big data amounts?

    Actually the idea is great.

    Thnx!

  3. Willem van Bergen Says:

    Development has stalled somewhat, as the plugin currently performs the task I need it to do. It is however in production use in several projects. I would urge you to make create issues on the GitHub project if you’re missing functionality, but I cannot promise to actually implement it :-)

    About performance: the plugin does not really perform queries itself, it just makes it easier to write complex OLAP queries. So, beside some constant overhead to build the SQL query and interpret the result, the performance is dependent only on the database work. The heavy lifting is done by your database. So, wise database design and setting the appropriate indices is a must to get decent performance.

    At Floorplanner, we use this plugin to query our data of 1+ million users on the live database and it performs fine. Of course, you can always create a separate database with customized indices for reporting only to get improved performance.

  4. lux Says:

    hi, just wondering what OLAP data stores this will work with.

  5. Willem Says:

    It basically works on (a copy of) your application database, utilizing the ActiveRecord models you have for the tables in the database.

Leave a Reply