Tutorial part 2: Create BW Query

This tutorial is part of a course that explains the creation and registration of apps for the Dashboard Framework. In this step, you create a BW query that is responsible for providing values to a detail app. The query is to display telephone costs for a selected branch of a fictitious company for the individual days of the previous month, based on test data.

To use this test data, you require the following information:

The required test data can be accessed through the InfoProvider 0CCM_GD (Generated Data).

The telephone costs are in the key figure 0CCM_COST (Costs).

The branch that incurred the costs is in the dimension [0CCM_GD1] Source[0CCM_SRCL] Source.

Prerequisites

This procedure is part of a comprehensive tutorial on the topic Creating and Registering Dashboard Apps. During this tutorial, you create and register a number of apps and their associated BW queries.

Procedure

Start the BEx Query Designer, and enter the BW system or the BW client that is assigned to your SAP Solution Manager system.

To create a new query, choose the button (New Query). As the InfoProvider, specify 0CCM_GD (Generated Data). This InfoCube provides the test data that you want to display in the query.

Activate the display of the technical names by choosing the (Technical Names) button.

You want to display the metrics (in this case, the telephone costs) in the appropriate granularity. To do this, you need to set a corresponding filter.

Switch to the Filter view, and drag the following characteristics from the Dimensions pool to the Characteristic Restrictions subscreen:

Dimensions[[0CCM_GD4] Granularity[0SMD_GRDA] Date Granularity

Restrict this value by choosing the Restrict... entry from the context menu for the value, and in the window that then appears, in the Show dropdown list box, choose the entry Single Values, and from the list, move the value [DAY] DAY into the selection.

Dimensions[0CCM_GD4] Granularity[0SMD_GRTI] Time Granularity

Restrict this value by choosing the Restrict... entry from the context menu for the value, and in the window that then appears, in the Show dropdown list box, choose the entry Single Values, and from the list, move the value [DAY] DAY into the selection.

Dimensions[0CCM_GD5] Profile[0CCM_PRFL] Profile

Restrict this value by choosing the Restrict... entry from the context menu for the value, and in the window that then appears, in the Show dropdown list box, choose the entry Single Values. In the Direct Input field, enter the value SAND100, and move this value into the selection.

Note

With this profile, you define whether the values generated should always be identical or should be randomly generated. With the profile SAND100, the current date is used as the initial value for the random number generation, which means that – if you call the query on a particular day – the same value is always displayed for a particular metric (such as the costs for telephone calls from one branch to another at a particular time), that is, that the displayed values are consistent across multiple calls.

End of the note.

Since the app is to display all days of the previous month, set a suitable default value for the calendar day. To do this, from the dimensions, drag the entry [0CCM_GDT] Time[0CALDAY] Calendar Day into the Default Values subscreen. Restrict this value by choosing the Restrict... entry from the context menu for the value, and in the window that then appears, in the Show dropdown list box, choose the entry Variables, and from the list, move the value [0VDAYLM] Last Month into the selection.

Switch to the view Rows/Columns.

The values for one branch are to be displayed. However, since it is only known at runtime which branch this is, drag the dimension [0CCM_GD1] Source[0CCM_SRCL] Source to the subscreen Free Characteristics.

In the query, you want to display the telephone costs for the individual days of the previous month. Therefore, drag the corresponding key figure 0CCM_COST (Costs) into the subscreen Columns and the dimension [0CCM_GDT] Time[0CALDAY] Calendar Day from the subscreen Free Characteristics into the subscreen Rows.

Note

The Query Designer Preview subscreen shows the query output as a 2-column table. The number of rows is the number of sources (calendar days of a month) plus the header. The query outputs a table that of two columns and up to 32 rows. The size of this table in the BW query must match the size of the corresponding connection in the Xcelsius app, with the addition of the empty row in the connection, which is reserved for the technical names.

End of the note.

By default, the result row is always included in the display of a query. However, this is not required for the app in this case. Therefore, in the Rows subscreen, select the entry [0CALDAY] Calendar Day and, in the Properties subscreen on the right, in the Display dropdown list box, change Results Lines to the value Always suppress.

Save the query with the technical name ZTUT2_COSTS_DAY and the description Daily Costs for the Last Month.