Using existing SAP technology to integrate your live BW Query data within Microsoft PowerPoint helps you avoid extra work. You can achieve this direct data connectivity without the need for third-party software. Instead, you can use basic Microsoft Office functionality with the SAP NetWeaver BW Object Linking and Embedding Database provider, which is standard with SAP GUI 7.10.
Key Concept
Direct SAP NetWeaver BW data integration reduces redundant work. For example, instead of copying charts or pictures into your enterprise presentation each month, you just have to refresh your build in the data sheet by using SAP NetWeaver BW queries.
Most of us have had to present company data using Microsoft PowerPoint — or have had to prepare such presentations or the screenprints for such presentations. Chances are, you ran a BEx query, aligned a specific chart to that data, and copied the chart into the designated PowerPoint presentation.
Have you ever calculated how many hours per year you or your colleagues spend with copy-and-paste actions for such presentations? I bet that it is quite a lot more than just a single day of work, despite the fact that it is a mind-numbing task to copy and paste over and over again.
I show you to how to implement a PowerPoint presentation that directly loads its data from SAP NetWeaver BW by using a query. The process I describe helps you to define a presentation once and have the presentation adopt its charts by just a simple refresh of query data.
To follow this process, you need:
- SAP BW 3.x or SAP NetWeaver BW 7.x
- SAP GUI 7.10 installed at the client PC to use for the PowerPoint data integration
- Experience with SAP BW 3.x or SAP NetWeaver BW 7.x query builder
- Microsoft Office: I used Office 2007 in my example. The functionality is available with older versions of Office, but the screens might look a bit different.
The Data Connection
The first thing you do is establish the connection between Office and SAP NetWeaver BW. You can do this either directly within PowerPoint or from any other Office component because all the components share the data connections once established. I explain how to set up such a connection with Excel because as an added benefit, you can also use Excel pivoting to analyze your data in addition to the PowerPoint integration. PowerPoint internally uses an Excel data sheet to hold the data it uses for its charts, so later you just update that hidden Excel sheet.
Note
You do not use BEx Analyzer or any other SAP application to connect and dynamically use SAP NetWeaver BW query data within Excel or PowerPoint. All you do is use the Object Linking and Embedding Database (OLE DB) data connection that is installed with your local SAP GUI.
To dynamically integrate SAP NetWeaver BW data into Excel, just start Excel. Select the Data tab to connect to an external data source. Use the From Other Sources entry and select the From Data Connection Wizard entry (Figure 1).
Figure 1
Display the Excel data connections
The next dialog prompts for several data sources. Select the Other/Advanced entry (Figure 2). Click the Next button to continue.
Figure 2
Data connection wizard data sources
The next screen asks you for the type of OLE DB provider you want to use (Figure 3). Scroll down to the last entry, which should be the SAP BW OLE DB Provider entry.
Figure 3
Available OLE DB providers
Select the entry and click the Connection tab in Figure 3. This leads you to the connection settings for the OLE DB provider (Figure 4). In the Data Source and Location fields, either enter the IP of your SAP NetWeaver BW server or the server name. Deselect the Blank password option and provide your SAP NetWeaver BW user name and password. Providing the password at this point allows you to connect to your SAP NetWeaver BW system for test purposes. In addition, you indicate that this connection requires a password for authorization to SAP NetWeaver BW. Testing the connection helps you find connectivity problems at the first phase of data integration. Once the test succeeds, you can be sure that the connection settings you provided are valid and will not cause problems at later pivoting functionality. You can test your connection to your local SAP NetWeaver BW server by clicking the Test Connection button.
Figure 4
Specify the connection settings
In Figure 4, the Allow saving password option is selected by default. If you use this option, your password is saved with your user name in the connection parameter file, which I do not recommend. Deselect this entry to ensure maximum security.
After you finish testing your connection, select your SAP NetWeaver BW system from the SAP dialog in Figure 4, enter your credentials, and click the OK button (Figure 5). After successfully connecting to SAP NetWeaver BW, you see the success dialog from Office (Figure 6).
Figure 5
SAP NetWeaver BW logon screen
Figure 6
Successful connection with Office
After entering your credentials, the system asks you to select a query or InfoCube that you want to deliver the data. If using a query, make sure that it accepts external access by using the BEx Query Designer option.
Tip!
Query access to Excel or other Office components such as PowerPoint only works when you select the Allow External access to this Query option in the BEx Query Builder settings. You can find this setting in the query properties in the Advanced tab.
In my example, I selected InfoCube 0CD_C01 (Figure 7). All InfoCubes use the prefix $ to identify direct access to the InfoCubes while queries are displayed as InfoCube name/Query name, as shown in Figure 8.
Figure 7
Display the selected InfoCube
Figure 8
Display the selected query
I’m using the InfoCube, so I select it and then click the Next button to finalize the data connection from Office (Excel) to SAP NetWeaver BW. In the next screen, you can name your Office data connection (Figure 9). You can also use the description field to enter more information about the connection — such as This connection delivers sales data.
Figure 9
The final connection dialog with description options
Done! You just established a connection between Office and SAP NetWeaver BW without using BEx. This connection is now available in all Office components — including Excel, PowerPoint, and Access.
To choose this connection, click the Existing Connections button in Figure 1, which displays the connection name in Figure 9. After selecting the just-created entry for an existing data connection, the system prompts you for the insertion range for the SAP NetWeaver BW data (Figure 10). I selected the PivotTable Report option so I can use the data flexibly.
Figure 10
Import Data dialog
Click the Properties button to define further settings, such as the maximum number of records to retrieve from SAP NetWeaver BW (performance option) or if SAP NetWeaver BW formats (e.g., number format) should be displayed in Excel (Figure 11).
Figure 11
Display properties for the data connection
Next, click the Definition tab to define where to store your connection file (Figure 12). This information allows you to easily exchange the connection files (Data Sources) among your colleagues. Just copy and paste the files into your local profile, such as:
C:Documents and SettingsYOUR_USERMy DocumentsMy Data Sources SD_C01 $0SD_C01.odc
Figure 12
The connection file directory information
Tip!
Storing the connection file on a shared network drive allows all users in your network to access that data. For security reasons, make sure you do not store your user ID and password with the file properties.
At this point, I have defined a data connection and can use SAP NetWeaver BW data in Excel. Figure 13 shows the resulting Excel pivot report — which actually works a bit faster than native BEx reports.
Figure 13
The Excel pivot report with an active SAP NetWeaver BW data connection
Use the Data Connection within PowerPoint Presentations
Now I will show you how to integrate the selected InfoCube data into a PowerPoint presentation. Open a blank PowerPoint presentation and insert a chart into a new slide. In this example, I use a pie chart because it best reflects my data. You can use whatever chart works best for your data. To assign your SAP NetWeaver BW data connection to this chart, right-click the chart and select Edit Data (Figure 14).
Figure 14
Edit the data in your PowerPoint chart
Next, align your defined data connection with PowerPoint’s designated data area. Your screen should now be split like the one in Figure 15, with the data range selected in Excel automatically.
Figure 15
Split PowerPoint screen with the data selected
In my example, the data in the Excel file indicates five rows and four columns. You need to replace this placeholder with your live data. First, extend the range (using the lower left corner of the blue frame in Figure 15) to the maximum number of rows you expect to need. Select all cells and delete the entry of the selected cells. Your data range should be empty and the presentation chart should not show anything. Click the Data tab in the connected Excel file (Figure 16). Then click the Get External Data button to connect to the Existing Connections area.
Figure 16
Connect to the Existing Connections
The data connection you previously created appears when you click Existing connections. In my example, I selected the data from InfoCube 0SD_C01 (Figure 17).
Figure 17
Select a connection
The system prompts you for a cell range in Excel and then asks for your SAP NetWeaver BW credentials to log on to your system. This is why I started with Excel — you are familiar with what to do next.
Point the data to cell A1. The system gives you the warning in Figure 18 to inform you that you are overwriting the existing destination content. Ignore the warning and click the OK button.
Figure 18
Excel data file warning
You are now ready to define your pivot table. In my example, I selected a key figure (sales order items) and a characteristic (0COUNTRY) to define my data. The chart automatically pulls your SAP NetWeaver BW data. To avoid displaying the grand totals (the default setting with Excel pivot tables), right-click your pivot table and select PivotTable Options. Then deselect the grand totals for rows and columns (Figure 19).
Figure 19
Turn off the grand total rows and columns
Done! You just finalized your SAP NetWeaver BW data integration with PowerPoint. Instead of displaying fixed data, PowerPoint now displays the live data from SAP NetWeaver BW (Figure 20).
Figure 20
PowerPoint with live data from SAP NetWeaver BW
Save your PowerPoint file to save the data connection to SAP NetWeaver BW for the next presentation. To refresh your presentation, refresh the underlying SAP NetWeaver BW data by right-clicking the chart, selecting Edit data, and clicking the Refresh button in the pivot table menu bar.
When you click the Refresh button, the system prompts you for your SAP logon information. After successful authorization, you need to accept the new data coming from your data connection as shown in Figure 21 (your dialog might vary due to name and destination). After confirming the data connection by clicking the Yes button, the data in your PowerPoint presentation is refreshed with the actual SAP NetWeaver BW data.
Figure 21
Dialog to accept the data connection
Another way you could refresh the data is to use a macro. Just add a refresh button to your presentation and assign a macro (you need to define the macro first) that carries out all the previous steps automatically.
Office 2007 does not offer a macro recorder button in PowerPoint anymore. Use the keyboard commands Alt-T, M, R (all in sequence) to start the macro recorder in PowerPoint 2007. Use the same keyboard combination to stop recording. You can find information about how to create a macro within PowerPoint 2007 in MSDN.
Joerg Boeke
Joerg Boeke is an SAP NetWeaver BW solution architect and senior consultant working with BIAnalyst GmbH & Co.KG, with 19 years experience in SAP NetWeaver BW, having worked on it since SAP BW 1.2A. He offers significant expertise in the SAP NetWeaver BW reporting area, including design, data integration, data visualization, performance optimization, and the cleanup of existing SAP NetWeaver BW systems. He is the author of SAP BW 7.x Reporting - Visualize your data.
You may contact the author at Joerg.boeke@bianalyst.de.
If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.
Premium
