Larry Sackett, president and CEO, E.J. Barry Company, is the author of the SAP PRESS book MDX Reporting and Analytics with SAP NetWeaver BW. This tip about the use of OLAP formulas is taken from an article he wrote for BI Expert in January 2010. In “SAP NetWeaver BW and Xcelsius: Excel 2007 PivotTables Provide Enhanced Interactive Reporting and Analytics Options,” he explains how to use Microsoft Excel 2007 PivotTables with SAP NetWeaver BW and Xcelsius to create a low-cost but highly functional reporting and analytics system.
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 providi ng 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 1 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:
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 1 Convert a PivotTable to an OLAP Formula