Query Design 

As a basis for data analysis and planning, you define queries for the various InfoProviders. By selecting and combining InfoObjects (characteristics and key figures) or reusable query elements, you determine the way in which you evaluate the data in the selected InfoProvider.

The BEx Query Designer is the tool you use to define and edit queries.

Main Components

The most significant components of the query definition are filters and navigation:

●      The filter defines the possible set of results that is restricted with selections of characteristic values of one or more characteristics. For example, you restrict the characteristic Product to the characteristic value Fax Devices.

●      You define the contents of the rows and columns for the navigation. The arrangement of row and column content determines the initial view for the query.

You can also select free characteristics to change the initial view at query runtime. You use this selection to specify the data areas of the InfoProvider through which you want to navigate.

For example, the characteristic Customer is in the rows of the initial view. By filtering on the product Fax Devices you only display customers who purchased a fax device. If you include the characteristic Distribution Channel from the free characteristics in the rows, you enhance the initial view of the query. You see which customers bought fax devices from which distribution channels.

The query is based on the two axes of the table (rows and columns). These axes can have a dynamic number of values or be mapped using structures. Structures contain a fixed number of key figures or characteristic values. You can save the structures in the InfoProvider so they can be used in other queries.

Defining Characteristics and Key Figures

Query definitions allow the InfoProvider data to be evaluated specifically and quickly. The more detailed the query definition, the faster the user obtains the required information.

You can specify the selection of InfoObjects as follows:

●      You restrict characteristics to characteristic values, characteristic value intervals, or hierarchy nodes

For example, you restrict the characteristic Product to the characteristic values Telephone and Fax Devices. The query is then evaluated for products Telephone and Fax Device only, and not for the entire product range.

●      You restrict key figures to one or more characteristic values

For example, you can include the key figure Revenue in the query twice. You limit the revenue once to the year 2006 and once to the year 2007 (2006 and 2007 are characteristic values of the characteristic Calendar Year). In this way you only see the revenue data for these two years.

●      You use a formula to calculate key figures

For example, you can define a formula that calculates the percentage deviation between net sales and planned sales.

●      You define exception cells

You can define exception cells for tables with a fixed number of rows and columns. This is only the case for queries, such as for a corporate balance sheet.

For example, you can override the values at the intersections of rows and columns with formulas. These values that are recalculated using the formula are displayed instead of the default values.

●      You define exceptions

In exception reporting, you select and highlight values that are in some way different or critical. You define exceptions by specifying threshold values or intervals and assigning priorities to them (bad, critical, good). The priority of the exception defines the warning symbols or color values (normally shading in the traffic light colors red, yellow, and green) that the system outputs depending on the strength of the deviation. You also specify the cell restriction with which you specify the cell areas to which the exception applies.

●      You define conditions

Conditions are criteria that restrict the display of data in a query. This allows you to hide data you are not interested in.

You can specify whether a condition applies to all characteristics in the drilldown, to the most detailed characteristic along the rows or columns, or only to certain drilldowns of defined characteristics or characteristic combinations.

When defining conditions, you enter threshold values and operators such as Equal To, Less Than, Between, and so on. Alternatively, you display the data as ranked lists with operators such as Top N, Bottom N, Top Percentage, Bottom Percentage, and so on.

For example, you define a ranked list condition that displays the top three products that generate the largest net sales. You want to see the top three sales channels for each of these products. All other products and sales channels are hidden.

If you restrict or calculate key figures, you can save them in the InfoProvider for re-use in other queries. When using reusable query elements, you only have to edit the query element in one query, and the changes then automatically affect all other queries based on this InfoProvider and that contain this query element.

Flexible Use of Queries

To use queries flexibly, you can define variables. These serve as placeholders for characteristic values, hierarchies, hierarchy nodes, texts, or formulas. At query runtime, users can replace the variables with specific values. A query definition therefore can therefore serve as the basis for many different evaluations.

Use of Queries

A query is displayed with BEx Web in the predefined initial view in the SAP NetWeaver portal or in the BEx Analyzer, which is the design and analysis tool of the Business Explorer and is based on Microsoft Excel. By navigating in the query data, you can generate different views of the InfoProvider data. For example, you can drag one of the free characteristics into the rows or columns or filter a characteristic to a single characteristic value. To ensure that the views of the query you create in this way are also available for use in other applications, save them as query views.

More Information

Query Design: BEx Query Designer