5

Our company loves reports that calculate obscure metrics--metrics that cannot be calculated with ActiveRecord's finders (except find_by_sql) and where ruport's ruby-based capabilities are just too slow.

Is there a plugin or gem or db adapter out there that will do large calculations in the database layer? What's your solution to creating intricate reports?

btelles
  • 5,390
  • 7
  • 46
  • 78
  • I see a lot of people are viewing this question, but no one seems to want to bite. Is there anything prohibiting people from taking a stab or two? – btelles Jun 26 '09 at 17:02
  • I think your question is a tad too vague.. the concept sounds like a lot of fun.. but your specific scenario may not be described enough. – Neil N Jun 26 '09 at 20:00
  • In my opinion there's anything wrong using SQL to get your reports. Remember the 80-20 percent rule: ActiveRecord makes it easy to solve your CRUD problem, the other 20 percent is up to you. Also, in my experience reports doesn't have anything to do with models you use in your application. – Igor Jun 27 '09 at 12:26

5 Answers5

2

Although not database agnostic, our solution is plpgsql functions where it becomes really slow to use Ruby and ActiveRecord.

Omar Qureshi
  • 8,963
  • 3
  • 33
  • 35
  • Yeah, we're really trying to stick with something that's db agnostic. Thanks for the input though! – btelles Jun 29 '09 at 06:27
1

Thoughtbot's Squirrel plugin adds a lot of Ruby-ish functionality to ActiveRecord's find method, with multi-layered conditionals, ranges, and nested model associations:

www.thoughtbot.com/projects/squirrel/

Arrel
  • 13,558
  • 7
  • 26
  • 24
  • Excellent idea! We've looked at searchlogic and squirrel for generating 'where' statements more effectively, but the problem we're encountering is with calculating values between multiple fields in one query... I'm not sure if these will help in this particular use case. But let me know if they do! – btelles Jun 29 '09 at 06:11
1

Is there anything inherent about your reports that prevents the use of an SQL view or stored procedure?

In one particular project, a technique I often find useful is to create your SQL query (that may be quite complex) as a named view in the database, and then use 

 YourModel.connection.select_all(query)

to pull back the data. It's not an optimal approach; I'm keen to explore improvements to it.

Unfortunately, as you suggested, the support for doing computing complex database-based reports within rails seems fairly limited.

Lee
  • 905
  • 2
  • 9
  • 19
  • Yes, our shop has a SQL guru who would really like that option. And it seems like a pragmatic approach... I have no problem with that, save one...it's a little difficult to maintain db agnosticism when I create migrations that include stored procedures that are specific to a database type. Views are definitely up for grabs though! Thanks for the input, and sorry for the late response! – btelles Jun 29 '09 at 06:24
1

It sounds as if your tables could be normalized. At one place I worked, the amount of normalization we did was impacting our reporting needs, so we created some shadow tables that contained a bunch of the aggregate data, and did reporting against that.

I agree with Neil N's comment that the question is a little vague, but perhaps this gets you moving in the right direction?

Tony Collen
  • 111
  • 2
  • Yes, the question is definitely vague. But data normalization would solve a slightly different problem, and further normalizing our data would actually probably slow down queries a little more. Thanks for contributing though! – btelles Jun 29 '09 at 06:36
  • What I meant was, consider de-normalizing for reporting purposes. – Tony Collen Jun 29 '09 at 16:33
0

You might want to look at using DataMapper or Sequel for your ORM if you're finding that ActiveRecord lacks the expressiveness you need for complex queries. Switching away from ActiveRecord wouldn't be a decision to take likely, but it might be worth investigating at least.

Pete Hodgson
  • 15,644
  • 5
  • 38
  • 46
  • Although right now it's a little too late in our project to do this, I believe this will be our choice moving forward. I haven't looked much into DataMapper, but have heard many great stories about it. Thanks Pete! – btelles Jun 29 '09 at 06:41