With the SAP NetWeaver BW Connection, we can deliver SAP BW data to dashboards that are hosted on the same SAP BW system. With this setup we don't need a separate SAP BusinessObjects BI Platform to host the dashboards, and the connections to source systems.
To set up a connection between SAP BusinessObjects Dashboards and SAP BW, you have to make sure that your SAP installation fulfills the following minimal technical requirements:
- SAP BW 7.01, service pack 05 with ABAP and Java stacks deployed
- SAP BusinessObjects Xcelsius Enterprise 2008 Service Pack 02
- SAP Frontend 7.x installed at client with BI Add-Ons
For this recipe we are using a simple BEx Query to connect to.
- Open a new SAP BusinessObjects Dashboards file. Open the Data Manager and add a new SAP NetWeaver BW Connection.
- Click on the Browse button to log in to the SAP BW system. Select the correct SAP BW system, enter your login credentials, and click OK.
- Use the Find option to search for your query. Click Open to select the query.
- Go to the Data Preview tab and click the Refresh Data Preview button. A preview of the layout of the returned data will now be shown here. In our example, there are seven columns: four characteristics and three key figures.
- Go back to the Definition tab.
- Select Cross-Tab Data from the Output Values section and bind it to the spreadsheet. For each column in the Data Preview tab (step 4) you need a column in the spreadsheet.
- Go to the Usage tab and select the Refresh Before Components Are Loaded option. Close the Data Manager.
- Add a Spreadsheet Table component to the canvas and bind it to the same cell range as you did in step 6.
- If you hit the Preview button a message will appear stating that it is not possible to preview this dashboard with BI Query connection data and whether you want to continue. If you hit Yes, the dashboard will be previewed without live data from SAP BW.
- For this example, click No. The Publishing to SAP BW recipe in Chapter 9, Exporting and Publishing, covers the Launch function to deploy the dashboard with an active connection to SAP BW.
The SAP NetWeaver BW Connection enables us to retrieve data from a SAP BW system via a BEx Query. For this example, we used a simple BEx Query we created in the BEx Query Designer with four characteristics in the rows and three key figures in the columns. In the properties of the characteristics we defined the Results Rows as Always Suppress so the output won't include this row.
If we run this BEx Query, the result would be as in the following screenshot. The first row shows the headers for the three key figures. The second row shows the headers for the four characteristics. In this row the Unit information for the key figures placed (EUR) is also placed when available.
Tip
Running the original BEx Query is a great way to get an overview of how the data output will eventually look when setting up the spreadsheet. Moreover, if you are using multiple characteristics and key figures, this will give you a view that is clearer than the Data Preview tab in the Data Manager.
In this section, we will take a detailed look at the options for data input and output in the SAP NetWeaver BW Connection.
Just as we have seen how to use prompts in the Using Query as a Web Service (QaaWS) recipe, we can use BEx Query variables and filters to fetch a limited set of data by following these steps:
- Go to the Data Manager.
- In the Output Values area search for Variables and select Value Help for the variable you want to use.
- Bind Current Filter Value (Key) to a range of cells. In these cells the values you can choose from will be inserted.
- In the Input Values area select the variable and bind it to a cell. This cell should contain the value that the BEx Query uses as input for the variable.
- Now add any Selector component to the canvas.
- Bind its Labels field to the cell range you bound to in step 3.
- Set the Insertion Type to Label and bind the Destination field to the same cell as you bound to in step 4.
The procedure for filters is the same as for variables. The only difference is that in the Output Values area you need to select the Characteristics instead of the Variables elements.
Tip
If you are using a BEx Query variable that represents a data interval, the value that you use as input for this variable should have the following format: StartValue - EndValue, (a space before and after the minus).
For a BEx Query variable that supports the input of multiple values, the input value should have the following format: Value1; Value2; Value3 (a semicolon followed by a space between the values).
You can use the Excel Concatenate function to combine the output of two selectors in a single cell and bind this cell to the variable in the Input Values area.
Note
The master data values that we added to the dashboard in steps 2 and 3 of this section contain by default values of posted data. In the BEx Query Designer, we can alter this setting in the Extended tab of the Properties of a characteristic under the Filter Value Selection at Query Execution section.
Only Posted Values for Navigation: Only values that are in the result set of the BEx Query are available. This implies that master data values might disappear when a certain filter has been set.
Only Values in InfoProvider: Only the master data values that are in the InfoProvider are used.
Values in Master Data Table: All available master data values of the characteristic will be used, whether they are present in the InfoProvider or not.
The Output Values area includes the following elements:
- Cross-Tab Data: Provides the complete query output
- Characteristics: Provides fields to create a list of values
- Variables: Also provides fields to create a list of values
- Static Filter: Gives information about the filters that have already been created in the BEx Query
- Information: Provides information about the BEx Query itself, like the Query Technical Name or the Last Data Update
- Messages: Provides BEx Query error messages
The SAP NetWeaver BW connection gives us the option to let the data return as Raw or Formatted. By default, the Raw option is selected, as seen in the following screenshot. If you choose Formatted, the BEx format will be used, which means that the data includes information such as the number of decimals and currency.
