Small Business Intelligence

When you ask people about business intelligence, some think it’s  something only big companies have or can afford. Mention of those two words will often provoke stories about how their company spent millions on a data warehouse project that was ultimately a failure.

While it’s true that BI is often not handled well, every business needs it and indeed, has it. Whether it’s a spreadsheet, accounting software reports (such as Quickbooks, or Great Plains), or reports pulled directly from your production databases, most if not all businesses need to look at measures of how they are doing.

There are definite advantages to being more systematic about it though. Versions of spreadsheets can get lost or confused. Access databases are prone to corruption and can’t handle large volumes of data. By looking at single silos of information (accounting, web traffic, etc.)  you run the risk of missing connections and seeing the big picture.

Lots of companies will offer you Business Intelligence and Data Warehousing solutions. Most of us have heard of multi-million dollar data warehouse projects gone sour. It doesn’t have to be that way, and you can get there from wherever you are.

Going Open Source

There are a lot of BI solutions out there, and some are good and easy, and others are good, but complex and require a lot of expertise to implement properly. Most of them are very very expensive. We decided to evaluate, and then implement on an open source BI solution called Pentaho. In upcoming posts, you’ll see some tips and impressions. We also opted for support, but I have to say about half of our problems have been solved by googling. The documentation is a bit incomplete but getting better. I’d say it’s a good choice when you can’t afford a more packaged solution, and it’s also good if you want to get into the guts and implement something truly unique.

What you get (for free!)

  • Pentaho Data Integration (PDI) – This is an ETL workflow tool similar to SSIS. It actually has several more useful modules out of the box, and easily talks to multiple types of database systems. We find it useful for pulling data from a SQL server system into a MySQL business intelligence database.
  • Pentaho Report server
  • Pentaho Design studio  and Metadata editor (for building data models)
  • There are also Mondrian analysis tools, and Weka data mining

The enterprise ($) version adds an interactive analyzer tool, dashboards, and several other things.


  • Opensource/open scripting. All components in the system are written in Java, and source code is available for most of them, including several third party extensions. The Data integration flows are very flexible because they include Javascript scripting modules. I think Javascript is easier to learn than C# (comparing to SSIS)
  • Connectivity to multiple databases. While you can configure ODBC connections for other db’s in SSIS, it’s finicky, and Pentaho includes models for most of them (dialogs that you fill in with connection specific properties.)
  • Works with open standards for mail, etc. for easy enterprise integration.
  • Cross platform. This is especially cool, as you can develop on windows or mac laptops,  and deploy on Unix servers.
  • Free! (well most of it.)
  • google-able support.  There’s a fairly large population of users who post in forums and on blogs like this one.


  • It’s a bit of a fast moving train. It takes some discipline to settle on a specific version, especially when you see new features appearing all the time. Unlike commercial product where there are 1 or 2 releases a year, this moves fast. The supported enterprise version is slower, and better QA’d.
  • New features often aren’t QA’d well. It’s best to give them bake time, or try them (and give feedback) but don’t deploy to production until they are baked.
  • It’s easier to get help on new features, than when you run into a problem with old. The community seems to have “moved on” including the paid support folks.
  • Performance optimization is uneven. There are some features that are really efficient and fast, and others are, well, not.  This is probably a function of “baked-ness” and how much that particular feature is used.  For some functions like insert-update, it’s better to do it in sql anyway.
  • Some functions aren’t free. There’s a really cool analyzer mode that’s available in the enterprise version. It’s still cheaper than anything else out there, so this isn’t really a disadvantage. It’s just if you need to cheap out, be aware, that you can’t have it all!

That’s all for now. I’ll publish some specific tips in coming weeks.