Small to midsized businesses (SMBs) often begin -- and sometimes end -- their quest for business intelligence with Microsoft's ubiquitous Excel. However, with an increasing number of add-ins -- not to mention a long look at what's up with Microsoft Business Solutions -- SMBs' options will multiply.
"Enterprise analytics" is the big buzz these days — especially in big enterprises that have the breadth of IT resources to think big. However, what about SMB analytics? How do organizations below $1 billion in revenue, and especially those in the $50 million to $500 million range, meet their analytic needs?
Put yourself in the position of a typical SMB manager. Your IT resources are limited, budgets are tight, and your employees don't have time to climb steep learning curves. Ideally, you want an analytic solution that's easy to install and maintain, inexpensive to acquire, and somewhat familiar to the people who'll use it. Not surprising then that an analytic solution that leverages Microsoft Excel is likely to be high on your list.
Anecdotally, Excel is considered the world's most popular analytic tool, partly because Excel ships with every copy of Microsoft Office: and Office is a standard component on millions of corporate desktops, whether Mac- or PC-based. Furthermore, a mainstay of business analytics is financial analytics; a recent Yankee group survey found that more than 11 percent of SMBs (in the U.S.) run all their accounting on Excel. Apparently, most of the rest use Intuit's Quicken and QuickBooks or offerings from Sage/Best Software. Some use Microsoft Business Solutions (MBS) and a few use online offerings such as NetSuite.
The alternatives to Excel analytics for SMBs often aren't that compelling: Pony up a lot of cash to get the benefit of an "enterprise-level" analytic suite that your business can grow into but might never use to its full potential. Or stick with the built-in reporting and analytics shipped with your accounting or other line-of-business application. Either way, you're between a rock and a hard place. Or you could go with Microsoft Data Analyzer, a proprietary OLAP front end that inexplicably doesn't run in Excel. Otherwise, Excel analytics using add-in applications offer the only viable alternative.
Let's face it, the biggest advantage Excel has in terms of SMB analytics is that it's there — on the desktop, that is. It's also paid for; most people are generally familiar with it, and some are already spreadsheet jocks. Either way, Excel covers the bases in terms of visualizing, manipulating, publishing, and sharing information. So it's a no-brainer to consider Excel as a foundation for SMB analytics (you can read more about Excel integration in Cindi Howson's BI Scorecard: Excel Integration, available online in the May 2004 archive).
Excel analytic applications operate as add-ins to Excel. They're usually easy to install and integrate fully into Excel's menu, help system, and worksheet/workbook paradigm. Most vendors sell Excel analytic add-ins targeted at SMBs for $299 or less, and they can be bought one at a time; you can download many from the Web. Conversely, vendors who target analytic add-ins at larger businesses usually expect multiseat licenses that push the acquisition cost alone up into thousands or even tens of thousands of dollars — partly required to fund a direct salesperson's visit to your business.
The availability of PivotTables, PivotCharts, and other functionality, as well as the ability to handle real-time data feeds (such as stock price tickers) has enhanced the potential of Excel as an analytic front end. Integration with Microsoft SQL Server Analysis Services "cubes" provides the kind of optimized, multidimensional data source that slicing and dicing analysts depend on. As SQL Server moves down the food chain and is shipped as the database engine for more SMB applications, the ability to better leverage data in SQL Server becomes an important functional asset of Excel.
What Add-Ins Do
Excel analytic add-ins adopt the look, feel, and functionality of their host and manage a wide range of tasks, especially if they're connecting to a multidimensional data source, such as an Analysis Services cube. Add-ins may provide general ad hoc "slice and dice" analytic capabilities, a specific or upgraded analytic function (such as improved data mining or scorecard visualization), or the ability to generate production reports from within Excel. A partial list of capabilities includes:
Selecting data hierarchies or dimensions within those hierarchies to construct meaningful data views (for example, selecting specific cities or salespeople within a territory hierarchy to help with sales analysis.)
Navigating the data presented by these views using drill down, drill across (from one hierarchy to another), or drill through (from the summary to transaction level data).
Visualizing the data presented by these views in the form of tables, hierarchy trees, 2-D and 3-D charts, scorecards, and top-n or bottom-n rankings.
Data write-back so that changes made to numbers during the analytic process are written back to the data source from Excel (for example, to increase or decrease budget numbers after they're compared to actuals)
Securing the data based on user roles so that people only see what they're entitled to see; also, using simple business rules to highlight exceptions in the data (for example, using a numeric threshold to change the color of the data if a value is above or below the threshold).
Creating metadata layers to allow business users to more easily understand, navigate, and select data in complex back-end applications (such as an accounting general ledger module).
Formatting information into content packages (reports and report packs) that you can email to other users, access over the Web, or simply share with other Excel users.
Join us for a roundup of the top stories on InformationWeek.com for the week of December 14, 2014. Be here for the show and for the incredible Friday Afternoon Conversation that runs beside the program.