Data and the Web

I just read a pretty interesting article by Neil Raden called “Business Intelligence 2.0: Simpler, More Accessible, Inevitable” (HT: Snaplogic) and would recommend giving it a read.

Historically, business intelligence hasn’t been all that its cracked up to be. Very expensive data warehousing systems are put in place. Existing reports are re-created and all kinds of new objects/reports are added. Everyone is thoroughly trained on the system. Pretty 3D graphics are added to the dashboard. The project goes over budget. Users revert to using Excel.

Some would say that BI is just a fancy way to do organizational reporting. There’s a lot of truth to this; why else do people continue to rely on their spreadsheets when they need to do some quick and dirty analysis? I think the answer is that there is a substantial ad hoc component to the “intelligence” part of business intelligence that will never be captured by a large, centralized system.

Having a few BI gurus setting up reports for everyone just isn’t an efficient use of resources. Nor does it capture the collective brain power of the organization. And there is quite a bit of this power ready to be tapped, even in the deepest corners of a company.

For example, we’ve done a lot of work with folks in the accounts payable industry. AP is not what you’d call a very sexy part of the organization — however, billions of dollars flow through it each year, as the keepers of the company checkbook. There are efficiencies to be gained, analyses to be done and, in our experience, a whole slew of people eager to do a bang-up job. However, when an AP manager needs to get something from the legacy system or just wants to create a new type of report they have one of two options — either go to IT and hope they can get a report created within the next couple weeks or go to mattresses with Excel/Access and do what they need to do themselves.

Neil echoes this when comparing BI 1.0 to BI 2.0:

BI 1.0 Fallacy: Most users want to be spoon-fed information and will never take the initiative to create their own environment or investigate the best way to get the answers they need.

BI 2.0 Reality: The Consumer Web invalidates this idea. When given simple tools to do something that is important and/or useful to them, people find a way to “mash up” what they need.

We’ve seen people’s initiative on display time and again and are really happy that Kirix Strata is playing a part in making this type of ad hoc analysis, integration and reporting easier than ever.

So, give those articles a read and see what you think. Also, please consider joining us on Wednesday at 1 PM EDT for our free hour-long web seminar with Snaplogic called “BI 2.0: Data Visualization and Spreadsheets on Steroids.” All the pertinent details can be found here. Hope to see you then!

During our journey from the MySQL Conference last week to the Web 2.0 Expo this week, we’ve continued to see a common pattern. There are a tremendous number of back-end business systems and almost all of them seem to rely on the humble Web browser for the front-end.

Now there is a very good reason for this — the Web is a tremendous distribution platform and companies are taking advantage of the same protocols and tools on their intranet that have already propelled the growth of the Internet. And, bottom line, browsers are really, really easy to use.

Data Integration 2.0

As an example of this situation, we met some folks last week from Snaplogic. Their product makes it really simple to integrate data from back-end systems and enables you to access data from anywhere, mash it up and publish it. However, when they were demoing the software, there wasn’t particularly much to show off, since a normal browser can’t do much with the data except let you “Save As” to your desktop.

Thankfully, this type of front-end work is perfectly suited for a data browser. So, we decided to demo each other’s products at the Web 2.0 Expo this week. Snaplogic is able to easily show the value they bring to the table by actually displaying the data integration they can do. We put together a quick dashboard with Snaplogic data and were able to show off all the cool stuff you can do once you’ve got a data feed to work with. Indeed, this was like the serendipitous combination of chocolate and peanut butter.

The Last Mile

This reminded me of a great metaphor used in a post by Juice Analytics called “The Last Mile of Business Intelligence.” The idea is that businesses have built lots of large back end systems — from data integration and warehousing to BI and reporting systems. But, in the end, it still seems that actual users are left out in the cold. The “last mile” between the heavy machinery and the actual knowledge worker has not been adequately bridged.

So unfortunately, the typical pattern involves working with a back end system via a Web browser and then, ultimately, exporting a CSV file to work with in Excel.

This is not terribly efficient. A data browser enables you to open up those same intranet pages and perform your normal browsing tasks. However, because it’s a data analysis tool at heart, you can actually open up data sets and start working with them directly. Then you can do fun things like bookmark tables, build calculations, create reports and write queries and have it refresh the next day with the latest data. Because of this, it plays very nicely in the worlds of of exploration and reporting per Juice’s “data value framework.”

Why Incorporate the Web?

In the past we’ve shown off some of the fun things Strata does — like open up HTML tables natively or showing RSS feeds in a table view — but often we’ll get a response like, “that’s cool, but what do I do with it?” There is no question that tools like these can be very useful for some tasks (e.g., integrating data from the web with local tables), but they’re really just icing on the cake.

The important thing is how tightly the web is integrated with the data analysis tool. This opens up all kinds of possibilities for direct interaction with both the Web infrastructure and the database systems that power business intelligence in the enterprise. Add some Web APIs into the mix, and now you could have access to the contact information in your ACT database, the corporate newsletter contacts in a MySQL system and all the recent sales information from your Salesforce.com account. Explore and integrate at will.

Given that the meme “Enterprise 2.0″ resounds louder each day, we should only expect to see Web infrastructure reaching deeper and deeper into organizations. In the end, maybe part of the BI “last mile” solution is just giving businesses a browser with a bit more intelligence.

If you haven’t checked out what the fine folks at Many Eyes are doing with “community” data visualization, it is well worth a peek. I took a look at one of their recent blog posts today regarding the new map visualizations they are offering. Very nice stuff indeed.

However, the thing that really caught my [many] eye[s] was the mention of a data set denoting “the regional slang for those odd little bugs that curl into balls.” This is definitely not something that keeps me up at night, but I’ve always wondered about the monikers of these benign little creatures. I grew up calling them “pill bugs.” However, probably due to some deep psychological desire to be accepted in elementary school, I eventually started referring to them as roly-polies, since that is what my friends called them.

I dug up the visualization in question and was pleased to see that I probably wasn’t the only kid in Chicagoland that may have struggled with these entomological naming conventions — in fact, there are a bunch of other names given to these li’l guys across the US. For posterity, Illinoisans call this thing a Pill Bug, Roly-Poly, Potato Bug, Sow Bug and Doodlebug 15%, 41% 7%, 6%, and 3% percent of the time, respectively.

The one downside that I’ve encountered with Many Eyes is they only seem to provide the underlying data set in a .txt file (albeit in tab delimited format). Strata doesn’t yet recognize the tsv-in-txt’s-clothing just yet, so you need to save the file to the desktop and then open it up in Strata (selecting a text-delimited file type with a tab delimiter). But, once you get it in there, fire up your analytical skills and manipulate away.

Also, please report any bugs while you’re at it.

Edit: Per Wikipedia, there are a bunch of other common names associated with these insects, including my favorite, “cheeselog.”

This afternoon I was doing some analysis on our web logs and thought it may make for a good screencast and blog post. We currently use a combination of AWstats and Google Analytics for our web stats but are increasingly using Strata to dig deeper into the raw web logs for the more customized things that aren’t readily available otherwise.

Also, honestly, it is kind of fun to plow through almost a million records on your own. Hmmm, maybe I should get out more.

The topic of the screencast below are the search terms people enter to find things in our phpBB3 support forums. These terms are embedded in the “request” field of the apache logs and I couldn’t find a way to get them without digging into the logs themselves (NOTE: I wouldn’t doubt that there is some way to do this via a mod to phpBB or a filter in Google Analytics… but since I couldn’t find anything via a quick Google search, using Strata just ended up being a lot faster).

An example of a search string we’re dealing with is:

GET /forums/search.php?keywords=proxy HTTP/1.1

So the trick was to parse the search keywords out of the field and then group them together to see what people were searching for… and in turn give us the chance to improve our support area by targeting some of these search terms and expanding our documentation accordingly.

Hope this video proves helpful:

(And here’s an embeddable YouTube version…)

TECHNICAL NOTE:

I downloaded the Apache logs from the server and, due to the file size, decided to import them into Strata rather than open the file and work with it directly. To import your logs, go to Import, select text-delimited files, and then import as space delimited with quotation marks as the text qualifier.

TECHNICAL NOTE 2:

For posterity, here are the functions that were used in this screencast:

STRPART(string, section [, delimiter])
SUBSTR(string, start [, length])
CONTAINS(string, search string)
IIF(boolean test, true value, false value)

A friend of mine uses Microsoft Excel quite a bit and recently asked me what Strata can do that Excel can’t. This is a very reasonable question to ask.

In fact, as an avid spreadsheet user myself, Excel lets me do all kinds of great things with data like creating budgets or putting together various lists. I can use formulas to create instant calculations and change data on a whim to perform what-if scenarios. Excel even gives me a few “database” tools to use, like sorting and filtering.

However, the strength of a spreadsheet lies in its ability to handle unstructured data really well. When I create a budget, I’m happy to mingle a column heading, my data points and a sum/total in the same column — and Excel is delighted to let me do it (or, at least, so suggests Clippy). It is cell-based, so you can place data wherever you’d like without any concern.

The trouble comes when you start dealing with larger amounts of structured data. We’ve seen this issue a lot, particularly when working with corporate clients. Excel is the most familiar tool for ad hoc calculations, but when something comes up where a user is presented with 20,000 records (or millions), it gets a little more dicey. Often the only option is to start working with a desktop database like Access. Unfortunately, a desktop database can often be a bit too complex for someone who just wants to quickly use their data like they would with a spreadsheet.

This is where Strata can really help. At its core, it was built to solve the problem of data usability. Basically, we’re trying to give people the ability to handle structured data really easily, wherever they may encounter it.

Strata will happily take the tens of thousands or tens of millions of records and let you create calculations instantly across the entire column. Or, just like Excel, you can sort or filter your data, but do so across the entire data set with a single click. Of course, there are plenty of more “database” things you can do too (relationships, queries, reports, scripting, etc.), but the key is being able to quickly and easily use the data however you wish.

A pretty classic business issue came up in a forum post today. In this situation, Greg was trying to identify duplicate inventory items in a 63,000 record file. He created a calculation to remove some “noise” from the data, then he grouped it together and found out which ones were duplicated. From there, he could take the results and remove the duplicated records from the original database to prevent future processing errors.

This process would have taken all of a couple minutes to perform. With a spreadsheet, however, this would have been much more cumbersome because of the file size (it would barely fit in most versions of Excel) and the need to copy a formula over 63,000 rows. I’m actually not sure if Excel could handle the grouping function in the same way.

Excel is a excellent tool for unstructured data, but just wasn’t designed for the rigors of handling structured data. One of the many things Strata offers is an easy transition for folks needing to analyze larger amounts of structured data.

Do you have any data issues that seem to be pushing the scope of your spreadsheet? Let us know, we’d be happy to help.