The following steps demonstrate how to create a query.
Step 1. Open SAP Business Explorer Analyzer by selecting Start Programs SAP Frontend SAP Business Explorer Analyzer from Windows, and then clicking .
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.
Step 3. We need to log on to the BW system. Select the system that contains our InfoCube, and then click to continue.
Step 4. Enter the correct user ID and password, and then click to continue.
Step 5. We have not created any workbooks yet. Click to display queries or create a query
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.
Step 7. Select our InfoCube, and then click to open a query design window.
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.
Step 9. Click to list the hierarchies associated with the characteristic.
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.
Step 11. Click to continue.
Step 12. Click to save and execute the query.
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.
Step 14. An Excel file is opened and populated with the query result. Examine the query result:
Step 15. Double-click the free characteristics Material number, Customer ID, and Calendar day, which produces a new query result.
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.
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.
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.
Step 19. Run the query. In the result, the Denver office is listed under the Midwest region. Save this query result as another workbook.
You created two queries with different key dates and saved the query results as workbooks.
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.
Part I. Guided Tours
Business Scenario and SAP BW
Creating an InfoCube
Loading Data into the InfoCube
Checking Data Quality
Creating Queries and Workbooks
Managing User Authorization
Part II. Advanced Topics
Aggregates and Multi-Cubes
Operational Data Store (ODS)
Generic R/3 Data Extraction
Appendix A. BW Implementation Methodology
Appendix B. SAP Basis Overview
Appendix C. Glossary
Appendix D. Bibliography