Chart Porn Part 2: This is Hans' recent video with more numbers

Chart Porn - Hans Rosling's Awesome Visualizations

Dr. Rosling discusses why calling it the third world or a developing country is not positively contributing to a positive discussion on world affairs.  He uses some amazing visualizations to prove his point:





Also, this visualization is free to use.  Dr. Rosling has made it available here:

http://www.gapminder.org/

Find the Right Metric: The NYPD and Prostitution

Looking at the wrong thing to achieve goals is more dangerous than having no goals.  When picking KPIs for executive dashboards, it's critical that we choose wisely, and that we're prepared to change.

So often, once dashboards are created, they seem to be set in stone.  When an executive says a metric is important, it is sometimes difficult to speak up against them and say that perhaps we're looking at the wrong thing.   If we're somewhat flexible on what we see, and we're willing to change in the future, it provides a safe place where mistakes can be made.  

The New York Police Department will often search suspected hookers to see if they are carrying an abnormal amount of condoms.  If they are, they use that as proof against the suspect that they are a sex worker.  Although this seems to satisfy the metric that prostitutes be arrested and off the street, it ignores the bigger goals of public health and safety.  It incentives women to not carry protection, thus spreading disease and sickness through the population.

The NYPD is looking at the wrong metric.  I suppose this is the risk when you use law enforcement to work on what is probably a primarily public health problem.

If call centers look at average call time, and they want the number as low as possible, because that means they are saving money, they might be looking at the wrong metric.  It might be incentivizing rude customer service, abruptness, and poor call etiquette.  Measuring customer satisfaction or absolute resolution time might be a better number to look at.  Perhaps low calls into the center will emphasis the quality of the product you're selling and how easy it is to use.

But mistakes will always be made when selecting metrics and KPIs.  The trick is to make sure that the technology and the business are both able to quickly and effectively change it when the mistake is realized.

Just as Important as Control

I've been reading "Making Sense of Behavior", by William T. Powers.  The book is about controlling human behavior, but one section really made me think about the importance of proper business intelligence and SQL reporting for an organization who wants to achieve ambitious goals:

==================
"On a twisty mountain road with cars coming around every blind corner you don't look away for even a second!  Because the general rule is that if you want to control something, you have to perceive it.

When you're filling your bath, you don't just perceive that the water looks hot, you specifically use your temperature sensors in your hand to report on the current temperature of the water because that's what you're controlling: temperature.

Perception tells us the current status of whatever it is we're trying to control.  Without that information, received continuously or at frequent intervals, we can't control anything.  Perception is just as important as action, for controlling."
==================

Reporting is not our only means at perception, but it is a powerful one.  And when organizations ignore effective reporting and business intelligence strategies, they do so at their own peril.   I have always felt that business intelligence was nothing more than delivering the right data to the right person at the right time.

Data Explosion - Great Story

I got this story from Scott Cameron of Hitachi Consulting, although I've heard it before:

The story of the man who invented chess can help illustrate the problem of data explosion.

The man lived in India, and according to legend his name was Sessa. The king of India was very impressed with the game of chess and asked Sessa to name his reward. Sessa's request was so modest that it offended the king: He asked simply for one grain of rice for the first square of his chess board, two grains for the second square, four grains for the third, and so forth, doubling the number of grains of rice for each of the 64 squares of the board. Of course, by the time the king's magicians calculated the total amount of rice needed to pay the reward, they realized that it would require a warehouse 3 meters by 5 meters by twice the distance to the sun to pay the reward.

The king gave Sessa a sack, pointed him to the warehouse and told him to go count out his reward - no rush.

When numbers increase geometrically (like they do in a data warehouse), they get large very quickly.

Feedback Loops - Donella Meadows

"Examples of compelling feedback is not hard to find. Suppose taxpayers got to specify on their return forms what government services their tax payments must be spent on....Suppose any town or company that puts a water intake pipe in a river had to put it immediately downstream from its own wastewater outflow pipe. Suppose any public or private official who made the decision to invest in a nuclear power plant got the waste from that facility stored on his or her lawn...Suppose the politicians who declare war were required to spend that war in the front lines.

There is a systematic tendency on the part of human beings to avoid accountability for their own decisions. That's why there are so many missing feedback loops."

She goes on to relate a story about Amsterdam:



"Near Amsterdam, there is a suburb of single-family houses all built at the same time, all alike. For some unknown reasons it happened that some of the houses were built with the electric meter down in the basement. In other houses, the electric meter was installed in the front hall.

It was discovered that the houses with the electric meter in the hall used 1/3 less energy."

She explains that this is because the feedback loop was available for all to see.

This is the main purpose of business intelligence. To provide a feedback loop that provides accountability at all levels of an organization.

Putting for Analytics

I often do my best thinking on a putting green that is conveniently located about half mile from my desk.

Recently, I was all lined up, taking a practice swing, and it was perfect, very smooth. My practice swings are usually pretty good, I remember and focus on all the technique I have learned from coaches, lectures, books, experience. I swing freely and effortlessly.

Things only seem to go awry when I put a ball in front of me. I stop swinging freely, I tense up, tighten my grip, and both my swing and the ball become erratic. I have spent years attempting to recreate my practice swing with a ball present, I am sometimes successful, sometimes less so, but I’m always a scratch golfer when I golf without a ball.

Why is there a difference between my practice swing and my real swing? I think it's because when the ball is there, I become so focused on performance that I no longer think about the process that gets me the putt I am after.

I have found the same to be true about a performance focused mindset in business intelligence. Companies spend much less time examining the fundamentals of what got them to where they are in the first place. While examining the reports of a standard company, all focus is directed on dissecting "What happened?" When companies are only performance-oriented, much time is spent analyzing sales by product, by territory, by sales person, by location, or by time of the year. Retail organizations look sharply at sales during "the season", while time and billing firms examine their "top performers" with a keen interest. But how much do these things impact the future? Regardless of what the answer might have been in the past, our current recession has made it very clear that the companies that are solid are those who focused on sound fundamentals, on the process. How effective is it now to examine December 2007 sales? Will those figures have any impact or accurate prediction on what sales will be like in December 2009? What about December 2010? It is likely that if companies continue to be overly performance focused, reactions will be tense and erratic.

I see examples of this behavior in the news, all the time. The latest government stimulus packages are very reactive, with little thought put into goals, impact, future. Wall Street is too results oriented. Every time a firm posts numbers that are shorter than expectations, there is a direct impact on stock price, which affects the longevity of that organization. As companies focus on current numbers, avoid creating a vision, and the process to implement it, they too compromise the future.

So I loosen up, I remember what I have been taught and what I have learned from experience. I know how to do this. I remind myself that keeping an eye on the ball is important...but I will never make it to the hole/goal if I lose my grip on the club.

Excel at Excel (Or My Quick Tips for MS Excel)

In this space, sometimes I'll offer strategic insight into the Business Intelligence space, and sometimes I'll offer helpful tips to the grunts* in the field, actually doing the heavy lifting of reporting, analyzing, and planning. These tips will cover mostly Microsoft products, since that’s what I specialize in. I have tips for SQL Reporting Services, SQL Integration Services, SQL Analysis Services, MDX, T-SQL, and most importantly, Microsoft Excel.

The major arrow of every analyst's quiver is Microsoft Excel. Without exception, it is heavily used at every single one of my customers. I won't give away all of my tips now. What would keep you coming back? Certainly not my charming personality...
Here are a few overlooked gems that Microsoft Excel offers you:

-By default, when you look at a spreadsheet, you see the results of the formulas, not the formulas themselves. You can manually set "Show Formulas", or you can just click CTRL-' (that a single quote.) Usually you can find that key just to the left of the 1 key on the top row of your keyboard.

-You can look at the same workbook in two different windows. You can keep "Show Formula" on one view of the workbook and the default results of the formulas on the other view of the workbook. This works great if you have multiple monitors, displaying each view on its own monitor.

-In Excel 2007, the status bar is better than ever. Highlight some cells and look at your status bar at the bottom of the screen. You'll see a count, sum, and average right in front of you. In addition, right-click on the status bar and check out all of your options. You'll be surprised at what you can view down there.

-Most people know how to save a workbook, but did you know you can save an entire workspace? When you work on those month-end reports, and you find yourself looking at the same four worksheets over and over again, save it as a workspace. The next time you work on month-end, just open up the workspace and those four worksheets will open right in front of you. It's hidden on the View tab of the ribbon. It's called "Save Workspace."

-If you want the same report to open each time you start your computer, just follow these steps. First, put a shortcut to MS Excel in the Startup folder of your Start menu. Then find the startup folder of MS Excel. Put your workbook in there. You can usually find it at "C:\Users\Ike(unless your name is not Ike, then just use your name)\AppData\Roaming\Microsoft\Excel\XLStart".

That's it for now. Keep checking back for more quick tips and hints.

*I use the word “grunts” with nothing but respect and fondness.

QlikView vs Gemini

Last week, at Microsoft's Business Intelligence Conference, I was excited to see that QlikView had a booth. There's been a lot of buzz about this little company. Gartner recently named them as a challenger in the BI space. They are positioning themselves as a BI solution that the business users control with very little IT involvement. As far as I can tell, here are their main selling points:

-No IT-built cubes. I can see why this is so attractive. Most contemporary business users see IT as their main stumbling block. Business users hate waiting so long to see business requirements finally make their way into the cubes. QlikView does this by creating something called "in-memory" cubes. Supposedly, you create the cubes using the interface and it stores them internally for you. All you need is access to the data warehouse.

-Lightening-fast queries. Because the data is stored in memory, response time is minimized and performance is increased.

-Intuitive user interface design. I didn't find it all that intuitive. It was OK, but I thought it was a little clunky.

Anyway, I think this company has some really neat technology, but it probably doesn't have much of a future on the Microsoft platform. Microsoft headlined Project Gemini at the conference. It seemed to me to be the exact same product, including using "in-memory cubes." They also used the phrase "empowering business users." That is the same phrase the QlikView booth guys said about their product. Microsoft demo'd Gemini and it looked exactly like MS Excel. They showed how they can build in-memory cubes, upload it into SharePoint, and share them with other business users. Microsoft said that Gemini was just another cube storage option. You’ll be able to choose between MOLAP, HOLAP, and in-memory cube storage. It was pre-beta (probably pre-alpha) and I think it spells bad news for QlikView. We’ll see how quickly Microsoft can get this shipped to us.

Here are some links for you:

http://www.qlikview.com/home.aspx
http://www.microsoft.com/Presspass/press/2008/oct08/10-06BI08PR.mspx

The Ideal Customer: One Person Who Calls The Shots

Further reading from Mr. Parmenter’s “Key Performance Indicators”, unveiled this nugget; “Any layer between the CEO and the [KPI] team indicates that Step 1 has not been successfully achieved. This point is so important that the project should not proceed if the CEO does wish to be involved in this way.”

Mr. Parmenter’s point is that the KPI project team should report directly to the CEO and no one else.

I was just talking to my oft-quotable, former business partner, Roy Allen. He said, “I only want to work with customers who have a single person who is responsible for the entire organization, and that person mandates what the other employees of that organization do.”

In other words, Roy likes decision makers who actually make decisions and he wants to report directly to that decision maker. He feels like that’s the only way to ensure success of his projects at that organization.

I don’t know about Roy’s projects, but Business Intelligence projects need a top-down mandate in order to succeed. I don’t always get to report to the CEO, but I love it when I do.

Don't Rush to Buy Software

I was reading “Key Performance Indicators”, by David Parmenter today, and came across an interesting passage. Mr. Parmenter says, “Much can be done with existing application such as Excel, PowerPoint, SharePoint Team Services, and Access. There is often no need to purchase specialized software at this stage. Any such purchases can be done more efficiently and effectively 12 months down the track.”

Wow, finally someone is speaking my language. Managing a business through effective KPIs is such a drastic culture change, that most organizations fail at it. Instead of introducing new tools and applications, organizations should really take a hard look at the software they already own and use. Once the KPI project is in place and a seamless part of the atmosphere of the company, then new and more effective tools can be researched.

Though Mr. Parmenter is a technology agnostic consultant, I find it interesting that he lists all Microsoft applications. I suppose he does this because it is safe to assume that most organizations already own these products.