Reporting Server > Using the Adapter for SAP Business Intelligence Warehouse (BW) > Creating BEx Queries

Creating BEx Queries

Topics:

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.

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:

  • InfoCube. An InfoCube is a self-contained set of business data. The physical data store is called the BasicCube. It consists of InfoObjects and is structured in a star schema, which means that one large fact table contains the key figures (numeric data values). This fact table is surrounded by smaller dimension tables that store characteristics (categories of data). Other types of InfoCubes display logical views of a data set. Some examples follow.
    • RemoteCube. A RemoteCube is an InfoCube whose transaction data is not managed by BW, but externally
    • SAP RemoteCube. An SAP RemoteCube is a RemoteCube for which you can define queries by accessing transaction data in other SAP systems.
    • Virtual InfoCube with Services. A Virtual InfoCube with Services is an InfoCube whose data is not stored in BW and for which a user-defined function module is used to retrieve the data.
  • ODS Object. An ODS object is a set of cleaned up transaction (detail level) data. It contains key fields (for example, item number) and data fields (such as order status or customer).
  • InfoSet. An InfoSet is a join between ODS objects and/or InfoObjects. It is a definition of the conditions that determine how the objects should be joined.
  • MultiProvider. A MultiProvider is a combination of data from separate InfoProviders. It does not contain data; data comes from the separate InfoProviders.

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.

BEx Query Terminology

The basic building blocks of a query cube are:

  • Characteristics. Characteristics, also called dimensions, are classifications such as Region or Time, that you use to analyze and compare business performance.
  • Attributes. Attributes, also called properties, are additional information about a characteristic. For example, the material characteristic might have attributes such as color or weight.
  • Key Figures. Key figures, also called measures or facts, are quantifiable values used in evaluating performance. An example of a key figure is sales revenue.
    • Calculated Key Figures. These key figures are not stored in the data. They are defined in the BEx query and evaluated at run time.
    • Restricted Key Figures. These key figures are filtered by selecting one or more characteristics. They can be filtered versions of basic key figures, calculated key figures, or other restricted key figures.
  • Hierarchies. Hierarchies are groupings of characteristics according to individual evaluation criteria. For example, a customer characteristic can be grouped by geographical location, industry, or some other criteria. A characteristic can have multiple hierarchies.
  • Variables. Variables are parameters of a query. They are set in the query definition and may get their values in different ways. For example, some variables are given default values and others require the user to enter a value. For more information about variables, see Variable Types and Reporting With Variables.
  • Filters. Filters are used to restrict the output of a request. Although not included in a WebFOCUS synonym, they affect the outcome of a WebFOCUS request.

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.

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.

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.

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.

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.

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.

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:

  • Restricted Key Figures. (Basic) Key Figures for the InfoProvider that are restricted (filtered) by selecting one or more characteristics.
  • Calculated Key Figures. Formulas that consist of (basic) Key Figures for the InfoProvider and/or calculated Key Figures that have already been created.

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.

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.

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.

Viewing Query Properties and Releasing for OLAP

  • To view the properties of a query, click the Query Properties icon on the toolbar. The Query Properties dialog box opens.
  • To release a query for OLAP, click the Query Properties icon on the toolbar and check the following in the Query Properties dialog box:
    • Allow External Access (for SAP BW releases 3.x and higher).
    • Release for OLE DB for OLAP (for SAP BW releases prior to 3.x).

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.

WebFOCUS

Feedback