ABAP REPORTS

Data flow between SAP and BW

ABAP TOPIC WISE COMPLETE COURSE

BDC OOPS ABAP ALE IDOC'S BADI BAPI Syntax Check
Interview Questions ALV Reports with sample code ABAP complete course
ABAP Dictionary SAP Scripts Script Controls Smart Forms
Work Flow Work Flow MM Work Flow SD Communication Interface

Data flow between SAP and BW

SAP BW uses the pull model to pull needed data from its data sources. Data extraction from within SAP BW shall be scheduled. SAP BW sends data extract requests to the appropriate data sources. The OLTP system collects/extracts and transports requested data to SAP BW. SAP BW loads its data in its data stores.

SAP BW staging engine needs to know full metadata and data content definitions (structures) from its SAP R/3 data source (extraction engine layer). This is achieved by importing metadata from the source SAP R/3.

The SAP BW specific content and environment setup process in SAP R/3 is very similar to the SAP R/3 OLTP release upgrade. To verify that the SAP R/3 OLTP instance is properly upgraded with the SAP BW add-on, list table AVERS using transaction SE16. It must list BW-BCT, as shown .

SAP provides several transports to load business content in SAP R/3. The actual number of transports varies based on your SAP R/3 version (3.0D through 4.6B) and the SAP BW version . These upgrade programs load objects in SAP R/3 for SAP BW data extractors in terms of database tables, InfoObject definitions, Extract structures, Extract programs, and other programs needed to build and set up data extraction schemes for SAP BW.

Metadata Management in SAP OLTP

After installing business content in SAP R/3, we need to create a logical system name using transaction SM59 for SAP R/3 instance as a data source. We will use this instance logical name to connect SAP BW to SAP R/3. Transaction SM59 requires special user privileges. In the SAP R/3 OLTP environment, the basis administrator usually has privileges to create logical system names.In the SAP BW development environment, a few SAP BW developers may require Basis-level privileges to define logical system names for data sources.Metadata is stored in several tables, both in SAP BW and SAP R/3.

Table RODIOBJ contains metadata for all InfoObjects in the source system defining its associated key figures, characteristics, time, and unit of measure in tables RODKYF, RODCHA, RODTIM, and RODUNI, respectively. Table RODIOBJCMP contains only the compounded InfoObjects associated with an InfoObject for the source system.

After defining a source system and testing RFC level connections to the source system, you are ready to load metadata for InfoSources and other SAP BW specific objects defined in SAP R/3. Click the Source systems tabstrip. Expand the Source systems tree, and then right-click the Source System you want to load metadata from.

After updating metadata in SAP BW, go to the InfoSources and InfoObjects tabstrips. Expand the respective object trees. Many new entries are now listed as a result of the metadata update. You need this metadata before activating predefined SAP business content such as InfoSources, transfer rules, communication structures, update rules, InfoCubes, queries, key performance indicators, and channels.

For non-SAP R/3 data sources, first you define a data source in SAP BW for a flat file. Then depending on the content of the flat file, you may create a new InfoSource for master or transaction data.

If the flat file contains master data, it must have only one type of master data InfoObject. In other words, from one file you cannot load master data for customers and material.

How to logon to SAP BW

How to start SAP BW Project ?

Data Warehouse development methodologies :

1.Top-Down Approach

The top-down approach is usually business driven, meaning that the data warehouse is a corporate directive. It has full support, business and financial, from senior management of a company, who has a clear understanding of what to expect from a data warehouse. The hardest part of top-down methodology is that all business organizations have to agree on a common set of Key Performance Indicators and other analytical key measurements needed to make tactical and strategic business decisions.

The benefit of this approach is that you have full corporate-level sponsorship to implement a data warehouse. However, in a corporate-wide initiative, the initial scope of what to implement can be very broad. Also, it will take a long time before end users actually see the value of a data warehouse.

Bottom-Up Approach

The bottom-up approach has been the most common way of implementing data marts. In this case, either a business manager or a department manager steps up to launch a data warehouse project with full financial support. This approach has several advantages: limited scope of what to implement, control over what to analyze, and easier rollout at a department level.

The problem with this approach is that the implementation scope is very limited. The implementation is not visible at the corporate level and carries with it typical data-mart-related issues, such as inconsistent enterprise-wide data definitions and supporting technologies.

Hybrid Approach-SAP Approach

SAP proposes a hybrid approach for BW that takes advantage of the top-down and bottom-up methodologies.The corporate directive is one key feature of the top-down methodology. Here individual department business drivers can exploit SAP BW technology to implement a specific, business-focused data mart solution .These data marts do not become obsolete when a corporate-wide initiative is launched; instead, they become an integral component of the corporate information supply chain. The SAP Accelerated SAP (ASAP) methodology for BW is a hybrid of the top-down and bottom-up approaches in implementing data warehouses.

Initial Assessment-Making a Case for SAP BW

Initially, we have to limit the scope of an SAP BW project to one of the common business process areas, such as Sales and Distribution, Financial Accounting and Material Management, or Human Resources. Draft a project charter that articulates the value of SAP BW in terms of its data analysis flexibil ity, speed, manageability, and information delivery. Present a case for SAP BW to your senior management. Get commitment from a high-level business sponsor before doing further SAP BW project-detailed scoping.

The SAP BW project scope outlines what data analysis functionality will be delivered.Data analysis and reporting needs are classified in the following groups:

Operational Reporting : Requires real-time state-of-business operations, such as overdue orders, orders on hold, or released sales orders at the time of inquiry. These types of inquiries are very much transaction-centric.

Management Reporting and Analysis : Business managers or business analysts define what that time period should be and for what data set. This could be daily, weekly, or monthly.the management reporting and data analysis are subject-centric, such as sales, finance, and human resources.

Enterprise-Wide Reporting: Covers a broad range of data analysis needs across several subject areas spanning several years of data. Here, analysts and data-mining experts explore large amounts of data for strategic planning.(5.1)

How to logon to SAP BW

How to log on SAP BW Admin. Env.

SAP R/3 and SAP BW share the same BASIS technology. SAP BW is installed as a separate instance. SAP R/3 and SAP BW cannot coexist on one database instance. Usually, the system administrator might configure one or more application servers to log on to SAP BW. The SAP BW administrator creates a logon account for you based on your role in the organization and SAP BW usage.

SAP user interface, SAPGUI, software is needed to make a connection to SAP BW for administration and to design data warehouse objects Along with the SAPGUI frontend, Microsoft Office 97 and special SAP BW frontend components are installed on the SAP BW developer and end-user workstation. After the SAPGUI installation, the system administrator sets up logon properties needed to connect the SAP BW instance from your workstation.

Depending on network and SAP BW server activity, the SAP BW startup screen might stay on the screen for a while until connection to SAP BW is established. SAP BW then sends a message back to the end user's workstation and displays the SAP BW session logon window requesting user log on information as shown below.

To complete the logon session to SAP BW, you need a client, user name, and password. SAP BW security administration staff assigns you this information based on your SAP BW interactions. Enter client, user name, and password information in the SAP BW user logon screen, as shown and Language entry is optional. SAP BW supports multiple languages. If you do not enter a language key, the default language is used. In figure below , the user is BWUSER, logged under client 100, and the selected language is English (EN). After entering this information, press the Enter key or double-click the check mark located in the upper-left corner of the logon screen.

When you log on to SAP BW for the first time, you are immediately prompted to change your password. You are asked for your initial and new password. Enter your old and new password and click OK. SAP BW confirms if your new password is valid and accepted.

SAP R/3 supports multiple clients within one instance, such as training, development, and testing. This is possible in SAP R/3 because all entities, except master data objects, are client dependent.

In SAP BW, you cannot have multiple clients because SAP BW data objects are client independent. Client 000 in SAP BW is reserved for SAP internal usage. One additional active client is defined for the rest of the SAP BW activities. The reason for a client-independent environment in SAP BW is that a data warehouse holds and shares information across all business units, organizations, or legal entities of an enterprise as well as data that does not belong to any organization such as industry business performance benchmarks and Key Performance Indicators (KPI).

Data consistency is another reason for having one single client in SAP BW. You need consistent business rules regardless of who owns the transaction data. Having clients in SAP BW will restrict or add complexity in data navigation and management across multiple clients and will introduce data quality problems.

SAP BW Third Party Solutions

SAP BW is based on common industry-accepted standards that enable third-party vendors to integrate their products with SAP BW. Since 1998, several third-party vendors have certified their products to load data to and access it from SAP BW. Data loading tools use Staging BAPI, and data access tools use ODBO.

Most data warehouse and business intelligence vendors provide a collection of tools that support a specific function needed to construct a data warehouse such as data extraction, data transformation, and end-user data access tools. Often, tools provided by vendors, even from one vendor, do not support a common development environment or user interface. This situation makes it difficult for organizations to build dedicated teams for specific tasks of a data warehouse construction process.

Though the number of vendors providing data warehousing solutions and tools is increasing, the following section compares only a few tools available at the time of this writing that interface with SAP R/3 and SAP BW.

ActaWorks for SAP from Acta Technology is a data extraction, transformation, and data-loading product. Though ActaWorks provides some initial SAP R/3 data extraction schemes and generates ABAP code to populate data marts, you must develop change identification and delta change capture techniques for transaction data in SAP R/3. ActaWorks is a good product, but from an overall global information delivery perspective, it is not an information delivery environment. To fill this gap, Acta is partnering with Cognos and other vendors to provide a frontend environment to complete its data warehouse solution.

inSight from arcplan is among the first few ODBO-certified products for SAP BW. inSight is a very mature product. You can access EIS data from SAP R/3 and SAP BW queries. arcplan has gone one step further when it comes to delivering SAP R/3 and SAP BW data analysis and reporting environments to the Internet by providing ActiveX controls for pure Web clients. arcplan has introduced another product, DynaSite, for pure Java-based clients.

Evolutionary Technology Inc. (ETI) is best known for its data extraction technologies. Its products are powerful and can extract data from just about any data source, including SAP R/3. Its products are also certified to load data in SAP BW using Staging BAPI. In comparison to SAP BW, ETI covers only data functionality in BW.

Information Builders, Inc. (IBI) is one of the pioneers in enterprise reporting and data warehousing. IBI provides several tools that cover similar functionality as SAP BW. However, such tools still use old technologies and are not integrated. They do, however, provide a robust Enterprise Data Access (EDA) server that supports distributed queries across homogeneous and heterogeneous data sources and platforms.

Informatica has been a leading data mart construction tool provider for more than a decade. Informatica products can extract data from flat files and relational databases to process and load data in target data marts. Informatica was late to enter the ERP data extraction race. Recently, Informatica has filled the ERP data extraction gap by adding new data extraction components for SAP R/3 and PeopleSoft. Informatica's PowerCenter was among the first wave of products certified for Staging BAPI to load data in SAP BW.

Oracle is in direct competition with SAP BW in the data warehouse market. Oracle provides many tools to build data warehouses and deliver information. Oracle has a good potential to provide a unified data warehouse environment but still lacks product integration when compared with SAP BW. Oracle also provides Oracle Tool Kit for SAP R/3.

Seagate is a midrange data warehouse construction and information delivery tool provider. Seagate is acquiring technologies from several vendors and offers an integrated data warehouse solution. Seagate Holos OLAP is the OLAP engine. The Reporting tools come from Crystal that can access data from most relational databases. Seagate also announced that it will build data extraction tools for SAP R/3.

Sample SAP BW Report

The first step to access SAP BW reports is to start the BEX Browser. You will be prompted for your user name and password assigned by your SAP BW administrator.

Once SAP BW accepts your user name and password, the BEX Browser displays all reports and analytical applications for which you are authorized. On the left are the channels. Each channel contains one or more reports.

Figure below shows that you are in the General Purpose Channel; the title also shows the channel name. This channel has two clusters. Clusters are used to logically group a set of reports that address a similar subject area. This example uses Customer and Employee channels.

To launch an analytical application from the BEX Browser, simply right-click the report text, Sales Distribution by Employee, as shown in figure below. This action gives you several options, such as Execute, Preview, and Change. Select Execute to open the selected report.

Here you can drill down by product hierarchy as shown below or some other navigational attributes. You can also normalize data in several ways to meet your analytical needs.

Data Flow in SAP BW

When the user launches a report from BEX Explorer, it starts a BEX Analyzer session and opens an Excel workbook that contains an embedded BW query.

BEX Analyzer sends a request to the BW application server for relevant data. If this is a new query for a user, no data is found at the application server level. The query is then pushed further down to the database server to fetch a result set from an InfoCube or an aggregate associated with an InfoCube.

The database server fetches data from an InfoCube and hands it over to the application server, where it restructures the table-oriented data to a multidimensional structure, called a query cube, as shown below.

A query cube is the subset of an InfoCube, which is specified by the query. Some characteristics are restricted to specific values, whereas some are not chosen in the query. For this reason, the size of the query cube is much smaller than the actual InfoCube.

A query cube is unique to a user request and, hence, is not shared by other users. If several users start the same query, all will have their own unique query cubes built at the application server.

This is because the result set may vary based on selected dimensions or special filters. Also, each will have full control on how to navigate through the query cube (slicing and dicing) without interfering with analytical work done by others.

As the number of query cubes increases, the application server may run out of memory very quickly. For this reason, the application server for SAP BW requires more memory as compared to traditional SAP R/3 for OLTP instance. Moreover, the SAP BW application server needs more computer power due to data aggregations and computation requirements to satisfy end-user data analysis needs.

Once a query cube completes, a portion of data from the query cube is pushed up to load data in Microsoft Excel worksheets based on the current end user's data view.

Thenthe end user performs typical slice-and-dice and drill-down data analysis using BEX Analyzer functions. If the user needs additional drill-down data that is not presented at a given time, the request is sent to the query cube to send data in the spreadsheet for drill-down analysis.

If that detailed data is not in the query cube, the OLAP processor will refresh the query cube from the InfoCube. This navigational and data refresh process continues until the data requirements are met at each level.

A query cube always relates to only one InfoCube. In other words, you cannot build a query cube that gets data from more than one InfoCube.

SAP Data Warehouse Building Strategies

SAP customers have taken several approaches to building a data warehouse using R/3 technologies.

Database-Centric Data Warehouse:

In the database centric approach we have to make a copy of an R/3 database for users to report against. Conceptually, the idea is simple, but it is an operations keeping R/3 OLTP and R/3 reporting in synch is very difficult.

The benefit of this model is that you will have no performance degradation on your R/3 OLTP environment due to reporting and data analysis activities. We can define special user authorization and profiles for reporting users . This R/3 can also become the data source for intra-application data instead of OLTP R/3.

The problem in this approach is that we you have access to only the data that exists in the OLTP instance that is we have no historical data to report on. Moreover, we will face the same limitations and complexities of R/3 information systems and reporting tools that are not Web enabled. This database-centric approach will be an expensive proposition due to additional hardware and intense operations tasks to keep both instances operational at all times.

ALE-Centric Data Warehouse

In this model we do not copy a full database but rather use ALE to propagate specific changes into the reporting environment-another R/3 instance.

Based on the customer's business model, ALE packages transaction and/or master data in the form of Intermediate Documents (IDOCs). IDOCs are data containers similar to EDI standard documents. The figure below shows an example of how a MNC used this approach to implement an ALE-based reporting environment .

Here four individual R/3 instances were connected via ALE:

R/3 instance to manage master data.
R/3 instance for financial operations.
Instance for logistics operations.
Instance for reporting.

Under this model, special SIS structures and special ledger structures are transferred to reporting instances using ALE, copy management, and some homegrown techniques to synchronize objects across all instances. ALE copied new changes in the reporting instance at 10-minute intervals.

Additional SIS structures were updated within the reporting instance using copy management techniques to build additional tables containing several levels of aggregated data for reporting. This enabled end users to access near real-time data without impacting performance on the R/3 OLTP operations. For corporate central data warehouses, new data was extracted from the reporting instance instead of the R/3 OLTP instance.

The ALE centric reporting environment advantages:

Contains only that data required for reporting and analysis. It is not an identical copy of OLTP R/3 instances.
Can accumulate historical data.
Can import non-R/3 data for reporting.

The disadvantages of this environment are :

Has the same inflexible R/3 reporting tools.
Requires additional application servers to manage ALE traffic for all four instances.
ALE technology is not rich and mature enough to handle large data volumes and user-specific data objects.
Physical database implementation is still R/3-transaction-centric. Has no true multidimensional star data structures.
Is heavy on hardware/software/network resources.

Third-Party-Tool-Centric Data Warehouse:

Third-party data extraction tools work to a point when transaction and associated data volume is small and business rules/configuration behind such data sets is simple. Some data extraction tools extract data at the database level, leaving critical data behind in pool and cluster tables

Often, the ABAP code generated by these tools is not optimized enough to fetch and transport data in the most efficient manner. One of the major problems in implementing a third-party solution is that the OLTP and data warehouse/OLAP teams do not share the same infrastructure.