A couple of blog entries ago, I mentioned that I was prepping a revamp of the main site, blog and Exchange application.

One of the key drivers behind this was that the Exchange application was going to have a different user experience than the blog and main site, and that I wanted to at least force some consistency among the sites.

Another reason why I wanted to to do this was to get the main site up to modern coding standards. I threw the site together using tables, mainly because I could get the layout grid up and running in a very short period of time.

My current site layout uses an overly simplistic 2 column grid, which really limits how I present my content, especially what appears "above the fold".

As a proponent of typographic grids for report design, I felt I needed to eat my own dog food, and the new sites will use a 12-column typographic grid, with table-free code. As long as you're using a relatively modern standards-compliant browser, the site will render nicely.

I'll also be validating my code with W3C standards to make sure the site is fully compliant.

Since I also happened to learn JQuery with a dashboard challenge that I worked on, I'll be adding JavaScript interactivity to the front page only. A little goes a long way, so no need to overdo it.

The best thing is that most of the changes are css and layout-based, so the work isn't even remotely as intensive as getting the first iteration of the site out.

Just stumbled on Palo, which seems to be a genuine alternative to IBM-Cognos' TM1 product.

The best part is that it exists as a free, open-source version. Definitely worth further investigation.

As one or two of you might have noticed, the release date for the Braintapper Exchange has been pushed to January of 2010. Some stuff came up, mostly in the realm of "new ideas" to improve the base implementation.

Following this release will likely be the 2.0 of the web site. What? So soon? The current site design has been fine as a "get something online fast". While it has given me a professional presence on the web, there are some key marketing goals that I wanted to accomplish that can't be met with the current design. Fortunately it's just a template facelift, the underlying "engine" driving my site will be "same old, same old".

RegExr Desktop is a free (as in beer) fantastic cross-platform tool (made with Adobe AIR) for testing out your regular expressions.

Highly recommended.

For some reason, Regular Expressions have been very difficult for me... much like rocket science.

They are, however, crazy powerful.

Something I've been trying to implement is pulling in time entries associated to a Fogbugz case and pushing them into the case's elapsed hours. Fogbugz time entry system is not idea for a services company, and most companies have their own time reporting system.

The methodology that I'm using is that in my Freshbooks time sheet, I'm using a text convention like this:

[123] Note goes here

Where the case number is in the square brackets. If the square brackets are left out, then there is no case associated with the time entry. Example below:

Note goes here

What I want to do is to use Pentaho to parse the note, and create a case number and note for me to insert into my data warehouse. I can then create a transformation that will, using Fogbugz's API, push the total hours for each case back up to Fogbugz, so that I can use them for Evidence Based Scheduling.

So here's the regular expression I used to extract the data:

(\[(.*)\])(.*)

So what this gives me in Pentaho are three data fields (using my first example above):

Field 1: [123]

Field 2: 123

Field 3: Note goes here

Not bad. But for the second example, without a case reference, I get:

Field 1: Null

Field 2: Null

Field 3: Null

I can easily fix this with a formula step in my transformation, but I know it can be handled in Regex. The only problem is that the knowledge is above my skill level.

Enter Stack Overflow. I basically summarized my scenario above, and in less than 5 minutes, I had a working answer.

(\[(.*)\])?\s*(.*)

Not very different than my original string, but it gives me:

Field 1: Null

Field 2: Null

Field 3: Note goes here

Now I can map Fields 2 and 3 to my time entry table using only one Pentaho step. Nice!

One more thing - if you're not using StackOverflow to solve your technical problems, isn't it about time you started?

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.

For those of you that don't know, Google has a free charting API.

The charting API, which is based on URL construction, is quite powerful.

Dealer Diagnostics figured out how to make nice looking bullet charts using the API.

Because the Google API creates an image you can use in a standard <img> tag, you can integrate it into any reporting tool that allows you to construct HTML elements. For example, if you've ever played with "microcharting" in Cognos Report Studio, you'll find that you'll spend more effort than it's worth fine tuning the charts. In some cases, it would be easier to simply use the Google Charts API instead.