Business Information Warehouse for SAP (Prima Techs SAP Book Series)

Variables make queries flexible and dynamic. With variables, you set selection values at runtime instead of hard coding in the query definition. These variables are defined in the SAP BW instance, via transaction RSZV or from the main SAP BW menu option. From the SAP BW main menu, select Business Explorer and select the option Maintain Variables and define a new variable, for example, ZCUSTMR, associated with characteristic 0CUSTOMER, as shown in Figure 11-18. This means that when you refer to characteristic 0CUSTOMER, the variable ZCUSTMR is accessible. You can include these variables in the query definition to filter data.

Figure 11-18: Defining Variable ZCUSTMR in SAP BW using Transaction RSZV that Will be Used to Dynamically Set Values for a Customer to Select.

To add a variable in an existing query, you need to edit the query and assign a variable to a characteristic, as shown in Figure 11-19. Save this query and exit the Edit Query window. The BEX Analyzer refreshes the query and prompts for a Customer. Enter a Customer or use the default value; in this example, the default value is DIGITAL. The query then takes this value, sends the variable value to the OLAP server, and uses it to select data (see Figure 11-18).

Figure 11-19: Customizing the BEX Analyzer Query to Select Customer Data Based on User Input using a Variable. At the Query Runtime, the User is Prompted for Variable Value. This Variable Value is Used to Select Data at the Database Level.

You can define variables for Characteristic, Texts, Formulas, Hierarchies, and Hierarchy nodes. Variables play a key role in implementing slowly changing dimensions, the characteristics of other data objects that change in time. For example, the Material structure often changes. To implement this time dependency, the material must also be stored with valid time periods. Then from the query, a variable, for example ZTIMPRD, is sent to the OLAP processor to select master data valid for the selected time period. To transfer this variable from the query to the OLAP processor, you need to edit the Query properties and enter a variable name in the Key date field, as shown in Figure 11-20. To open the Query properties window, open your query in edit mode, and then click the Query Properties icon.

Figure 11-20: Setting up the Query Global Properties for Time-Dependent (Slowly Changing Dimensions) Data Objects.

Often, a single query per spreadsheet is not sufficient to build a complex solution. Instead, worksheets are used as a collection of spreadsheets to build a complete solution; for example, you may want to build a reporting application that uses several queries to fetch data from orders, deliveries, and invoices. It saves retrieved data in several sheets with charts, graphs, and VBA programming that enables you to navigate from one sheet to another based on what the user has selected. With VBA programming, you can call all functions available in the BEX menu bar. Table 11-1 lists such VBA callable functions in SAP BW 1.2B.