Clickstream

Using Excel for Graphing

Most business intelligence products I've worked with do really simplistic graphing, decorated with a lot of useless [chart junk]. I often find myself pulling numbers and feeding them into Excel for better manipulation and graphing. Your typical BI tool can't automatically handle some incredibly useful charts which I learned years ago in Total Quality Management training and find myself reusing every year.

One of these is Shewhart control charts. These were originally aimed at dealing with variation in manufacturing processes. The basic idea of this chart is to show the normal operating boundaries of a process. You have upper and lower control limits as two lines, and your measurements as a third line that is (hopefully) between the other two. The control limits show the expected variation of measurements, which means that you can ignore your line if it bounces between the two, but if your graphed measures go above or below the limits you have a real problem.

The same numbers underlying the charts can be used as event triggers to notify someone that something may be a problem. I've used this technique with manufacturing data, with warranty data and I'm mulling it over for managing perishable inventories.

No BI tool I've seen knows how to do this kind of chart. You can fake it by creating metrics for the control limits and then graphing all three, but this isn't as easy as it sounds due to the calculations involved. It's much easier to extract the measurements from the data warehouse, generate the control limit data, and graph that.

Overall, I've been underwhelmed by the ease and functionality of charting in Excel. I use it because it helps do the calculations and graph the data more easily. Then I came across a few really helpful articles on exactly these topics.

The first is from an old issue of Quality Digest and shows how to set up Shewhart, pareto and box and whisker charts pretty easily. This, with the information from a recent article on making charts in Excel look the way you want provide all the information needed to use these techniques and do a good job.

I particularly liked the InformIT article on graphing support in Excel because it dives down into some of the technical details, showing some simple techniques and useful VBA that can be applied to really work the Excel chart engine. You can set up auto-expanding charts, multiple chart types on one chart and even charting functions. Worth a read if you have to prepare data for others and want to communicate it effectively.

Posted by Mark Friday, March 25, 2005 1:57:00 PM | permalink | (0) comments

Microsoft Office as Part of the BI Tool Layer

Every organizations uses spreadsheets to produce or present numbers, with the most popular being (of course) Excel. This is a fact of life and it's nice to see the BI vendors are finally realizing that part of their role is to provide a conduit for information, even if it means they don't own the final presentation layer.

I've been looking at a few to see how they work, and how well they do or don't hide the underlying data warehouse infrastructure. The latest releases are doing a much better job of this.

Instead of exporting to Excel or copying and pasting charts into Powerpoint, the integration now works fairly well. You can set the data to auto-refresh when you open a presentation, or tie a report into a tracking spreadsheet and refresh the numbers monthly. No more pasting data and moving formulas around manually to do the job.

The two that I've looked at a little more closely, since I use one and I'm considering the other, are Microstrategy Office and Business Objects Live Office. The thumbnail link to the screenshot (above) is for Microstrategy, showing the pull-down menu for reporting within Excel.

When Business Objects unveiled this at their annual conference, it highlighted some of the complexity behind the scenes. When Bernard Liautaud (the CEO) did a demo with Powerpoint he talked too long and the connection to the BI server timed out. Because you still have to maintain the login information in the BI environment, it's not a seamless integration to the user. They are still required to log in to the BI tool when getting data via Excel. If you email that spreadsheet to someone who isn't in the system, they can't refresh the data and in some odd edge cases, can't view the data that's already there.

Applaud the CEO who demos beta code at a conference. He was zero for three at this one. After the login timeout and a bug, he showed the new dashboard software. It's so easy an executive like Bernard can pull metrics and set up the format desired with a few clicks and drags! It was only a few clicks and drags, but this is web-deployed. Whoever designed the software disabled the back button in the browser and didn't provide any links off that page. It was like watching a mastodon sink into a tar pit.

More executives should demo their companies' products.

Posted by Mark 12:33:00 PM | permalink | (1) comments

Blogging at IT Toolbox

I started blogging for IT Toolbox today. I'm initially filling it up with entries from here so I have some content available. There will always be significant overlap, but I'll probably be splitting some posting between the two. Why the name "bounded rationality" for that blog, you ask? Was it because, in working with complex technologies, we are always limited in the options we can consider? Actually, it's because I was thinking about Herb Simon, and thought I'd give him a kudo.

Posted by Mark Wednesday, March 16, 2005 10:47:00 PM | permalink | (0) comments

To Evil!

I've been reading the backlog of Danny O'Brien's "To Evil!" column on OSdir.com and enjoying his findings of evil across the net. For example:

"They say that all that's required for evil to triumph is for good men to do nothing. If only it were that easy. I spent October eating nachos and playing Kingdom Of Loathing in my duvet, yet still no sign of several-headed whores of Babylon waving triumphantly from Buckingham Palace's balcony. Evil must try harder.

To give it its due, though, the forces of darkness did have quite a bash in September. Join me now as I throw solder flux on the votary candles, and invoke thrice this month's nominations for Evilness Incarnate (IT Department)."

Leave it to the hive mind that feeds BoingBoing to point out interesting things that I've missed.

And I forgot all about Kingdom of Loathing. I was a mid-level Disco Bandit, doing my disco dance of doom to all before I changed computers and forgot my login.

Posted by Mark 8:58:00 PM | permalink | (0) comments

The Big Blue Empire Retakes Ascential

A post on Ascential's fourth quarter financial analyst meeting has been in my backlog for a couple weeks, and now IBM made it a moot point. (If you were hiding in a closet somewhere and didn't hear, or if you don't pay attention to the ETL market, IBM re-acquired Ascential for $1.1B)

My original post was titled "Desperation at Ascential?" and had to do with their odd sales behavior during the fall of last year. They were heavily discounting deals, and I heard from several companies about offers that amounted to 100% discounts, selling based on license conversion and support costs only.

My initial reaction was that they were either desperate to make deals, or they were pushing for market share at the expense of margins. A third possibility offered up by a friend was that Ascential saw Informatica losing momentum, so Ascential was attempting to win deals on price and/or hurt Informatica's margins by driving down price. With the cash Ascential had in the bank, they could afford strategies like this, even if they might not play well with financial analysts concerned about margins.

My suspicion now is that they needed to make a threshold of new customers or market share as part of making the deal come off well prior to the IBM acquisition. We'll probably never know the truth. IBM has a way of making hostile takeovers look like agreeable buyouts to people who don't have good inside information.

The big question now is "what happens next?" Given IBM's abysmal track record in taking advantage of acquired technologies or keeping the product momentum, I don't hold out a lot of hope. Ascential was doing a terrific job of marketing, had the image of being the market leader, much needed product updates were in the works, and the financials looked decent.

My prediction is that they lose the momentum they had due to buyout turmoil, Informatica tried to pick up the slack, management at Ascential is disrupted while integrating with IBM management, and the products start to lag. Websphere has been IBM's focus for integration so we'll may see management of Ascential's products fall into that hole.

Maybe we'll all be lucky, and IBM will keep Ascential largely intact. People have said that about every company they acquire. Lotus Notes is the only product I can think of that's defied the curse of IBM acquisitions.

Update: I can't resist. The spellchecker wants to replace "buyouts". The question is, which sentence is better, the original:

We'll probably never know the truth. IBM has a way of making hostile takeovers look like agreeable buyouts to people who don't have good inside information.

Or the new spellchecked version?

We'll probably never know the truth. IBM has a way of making hostile takeovers look like agreeable buttocks to people who don't have good inside information.

Those with good inside information being proctologists, is my guess. What shareholder wouldn't jump at the chance to have agreeable buttocks for $18.50 per share?

Posted by Mark Monday, March 14, 2005 9:38:00 PM | permalink | (0) comments

InfoWorld Reviews Four Web Analytics Packages

There's a decent review of four web analytics systems in the February 21 issue of InfoWorld. The review covers Coremetrics 2005, NetIQ WebTrends 7.1, Omniture SiteCatalyst 11 and WebSideStory HBX On-Demand Web Analytics. I can't believe any company would name a product using that many syllables.

The products are more sophisticated than when I last looked at them. There is still one big failing though: they are all islands of data, and don't integrate well with either BI tools or internal data warehouses. They help with the many of the tasks involved in marketing and running web sites, but fall flat when you take into account the larger corporate environment.

The article offers a decent overview of the products and some highlights of specific features or failings. Don't expect to make any decisions based on the evaluation though. It's much too high-level for that.

Posted by Mark Tuesday, March 08, 2005 10:14:00 PM | permalink | (0) comments

Trailer Online for Hitchhiker's Guide to the Galaxy

Saw the trailer for the movie and it looks good, but then that's what trailers are for. If that has you reminiscing, you can play the 20th anniversary edition of the Infocom game at the BBC site. If you never encountered it, this was the fiendishly difficult pinnacle of Infocom's text adventures.

I first heard the Hitchhiker radio series in 1981 and I still have my original recorded-off-the-air cassettes. I'm leery of going to see a movie based on a book based on a radio series, particularly without Douglas Adams overseeing production. I'll end up going anyway. I went to the last two Star Wars films, much to my regret.

Posted by Mark Sunday, March 06, 2005 10:39:00 PM | permalink | (0) comments

A 50% DW Project Failure Rate is Nothing New

There have been a lot of articles lately about project failures in IT. The latest is this one about 50% of DW projects being "doomed", based on a Gartner study. There's nothing specific to data warehouses about this failure rate. The stats haven't changed since I got my degree from the Software Engineering Institute, an organization specifically created to help make software development into more of an engineering discipline.

The reason the stats haven't changed is that people keep making the same mistakes, the biggest being blind trust that more technology is somehow going to solve the problem. Fred Brooks wrote "No Silver Bullet" to explain that new technology won't change the failure rate.

Let's look at some of the statistics from a collection of project failure stats (I can't find the original source for these):

CHAOS Report: According to the Standish Group's 2003 CHAOS Report, an analysis of 13,522 IT projects shows that 15% ?failed? and another 51% were considered ?challenged.? While the overall failure rate is down significantly from a 1994 level of 31%, 82% of projects now experience significant schedule slippage and only 52% of required features and functions currently make it into the released product. Source: The Standish Group.

Hackett Group: Thirty percent of all application projects lasting more than a year ?failed to meet business requirements,? a Hackett Group survey found. ?This failure rate would be untenable in virtually any other corporate function.? Source: The Hackett Group, ?2003 Profile of World-Class IT."

Kweku Ewusi-Mensah: A 1994 survey of 82 Fortune 500 companies by Kweku Ewusi-Mensah and Z.H. Przasnyski found that 44% of respondents had experienced ?total abandonment? of an IT project and another 16% had experienced ?substantial abandonment.? Source: Kweku Ewusi-Mensah, Software Development Failures.

KPMG: A 1995 KPMG study of 120 IT organizations in the U.K. found that 62% had encountered ?runaway projects? that either failed to achieve their objectives or experienced cost overruns of 30% or more. Source: A. Cole, Software World (UK).

OASIG: Another U.K. study by the OASIG special interest group in 1996 reported that 40% of IT projects fail or are abandoned, and 80% ?are delivered late and over budget.? Source: OR Newsletter, ?Why Do IT Projects So Often Fail?"

AMR Research: A 2002 study by AMR Research of 80 premier reference accounts of the top 12 CRM vendors found that 12% of their CRM projects failed to be implemented, 47% had significant end-user adoption problems, and 25% provided no definable advantage over the systems they replaced. Source: AMR Research, 6/03.

Every IT manager should be required to read Fred Brooks' writings before being allowed anywhere near an IT project. "The Mythical Man-Month" was written in 1975 and is still largely applicable today. This should be first, with "No Silver Bullet" a close second.

Posted by Mark Saturday, March 05, 2005 5:58:00 PM | permalink | (0) comments