Tutorial part 1: 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 simple BW query that is responsible for providing values to an app. The query is to output telephone costs broken down by the different branches of a fictitious company for the last 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.

You create this BW query in the first part of the tutorial, during which you learn about the options for creating your own app using a simple example.

Note Note

Ensure that you have already performed the previous steps, as described in Tutorial Part 1: Creating and Registering a Simple App.

End of the note.

Procedure

To create the BW query, proceed as follows:

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 data for the previous month, set a suitable default value for the calendar month. To do this, from the dimensions, drag the entry [0CCM_GDT] Time [0CALMONTH] CalYear/Month 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 [0CMLMON] Last Month into the selection.
Switch to the view Rows/Columns.
In the query, you want to display the telephone costs for the individual branches. Therefore, drag the corresponding key figure 0CCM_COST (Costs) into the subscreen Columns and the dimension [0CCM_GD1] Source [0CCM_SRCL] Source 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 (branches) plus the header. Since the fictitious company in the test data has eight branches, the query displays a table with two rows and nine columns. 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 [0CCM_SRCL] Source 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 ZTUT1_COSTS_MONTH and the description Costs for the last month.

Result

You have created the BW query to supply data for the dashboard app. When registering the app in the Dashboard Framework (see Tutorial Part 1: Registering the App), you will specify this query.

Note Note

If you create your own queries, check whether it displays the expected behavior before registering the app. You can use the transaction RSRT (Query Monitor) to do this.

End of the note.