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.

I'm Not Pete Rose

I went to Lake Havasu with my old business partner, Roy Allen, this weekend.
While we were out boating with our families, hanging out in the nice cool lake, avoiding the 120 degree heat, he said something pretty interesting.
He said, "You know your problem, Ike? You suffer what most of us suffer from. You are a terrible player-manager."

After I got over my shock, I asked him what he meant by that. He explained that there hasn't been a really good player-manager in baseball since Pete Rose, and even he wasn't all that great. He said that in a services firm, there are players (billers) and there are managers, and you can't be really good at both. You can stink at both, or stink at one, but being good at both is not an option. He explained that someone has to play the game and someone has to watch the players objectively, because only the manager will have good insight on how the game is progressing.

After some thought, I totally agree. And since I find consulting, teaching, and creating solutions so much fun, it looks like I'm going to need a full-time manager for the rest of the staff.

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.

Down the Rabbit Hole

At the beginning of Brian Larson's book, "Delivering Business Intelligence with Microsoft SQL Server 2005", the author quotes Alice's Adventure in Wonderland.

"Would you tell me please, which way I ought to go from here?" asked Alice.
"That depends a good deal on where you want to get to," said the Cat.
"I don't much care where," said Alice.
"Then, it doesn't matter which way you go," said the Cat.

The Cheshire Cat reminds us how important goals and direction are to leadership. All the immaculate data in the world won't help an organization if it doesn't have a clear idea on where it wants to go. Unfortunately, it is sometimes a chicken and the egg situation. Leadership has a hard time mapping direction when it doesn't have a clear idea on the current state of the company.

This analogy provides an excellent case on why monitoring projects should be implemented before planning projects.