Creating a Query Using BEx Analyzer - SAP BW

The following steps demonstrate how to create a query.

Work Instructions
Step 1. Open SAP Business Explorer Analyzer by selecting StartWork InstructionsProgramsWork InstructionsSAP FrontendWork InstructionsSAP Business Explorer Analyzer from Windows,and then clicking enable-macro.

Microsoft EXCEL

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

Step 2. ClickClick to open or create a query.
Microsoft EXCEL

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

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

Sap logon at PWC BWC2 2.0 B

Step 5. We have not created any workbooks yet. Clickquries to display queries or create a query
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 newto create a query.
create a query.

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

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 clickto save and run the query. The result will not display Sales rep. ID in the hierarchy we created in "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.
New query

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

Step 10. In the pop-up window,select IO_SREP hierarchy,and then clickok. Notice the date 31.12.9999 in the Key date block.

Select Hierarchy

Step 11. Clickok to continue.

Characterstic sales

Step 12. Click Clickto save and execute the query.
save and execute the query.

Step 13. Enter a technical name and a description,and then clickclick. We use a simple format <InfoCube name>_Q<two-digit number> to name the query,where "Q" stands for query.
Save query

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 although he or she made sales.


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

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. "Query Read Mode," discusses three types of query read modes.

Step 16. To save the query result,clickclick,and then select Save as new workbook…. In BW,the saved Excel file with the query result is called a workbook. This 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 clickok to save the workbook.

SAP BEX save to document store

Step 18. Let's create a new query assuming that the Denver office is still in the Midwest region. Recall,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 with 31.12.1999.
Select hierarchy

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

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 is often referred to as a today-is-yesterday scenario,and the key date 31.12.1999 query result is known as a yesterday-is-today scenario. In,"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.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

SAP BW Topics