Creating BEx Queries

In this section:

The adapter can report against several types of SAP BW objects, some of which represent views or subsets of the actual data. Using the Business Explorer (BEx) provided by SAP as its reporting and analysis tool, you can create queries (methods for extracting data) and reference them in WebFOCUS report requests.

A query is a subcube created by selecting characteristics and key figures. Using queries, the data can be quickly targeted and evaluated. The more precisely the query is defined, the smaller the subcube and the quicker the query can be navigated and refreshed. Selecting certain characteristics means that they can be more closely analyzed while others remain unspecified. The resulting key figures are aggregated across all characteristic values for the unspecified characteristics.

A default navigational state is also established in the query definition when you arrange the characteristics and key figures in the rows and columns of the query.

Only the data that is currently requested is transferred to the query. The OLAP processor builds the query from the data and provides methods for navigating through the data in several dimensions. Since a query preselects information, the same data can yield dramatically different results depending on the query used to view its contents.


Top of page

x
SAP BW Terminology

The Business Explorer (BEx) is the SAP BW component that provides reporting and analysis tools. BEx queries filter the data to create reporting objects or views called InfoProviders. Some InfoProviders contain actual data. Others provide views of data stored in elsewhere.

The basic elements used to define InfoProviders are called InfoObjects. An InfoObject is a business evaluation object, such as customer or sales revenue, relevant to your analysis of business performance. InfoObjects can be characteristics (dimensions), key figures (measures or facts), units, time characteristics, or technical characteristics (for example, request number).

The following InfoProviders can be used for WebFOCUS reporting:

An InfoObject catalog is an application-specific organization of InfoObjects, either characteristics or key figures.

To report against a cube, you must create a BW synonym that describes it to the adapter. To create a synonym for a query cube, you first create the BEx query. In the query properties, set the Extended Query Properties to Allow external access to this Query.


Top of page

x
BEx Query Terminology

The basic building blocks of a query cube are:


Top of page

x
Defining New Business Explorer Queries

How to:

You must define an SAP Business Explorer query before reporting from an SAP BW InfoProvider. This query serves as a template for data extraction from the cube.

Note: The information that follows is based on SAP BW Business Explorer documentation. SAP BW BEx documentation is available from http://help.sap.com.



x
Procedure: How to Select an InfoProvider to Query
  1. The Business Information Warehouse must contain at least one InfoProvider before you can define a new query. Start the Business Explorer Analyzer.
  2. From the BEx toolbar, choose Open.

    You will see the selection screen for all existing workbooks.

  3. Choose Queries.

    The selection screen displays all available queries.

  4. Choose New.

    You will see the selection screen for all InfoProviders for which you can define a new query or queries.

  5. Select the InfoProvider that has the data on which the query should be based. To display technical names for InfoProviders, set the Technical name on/off icon to On.

    The available objects in the InfoProvider you have selected are displayed as a directory tree in the left part of the screen.

Next, you will select the objects for the query and drag them to the appropriate boxes to build the query.



x
Procedure: How to Create a Query

The right area of the BEx screen contains selection boxes for the filter selection, the rows, the columns, and the free characteristics of the query.

Perform the following steps to create a query:

  1. Click the plus or minus sign to the left of the dimension or Key Figures you want to query.

    The object list will expand and display a list of all the available Key Figures or characteristics.

  2. Drag and drop characteristics and Key Figures from the InfoProvider into the selection box of the query definition.

    These may be filters, rows, columns, and free characteristics.



x
Procedure: How to Filter a Query

You can filter queries in order to place restrictions on them. Filter selection restricts the entire query. To select fields you want to use to filter the query, complete the following:

  1. From the object list of the InfoProvider, select the characteristics or the key figure upon which the query should be based.

    Note: Since they are used in definitions, fields selected as filters are not displayed in the Adapter for SAP BW metadata. They are used to screen the data and thus contain no information to be reported on. If you wish to screen data and report from it, see Restricting Query Characteristics or Restricting and Calculating Key Figures.

  2. Drag the object to the Filter box.
  3. Right-click the object in the filter box. A dialog box opens showing the possible filter definitions for the object.
  4. Select either a single member, a range of members, or a variable for the filter.

Top of page

x
Restricting Query Characteristics

How to:

When defining a query, you may restrict characteristics to a single characteristic value, a value interval, a hierarchy node, or a characteristic value variable.



x
Procedure: How to Restrict Characteristics
  1. Choose the characteristic from the InfoProvider for which you want to select a value range.
  2. Drag the characteristic into the appropriate selection box of the query definition (rows or free characteristics).
  3. Select the characteristic you wish to restrict (or filter). Using the right mouse button, select Restrict from the Context menu.
  4. Choose whether you want to restrict the characteristic to a single value, a value interval, or a hierarchy node.

    Tip: You can enter the characteristic values or hierarchy nodes you want to use, or you can display a list of all possible values by clicking the magnifying glass to the right of the input field.

  5. Confirm your entries by clicking OK.

Top of page

x
Restricting and Calculating Key Figures

How to:

You can restrict Key Figures to characteristic values, characteristic value intervals, or hierarchy nodes. For example, a restricted key figure would be Sales revenue in 1st quarter.

You can also restrict the Key Figures of the InfoProvider for the query definition, or, using a formula, you can calculate new Key Figures from the (basic) Key Figures:



x
Procedure: How to Restrict Key Figures
  1. Drag a (basic) key figure into the key figure selection box. Alternatively, select the header of the selection box for rows or columns and, using the right mouse button, select New Structure from the Context menu.
  2. Select the Structure directory, and, using the right mouse button, choose New Selection from the Context menu. The New Selection screen opens.
  3. Enter a description of the restricted key figure in the text fields located in the upper part of the screen.
  4. Underneath the text fields, on the left, is the directory of all the objects available in the InfoProvider. Use the empty field on the right-hand side of the screen for the definition of the new selection.
  5. Using drag and drop, choose a key figure from the InfoProvider, and restrict it using a selection of one or more characteristic values.
  6. Select OK. The newly restricted key figure is defined in the structure.


x
Procedure: How to Calculate Key Figures
  1. Create a new structure in the rows or columns of the query definition by highlighting the row or column directory using the right mouse button and selecting New Structure in the Context menu.
  2. Drag a (basic) key figure of the InfoProvider into the directory of the new structure.
  3. Select the Structure directory, and choose New Formula. The Formula Definition screen opens.
  4. Enter a description of the formula in the text fields located in the upper part of the screen.

Note: The entry field for the formula is underneath the text fields. In the bottom left of the screen are all of the operands available for the formula definition. These are the Key Figures that you have already defined in the structure, and all of the formula variables in the Variables directory that have been created in the variable maintenance.

The functions available as operators are on the right-hand side of the screen. These are symbols for the basic arithmetic operations and directories with calculation functions such as percentage or trigonometric functions. To the right of the operators is a number block.



x
Procedure: How to Define a Formula
  1. Choose the operands you want to use, and insert them in the entry field for the formula by double-clicking or by using drag and drop.
  2. Choose the calculation functions you want to use by either clicking the symbols for the basic arithmetic operations, double-clicking to select the individual values, or dragging the entire key figure into the formula box.
  3. Select the number values for the formula by clicking the number block.
  4. Define your formula using the available operands and operators.

    If you want to use a variable that is not contained in the operands, you must create the variable first.

  5. Check the formula definition for correctness by pressing the scale icon.
  6. Enter the name of the formula column in the description box.
  7. Select OK. The newly calculated key figure is defined in the structure.

Top of page

x
Viewing Query Properties and Releasing for OLAP

This enables the query to be displayed as a QUERY_CUBE for reporting purposes. The query elements (hierarchy levels, measures, variable, and properties) will be mapped to corresponding OLAP elements to create a synonym.


iWay Software