Using Excel in the context of BW* is nothing new: (1) there is BEx Analyzer and (2) there is the native pivot tables inside Excel that connect via OLE DB for OLAP (ODBO) to BW. While (1) has mostly been the option of choice, option (2) has tended to be sidelined for the following reasons:
- it is still little known albeit possible since BW 1.2,
- Excel's pivot tables were really very basic and not a viable alternative to Excel add-ins - be it BEx Analyzer or add-ins created by our competitors,
- meta data integration between BW and Excel's pivot tables was poor: e.g. external hierarchies showed up as separate dimensions or units and currencies of key figure values were not displayed (see figures 2, 3 for examples of the situation with Excel 2003).
Interesting things have happened in the meantime and with Excel 2007 together with NW BI 7.0 SPS14 problems 2. and 3. have vanished. The goal of this blog is to make you aware of that (and thus to tackle 1.) and to show you that using native Excel 2007 on top of NW BI 7.0 is now a viable and additional alternative. See figure 1 for a simple example.
So what has changed? First of all, Microsoft has heavily invested in reworking pivot tables in Excel 2007: UIs and a large number of features that make it a viable BI client overall. When Excel 2007 connects to an ODBO provider it asks for the latter's abilities which it then exposes to the end user. Actually, this mechanism worked only with Microsoft's Analysis Services. With BW's old ODBO provider (and the associated MDX counterpart on the server side) abilities did exist but were simply not exposed at all so that a basic default offering applied (see example in figure 4).
Now, here is a list of things you should consider when you want to test this new offering:
- Make sure that the BW system that you want to use is on NW 7.0 SPS14.
- Make sure that you have the right SAP ODBO driver installed on your PC. Check OSS note 1134226 for details.
- Check the step-by-step guide Connecting to SAP BW with Excel 2007 PivotTables and ODBO provided by our friends from Simba. You find it in SDN or via Simba's homepage (www.simba.com).
- Check more details around the topic in this presentation.
Finally, I like to invite you to provide us with feedback. This blog has been kept short on purpose and there many more interesting little details to talk about. Overall, the nice message is that is now really hard to notice whether Excel 2007 is connected to MSAS or to BW. Let us know what kind of technical information you would like to see in SDN on this topic.
PS: Regarding the many questions on the non-unicode problem (see comments below) please check OSS note 1173537.
* BW is now officially labeled NetWeaver BI (NW BI). As BI is also frequently used in its general, non-SAP-specific sense we use BW in this blog.
Figures referred in the text. Click on them to enlarge.
| Figure 1 | Figure 2 | Figure 3 | Figure 4 |
|---|---|---|---|
 |  |  |  |
37 Comments
