Beyond the obvious size and resources, I don't consider my one-man shop much different from any other company.

My business collects its own performance data, and if I don't keep track of it, I'm a little lost.

So while a full data warehouse is probably overkill for me, a mini-warehouse is quite useful.

The last time I was an independent contractor, I ran my business on Excel. Accounting, invoicing, you name it. In the past seven years, the world has changed a little. There are plenty of free, "in the cloud" applications that a small business like mine can take advantage of. And, of course, if I grow large enough, I can buy more capacity.

So let me give you an overview of how I run my business so that you can get an idea of what my data needs will be.

Accounting

I use Freshbooks to do my billing as well as my time and expense tracking. It's a nice little app, grown locally in Toronto. When I was with my last job, I used Harvest, which is also an excellent app. Because we had a billing solution in place, Harvest was a more appropriate solution. Freshbooks will even send out snail-mail invoices for a nominal fee.

KPIs

• Billings (Dollars and Hours)
• Utilization
• Expenses Incurred
• Age of Accounts Receivables

Reporting

• Income Statement
• Balance Sheet
• Cash Flow Statement

Work Management

Fogbugz is the cornerstone of my operations. I use it for tracking knowledge, tasks and all sorts of useful things. At the atomic level, every activity at Braintapper is rooted in a Fogbugz case.

KPIs

• Number of open cases
• Average age of open cases
• Number of recently closed cases
• Time spent on cases
• Estimation accuracy

CRM

I use Highrise for CRM. I only need to track very simple things like new leads and activities. Most actual deals and tasks still get managed in Fogbugz. I don't really think I'll ever need to upgrade this account, since I don't really need the additional functionality.

KPIs

• Number of new leads
• Close percentage
• Pipeline size

Reporting

• Pipeline
• "Tickler" list

Putting it all together

So, as you can see, even for a one-man shop, I have a list of analysis requirements that is pretty typical for a consulting company of any size. And, these are just my initial requirements. I haven't even talked about joining any of my data sources together yet.

ETL

So the solution to do my data extraction is relatively easy. It's going to be a hybrid of shell scripts and Pentaho Data Integration. All of my solutions above provide a REST API, which makes for easy data extraction using curl statements.

Some of these statements are easily handled in Pentaho, with the exception of Freshbooks. Freshbooks requires a POST with an XML payload, and the "Execute a process" step seems to have trouble with spaces in the payload. No worries, a 3 minute shell script can extract all my Freshbooks data into XML to be handled by Pentaho.

In terms of databases, I basically stage the data into a staging database, and subsequently clean and transform the data into my mini-warehouse. Easy stuff.

Reporting

So, what is my Reporting solution going to be? Being a Cognos developer, you'd think Cognos would be my solution, right? Wrong. Cognos is not cheap, and there's no "free" version comparable to what Microstrategy is offering. Even if I did have cash to spend, the fact that Cognos only supports IE for the advanced studios is a dealbreaker.

Speaking of Microstrategy, that one's off the list too. Nothing against Microstrategy, but it requires a Windows server, and I'd rather have a solution that runs on any platform.

Pentaho does have a reporting solution as well, but to get any decent documentation, you have to buy a server maintenance license. No thanks. After I did my Dashboard exercise, I realized that it took me just as long to write a dashboard from scratch (and that wasn't much time at all) as it would for me to fine tune the exact same report in Report Studio. What?

Report Studio is one of those 80/20 tools. You can get your data and raw layout done very quickly (80% of the task in 20% of the total time), but the remaining 20% (and 80% of the total time) to get "pixel perfect" (I'm using this term very liberally) is time consuming. The easy response is to deliver a substandard report that has all the data there - I know plenty of people who will call it a day at that point. Myself, I would rather deliver the maximum amount of quality in a reasonable amount of time.

If I have to deal with a clunky interface, I'd rather write my own code, thank you. So what I'll be doing is basically a simple web app using the open source CodeIgniter framework, jQuery, and the Google Charts API. The coolest thing is that I'll probably be able to get my reports done quite quickly, with very granular control over the appearance in a short period of time.

Development

So as I develop my solution, I'll be posting some of the tips, tricks and hurdles that I encounter. I've already built out my ETL, which took less than a day. While I might complain about the one Transformation step that didn't allow me to do my entire ETL in Pentaho, it offers more transformations out of the box than some very expensive, name-brand ETL solutions... for Free (as in beer) no less.

In the coming weeks, I'll try to post information with respect to my dashboard design process as well. Keep in mind that design is not the same as esthetics. In business intelligence, the design of the dashboard is absolutely critical.

A great dashboard always has the right information, concisely summarized, and intuitively placed.

This is much easier stated than done.