Download chapter 7: 'Information Access, Analysis, and Presentation'
This chapter is excerpted from the book titled, 'Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver, 2nd Edition', authored by Kevin McDonald, Andreas Wilmsmeier, David C. Dixon and W. H. Inmon, published Aug, 2006 by Wiley Publishing, ISBN 978-0-7645-9637-7, Copyright 2006 Wiley Publishing. For more information, please visit: www.Wiley.com
Query Processing Overview
Let's look at the query request response process in the context of a typical business question. We will use the example of analyzing revenue and contribution margins across multiple geographies and customers to illustrate how the information request response process works in SAP BW. Figure 7.2 depicts the analysis process in SAP BW. Figure 7.1 Main architectural levels of SAP BW.
In our scenario, we'll assume that this is the first time the corporate controller has requested contribution margin and revenue to be analyzed by geography and customer. After launching her favorite Web browser and navigating to the SAP BW reporting homepage on the corporate intranet, the controller selects the Create New Query option. After selecting this option, she invokes the meta data services of SAP BW to return a list of potential information providers that may be queried. The controller selects the appropriate InfoProvider, and again invokes the meta data services in SAP BW. The services return a list of dimensions that contain characteristic InfoObjects as well as a list of key figures. The controller selects the Revenue and Contribution key figure, as well as the Geography and Customer characteristics, and assigns them to the rows or columns of the query result sets based on her preference. She then executes the query.
At the center of the information access, analysis, and presentation services in SAP BW is the OLAP engine. An information consumer application requests information from the OLAP engine in the form of a multidimensional expression or similar selection request. In our example the controller requests revenue and contribution margin for the current period and fiscal year for all customers and all geographies. Leading business intelligence tools generate selection expressions so the end users do not need to know the specific syntax. The request is sent from the browser to the SAP BW server. Consumers may be a browser, mobile device, Excel spreadsheet, or as in our example, a third-party client application. The SAP BW server takes the request through one of a number of standard interface techniques and hands it to the OLAP engine for fulfillment. (We will describe the interfaces in detail later in this chapter.)
The SAP BW server takes the request and determines if there is an aggregate cube in existence that may satisfy the request. In our example an aggregate cube that summarizes customer or geographies may be a candidate for optimal retrieval. If no such aggregate cubes exist for the query, the application server makes a request to the database server.
The database requests and selects records from an InfoProvider, or an aggregate, that is stored in a relational database or multidimensional database for aggregates as a star schema. The records that are selected from the database server may be returned to the application server. Depending on the query's read mode settings, records are cached for the OLAP engine to process and, ultimately, to calculate and return a query navigation state to the presentation server. The caching of the selected data on the application server is sometimes referred to as a query cube depending on the read mode that is set for the query. The OLAP engine will use the query cube to calculate and return result sets to the client for subsequent information requests, assuming the query cube contains the necessary data to respond to the request. We will discuss the options for setting a query's read mode and the impact on performance in Chapter 10.
The controller in our example would receive a listing of revenue and contribution margins for all customers in all geographies. She notices that revenue, which is displayed in U.S. dollars (USD), is higher than she had planned and proceeds to investigate which geography is exceeding her expectations. Her request is sent from the client to the application server, where it is once again handled by the OLAP engine. Once the appropriate storage service returns the records, the OLAP engine creates the query data and proceeds as previously described. The controller learns that the United Kingdom is exceeding her sales expectations.
Note that the OLAP engine will take care of any currency translations based on the meta data of the InfoObjects included in the query, as well as the meta data of the query itself. In this case the geographies are countries, and most countries have their own currency. The controller also may wish to analyze the revenue in the group currency of USD. Upon doing so, she notices that Argentina has failed to meet her sales expectation. Not satisfied she has found the cause of the shortfall, she investigates further and requests the currency be converted to local currency for all countries. Arequest is once again sent to the OLAP engine to be processed. The local currencies are either retrieved or calculated, depending on the modeling of the InfoMart, and the newly calculated query slice is returned to the client. Now the controller realizes that Argentina did in fact meet sales expectation but experienced a significant foreign exchange impact as a result of the rate dropping.
The process of requesting and responding to queries is the central concept of this chapter. We will first investigate the presentation services and then move to the information access and analysis services. It is not our intent here to re-create SAP documentation, nor is it possible for us to cover every reporting and analysis feature of SAP BW. However, we will look behind the scenes at the central services SAP BW performs and lay out the possible options for putting them to use to solve business problems.
Visit the Wiley Publishing website for a detailed description and to learn how to purchase this title.