Excel Services BI Goodness
I’ve always had a soft-spot for budgeting/forecasting applications. Show me a nice tight revenue/expense cube, a decent Excel-based client, and interactive browser-based reporting capabilities and I’ll show you a company with an efficient forecasting process. Well, I’ll show you a company with the capability to have an efficient forecasting process. Microsoft has been inching towards a BI stack that would offer this capability since Analysis Services was released with SQL Server 7. With Office 2007 they took the leap forward that I’ve been waiting for. Finally, I think they have a viable stack. One of the huge holes so far has been lack of a (thin) interactive browser solution. Cross that one off the list…
Check out the following screen shots I took of a demo I put together using a basic AS 2005 cube, Excel 2007 and Excel Services. Thin, reasonably interactive, and not a single line of custom code. My objective was to create a series of interactive financial reports that didn't feel like Excel pivot tables when you used them. Much to Excel 2007 and Excel Services credit, this was fairly easy to achieve. Anyone who has used Hyperion Essbase before would feel right at home using these reports - simply double-click on any item that you want to zoom in or out on.
First, your run of the mill budget variance report. You can choose your time period, organizational hierarchy point, and drill up/down on the various line items (i.e. revenue, expense, statistics).
In this picture, I just double-clicked on the Expense line:
You can click on the filters to select other items in the desired dimension. For example, in the next two shots, I’ll select Engineering to view their performance:
Of course, no real financial analyst can live without a sum of the pieces report such as the next report. By default, I set this one up to initially just show total Adventure Works in a single column. In this picture however, I’ve drilled in on Adventure Works to show each division in a column along with the total of the divisions. As before, show/hide detail by double-clicking on each row heading.
Finally, the requisite trend report:
Excel Services rocks! This is the type of functionality that my clients have been seeking...