Defining New Queries
Use
To analyze data in the BEx Analyzer or in BEx Web applications, you require a query as a data provider. Queries also act as data providers when you create reports. To do this, you define new queries in BEx Query Designer based on InfoProviders.
Prerequisites
At least one InfoProvider is available in the BI system.
You have called BEx Query Designer using one of the following options:
● Standalone, choosing Start → Programs → Business Explorer → Query Designer.
● BEx Web Application Designer:
You have chosen Tools → BEx Query Designer in the Web Application Designer menu bar.
● BEx Analyzer:
You are in an empty cell and have chosen Tools → Define New Query in the analysis toolbar.
● BEx Report Designer:
You have chosen Tools → BEx Query Designer in the Report Designer menu bar.
You have then chosen Query → New in the Query Designer menu bar.
Procedure
Overview
You define queries by:
● Selecting an InfoProvider for which the query is defined
● Selecting reusable structures that already contain characteristic or key figure combinations (for example, contribution margin schema)
● Selecting characteristics from the InfoProvider
● Restricting selected characteristics to characteristic values, characteristic value intervals, or hierarchy nodes
● Using variables for characteristic values, hierarchies, hierarchy nodes, formulas, and texts or defining new variables where necessary
● Selecting key figures from the InfoProvider
● Formulating calculated key figures
● Restricting key figures by combining them with characteristics
● Defining exception cells
● Arranging the characteristics and key figures in rows or columns and thereby establishing an initial view for the query analysis
The steps that are not in bold are optional. You do not have to use these functions.
With an InfoCube as an InfoProvider, you can display and sort the key figures in a hierarchy. The key figures are then displayed in the InfoProvider screen area. More information: Creating InfoCubes.
Defining Queries
The selection screen is displayed for all InfoProviders that you can define a new query for.
...
1. From your history, or from all available InfoProviders under InfoAreas, select the InfoProvider that you want to base your query on.
If you want to show the technical name of the InfoProvider, choose the Technical Name On/Off icon.
2. The available objects for the InfoProvider you selected are displayed as a directory tree in the InfoProvider screen area.
You can sort the objects of the InfoProvider alphabetically (either in descending or ascending order) by clicking the corresponding arrows in the title bar. If you click three times, the system displays the data in the default order again (from top to bottom: Structures, Filter, Key Figures, and Dimensions).
The Filter screen area contains empty directories for the filter selection; the Rows/Columns screen area contains empty directories for the rows, the columns, and the free characteristics of the query. The preview of the results area for the query can be found at the bottom right. This area is also empty initially.
For more information about the process of creating a query, see Working with the BEx Query Designer, in the Proceed as follows to create a query section.
3. Make the selections for the filter of the query. Note that the filter selection has a restricting effect on the entire query, which means that all the InfoProvider data is aggregated with the filter selection. In the InfoProvider directory tree, select the characteristics or the key figure that you want to base the query on.
○ Characteristics
Expand the required dimension directory and first select one or more characteristics that you want to restrict to one or more values, a variable, a value interval, or a hierarchy node from the InfoProvider. Expand this characteristic and using drag:
■ One or more single values to the filter’s Characteristic Restrictions area
■ One or more characteristics to the filter’s Characteristic Restrictions area. Click the secondary mouse button on a characteristic to display the context menu and choose Restrict. Now select a characteristic value interval or a hierarchy node (more information: Restricting Characteristicspurl_link_0002_0003_0013).
○ Key Figure
Decide whether to base all the query data on the numerical values of just one key figure or to base it on multiple key figures. If you want to base the query on the numerical values of just one key figure, select the relevant key figure for the InfoProvider tree and drag it to the Filter area. You can also use a restricted or calculated key figure (more information: Defining Restricted Key Figures and Defining Calculated Key Figures).
4. If required, define default values for the filter by dragging the required filter values into the filter’s Default Values area. More information: Filtersapurl_link_0002_0006_0013.
5. Specify which characteristics, key figures, or structures you want to be available for navigating through the query data. Drag the corresponding directories of the characteristics in the InfoProvider tree to the Rows, Columns, or Free Characteristics directories in the Rows/Columns screen area, or drag the structure directories into the rows or columns.
If a characteristic has a compound characteristic, the compound characteristic is automatically added during the drag and drop action. This also applies to copying and pasting.
The compound characteristic is always added to the characteristic restrictions of the filter. For example, characteristic Country is compounded to characteristic Region. If you drag the Region characteristic to the Free Characteristics area, the characteristic Country appears automatically in the Characteristic Restrictions area of the filter.
You can use any number of characteristics in the query. Note, however, that the number of characteristics in the drilldown is restricted. An unlimited number of characteristics is permitted in the Free Characteristics area. Note, however, that the rows and columns can contain a maximum of 50 - <number of structures> characteristics only.
If the query does not contain a structure, a total of 50 characteristics are permitted in the rows and columns. If you use one structure, 49 characteristics are permitted, and with two structures, 48 characteristics are permitted in the rows and columns.
If you have not selected any key figures for the filter, define one or more key figures either in the rows or columns of the query. Drag a key figure from the Key Figures directory in the InfoProvider tree to either the Rows or Columns directory. Query Designer automatically generates a new structure containing the selected key figure. This structure has the default description Key Figures.
You can also define a new calculated key figure and use it for the query. For example, instead of "Sales revenue", define the key figure "Price", which is calculated by dividing "Sales revenue" by "Quantity". Select the Key Figures directory in the InfoProvider tree and in the context menu, choose New Calculated Key Figure.
You can restrict key figures to characteristic values, characteristic value intervals, or hierarchy nodes by selecting the key figure and choosing Edit in the context menu. "Sales revenue in first quarter" is an example of a restricted key figure.
By placing the characteristics, key figures, or structures either in the rows or columns directory, you configure the first navigational state of the query (start view). In the lower right area of the screen, you can see a preview of the results area of the query. This is displayed in the start view that you selected.
You can use drag and drop to change how the key figures and characteristics are arranged in the rows and columns until the preview displays the results area you want.
6. When you have created the query, you can check the query definition by choosing Query → Check in the menu bar.
7. Choose Query → Properties in the menu bar if you want to change the Description, settings for Result Position, Display Options and Number Display, or Key Date for the query. More information: Query Maintenanceapurl_link_0002_0007_0013.
8. Save the query. Enter a Technical Name and a Description for the query. The Technical Name must uniquely identify the query; means that the name may appear only once across all InfoProviders in the BI system. The technical name can be up to 30 characters in length and must begin with a letter.
Result
The query has been saved and can now be:
● Used in BEx Analyzer as a data provider for design items
● Used in Web Application Designer as a data provider for Web items
● Displayed in BEx Web Analyzer: Choose Query → Execute…
You can analyze the query data in Business Explorer.
More Information:
Properties of Query Components
Functions of the Menu Bar in the Query Designer
Authorizations for Query Definition and Information Broadcasting
More information about displaying and sorting key figures in a hierarchy: Creating InfoCubes
More information about using non-cumulative InfoCubes as InfoProviders: Performance Tips