Business Intelligence and Excel: Happily Married?

Are spreadsheets a good match with business intelligence systems entrusted with providing consistent, reliable insight? The latest BI-Excel integrations let you have your spreadsheet-based analysis and always-validated, up-to-date data, too. Which add-in, plug-in or native link is right for you?
BI, Excel and Planning

BI and Excel, then, still have their squabbles to settle. Add planning to the mix and it gets even more complicated. Excel is widely used for planning purposes. In preparing plans, people often start with actuals and develop forecasts from those actuals. Where do these actuals come from? A BI report! In the absence of a planning system, there is no better place to enter plan data than in Excel. Planners need Excel in the BI mix. It’s perfect for entering data, doing what-if-analysis or producing forecasts based on formulas. It only becomes a nightmare when someone has to consolidate, reconcile and version multiple plans. In other words, all the time!

All of these issues are at the heart of Microsoft’s major product release, Performance Point Server 2007. PerformancePoint attempts to take two of Microsoft's greatest assets, Excel and Analysis Services, and address a need that every organization has. While the BI market is still ripe for opportunity and growth, some executives and organizations don’t quite get the value of BI. But planning is something everyone does. Streamlining and bullet-proofing that process would seem a smart thing to do.

In this regard, all those actuals that planners currently get from a BI report reside in a specialized Analysis Services cube. There is no manual export from a BI report, then import into Excel.

Actual and plan presented within PerformancePoint
Click to enlarge in another window
Actual and plan data are presented within a spreadsheet as part of the PerformancePoint Planning application (as shown at right). All the what-if analysis that, to date, has been done in a disconnected way is integrated with a server-based model in PerformancePoint. If a manager wants to test the impact of increasing headcount, they can immediately see the impact on margin. Rules from the server-based model highlight margin in green if the change is good, and red if it is below target. Once satisfied with the plan, data within Excel is then submitted to the same Analysis Services cube from which it originated. It’s not the messy, manual reconciliation process that many companies currently go through.

In this way, Microsoft is attempting to marry the best of all worlds:

- Planners get their data in an interface they are most familiar with and in a tool that provides what-if analysis, a powerful formula language, and data entry capabilities,

- Information is stored centrally in an Analysis Services cube to ensure consistency and common business rules, and…

- BI users can access and analyze this information via their preferred tool, whether a report, dashboard, scorecard or Excel.

While all of this would sound like a match made in heaven, this addresses only part of the picture here: the Excel integration. Excel integration with Microsoft Analysis Services and Performance Point gives Microsoft much to brag about, but the same is not true of relational data. Excel, for example, cannot access the business model used in Reporting Services. As well, BI competitors have gone further in the integration of BI with PowerPoint, Word and Outlook.

Excel for Everyone?

While all this integration — from Microsoft and from leading BI vendors — bodes well for Excel users, not everyone is an Excel user. BI penetration has been deepest for power users but modest for executives and paltry for front-line workers. Providing these users with the right interface will be key in ensuring that BI becomes something for everyone.

In the past, BI and Excel were often viewed as separate, competing tools that interfered with each other. Excel could undermine BI’s credibility. Conversely, Excel loyalists often perceived BI as an unnecessary middle-man when it came to access to the data. With all vendors working on ways to improve the integration between the two, such integration can help eliminate data chaos and satisfy a powerful group of BI users.

Cindi Howson, Founder, BIScorecard , a Web site for in-depth BI product reviews Author: Successful Business Intelligence: Secrets to Making BI a Killer App Author: Business Objects XI (R2): The Complete Reference