A key design goal for the release of Microsoft Excel 2007 was to incorporate functionality from ProClarity Analytics, a BI tool that Microsoft acquired in 2006. These functions included an enhanced drag-and-drop PivotTable designer, recognition of multiple hierarchies, display of visual totals, and the ability to display member properties (SAP NetWeaver BW display attributes) in tooltips and on the columns of a PivotTable.

Microsoft incorporated a host of graphically appealing styles and layouts that you can apply to PivotTables or Pivot Charts. In addition, Microsoft greatly improved conditional formatting using color scales, data bars, and icons to highlight exceptions in data. Finally, Microsoft integrated MDX filters from ProClarity Analytics, such as finding the top or bottom x customers or identifying products with sales that exceed some threshold in a particular time period or region. The end result is that companies using Microsoft Analysis Services have a low-cost and powerful tool to analyze and report on data in OLAP InfoCubes. Excel 2007 connects natively to MS Analysis Services. Now Excel 2007 connects to SAP NetWeaver BW with the same functionality.

Simba Technologies developed the original MDX-based OLE DB for OLAP (ODBO) provider for SAP Business Information Warehouse (SAP BW). Simba upgraded the provider with the same features and functionality of Excel 2007 to analyze OLAP data in InfoCubes and BEx query InfoCubes. It’s next to impossible for end users to know whether they are analyzing data from MS Analysis Services or from SAP NetWeaver BW.

The icing on the cake is that SAP BusinessObjects’ data visualization tool, Xcelsius, uses Excel to bind data to interactive (Adobe Flash) charts, maps, gauges, and what-if sliders (Figure 1). There is no additional requirement for use of Xcelsius to analyze data in SAP NetWeaver BW via Excel 2007 PivotTables. Users only need basic Excel skills to learn to use Xcelsius.

Figure 1

Excel 2007 PivotTable and 3-D Pivot Chart

Let’s see how Excel 2007 PivotTables work.

Make a Connection

Excel 2007 PivotTables has a connection wizard that helps you connect to InfoCubes or BEx query InfoCubes. Excel 2007 replaced the traditional menus and toolbars with ribbons. Each tab of the ribbon contains a set of related tools. On the left side of the Insert ribbon is an icon for a PivotTable. Clicking the PivotTable icon launches a series of steps to make a connection to NetWeaver BW using the Simba Provider. Here’s an overview of the steps to connect to the DalSegno Company Reporting Cube, 0D_DX_M01.

When you click the PivotTable icon from the Insert ribbon, the Create PivotTable window opens. Select Use an external data source and click the Choose Connection button. This opens the Existing Connections window. If you haven’t already defined a connection, click the Browse for More button at the bottom of the window. The Select Data Source window opens; click the New Source button.

Select Other/Advanced in the Data Connections Wizard window. This opens the Data Link Property window, which shows a list of the available connection providers. The Simba provider for SAP NetWeaver BW is called the SAP BW OLE DB Provider (Figure 2).

Figure 2

Simba Provider for SAP NetWeaver BW

After selecting the SAP BW OLE DB Provider, click the Next button. You are asked to enter the Data Source, which is the name of your SAP NetWeaver BW server. Enter your User name, uncheck the Blank Password option, and enter your SAP NetWeaver BW password (Figure 3).

Figure 3

Enter the connection information

Then enter the name of the catalog to which you want to connect. The catalog can be an InfoCube, MultiProvider, or a BEx query InfoCube. Chose the $INFOCUBE catalog and click the OK button. You are presented with a list of database options (Figure 4). Select the DalSegno Company Reporting Cube, click Next, and then Finish. The Import Data window opens, with the default location for the PivotTable in cell $A$1. Click OK. An empty PivotTable appears along with the PivotTable Fields Selection box. From here, you can design your PivotTable, as shown in Figure 5.

Figure 4

Make a connection to the DalSegno Company Reporting InfoCube

Figure 5

PivotTable with a PivotTable Field List

A couple more wrinkles are involved in saving your password, which I won’t go into. Simba wrote an excellent overview of how to make a connection from Excel 2007 to NetWeaver BW and saving a password called “Connecting to SAP BW with Excel 2007 PivotTables and ODBO.”

Design a PivotTable

Microsoft has made it much easier to design a PivotTable and Pivot Chart. Figure 6 highlights a number of improvements in Excel 2007 PivotTables. The numbers in the list correspond to the numbers in Figure 6.

Figure 6

Steps to design a PivotTable

1. Making a connection to an InfoCube, MultiProvider, or BEx query InfoCube automatically populates the Field List. You can see that multiple hierarchies for Calendar Year/Month are displayed, as is an external Product Hierarchy. One of the most significant improvements in Excel 2007 PivotTables is that ability to display multiple dimensions in the Fields List.
2. Instead of dragging and dropping fields onto a grid, as in Excel 2003, users can drag and drop them from the fields list to the rows, columns, filters, and values section shown on the right of the PivotTable Field List dialog box.
3. Every time you drag a new field into the rows, columns, filters, or value fields, Excel 2007 executes an MDX query to the SAP NetWeaver BW system. You can turn this off by checking the Defer Layout Update check box at the bottom of the Field List. When you finish selecting fields, click the Update button to produce the PivotTable. You have to uncheck the Defer Layout Update check box if you want to make changes to the pivot table.
4. With the Product Hierarchy on the rows and the YEAQUAMON hierarchy on the columns, a user has the option to drill down and drill up from the levels of the hierarchy, shown with the plus (+) and minus (-) indicators.
5. Excel 2007 automatically provides grand totals for the columns — you can turn this feature on and off.
6. Populating the Report Filter section of the Field List places the selected dimensions, Region Code, and Distribution Channel as drop-down boxes.

Pivot Charts

Excel 2007 incorporates a new graphics engine that lets you choose from dozens of different chart types, styles, and layouts, as well as predesigned templates. Charts can include special effects such as 3-D, transparency, and soft shadows. Figure 7 shows an example of a 3-D bar chart with an attached data grid. Every element of a chart can be customized and you can create and save chart templates. Pivot Charts are fully interactive; drilling down on an item in a chart drills down the corresponding element in the PivotTable.

Figure 7

Interactive PivotChart

OLAP Formulas

One of the drawbacks of using PivotTables with other Excel functions is that a PivotTable is locked — you can’t insert rows or columns within the PivotTable. Microsoft added a new set of OLAP InfoCube functions (called OLAP formulas) that enable you to unlock a PivotTable and insert rows and columns by providing a direct connection to an OLAP InfoCube from each cell in a spreadsheet. Inserting rows or columns doesn’t change the connection or the MDX InfoCube meta data contained in the cells.

PivotTables can be converted automatically to OLAP formulas. Here’s an example. Figure 8 shows a PivotTable before and after converting it to OLAP formulas. Some interesting things are happening in this example:

1. Converting to OLAP formulas is as simple as a mouse click. You are asked if you also want to convert the filters. In most cases you won’t want to.
2. The filter for Region Code remains intact after the conversion, meaning that you can still slice the filter.
3. After conversion, each cell has its own connection to the InfoCube that points to a unique intersection of dimensions/members in the InfoCube space.

The spreadsheet cell C5 contains the following expression:

=CUBEVALUE("$INFOCUBE $0D_DX_M01",$B$1,$A$3,$A5,C$4)

It’s using the OLAP InfoCube function CUBEVALUE. The first argument is the connection string that you created to the DalSegno Reporting InfoCube. Each cell location in the following arguments contains a specific value in the InfoCube:

• Cell $B$1 contains the All Region Code member
• $A$3 contains the Net Sales measure
• $A5 contains the Bag & Outdoor product group member
• C$4 contains Calendar Year 2005

The use of relative cell relationships makes it possible to insert rows and columns without altering the contents of a particular cell. If you change the value of Region Code in cell $B$1 to Northeast, the CUBEVALUE expression changes to give you net sales in the Northeast. This is all done automatically as a result of converting a PivotTable to OLAP formulas.

Figure 8

Convert a PivotTable to an OLAP Formula

Excel 2007 PivotTables with Xcelsius

Xcelsius is an excellent tool for creating dynamic dashboards and data visualizations. It is built on two primary technologies: Excel and Adobe Flash. Excel is used to bind data to charts, text boxes, containers, and maps. Figure 9 shows a simple Xcelsius dashboard page with a bar chart, pie chart, what-if slider, and a gauge. You can connect Excel 2007 to SAP NetWeaver BW, create a PivotTable, and design a dynamic Web page, all from within the Xcelsius workspace. If you know how to use Excel, you have all the basic skills required to design Xcelsius presentations.

Figure 9

Interactive Xcelsius Web page

The combination of Excel 2007 PivotTables and Xcelsius provides end users with a self-contained environment for self-service reporting and analytics. PivotTables and Pivot Charts are easy to design and enable users to analyze large volumes of data quickly and efficiently. There is no additional cost to connect to SAP NetWeaver BW because everything required is part of the SAP NetWeaver BW license.

Xcelsius adds dynamic data visualizations of data in PivotTables in a format suitable for any level of an organization, from senior executives to managers. Designing Xcelsius presentations requires no programming skills; if you know how to use Excel, it’s easy to learn Xcelsius.