Creating a Query Using BEx Analyzer

The following steps demonstrate how to create a query.

Work Instructions

Step 1. Open SAP Business Explorer Analyzer by selecting Start Programs SAP Frontend SAP Business Explorer Analyzer from Windows, and then clicking .

SCREEN 5.1

Note

BEx requires the gateway service to communicate with the SAP application server. Appendix B, SAP Basis Overview, discusses the gateway service and the SAP application server.

Step 2. Click to open or create a query.

SCREEN 5.2

Step 3. We need to log on to the BW system. Select the system that contains our InfoCube, and then click to continue.

SCREEN 5.3

Step 4. Enter the correct user ID and password, and then click to continue.

SCREEN 5.4

Step 5. We have not created any workbooks yet. Click to display queries or create a query

SCREEN 5.5

Step 6. Review the list of InfoAreas that is displayed. Our InfoArea – demo does not appear in the list because it has only one InfoCube, which does not have a query yet. Click to create a query.

SCREEN 5.6

Step 7. Select our InfoCube, and then click to open a query design window.

SCREEN 5.7

Step 8. In the new window, drag and drop three key figures to the Columns panel, characteristic Sales rep. ID to the Rows panel, and three other characteristics to the Free characteristics panel.

If desired, you can click to save and run the query. The result will not display Sales rep. ID in the hierarchy we created in Section 3.6, "Entering the Master Data, Text, and Hierarchy Manually," however.

To display the hierarchy in the query result, right-click Sales rep. ID and select Properties.

SCREEN 5.8

Step 9. Click to list the hierarchies associated with the characteristic.

SCREEN 5.9

Step 10. In the pop-up window, select IO_SREP hierarchy, which we created in Section 3.6, and then click . Notice the date 31.12.9999 in the Key date block.

SCREEN 5.10

Step 11. Click to continue.

SCREEN 5.11

Step 12. Click to save and execute the query.

SCREEN 5.12

Step 13. Enter a technical name and a description, and then click . We use a simple format _Q to name the query, where "Q" stands for query.

SCREEN 5.13

Step 14. An Excel file is opened and populated with the query result. Examine the query result:

1. The Sales rep. ID data appear in column A. The Step 8 free characteristics data are not visible.
2. In the Chicago office, materials sold by Kevin are in unit EA, and those sold by Chris are in unit DZ. It is not appropriate to generate a total by combining EA and DZ, so the cell for the Chicago office contains *.
3. Besides the three regions, another node appears at the same hierarchy level called Not assgnd Sales rep. ID (s) with SREP11 under it. The reason is that SREP11 is not in the sales organization (Table 1.3) although he or she made sales (Table 1.4).

SCREEN 5.14

Step 15. Double-click the free characteristics Material number, Customer ID, and Calendar day, which produces a new query result.

SCREEN 5.15

Note

Free characteristics allow for drill-down. Use of free characteristics with an appropriate query read mode reduces the I/O volume for the initial query result, thereby improving query performance. Section 13.3.1, "Query Read Mode," discusses three types of query read modes.

Step 16. To save the query result, click , and then select Save as new workbook…. In BW, the saved Excel file with the query result is called a workbook. Section 5.2 demonstrates how to access and open the workbook using three different methods.

SCREEN 5.16

Note

Workbooks are saved as Binary Large Objects (BLOBs) in the database. A BLOB is a special data type that stores blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) in the database. In addition, it allows for efficient, random, piecewise access to the data.

Step 17. Enter a description for the workbook, and then click to save the workbook.

SCREEN 5.17

Step 18. Let's create a new query assuming that the Denver office is still in the Midwest region. Recall that in Chapter 1, we noted that before January 1, 2000, the Denver office belonged to the Midwest region.

Create another query by repeating the previous steps, but this time replace the key date 31.12.9999 in Screen 5.10 with 31.12.1999.

SCREEN 5.18

Step 19. Run the query. In the result, the Denver office is listed under the Midwest region. Save this query result as another workbook.

SCREEN 5.19

Result

You created two queries with different key dates and saved the query results as workbooks.

Note

In the data warehousing world, the key date 31.12.9999 query result (Screen 5.14) is often referred to as a today-is-yesterday scenario, and the key date 31.12.1999 query result (Screen 5.14) is known as a yesterday-is-today scenario. In Section 7.3, "InfoCube Design Alternative II-Dimension Characteristics," we will encounter two other scenarios, yesterday-or-today and yesterday-and-today. These scenarios are developed to validate and analyze data when the situation involves a slowly changing dimension.