You are here
Microsoft Office Excel 2007 as front-end to SQL Server 2005
Over the past years, Microsoft has been building –slowly but certainly- its Business Intelligence & Performance Management vision & product stack. Despite Microsoft's roots and its reputation for building user friendly end-user products, most of the work in the last 5-10 years was in the underlying architecture of the database, OLAP structures & ETL-layer. An often-heard criticism was that something was "missing” at the front-end to exploit the underlying, meanwhile decent & performing, BI components.
Excel 2007, as a component of Office 2007, has been marketed as a major step forward in addressing this lack of end-user functionality. After all, for most of the "information workers”, Excel has been a familiar solution since years. Allowing to access BI from this environment would indeed be a major step forward. In this Insight we explore whether Microsoft indeed achieves this "breakthrough” with Excel 2007.
Microsoft's BI/PM Offering
Let's start by introducing Microsoft's Business Intelligence/Performance Management suite. There are two key components – the back-end platform and the end-user tools.
Figure 1 : Microsoft's Business Intelligence/Performance Management suite
The Back-end BI platform - formed by SQL Server 2005 - is composed of four technologies:
Relational Database: a robust, scalable and enterprise-ready database platform.
Integration Services: ETL component used to move, cleanse, transform and integrate data between sources and destinations.
Analysis Services: Unified Dimensional Model, OLAP technology and advanced analytical features like Key Performance Indicators and data mining.
Reporting Services: reporting authoring platform and report publishing server.
The End-User tools and Performance Management applications are used to access and analyze the data stored on the BI/PM platform:
Reporting Services partially integrates at the front-end side as the platform that delivers the standard reports. It also includes Report Builder which allows business users to create simple ad-hoc reports.
With Excel 2007, end-users can use a familiar desktop product to query and analyze the data without learning new software.
Performance Point Server 2007 is an integrated Performance Management application, allowing business executives and end-users to monitor (dashboards and scorecards), analyze (typical OLAP functionality) and plan (budgeting, planning & forecasting and consolidation) key financial business activities & processes.
Sharepoint Server is the hub that brings all of the front-end delivery together. It's a tool for organizing and navigating the Reporting Services standard reports, Excel PivotTables, Performance Point reports, dashboards, scorecards and Planning models.
Analysis Services is a key component in Microsoft's BI/PM stack, with integration with Reporting Services, Excel 2007 and Performance Point Server. It's considered a best practice to access nearly all of your data – whether traditional OLAP, relational reporting or analytic application - via Analysis Services.
Analysis Services 2005
Central to MS Analysis Services 2005 is the UDM, or Unified Dimensional Model. It gives organizations the ability to model a variety of data sources – data warehouse, relational OLTP databases, flat files and web services – and come to an integrated, disconnected semantic view of all the data that is of importance to the enterprise.
In theory this means that you could build a UDM Cube without the intermediate step of building a data warehouse and eliminating the costly and time consuming ETL phase.
For operational reporting this is appealing. Since these are very often applications with low latency, they can additionally benefit from Proactive Caching in Analysis Services. This feature identifies new data in the transactional system and enables caches for uninterrupted high-performance querying while the new data is added to the cube. If you need real time data, you can even manage the UDM without having to explicitly build a separate MOLAP store. These options will off course only work if the transactional system has enough data quality, is integrated, and contains all the data you need.
For standard reporting, ad-hoc query and analysis you'd still need a data warehouse in between the data source and UDM Model to handle more complex data transformations, data quality and data integration efforts, to support historic data not covered in the operational systems, and to overcome the performance conflict of directly accessing data on OLTP databases.
Aggregations are crucial for a well performing BI platform. Aggregations are pre-computed and pre-stored summarizations of the detailed data in order to improve dimensional query performance. Analysis Services is able to offer this in a way that's cheaper and easier to manage than a relational database.
Furthermore, Analysis Services uses its own query and scripting language, MDX or Multidimensional Expression. MDX, far more powerful then SQL, is a true analytical language. It is able to define business calculations like Profit, Sales same period last year, and define sets like ‘Top 10 Customers' or ‘Worst Sold Products'. Additionally, MDX is able to define different aggregation techniques for non-additive and semi-additive measures. The fact that all of these calculations are stored on a central server, means that all business users – no matter what tool they use to access Analysis Services – will use the same formula.
Last but not least, Key Performance Indicators, Dimension attributes, drill through, writeback, actions and translations are very interesting features that can be defined in an Analysis Services UDM.
Finally, Data Mining components in Analysis Services provide predictive analysis functionality based on historical data.
Office Excel 2007
The real power for "Information workers” lies in the tight support of Excel 2007 for MS Analysis Services 2005.
Excel 2007 enables users to easily build PivotTables and -Charts that connect to Analysis Services UDM Models. It has become significantly easier than it was with prior versions of Excel. With only a couple of mouse clicks, the PivotTable Field List exposes a user-friendly metadata layer of Analysis Services to the end-user. Many of the advanced Analysis Services features – such as KPIs, drill-through actions and member properties are now supported.
This paragraph describes the OLAP functionality of Excel 2007 and discusses some strengths and weaknesses.
The Field List is crucial in the enhanced user experience. At the top you will find the items created in the Analysis Services UDM. At the bottom there are four different areas: Report Filter, Column Labels, Row Labels and values. The Field List makes basic OLAP interactions like drill across, nesting and slice and dice a simple operation.
Drill down/up is directly supported within the PivotTable report or –Chart by expanding or collapsing specific members. This can be done either by using the right mouse click menu, or by using the + sign next to the dimension names.
In the area of Exception Reporting, where critical values (e.g. sales amount does not meet the forecast amount) can be highlighted, there is extended conditional Formatting that discovers trends and patterns easily in the overload of information. This is illustrated in Figure 2.
Figure 2 : Conditional Formatting & Exception reporting in Microsoft Office Excel 2007
Nothing is foreseen in Excel 2007 to narrowcast the data (only the relevant sections of a report are shown). Top x, or 80/20 rules can be defined via conditional formatting, but these rules cannot be used to hide data from your analysis.
Although you can sort the detailed data and all of your subtotals in the PivotTable, by default no ranking functionality is foreseen.
One feature that is clearly missing as well is the ability to create calculated members/measures and sets within Excel itself (besides the ones already provided in Analysis Services). An excel add-in however, OLAP PivotTable Extensions, offers this functionality. Additionally, there is a workaround via OLAP formulas.
Excel 2007 has a new charting engine that provides better charts and visualization capabilities. Trends (comparisons between periods) for instance can be visualized using a multiline chart. This is illustrated in Figure 3. It's worth mentioning that Drill down is supported by clicking on values or dimension items in the chart.
Figure 3 : Multiiline Charting in Microsoft Office Excel 2007
Other interesting features
Besides the new/existing OLAP features of the Excel PivotTables, there are some other interesting reporting/formatting features that come with the new Excel release.
Typical analysis tools don't allow for much formatting. In this area, Excel 2007 is outstanding as there are many options to format the Excel PivotTable or -chart. You can for instance quickly apply a predefined or custom style to a PivotTable, and changing the layout of a PivotTable is also much easier to do in the new user interface.
Next, Dimension attributes are supported in PivotTables. Attributes let you see additional information about a dimension member (e.g. the address of the customer). You can choose to make them visible when hovering over a dimension member, or alternatively display them in the PivotTable alongside the dimension member.
Another interesting feature are the OLAP formulas. This feature "unlocks” the PivotTable, converts the dimension members, measure values and member properties into cube formulas/references and lets you take advantage of Excel's free-form capabilities. In this way you can add calculations by adding new rows or columns into your analysis.
The combination of Excel 2007 with MS Office SharePoint Server 2007, lets you to take advantage of Excel Services. This is a server-based version of Excel that is optimized for managing and sharing spreadsheets.
By providing centralized access to a spreadsheet to a specific audience or set of users, you can protect sensitive data by controlling viewing rights, and help ensure everyone is using the most current, accurate information. In this way you have control on what data is displayed, and you're able to maintain a single version of the Excel workbook.
This service also helps you interact with Excel Spreadsheets from within a web browser without client code. You can decide to publish only specific pieces of a spreadsheet. For instance PivotTables or an Excel 2007 "Scorecard” that tracks Analysis Services KPIs, can now be accessed by using an Office SharePoint Server 2007 portal and dashboard.
Finally, the Data Connection Library, also an Excel Services feature, enhances excel's connection-management capabilities by reducing the complexity to set-up and distribute a database or analysis services connection.
SQL Server 2005, including Integration Services, Analysis Services and Reporting Services support a large part of the BI needs of a business. When paired with the capabilities of Excel 2007, organizations enable the delivery of intelligence to the desktop of every end-user.
It's basically a very rich end-user tool for any "information worker” in the company.