Business Information Warehouse for SAP (Prima Techs SAP Book Series)

In 1997, SAP launched an initiative to extend the reporting and analysis capabilities in the R/3 OLTP environment. This initiative was a direct result of SAP customers' strong, unified voice on providing a robust, stand-alone data warehousing environment. This initiative, once called the Reporting Server, became the largest development project in the history of SAP after the SAP R/3 development. SAP selected five companies to pilot SAP BW in 1997. In 1998, SAP launched a so-called Early Customer Program (ECP) with six customers to gather requirements and to do a proof of concept at customer sites. Digital Equipment Corporation was among the participants in the ECP program. Release 1.2A of BW was made available to the public in September 1998.

SAP Business Information Warehouse is the latest generation of business intelligence solutions. SAP BW is not only a data warehouse, but also forms a data integration hub for SAP New Dimension products. BW is also one of the New Dimension products. From data extraction to data management and analysis, SAP BW provides a robust set of decision-support and reporting capabilities that function as a single packaged software solution. SAP BW software enables users to build an open and dynamic data warehouse needed to share information across the New Dimension products under the mySAP.com framework.

The Accelerated SAP (ASAP) methodology for SAP BW is an excellent way to implement BW.

Though SAP BW ASAP methodology is comprehensive, individual consulting and system integration organizations may enhance or add value to SAP BW ASAP methodology due to their business practices and methodologies. Make sure that their SAP BW methodologies are aligned with the SAP BW ASAP methodology.

Business Information Warehouse Architecture

Business Information Warehouse is an end-to-end data warehousing solution. BW is not an add-in reporting module in an R/3 OLTP system. BW is a standalone product built on top of R/3 BASIS technology. Chapter 1 discusses four technical layers needed to build a data warehouse. In this section, you learn how these four layers are architected in BW, as shown in Figure 2-8.

Figure 2-8: Architecture of Business Information Warehouse.

The data provider services manage all interfaces to all inbound data objects. The inbound data may come from R/2, R/3, or files with known data structures. An SAP BW instance can be a data source to another SAP BW instance. A special programming interface, called Staging Business API (BAPI), is used to pull data in BW. To implement Staging BAPI solutions, I have used Informatica's Power-Center, ActaWorks for SAP BW from Acta Technology, and Genio from Hummingbird. Today, several Staging BAPI-certified data Extraction, Transformation, and Load (ETL) tools from several third-party vendors are available. You learn more about Staging BAPI implementation in Chapter 13, "Enhancing Business Content and Developing Data Extractors."

The service provider layer in Figure 2-8 is the heart of the BW engine. It is here that SAP BW manages all data objects in several persistent data objects such as Metadata, InfoCube, and ODS. Services at this layer interface with data provider services to offer a robust data staging process. The intelligent OLAP processor manages all end-user OLAP activities and processes user requests in a very efficient way. Based on the Microsoft multidimensional database access standard, OLE DB for OLAP (ODBO), SAP has implemented a set of BAPIs that enables third-party tools to access BW data without knowing the complexities of BW data structures. The list of ODBO-certified vendors is rapidly growing. Check the SAP Web site at http://www.sap.com/bw for an up-to-date list of ODBO-certified vendors. To demonstrate ODBO integration with SAP BW 1.2B and 2.0A, I used third-party vendor products from arcplan, Brio Technology, and Business Objects to build analytical applications; I also used inSight from arcplan to build pure ActiveX/ODBO-based pure Web analytical applications. Use ODBO implementation to build a pure Web application using ActiveX controls. You learn how to use ODBO to access data from SAP BW in Chapter 15, "Integrating Third-Party Data Access Products with SAP BW."

The information provider layer in SAP BW is a powerful information delivery architecture using Business Explorer. The Business Explorer Analyzer enhances Microsoft Excel 97 with additional functionality to analyze data combining several workbooks to build a complex data analysis environment. Services at this layer manage all user activities. Information is delivered to authorized consumers from a global catalog, which is tightly integrated with user profiles. The information delivery and service provider services work hand in hand to optimize resource utilization.

Prior to SAP BW 2.0, the only way to publish SAP BW reports/queries on the Web was to save a BEX Analyzer-generated worksheet on the Web server that could be launched via Internet Explorer; however, the SAP BW front-end must be installed on the client workstation to navigate data received. Internet Explorer can open the BEX worksheet, and it will launch the BEX Analyzer session for data navigation and analysis. However, in BW 2.0, you can use the SAP Internet Transaction Server to publish BEX Analyzer Queries in HTML format, which can view data from SAP BW dynamically from Internet browsers, as shown in Figure 2-8 on the top left block enclosed with dotted lines.

The SAP BW data warehouse management layer manages all operations, such as software upgrades, change management, performance tracking, scheduling jobs, and security management. It is similar to the R/3 administration environment.

This means that the R/3 BASIS and database support team can manage the SAP BW environment without much training.

The staging engine is a "process" that facilitates data sourcing and construction of information objects within SAP BW. The staging engine process spreads across the data provider and service provider architectural layers. You learn more about the staging engine in the next two sections.

Business Content in the Business Information Warehouse

SAP BW is built on several classes of "information objects," which come in many shapes and forms. The best way to understand these objects is to install SAP BW business content and understand how such objects are defined, linked, and configured in SAP BW. The business content in SAP BW 1.2B and BW 2.0 comes with several demo applications, such as Sales and Distribution and Profitability Analysis. These are good examples, and I suggest you explore these demo applications before designing your own.

Business content, as shown in Figure 2-9, consists of the following objects (in BW 1.2B and BW 2.0A):

Figure 2-9: Business Content in SAP BW 1.2B and BW 2.0A

• InfoObject. An InfoObject is a basic entity defined in SAP BW. There are two types of InfoObjects: characteristics such as customer, product, and vendor; and key figures such as total cost, revenues, and so on. More than 1,000 InfoObjects are predefined in BW 1.2B, and more than 2,000 in SAP BW 2.0A, across all application modules of R/3.

• Data Extractor. Several function modules and generic data extractors fetch master data, text, hierarchies, and transaction data from an R/3 instance. Customers can enhance existing data extractors or write their own extractors using BW data extraction protocol.

• InfoSource. An InfoSource is a collection of InfoObjects that are logically connected. Data that logically belongs together from a business point of view and can be transported from the source system into SAP BW is offered together in one InfoSource. The InfoObjects, as shown in Figure 2-10 within the dotted circle, represent one InfoSource. SAP BW 1.2B provides more than 90 InfoSources; BW 2.0A has more than 110. In SAP BW 2.0A, the InfoSource boundaries, the dotted line in Figure 2-10, are no longer valid.

  Figure 2-10: SAP BW Staging Process in SAP BW 1.2B and SAP BW 2.0A.

• InfoCube. An InfoCube is a collection of several database tables that supports multidimensional slicing and dicing for OLAP analysis. An InfoCube is based on an extended star-schema model. A fact table containing individual transaction facts (measurements) is attached to a unique set of dimension tables needed to view facts at different angles (dimensions). SAP provides 45 prebuilt InfoCubes in BW 1.2B and more than 100 in BW 2.0A.

• Query. A query is a data subset of a specific InfoCube. These queries are designed to capture most common business analysis scenarios. More than 180 queries across 45 InfoCubes are available in BW 1.2B and more than 450 in SAP BW 2.0A.

• Workbook. A workbook is a collection of Microsoft Excel spreadsheets with embedded queries to provide a solution set. More than 180 workbooks are available in BW 1.2B, and more than 500 in SAP BW 2.0A.

• Key Performance Indicators (KPI). More than 1,000 KPIs are available in BW 1.2B. KPI is a result of a query or subset of a specific measure permanently stored in an InfoCube.

• Channels. A channel is a mechanism that organizes information catalogs to disseminate information. Channels use push technology. SAP provides 10 channels in BW 1.2B. End users can create their own channels and assign reports to channels by using the InfoCatalog manager. In SAP BW 2.0A, the channels are called Activity Groups. More than 70 Activity Groups are available in SAP BW 2.0A.

Business content in SAP BW is increasing with each release. In SAP BW version 1.2B, the business content covers logistics, accounting, and human resources. A complete list of business content is included in the SAP BW kit on CDs. A very brief summary of business content is listed here.

With the release of SAP BW 2.0, the business content strategy has changed from application specific to horizontal and vertical industry focused, such as retail, high tech, utilities, finance, and energy.

• The logistics area covers sales controlling, procurement, inventory control, warehouse management, plant maintenance, quality management, production planning, service management, and project system. Key performance indicators for the logistics area include incoming orders, sales, returns, order fulfillment rate, inventory turnover, material days of supply, delivery time, scrap, service level, lead time, scheduled delivery date variance, and capacity utilization. These KPIs are provided in the form of a query or a pre-calculated fact stored in an InfoCube.

• The accounting area consists of a general ledger, a special ledger, accounts receivable, accounts payable, asset accounting, cash management controlling, product costing, overhead cost, profitability accounting, profit center accounting, enterprise consolidation, and investment management. SAP provides a rich set of KPIs in this area such as profit, return on investment (ROI), contribution margins, liabilities, Day Sales Outstanding, cash position by day or currency, deprecations, cost variances, cost of overhead, and investments.

• Human resources business content includes personnel administration, payroll accounting, time management, recruitment, and training and event management. The HR KPIs in SAP BW 1.2B consist of headcount, productivity rate, illness rate, overtime, wage type overview, applications per offer, applications per entry, fluctuation rate, event costs, and fees.

The business content in the preceding list is not complete. It simply outlines the subject areas that SAP BW business content addresses. I recommend reviewing the documents that come with the SAP BW installation CDs for a detailed list of business content.

Staging Engine

The staging engine in SAP BW consists of several processes that gather data from data sources, clean and perform data transformation, and populate InfoCubes.

In BW terminology, the staging process is described in the following five steps (when the data source is SAP R/3), as shown in Figure 2-10. Though the staging process steps in SAP BW 1.2B and BW 2.0 have remained the same, the boundaries, shaded areas in Figure 2-10, of the InfoSource have changed. Note that in SAP BW 1.2B the scope of InfoSource spans all the way to the source OLTP systems, as shown by the shaded area. In SAP BW 2.0, the original InfoSource is broken into two separately logically connected objects, the DataSource and the InfoSource, as shown on the left in Figure 2-10. The reason for this InfoSource separation is that in SAP BW 1.2B, when you load data, you schedule at an Info-Source level, meaning to pull data from all source systems attached to an InfoSource. You could not load data selectively from one data source when an InfoSource had multiple data sources attached. In SAP BW 2.0, due to Info-Source and Data-Source logical separation, one can select a DataSource to pull data from.

1. SAP R/3 OLTP prepares needed data using the Extract Structure.

2. Extractor in SAP R/3 OLTP moves data to a Transfer Structure.

3. Using ALE or tRFC technology, SAP R/3 copies data to SAP BW in the form of Transfer Structure.

4. Using transfer rules defined in SAP BW, SAP R/3 data is transferred into the Communication Structure.

5. The Update Rules in SAP BW update InfoCubes out of the Communication Structure.

The data staging process is responsible for processing transaction data, master data, text, and hierarchies needed to build the extended star schema in SAP BW. Because update rules are source system independent, it is here that you qualify data coming from several R/3 instances and/or external data before updating an InfoCube.

Data Access Using Business Explorer and OLAP Processor

SAP BW does not use traditional R/3 OLTP reporting tools such as ABAP Query or Report Writer. It uses the Business Explorer (BEX). Business Explorer consists of two components: Browser and Analyzer, as shown in Figure 2-11. The BEX Browser is a Web-centric environment that provides access to a corporate information repository primarily based on SAP's BW InfoCatalog.

Figure 2-11: Business Explorer Browser and Analyzer.

You can use BEX Analyzer to execute queries without the BEX Browser. Note that the BEX Browser, and not the BEX Analyzer, has complete access to all objects in the Enterprise catalog that stores SAP BW and non-SAP BW references. Therefore, using BEX Analyzer, you are limiting visibility to the global catalog content that relates to the InfoCubes queries only. You learn more about developing queries using BEX Browser in Chapter 11, "Analyzing SAP BW Data."

BEX Analyzer is an Excel add-in (SAPBEX.XLA), as shown in Figure 2-12, that turns an ordinary Excel spreadsheet into a powerful SAP BW query development and end-user data access and analysis environment. You will do most of the development work using drag and drop or built-in functions. You could use VBA code to integrate several spreadsheets in a workbook to build dynamic windows event-based data analysis solutions. Appendix B "SAP BW and SAP R/3 Transactions, Tables, and Code Examples," lists available VBA function modules. For complex computation and data navigation schemes, you use user exits and write some ABAP code for data manipulation.

Figure 2-12: Business Explorer Analyzer.

Data Manager

The Data Manager manages data flow and storage in the InfoCubes, the Operational Data Store, and other database objects needed to maintain data integrity across the BW systems.

SAP BW was first implemented on Windows NT 4.0. There were two main reasons for developing SAP BW on Windows NT 4.0: first, to support SAP BW on the Microsoft platform using MS SQL Server 7, and second, to support Oracle DataBase Management Systems (DBMS) prior to implementing on a UNIX platform. At that time-early to mid-1998-Microsoft SQL Server 7 was still a beta product; therefore, SAP focused most BW development work on Oracle8 under Windows NT 4.0. However, toward midsummer 1998, SAP customers pressed SAP to implement SAP BW 1.2A on the UNIX platform as well. BW 1.2A supported the first UNIX implementation.

SAP BW's multidimensional capability is based on a Relational OLAP (ROLAP) model, that is, data is stored in a collection of relational database tables. This model is similar to the industry standard OLAP model called star schema. The star-schema model consists of one central database table, called fact, containing numerical measures (key figures) of business variables. This fact table is connected with several relational tables, each containing a business data analysis view, called dimension. This cluster of fact and associated dimension tables looks like a star, hence the name star schema. Alternatively, this star-schema data model is known as a cube; in SAP BW terminology, it is known as the InfoCube.

A typical star schema for sales analysis is shown in Figure 2-13. Four dimensions-product, store, time, and order-surround the sales fact table. This model enables analysts to view sales measures across a store in a particular geography, across a given time granularity (specified in time dimension), and across any product and order.

Figure 2-13: A Typical Star-Schema Model for Multidimensional Data Analysis.

In a star-schema model, the fact table is usually very large; it can consist of millions to billions of rows. On the other hand, dimension tables are relatively small, ranging from a few thousand to a few million rows. In a typical industry standard (as in Figure 2-13), the dimension table contains master data. These dimension tables are specific to a fact table. This means that dimensions are not shared across other fact tables (and cubes). When another fact table, such as a product forecast, needs the same product dimension data, another dimension table that is specific to a new fact table is needed. This situation creates data management problems because the very same information-in this example, the product-is duplicated in several dimension tables instead of sharing data from one single master table. You next learn how SAP BW handles this sharing of dimensions problem.

BW star schema is an extension of a star schema, as shown in Figure 2-14. Under the BW star-schema model, the dimension tables do not contain master data. The characteristics in a dimension table point to the relevant master data by use of a Set ID (SID) table. The SID table points to characteristic attributes, text, and hierarchies. This multi-step navigational task adds extra overhead when executing a query. However, the benefit of this model is that all fact tables (InfoCubes) share common master data tables. Moreover, the SID table concept allows users to implement multi-language and multi-hierarchy OLAP environments. Another benefit of this model is that it also solves data analysis problems associated with the dimensions that change with time, called slowly changing dimensions. I discuss these advanced topics in Chapter 12, "SAP BW-Defining Custom InfoCubes," where you learn about the dimensional data modeling and extensive query modeling needed to implement a custom InfoCube.

Figure 2-14: Star-Schema Model in SAP BW 1.2B.

The Operational Data Store (ODS) in SAP BW 1.2B consists of relational database tables. An ODS table is specific to a transfer structure. At present, ODS tables and InfoCubes must reside in one SAP BW instance. A stand-alone ODS environment is still under development. The structure of the ODS table is the same as that of a transfer structure of an InfoSource. SAP provides several options to load data in BW. You can load data in ODS only and then build cubes directly from ODS, you can load data in ODS and cubes in parallel, or you can load data in cubes without populating data first in ODS. In BW 1.2B, ODS has very limited functionality, such as no drill-down capability or modification of data prior to storage in ODS. Moreover, data transformations (using transformation rules) are not possible on incoming data before storing content in ODS. The ODS architecture in SAP BW 2.0 is very different than in SAP BW 1.2B. The new ODS is very flexible; the ODS 1.2B limitations described have been resolved in the SAP BW 2.0 implementation. Chapter 17, "The Operational Data Store in SAP BW 2.0," discusses SAP BW 2.0A ODS architecture and its implementation techniques. However, a stand-alone SAP BW ODS implementation is still under discussion.

BW OLAP Processor

The BW OLAP processor is a redesigned version of the SAP R/3 drill-down reporting environment but with a richer set of analysis and display functions integration with BEX.

The BW OLAP processor plays an integral role in analyzing the incoming queries. It is here that the OLAP processor decides whether to process data from a query cube (an in-memory multidimensional data view of an InfoCube based on query definition) or to fetch data from an InfoCube or an aggregate cube (a pre-summarized subset of InfoCube for specific query selection criteria) to meet end-user needs.

The OLAP processor accepts all requests generated from BEX or an ODBO compliant client application. The only difference is that the OLAP processor first translates ODBO requests from a Multidimensional Expression (MDX) to formulate SQL statements to fetch data from the InfoCubes or aggregates. By doing so, all non-R/3 client requests make use of BW services such as authorization, Info-Catalog, master data, and hierarchies navigation. You learn more about SAP BW ODBO implementation in Chapter 15.