Google+ Facebook Twitter MySpace SC

SAP BW MATERIALS

Business Scenario and SAP BW

The objective of data warehousing is to analyze data from diverse sources to support decision making. To achieve this goal,we face two challenges:
  • Poor system performance. A data warehouse usually contains a large volume of data. It is not an easy job to retrieve data quickly from the data warehouse for analysis purposes. For this reason,the data warehouse design uses a special technique called a star schema.

  • Difficulties in extracting,transferring,transforming,and loading (ETTL) data from diverse sources into a data warehouse. Data must be cleansed before being used. ETTL has been frequently cited as being responsible for the failures of many data warehousing projects. You would feel the pain if you had ever tried to analyze SAP R/3 data without using SAP BW.
SAP R/3 is an ERP (Enterprise Resources Planning) system that most large companies in the world use to manage their business transactions. Before the introduction of SAP BW in 1997,ETTL of SAP R/3 data into a data warehouse seemed an unthinkable task. This macro environment explained the urgency with which SAP R/3 customers sought a data warehousing solution. The result is SAP BW from SAP,the developer of SAP R/3.
In this we will introduce the basic concept of data warehousing. We will also discuss what SAP BW (Business Information Warehouse)is,explain why we need it,examine its architecture,and define Business Content.

First,we use sales analysis as an example to introduce the basic concept of data warehousing.
Sales Analysis-A Business Scenario
Suppose that you are a sales manager,who is responsible for planning and implementing sales strategy. Your tasks include the following:
  • Monitoring and forecasting sales demands and pricing trends
  • Managing sales objectives and coordinating the sales force and distributors
  • Reviewing the sales activities of each representative,office,and region
Suppose also that you have the data in Tables available about your firm's materials,customers,and sales organization.
 MATERIALS
CUSTOMERS
SALES ORGANIZATION
SALES DATA
The data in these tables represent a simplified business scenario. In the real world,you might have years of data and millions of records. To succeed in the face of fierce market competition,you need to have a complete and up-to-date picture of your business and your business environment. The challenge lies in making the best use of data in decision support. In decision support,you need to perform many kinds of analysis.
This type of online analytical processing (OLAP) consumes a lot of computer resources because of the size of data. It cannot be carried out on an online transaction processing (OLTP) system,such as a sales management system. Instead,we need a dedicated system,which is the data warehouse.
Basic Concept of Data Warehousing
A data warehouse is a system with its own database. It draws data from diverse sources and is designed to support query and analysis. To facilitate data retrieval for analytical processing,we use a special database design technique called a star schema.
Star Schema
The concept of a star schema is not new; indeed,it has been used in industry for years. For the data in the previous section,we can create a star schema like that shown below.
STAR SCHEMA
The star schema derives its name from its graphical representation—that is,it looks like a star. A fact table appears in the middle of the graphic,along with several surrounding dimension tables. The central fact table is usually very large,measured in gigabytes. It is the table from which we retrieve the interesting data. The size of the dimension tables amounts to only 1 to 5 percent of the size of the fact table. Common dimensions are unit and time,which are not shown in figure. Foreign keys tie the fact table to the dimension tables. Keep in mind that dimension tables are not required to be normalized and that they can contain redundant data.
As indicated in Tables the sales organization changes over time. The dimension to which it belongs—sales rep dimension—is called the slowly changing dimension.
The following steps explain how a star schema works to calculate the total quantity sold in the Midwest region:
  1. From the sales rep dimension,select all sales rep IDs in the Midwest region.
  2. From the fact table,select and summarize all quantity sold by the sales rep IDs of Step 1.
ETTL—Extracting,Transferring,Transforming,and Loading Data
Besides the difference in designing the database,building a data warehouse involves a critical task that does not arise in building an OLTP system: to extract, transfer,transform,and load (ETTL) data from diverse data sources into the data warehouse.
ETTL PROCESS
In data extraction,we move data out of source systems,such as an SAP R/3 system. The challenge during this step is to identify the right data. A good knowledge of the source systems is absolutely necessary to accomplish this task.
In data transfer,we move a large amount of data regularly from different source systems to the data warehouse. Here the challenges are to plan a realistic schedule and to have reliable and fast networks.
In data transformation,we format data so that it can be represented consistently in the data warehouse. For example,we might need to convert an entity with multiple names (such as AT&T,ATT,or Bell) into an entity with a single name (such as AT&T). The original data might reside in different databases using different data types,or in different file formats in different file systems. Some are case sensitive;others may be case insensitive.
In data loading,we load data into the fact tables correctly and quickly. The challenge at this step is to develop a robust error-handling procedure.
ETTL is a complex and time-consuming task. Any error can jeopardize data quality,which directly affects business decision making. Because of this fact and for other reasons,most data warehousing projects experience difficulties finishing on time or on budget.
To get a feeling for the challenges involved in ETTL,let's study SAP R/3 as an example. SAP R/3 is a leading ERP (Enterprise Resources Planning) system. According to SAP,the SAP R/3 developer,as of October 2000,some 30,000 SAP R/3 systems were installed worldwide that had 10 million users. SAP R/3 includes several modules,such as SD (sales and distribution),MM (materials management),PP (production planning),FI (financial accounting),and HR (human resources). Basically,you can use SAP R/3 to run your entire business.
SAP R/3's rich business functionality leads to a complex database design. In fact,this system has approximately 10,000 database tables. In addition to the complexity of the relations among these tables,the tables and their columns sometimes don't even have explicit English descriptions. For many years,using the SAP R/3 data for business decision support had been a constant problem.
Recognizing this problem,SAP decided to develop a data warehousing solution to help its customers. The result is SAP Business Information Warehouse,or BW. Since the announcement of its launch in June 1997,BW has drawn intense interest. According to SAP,as of October 2000,more than 1000 SAP BW systems were installed worldwide.

BW-An SAP Data Warehousing Solution
BW is an end-to-end data warehousing solution that uses preexisting SAP technologies. BW is built on the Basis 3-tier architecture and coded in the ABAP (Advanced Business Application Programming) language. It uses ALE (Application Link Enabling) and BAPI (Business Application Programming Interface) to link BW with SAP systems and non-SAP systems.
BW Architecture
The following figure shows the BW architecture at the highest level. This architecture has three layers:
  1. The top layer is the reporting environment. It can be BW Business Explorer (BEx) or a third-party reporting tool. BEx consists of two components:
    • BEx Analyzer
    • BEx Browser
  2. BEx Analyzer is Microsoft Excel with a BW add-in. Thanks to its easy-to-use graphical interface,it allows users to create queries without coding SQL statements. BEx Browser works much like an information center,allowing users to organize and access all kinds of information. Third-party reporting tools connect with BW OLAP Processor through ODBO (OLE DB for OLAP).
  3. The middle layer,BW Server,carries out three tasks:
    • Administering the BW system
    • Storing data
    • Retrieving data according to users' requests
  4. We will detail BW Server's components next.
  5. The bottom layer consists of source systems,which can be R/3 systems,BW systems,flat files,and other systems. If the source systems are SAP systems,an SAP component called Plug-In must be installed in the source systems. The Plug-In contains extractors. An extractor is a set of ABAP programs,database tables,and other objects that BW uses to extract data from the SAP systems. BW connects with SAP systems (R/3 or BW) and flat files via ALE; it connects with non-SAP systems via BAPI.
  6. The middle-layer BW Server consists of the following components:
    • Administrator Workbench,including BW Scheduler and BW Monitor
    • Metadata Repository and Metadata Manager
    • Staging Engine
    • PSA (Persistent Staging Area)
    • ODS (Operational Data Store) Objects
    • InfoCubes
    • Data Manager
    • OLAP Processor
    • BDS (Business Document Services)
    • User Roles
       BW ARCHITECTURE
Administrator Workbench  maintains meta-data and all BW objects. It has two components:
  • BW Scheduler for scheduling jobs to load data
  • BW Monitor for monitoring the status of data loads
This book mainly focuses on Administrator Workbench.
Metadata Repository contains information about the data warehouse. Meta-data comprise data about data. Metadata Repository contains two types of meta-data: business-related (for example,definitions and descriptions used for reporting) and technical (for example,structure and mapping rules used for data extraction and transformation). We use Metadata Manager to maintain Metadata Repository.
Staging Engine implements data mapping and transformation. Triggered by BW Scheduler,it sends requests to a source system for data loading. The source system then selects and transfers data into BW.
PSA (Persistent Staging Area) stores data in the original format while being imported from the source system. PSA allows for quality check before the data are loaded into their destinations,such as ODS Objects or InfoCubes.
ODS (Operational Data Store) Objects allow us to build a multilayer structure for operational data reporting. They are not based on the star schema and are used primarily for detail reporting,rather than for dimensional analysis.
InfoCubes are the fact tables and their associated dimension tables in a star schema.
Data Manager maintains data in ODS Objects and InfoCubes and tells the OLAP Processor what data are available for reporting.
OLAP Processor is the analytical processing engine. It retrieves data from the database,and it analyzes and presents those data according to users' requests.
BDS (Business Document Services) stores documents. The documents can appear in various formats,such as Microsoft Word,Excel,PowerPoint,PDF,and HTML. BEx Analyzer saves query results,or MS Excel files,as workbooks in the BDS.
User Roles are a concept used in SAP authorization management. BW organizes BDS documents according to User Roles. Only users assigned to a particular User Role can access the documents associated with that User Role.
BW Business Content
One of the BW's strongest selling points is its Business Content. Business Content contains standard reports and other associated objects. For example,BW provides you,the sales manager,with the following standard reports:
Quotation Processing
  • Quotation success rates per sales area
  • Quotation tracking per sales area
  • General quotation information per sales area
Order Processing
  • Monthly incoming orders and revenue
  • Sales values
  • Billing documents
  • Order,delivery,and sales quantities
  • Fulfillment rates
  • Credit memos
  • Proportion of returns to incoming orders
  • Returns per customer
  • Quantity and values of returns
  • Product analysis
  • Product profitability analysis
Delivery
  • Delivery delays per sales area
  • Average delivery processing times
Analyses and Comparisons
  • Sales/cost analysis
  • Top customers
  • Distribution channel analysis
  • Product profitability analysis
  • Weekly deliveries
  • Monthly deliveries
  • Incoming orders analysis
  • Sales figures comparison
  • Returns per customer
  • Product analysis
  • Monthly incoming orders and revenue
Administrative and Management Functions
  • Cost center:plan/actual/variance
  • Cost center: responsible for orders,projects,and networks
  • Order reports
  • WBS Element: plan/actual/variance
  • Cost center: plan/actual/variance
  • Cost center: hit list of actual variances
  • Cost center: actual costs per quarter
  • Cost center: capacity-related headcount
When necessary,we can also use a function,called Generic Data Extraction,to extract R/3 data that cannot be extracted with the standard Business Content.
BW in mySAP.com
BW is evolving rapidly. Knowing its future helps us plan BW projects and their scopes. SAP's e-business platform that aims to achieve the collaboration among businesses using the Internet technology. It consists of three components:
  • mySAP Technology
  • mySAP Services
  • mySAP Hosted Solutions
As shown in figure,mySAP Technology includes a portal infrastructure for user-centric collaboration,a Web Application Server for providing Web services,and an exchange infrastructure for process-centric collaboration. The portal infrastructure has a component called my SAP Business Intelligence; Using mySAP Technology,SAP develops e-business solutions,such as mySAP Supply Chain Management (mySAP SCM),mySAP Customer Relationship Management (mySAP CRM),and mySAP Product Lifecycle Management (mySAP PLM).

MYSAP TECHNOLOGY AND MYSAP SOLUTIONS


MySAP Services are the services and support that SAP offers to its customers. They range from business analysis,technology implementation,and training to system support.
MySAP Hosted Solutions are the outsourcing services from SAP. With these solutions,customers do not need to maintain physical machines and networks.
Summary
This introduced the basic concept of data warehousing and discussed what SAP BW is,why we need it,its architecture,and what Business Content is. Later chapters will discuss these subjects in more details.
Key Terms


Creating an InfoCube

In BW,Customer ID,Material Number,Sales Representative ID,Unit of Measure,and Transaction Date are called characteristics. Customer Name and Customer Address are attributes of Customer ID,although they are characteristics as well. Per Unit Sales Price,Quantity Sold,and Sales Revenue are referred to as key figures. Characteristics and key figures are collectively termed InfoObjects.
A key figure can be an attribute of a characteristic. For instance,Per Unit Sales Price can be an attribute of Material Number. In our examples,Per Unit Sales Price is a fact table key figure. In the real world,such decisions are made during the data warehouse design phase. InfoCube Design provides some guidelines for making such decisions.
InfoObjects are analogous to bricks. We use these objects to build InfoCubes. An InfoCube comprises the fact table and its associated dimension tables in a star schema.
In this chapter,we will demonstrate how to create an InfoCube that implements the star schema from figure. We start from creating an InfoArea. An InfoArea is analogous to a construction site,on which we build InfoCubes.

Creating an InfoArea
In BW,InfoAreas are the branches and nodes of a tree structure. InfoCubes are listed under the branches and nodes. The relationship of InfoAreas to InfoCubes in BW resembles the relationship of directories to files in an operating system. Let's create an InfoArea first,before constructing the InfoCube.
Work Instructions
Step 1. After logging on to the BW system,run transaction RSA1,or double-click Administrator Workbench.
Step 2. In the new window,click Data targets under Modelling in the left panel. In the right panel,right-click InfoObjects and select Create InfoArea….

Note
In BW,InfoCubes and ODS Objects are collectively called data targets.
Step 3. Enter a name and a description for the InfoArea,and then mark to continue.
Result
The InfoArea has been created.
Creating InfoObject Catalogs
Before we can create an InfoCube,we must have InfoObjects. Before we can create InfoObjects,however,we must have InfoObject Catalogs. Because characteristics and key figures are different types of objects,we organize them within their own separate folders,which are called InfoObject Catalogs. Like InfoCubes,InfoObject Catalogs are listed under InfoAreas.
Having created an InfoArea,let's now create InfoObject Catalogs to hold characteristics and key figures.
Work Instructions
Step 1. Click InfoObjects under Modelling in the left panel. In the right panel,right-click InfoArea–demo,and select Create InfoObject catalog….
Step 2. Enter a name and a description for the InfoObject Catalog,select the option Char.,and then click to create the InfoObject Catalog.
Step 3. In the new window,click to check the Info Object Catalog. If it is valid,click to activate the InfoObject Catalog. Once the activation process is finished,the status message InfoObject catalog IOC_DEMO_CH activated appears at the bottom of the screen.
Result
Click to return to the previous screen. The newly created InfoObject Catalog will be displayed,as shown in Screen
Following the same procedure,we create an InfoObject Catalog to hold key figures. This time,make sure that the option Key figure is selected Screen.
Creating InfoObjects-Characteristics
Now we are ready to create characteristics.
Work Instructions
Step 1. Right-click InfoObject Catalog–demo: characteristics,and then select Create InfoObject….
Step 2. Enter a name and a description,and then click to continue.
Step 3. Select CHAR as the DataType,enter 15 for the field Length,and then click the tab Attributes.
Step 4. Enter an attribute name IO_MATNM,and then click to create the attribute.
Note: Notice that IO_MATNM is underlined. In BW,the underline works like a hyperlink. After IO_MATNM is created,when you click IO_MATNM,the hyperlink will lead you to IO_MATNM's detail definition window.
Step 5. Select the option Create attribute as characteristic,and then click to continue.
Step 6. Select CHAR as the DataType,and then enter 30 for the field Length. Notice that the option Exclusively attribute is selected by default. Click to continue.
Note: If Exclusively attribute is selected,the attribute IO_MATNM can be used only as adisplay attribute,not as a navigational attribute. "InfoCube Design Alternative I Time Dependent Navigational Attributes," discusses an example of the navigation attributes.
Selecting Exclusively attribute allows you to select Lowercase letters. If the option Lowercase letters is selected,the attribute can accept lowercase letters in data to be loaded.
If the option Lowercase letters is selected,no master data tables,text tables,or another level of attributes underneath are allowed. "BW Star Schema," describes master data tables and text tables,and explains how they relate to a characteristic.
Step 7. Click to check the characteristic. If it is valid,click to activate the characteristic.
Step 8. A window is displayed asking whether you want to activate dependent InfoObjects. In our example,the dependent InfoObject is IO_MATNM.
Click to activate IO_MAT and IO_MATNM.
Result
You have now created the characteristic IO_MAT and its attribute IO_MATNM.
Note: Saving an InfoObject means saving its properties,or meta-data. You have not yet created its physical database objects,such as tables.
Activating an InfoObject will create the relevant database objects. After activating IO_MAT,the names of the newly created master data table and text table are displayed under the Master data/texts tab. The name of the master data table is /BIC/PIO_MAT,and the name of the text table is /BIC/TIO_MAT.
Notice the prefix /BIC/ in the database object names. BW prefixes /BI0/ to the names of database objects of Business Content objects,and it prefixes /BIC/ to the names of database objects of customer-created BW objects.
Repeat the preceding steps to create the other characteristics listed.
CHARACTERISTICS
The column "Assigned to" specifies the characteristic to which an attribute is assigned. For example,IO_MATNM is an attribute of IO_MAT.
The Material Description in Table will be treated as IO_MAT's text,as shown in Table,"Creating InfoPackages to Load Characteristic Data." We do not need to create a characteristic for it.
IO_SREG and IO_SOFF are created as independent characteristics,instead of IO_SREP's attributes. Section 3.6,"Entering the Master Data,Text,and Hierarchy Manually," explains how to link IO_SOFF and IO_SREG to IO_SREP via a sales organization hierarchy. "InfoCube Design Alternative I—Time-Dependent Navigational Attributes," discusses a new InfoCube design in which IO_SOFF and IO_SREG are IO_SREP's attributes.
BW provides characteristics for units of measure and time. We do not need to create them.From Administrator Workbench,we can verify that the characteristics in Table have been created by clicking InfoArea–demo,and then clicking InfoObject Catalog–demo: characteristics.
Creating Info Objects-Key Figures
Next,we start to create the keys.
Work Instructions
Step 1. Right-click InfoObject Catalog–demo: key figures,and then select Create InfoObject.
Step 2. Enter a name and a description,and then click to continue.
Step 3. Select Amount in the block Type/data type,select USD as the Fixed currency in the block Currency/unit of measure,and then click to check the key figure. If it is valid,click to activate the key figure.
Result
You have created the key figure IO_PRC. A status message All InfoObject(s) activated will appear at the bottom of Screen.
Repeat the preceding steps to create other key figures listed.
KEY FIGURES
From Administrator Workbench,we can verify that the key figures in Table have been created (Screen) by clicking InfoArea–demo,and then clicking InfoObject Catalog–demo: key figures.
Having created the necessary InfoObjects,we now continue to create the InfoCube.
Creating an InfoCube
The following steps demonstrate how to create an InfoCube,the fact table and associated dimension tables,for the sales data shown in Table
Work Instructions
Step 1. Select Data targets under Modelling in the left panel. In the right panel,right-click InfoArea–demo and then select Create InfoCube….
Step 2. Enter a name and a description,select the option Basic Cube in block InfoCube type,and then click to create the InfoCube.
Note: An InfoCube can be a basic cube,a multi-cube,an SAP remote cube,or a general remote cube.A basic cube has a fact table and associated dimension tables,and it contains data. We are building a basic cube.
A multi-cube is a union of multiple basic cubes and/or remote cubes to allow cross-subject analysis. It does not contain data. See,Aggregates and Multi-Cubes,for an example.
A remote cube does not contain data;instead,the data reside in the source system. A remote cube is analogous to a channel,allowing users to access the data using BEx. As a consequence,querying the data leads to poor performance.
If the source system is an SAP system,we need to select the option SAP RemoteCube. Otherwise,we need to select the option Gen. Remote Cube. This book will not discuss remote cubes.
Step 3. Select IO_CUST,IO_MAT,and IO_SREP from the Template table,and move them to the Structure table by clicking
Next,click the Dimensions… button to create dimensions and assign these characteristics to the dimensions.
Step 4. Click,and then enter a description for the dimension.
Note: BW automatically assigns technical names to each dimension with the format <InfoCube name><Number starting from 1>.
Fixed dimension <InfoCube name><P|T|U> is reserved for Data Packet,Time,and Unit. Section,"Data Load Requests," discusses the Data Packet dimension.
A dimension uses a key column in the fact table. In most databases,a table can have a maximum of 16 key columns. Therefore,BW mandates that an InfoCube can have a maximum of 16 dimensions: three are reserved for Data Packet,Time,and Unit; the remaining 13 are left for us to use.
Repeat the same procedure to create two other dimensions. Next,click the Assign tab to assign the characteristics to the dimensions.
Step 5. Select a characteristic in the Characteristics and assigned dimension block,select a dimension to which the characteristic will be assigned in the Dimensions block,and then click to assign the characteristic to the dimension.
Step 6. After assigning all three characteristics to their dimensions,click to continue.
Step 7. Select the Time characteristics tab,select 0CALDAY from the Template table,and move it to the Structure table by clicking
Step 8. Select the Key figures tab,select IO_PRC,IO_QUAN,and IO_REV from the Template table and move them to the Structure table by clicking.
Next,click to check the InfoCube. If it is valid,click to activate the InfoCube.
Result:
You have created the InfoCube IC_DEMOBC. A status message InfoCube IC_DEMOBC activated will appear at the bottom of Screen.
Summary
In this chapter,we created an InfoCube. To display its data model,you can right-click InfoCube–demo: Basic Cube,then select Display data model….
The data model appears in the right panel of Screen .
Note:
IO_SREG and IO_SOFF are not listed under IO_SREP as attributes; rather,they have been created as independent characteristics. "Entering the Master Data,Text,and Hierarchy Manually," describes how to link IO_SOFF and IO_SREG to IO_SREP via a sales organization hierarchy. "InfoCube Design Alternative I— Time-Dependent Navigational Attributes,"discusses a new InfoCube design in which IO_SOFF and IO_SREG are IO_SREP's attributes.
Key Terms

Loading Data into the InfoCube

Data reside in source systems. In the example used in this chapter,the data reside in our PC in four Microsoft Excel files. To set up a protocol to ensure that BW knows where to find these files and how to extract the data,we must define a source system in BW. To schedule a load,we must also define a BW object called InfoPackage.
For the InfoPackage to know the structure of the data's destination (either a characteristic or an InfoCube),we must define yet another BW object called InfoSource. If the destination is an InfoCube,after the data pass through the InfoSource,BW allows us to aggregate key figures. In BW,this aggregation procedure is called an update rule.
With this background information in mind,now let's define a source system so BW knows where to find the data.
Creating a Source System
A BW system can accept data from all kinds of source systems,such as the following:
  • R/3 systems
  • BW systems
  • Flat files
  • External systems through third-party ETTL tools
The following procedure shows how to create a flat file source system.Business Content,describes how to create an R/3 source system.
Work Instructions
Step 1. Select Source systems under Modelling in the left panel. In the right panel,right-click Source systems and select Create….
Step 2. Select the FileSystem,manual meta data,data using file inte option,and then click to continue.
Step 3. Enter a name and a description for the source system,and then click to create the source system.
Result:
You have created the source system. A status message Source system SS_DEMOFF creation successful finished will appear at the bottom of Screen.
Creating an Application Component
In BW,InfoSources and InfoPackages are organized using a tree structure called application components. The application components are analogous to the InfoAreas for InfoCubes. Next,we create an application component.
Work Instructions
Step 1. Select InfoSources under Modelling in the left panel. In the right panel,right-click InfoSources and select Create application component….
Step 2. Enter a name and a description for the application component,and then click to continue.
Result:
The application component has been created,as shown in Screen
Note:BW adds a prefix of "Z" to the technical names of application components,unlike the naming system used for other BW objects.
Creating an InfoSource for Characteristic Data
we learned that ETTL is a challenging task. BW uses InfoSources and transfer rules to define the mapping and transformation rules that govern the ETTL process.
In this section,we use IO_MAT as an example to show how to define an InfoSource and transfer rules for characteristic data. we will discuss how to define an InfoSource and transfer rules for transaction data.
Work Instructions
Step 1. Right-click the newly created Application Component–demo,and then select Create InfoSource….
Step 2. Select the option Master data/texts/hierarchies,and then click to continue.
Note:
In BW,characteristic data can consist of master data,texts,and hierarchies. Selecting this option ensures that the InfoSource to be created will apply to a characteristic. "BW Star Schema,"discusses master data,texts,and hierarchies and explains their relationships to characteristics.
Step 3. Enter IO_MAT,and then click to continue.
Step 4. Click to pass the message.

Step 5. Now we need to tell the newly created InfoSource where to find the data and what the transfer rules are. Right-click Material number,and then select Assign DataSource….
Note:
A DataSource is not only a structure in which source system fields are logically grouped together,but also an object that contains ETTL-related information. Four types of DataSources exist:
  • DataSources for transaction data
  • DataSources for characteristic attributes
  • DataSources for characteristic texts
  • DataSources for characteristic hierarchies
Generic R/3 Data Extraction,discusses how to create a DataSource.
Step 6. Enter the source system created,SS_DEMOFF,in the field Source system,and then click to continue.
Note:
DataSources are associated with source systems,so we must first tell BW where to find the DataSources.
Step 7. Click to pass these two messages.
Note:
When we created IO_MAT,we elected to create master data and texts as shown in Screen 2.16. When the source system is a file system,as in this example,because of this selection,BW creates two DataSources: IO_MAT_ATTR for master data,and IO_MAT_TEXT for texts. In the next section,when we load the data,we will see the difference between these two DataSources.
Step 8. Click to activate the transfer rules.
Note: The icon in the Tp column indicates that the transfer rule is a simple one-to-one mapping. By default,BW defines simple one-to-one transfer rules. In this case,we accept the default definition. We will show how to use ABAP to define transfer rules in "Creating an InfoSource for Transaction Data."
Result:
We have created an InfoSource and transfer rules. A status message Transfer rules IO_MAT_ATTR_AH activated will appear at the bottom of Screen 3.15. Now that the InfoSource and transfer rules are in place,we can create InfoPackages to load the characteristic data.
Creating InfoPackages to Load Characteristic Data
BW uses InfoPackages to define how to select data,and when to transfer and load the data. In BW,a characteristic can consist of three data components:
  • Master data,or attributes as they are sometimes called
  • Text
  • Hierarchy
"BW Star Schema," discusses master data,texts,and hierarchies and explains their relationships to characteristics.
Each part requires an InfoPackage. This section describes how to create InfoPackages to load master data and text. The next section covers entering master data and text manually.
Work Instructions
Step 1. Right-click Source System–demo: flat file,and then select Create InfoPackage….
Step 2. Select the DataSource Material number (Master data),enter a description for the InfoPackage,and then click to continue.
Step 3. Click the External data tab. Select options as shown in the screen. Enter a file name with a path. The file contains the data given in Table
Note:-
The data file can reside on a front-end computer (or Client workstation as it is called here) or one of the application servers in the SAP Basis three-tier architecture. In our example,IO_MAT_ATTR.CSV resides in our front-end computer. For better loading performance,it is recommended that you place data files in application server computers.
Step 4. Click the Schedule tab,select the option Start data load immediately,and then clickto load data.
Result:
The staging engine starts to load the data. A status message Data was requested will appear at the bottom of Screen 3.19.
Note: To schedule the loading operation for a later date or to execute loading periodically,select the option Start later in bckgrnd proc,and then click. A pop-up window appears,as shown in Screen 3.20. You can set up a background process to start the data loading job based on the criteria specified in Screen 3.20. The same window is used in R/3 for background job scheduling. In essence,BW Scheduler is embedded in InfoPackages.
MATERIAL NUMBER—MASTER DATA
By repeating this procedure and selecting DataSource Material number (Texts),we can load the texts in Table.

MATERIAL NUMBER—TEXTS
Checking Loaded Characteristic Data
Before we check the loaded characteristic data,let's confirm that the load operation was successful.
Work Instructions
Step 1. Click to view the status of the data load. It leads us to BW Monitor.
Step 2. Click to view the status of all data loads triggered by this Info-Package.
Step 3. Green lights in the left panel indicate that the data have been loaded successfully.
Result:
The data are now in IO_MAT. Next,we will display the IO_MAT contents to verify that the data have been loaded correctly.
Work Instructions:
The loaded characteristic data can be displayed in many ways. The next set of steps describes one of them.
Step 1. Right-click the characteristic Material number,and then select Maintain master data.
Note: The two under Material number indicate the InfoPackages.
Step 2. Click to display the contents.
Note: To see only some of the data in IO_MAT,we can use the text fields in this screen to specify selection conditions. For example,the selection conditions in this screen specify that a maximum of 1,000 entries with texts in English will be displayed.
Result:
The loaded data—both master data and texts—are displayed in Screen 3.27;they are correct.
Entering the Master Data,Text,and Hierarchy Manually
Using the procedure given in Section 3.5,we can not only display characteristic data,but also enter new data and change existing data. In this section,we use a different technique. First,we show how to enter master data and texts for characteristic IO_SREG. Then,we describe how to create a hierarchy for IO_SREP.
Master Data and Text
Work Instructions
Step 1. Select InfoObjects under Modelling in the left panel,right-click Sales region,and then select Maintain master data in the right panel.
Step 2. Click to display master data.
Step 3. Click to create a record.
Step 4. Enter data in the fields and click to continue.
Step 5. Repeat Steps 3 and 4 to enter two more records,and then click to save the data into the database.
Result: You have entered data into IO_SREG. A status message Data has been saved will appear at the bottom of Screen. Repeat the same procedure to enter data into IO_SOFF.
Hierarchy:
Table is actually a hierarchy. It should look like the hierarchy depicted in Figure when we visualize the table data.
TIME-DEPENDENT HIERARCHY STRUCTURES
This hierarchy indicates the Denver office was in the Midwest region before January 1,2000. On and after January 1,2000,the Denver office was part of the West region. In BW,a hierarchy such as the one in Figure is called a time-dependent hierarchy structure. "InfoCube Design Alternative III— Time-Dependent Entire Hierarchies," will discuss another type of hierarchy called a time-dependent entire hierarchy.
The following procedure describes how to create a time-dependent hierarchy structure for IO_SREP.
Work Instructions
Step 1. To create a hierarchy for IO_SREP,we must specify hierarchy properties in the IO_SREP definition. To modify the IO_SREP definition, double-click InfoObject Sales representative ID.
Step 2. Under the Hierarchy tab,select the option with hierarchies,and then select the option Hierarchy structure time-dependent.
Next,click to check it. If it is valid,click to activate the modification.
Note: The activation will create three database tables for this hierarchy: /BIC/HIO_SREP (hierarchy table),/BIC/KIO_SREP (hierarchy SID table),and /BIC/IIO_SREP (SID hierarchy structure).Besides DataSources for master data (IO_SREP_ATTR) and texts (IO_SREP_TEXT),IO_SREP will work with a third DataSource,IO_SREP_HIER. With IO_SREP_HIER,we can use an InfoPackage to load hierarchy data as well.
"InfoCube Design Alternative III— Time-Dependent Entire Hierarchies," gives an example of Entire hierarchy is time-dependent.
Step 3. In the Administrator Workbench: Modelling window,right-click the characteristic InfoObject Sales representative ID,and then select Create hierarchy….
Step 4. Enter a name and a description,and then click to continue.
Step 5. Right-click IO_SREP hierarchy,and then select Insert characteristic node….
Step 6. Enter IO_SREG,and then click to continue. Here we use IO_SREG data to create the hierarchy nodes.

.
Step 7. Select all three regions,and then click to continue.
Step 8. Three regions are displayed under IO_SREP hierarchy. To assign offices to each region,right-click one of the regions and repeat Steps 5,6 (enter IO_SOFF instead of IO_SREG),and 7. This screen shows that Denver,Los Angeles,and Seattle will be assigned to the West region.
Step 9. Because the Denver office is already assigned to the Midwest region,we see this message. As noted,the Denver office was part of the Midwest region before January 1,2000. For this reason,we need to put the Denver office in two places.
Select the option Copy duplicated node,and then click to continue.
Step 10. Now we need to specify the valid date periods for the Denver office. Right-click the first Denver in the Midwest region,and then select Change node(s)….
Step 11. Enter dates in Valid from and To,and then click to continue.
Step 12. Repeat Steps 10 and 11 to specify valid dates for the Denver office in the West region.
Step 13. Next,we assign sales representatives to each sales office. At the hierarchy leaf level,we can either insert IO_SREP values following the previous steps or right-click an IO_SOFF value,such as Atlanta in the East region,and then select Sales rep. ID insert…. We take the second approach.
SCREEN 3.45
Step 14. After assigning sales representatives to each sales office,click to save the hierarchy. Now we need to activate the hierarchy before we can use it.
Step 15. In the Administrator Workbench: Modelling window,right-click the newly created hierarchy IO_SREP hierarchy,and then select Activate.
Result:
You have created the hierarchy. Its color will change from gray to green. Now that we have loaded and entered all characteristic data,it is time to load the transaction data.
Creating an InfoSource for Transaction Data
we used BW's default simple one-to-one mappings for characteristic data. In this section,we will write a transfer rule in the ABAP language.
Table lists the material per unit sales price and quantity sold. It does not provide any sales revenue data,however. To improve future query performance,it is recommended that we calculate the sales revenue and save this result in the fact table,rather than calculate the sales revenue during a query run. The database design in Figure reflects this idea,and the following procedure shows how to implement it.
Work Instructions
Step 1. In the Administrator Workbench: Modelling window,select InfoSources under Modelling in the left panel,right-click Application Component–demo,and then select Create InfoSource….
Step 2. Select the option Transaction data,and then click to continue.
Step 3. Enter a name and a description,and then click to continue.
Step 4. The InfoSource is displayed but is not active yet. Double-click the InfoSource to create a communication structure.
Note: we skipped this step. BW created a communication structure for us automatically based on the characteristic attribute information.
A communication structure is needed here so that the data passed by an InfoPackage can feed multiple InfoCubes. See the note for Screen.
Step 5. Enter InfoObjects as shown,and then click to save and activate the communication structure
Result:
You have created the communication structure. A status message Communication Struct. IS_DEMOBC activated will appear at the bottom of Screen. Now we can clearly see that an InfoSource is simply a communication structure. Next,let's create the transfer rules.
Work Instructions
Step 1. In the Administrator Workbench: Modelling window,right-click the InfoSource–demo: IC_DEMOBC,and then select Assign DataSource….
Step 2. Enter SS_DEMOFF in the field Source system,and then click to continue.
Step 3. Click to pass the message.
Step 4. BW proposes a transfer structure based on the information of the communication structure. Under the Trans. structure tab,check four fields in the Selection column. You can use these four fields as selection conditions when loading data using an InfoPackage.
Note: A transfer structure maps DataSource fields to InfoSource InfoObjects.
Step 5. Under the Transfer rules tab,the simple one-to-one mappings appear in the left table,called Communication str./Transfer rules. Click at the intersection of IO_REV and Tp to replace the one-to-one mapping with an ABAP transfer rule for the sales revenue calculation.
Step 6. In the pop-up window,select the option @9WQLocalA,and then click to create an ABAP transfer rule.
Note:The first option,InfoObject,is the default—the simple one-to-one mapping. The second option,allows us to specify a constant. The third option,@9WQLocalA, enables us to define an ABAP transfer rule.
Step 7. Enter a name for the Transfer routine,select the option All fields,and then click to continue.
Note: In BW,the ABAP transfer rule is called a transfer routine.
Step 8. Scroll down the ABAP routine,to the right of RESULT=enter TRAN_STRUCTURE-/BIC/IO_PRC * TRAN_ STRUCTURE-/BIC/IO_QUAN. The statement should appear as shown on line 68. This ABAP statement specifies how we calculate IO_REV,the RESULT,from IO_PRC and IO_QUAN.
Click to check the ABAP routine. If it is valid,click to save it.
Step 9. Click to return to the main screen.
Step 10. The cell icon changes from to. Click to activate all transfer rules.
Result:
You have created transfer rules. A status message Transfer rules IS_DEMOBC_AH activated will appear at the bottom of Screen
Creating Update Rules for the InfoCube
Before creating an InfoPackage to load the transaction data,we must define rules for the InfoCube IC_DEMBC that determine how data will be updated. In BW,these rules are called update rules.
Work Instructions
Step 1. Select Data targets under Modelling in the left panel,right-click InfoCube–demo: Basic Cube,and select Create update rules in the right panel.
Step 2. In the Data source block,select the option InfoSource,enter the newly created IS_DEMOBC,and then click to move to the next screen.
Step 3. Accept the default update rules. Click to check the update rules. If they are valid,click to activate them.
Note: Section,"Preparing to Load Data into the ODS Object,Then into an InfoCube, "describes how to use ABAP to define an update rule.
Result:
You have created the update rules. A status message Update rules were successfully activated will appear at the bottom of Screen.
Note: BW release 3.0A allows you to create update rules for a characteristic's master data and text as well.
Create an InfoPackage to Load Transaction Data
Now we are ready to create an InfoPackage to load the transaction data. In this InfoPackage,we will specify how and when to load data.
Work Instructions
Step 1. Right-click Source System–demo: flat file,and then select Create InfoPackage….
Step 2. Select DataSource InfoSource Demo: IC_DEMOBC,enter a description for the InfoPackage,and then click to continue.
Step 3. Under the Select data tab,note the fields we selected in Screen. We can use the selection condition on 0CALDAY to load 1999 data first,2000 data second,and 2001 data last. If we do not enter anything,all of the data will be loaded together in one data load request. Because we do not have a large volume of data,we load them all together.
Note: 0CALDAY (Calendar day) is the characteristic in Screen that represents the Transaction Date in Table. We also included it in the InfoSource definition.
Step 4. Under the External data tab,select options as shown in the screen,and enter a file name with a path. The file contains the data from Table.
Step 5. Under the Data targets tab,select the option Select data targets,and then select the first row for our InfoCube IC_DEMOBC.
Note: If the InfoSource appears in the update rules for other InfoCubes,all of the InfoCubes will be listed in the table. We can then specify into which InfoCubes we want to load the data.
Step 6:Under the Schedule tab,select the option Start data load immediately,and then clickto load the data.
Result: 
A status message Data was requested will appear at the bottom of Screen.
Note: We can check whether the data were loaded successfully by using BW Monitor as shown in Screen "Checking InfoCube Contents," and "InfoCube Contents," both describe techniques for checking the InfoCube contents to confirm that the data loading process worked correctly. Table shows the sales data for our example. In the table,CS,EA,and DZ are abbreviations for Case,Each,and Dozen,respectively. The IO_REV column will hold the sales revenue data,which will be calculated by the transfer rule.
SALES DATA
Summary
In this chapter,we loaded the data described into the characteristics and InfoCube we created shows the many ways in which data can flow into a destination in BW. In this chapter,we followed the path linked via solid lines. The rest of this book will discuss the other paths.
DATA FLOW
Key Terms













       


Checking Data Quality

BW provides PSA (Persistent Staging Area),a place where we can display and check data before they are loaded into their destinations; we will learn how to use PSA in this chapter.
Checking InfoCube Contents
You can check InfoCube contents in several ways. One technique is introduced here; another method will be discussed later,"InfoCube Contents."
Work Instructions
Step 1. In the Administrator Workbench: Modelling window,right-click InfoCube–demo: Basic Cube,and then select Manage.

Step 2. Under Contents tab,click

Step 3. Click to display the data.

Note: BW uses </BIC|/BI0>/F<InfoCube name> to name an InfoCube's fact table in the database.
Result:
The loaded data are displayed later. As we can see,the transfer rules we created in "Creating an InfoSource for Transaction Data," worked correctly.


Using BW Monitor
Next,let's see how BW Monitor can help us troubleshoot during the ETTL process.
Work Instructions
Step 1. Select InfoCube–demo: Basic Cube,and then click.

Step 2. Leave Selection date blank to list the status of all data loads into this InfoCube. Click to continue.

Step 3. We see the status of all data loads (12 successful,7 failed),plus date/time information,the number of records selected from the source system,and the number of records coming into the BW system.
Double-click the last failed load operation to get its details.

Step 4. Click to display the error.

Step 5. Review the error message.

Step 6. Under the Details tab,notice how the data flowed and where the error occurred.

Result:
You have learned how to use BW Monitor to display the status of data loads and to find out when,where,and how errors (if any arose) occurred.
To examine the status of other data loads,we can click. In the new screen,we can specify selection conditions to display the status of the data loads in which we are interested. example,by clicking in the Further selections block,we can specify user-related selection conditions.


Using the Persistent Staging Area (PSA)
PSA stores data in its original source system format. In this way,it gives us a chance to examine the data before we send them into their destination—either characteristics,ODS objects,or InfoCubes.
Work Instructions
Step 1. Reload the data from previous Table. This time,in the InfoPackage created in screen 3.9, under the Processing tab,select the option Only PSA,instead of the default PSA and then into data targets (packet by packet).

Step 2. Start the data loading process as shown in Screen 3.71. After the Data was requested message appears at the bottom of the screen,click as shown in Screen 3.22. A green light appears next to the data load request,which indicates that the job was successful.
Next,click to display the PSA data.

Step 3. Click to see records 1 to 1000. BW sets this range by default.

Step 4. Review the 24 records stored in PSA. To modify one of them,you select the record and then click. Notice the zeros in the Sales revenue column; their presence indicates that the transfer rules have not yet been applied to the PSA data.

Step 5. After checking the data in PSA,now we can send the data to IC_DEMOBC.
In the Administrator Workbench: Modelling window,notice the load request InfoPackage–demo: IC_DEMOBC (25.10.2000) in the right panel. Right-click the request,and then select Start the update immediately.

Step 6. In the Administrator Workbench: Modelling window,right-click InfoCube–demo: Basic Cube,and then select Manage.
Under the Requests tab,notice the status of the data loads. The first row shows the status of the data load in Step 5. Click in the first row,which returns you to BW Monitor. You will see that the load from PSA into IC_DEMOBC was successful.

Result:
You have loaded data into IC_DEMOBC via PSA. Using PSA,you verified that the data were loaded correctly into BW.

Summary In this chapter,we checked InfoCube contents,used BW Monitor to analyze data loads,and used PSA to check data before they were sent to a destination InfoCube.
Figure shows how data flow in BW. In this chapter,we followed the path linked via solid lines. Later chapters in this book will discuss the other paths.
DATA FLOW

Key Terms



      


Creating Queries and Workbooks

we created an InfoCube;,we loaded data into it. In this chapter,we will create queries and reports using the BW presentation tool called Business Explorer (BEx). BEx consists of two applications: BEx Analyzer,which you use to create queries and reports,and BEx Browser,which you use to organize reports. We will work with both applications in this chapter. We will also demonstrate how to create and use variables.
Creating a Query Using BEx Analyzer
The following steps demonstrate how to create a query.
Work Instructions
Step 1. Open SAP Business Explorer Analyzer by selecting StartProgramsSAP FrontendSAP Business Explorer Analyzer from Windows,and then clicking .

Note
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. 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 "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,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 <InfoCube name>_Q<two-digit number> 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:
  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.

Note
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,click,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.

Note
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,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.

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

Result:
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.

Organizing Workbooks Using BEx Browser
After saving the two workbooks,we can reopen them using any of the following three methods.
Method 1: BEx Analyzer
If we now clickwe see the saved workbooks. If we double-click either of the workbooks,BEx Analyzer will open,and the saved workbook will be displayed.
SCREEN 5.20

Method 2: SAP GUI
Logging on to BW from SAP GUI,we see the workbooks listed under the Favorites folder.If we double-click either of them,BEx Analyzer will open,and the saved workbook will be displayed.

Method 3: BEx Browser
If we launch BEx Browser by selecting Start Programs SAP Frontend SAP Business Explorer Browser from Windows and log on to the BW system,we see the two workbooks displayed as Favorites. If we double-click either one of them,BEx Analyzer will open,and the workbook will be displayed.

For now,we know that BEx Browser is just a workbook organizer and that BEx Analyzer displays our query results. After we open a workbook,we can refresh it to get up-to-date data from the database by clicking on the toolbar. Managing User Authorization,we will demonstrate how to access other information resources from BEx Browser.

Using a Variable to Access a Hierarchy Node Directly
If the hierarchy is very large,users might want to jump directly to a particular hierarchy node when running a query. One way of achieving this goal is to use variables. Next,we describe a procedure to create a variable.
Work Instructions
Step 1. Log on to the BW system,and then click Maintain Variables or run transaction RSZV.

Step 2. Change the display mode to the create/change mode by clicking.

Step 3. Click to create a new variable.

Step 4. Name the variable as V_SREG. After entering the other basic information requested,click to provide more information.

Step 5. Enter IO_SREP in the field Characteristic,as it is associated with the hierarchy. Make other selections as shown in the screen,and then click to save the variable.

Next,we will use the new variable to enhance the query IC_DEMOBC_Q01 created.
Step 6. Return to the query created. Selectand then select Change query (global definition)….

Step 7. Right-click Sales rep. ID,and then select Restrict.

Step 8. Select Variable: sales reg. from the left window and move it to the right window by clicking. Next,click to continue.

Step 9. Click to save and execute the query.

Step 10. Click to save the query.

Step 11. Click to display the hierarchy nodes.

Step 12. Select East,and then click.

Step 13. Click to continue.

Result
The query result contains only the East region data.


Summary
In this chapter,we examined the basic features of BEx. We also learned how to create queries and workbooks using BEx Analyzer and how to access workbooks from BEx Browser. Figure illustrates this process.
QUERY AND WORKBOOK

Key Terms
          


Creating a Query Using BEx Analyzer
The following steps demonstrate how to create a query.
Work Instructions
Step 1. Open SAP Business Explorer Analyzer by selecting StartProgramsSAP FrontendSAP Business Explorer Analyzer from Windows,and then clicking .

Note
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. 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 "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,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 <InfoCube name>_Q<two-digit number> 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:
  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.

Note
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,click,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.

Note
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,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.

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

Result:
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.

Organizing Workbooks Using BEx Browser
After saving the two workbooks,we can reopen them using any of the following three methods.
Method 1: BEx Analyzer
If we now clickwe see the saved workbooks. If we double-click either of the workbooks,BEx Analyzer will open,and the saved workbook will be displayed.
SCREEN 5.20

Method 2: SAP GUI
Logging on to BW from SAP GUI,we see the workbooks listed under the Favorites folder.If we double-click either of them,BEx Analyzer will open,and the saved workbook will be displayed.

Method 3: BEx Browser
If we launch BEx Browser by selecting Start Programs SAP Frontend SAP Business Explorer Browser from Windows and log on to the BW system,we see the two workbooks displayed as Favorites. If we double-click either one of them,BEx Analyzer will open,and the workbook will be displayed.

For now,we know that BEx Browser is just a workbook organizer and that BEx Analyzer displays our query results. After we open a workbook,we can refresh it to get up-to-date data from the database by clicking on the toolbar. Managing User Authorization,we will demonstrate how to access other information resources from BEx Browser.

Using a Variable to Access a Hierarchy Node Directly
If the hierarchy is very large,users might want to jump directly to a particular hierarchy node when running a query. One way of achieving this goal is to use variables. Next,we describe a procedure to create a variable.
Work Instructions
Step 1. Log on to the BW system,and then click Maintain Variables or run transaction RSZV.

Step 2. Change the display mode to the create/change mode by clicking.

Step 3. Click to create a new variable.

Step 4. Name the variable as V_SREG. After entering the other basic information requested,click to provide more information.

Step 5. Enter IO_SREP in the field Characteristic,as it is associated with the hierarchy. Make other selections as shown in the screen,and then click to save the variable.

Next,we will use the new variable to enhance the query IC_DEMOBC_Q01 created.
Step 6. Return to the query created. Selectand then select Change query (global definition)….

Step 7. Right-click Sales rep. ID,and then select Restrict.

Step 8. Select Variable: sales reg. from the left window and move it to the right window by clicking. Next,click to continue.

Step 9. Click to save and execute the query.

Step 10. Click to save the query.

Step 11. Click to display the hierarchy nodes.

Step 12. Select East,and then click.

Step 13. Click to continue.

Result
The query result contains only the East region data.


Summary
In this chapter,we examined the basic features of BEx. We also learned how to create queries and workbooks using BEx Analyzer and how to access workbooks from BEx Browser. Figure illustrates this process.
QUERY AND WORKBOOK

Key Terms
       



Managing User Authorization

Security is an essential element of any information system. BW is no exception to this rule. It uses an R/3 utility called Profile Generator to manage authorization. In this chapter,we will demonstrate how to use this utility.
The foundation of SAP authorization management is based on authorization objects. These objects define what a user can do,and to which SAP objects. Such a definition is called authorization. For example,the authorization in Table allows users with that authorization to display and execute—but not change—the queries IC_DEMOBC_Q01 and IC_DEMOBC_Q02. This authorization is defined using authorization object S_RS_COMP.
Table. AN AUTHORIZATION FROM AUTHORIZATION OBJECT S_RS_COMP

Multiple authorizations are combined to create an authorization profile. In SAP,an authorization profile is assigned to a user role. Users assigned to the role have the authorizations to execute the defined business activities.
In this chapter,we will use Profile Generator to create user roles and assign users to the roles. In addition,we will demonstrate how to run R/3 transactions and access Web sites from BEx Browser. Although the sales manager may not need this function,we cover it here so that we can introduce an advanced feature powered by the integration of BEx Browser and Profile Generator. Let's start with a demonstration of the Profile Generator.
Creating an Authorization Profile Using Profile Generator
With Profile Generator,SAP has made authorization management very easy. First, we will create an authorization profile for a role. All users in that role can run queries but cannot change the queries.
Prerequisites:
Three users U_EAST,U_MIDWEST,and U_WEST have been created through transaction SU01.
Work Instructions:
Step 1. After logging on to the BW system,run transaction PFCG,or double-click Maintain Roles.

Step 2. Enter a name for the role,and then click.
Note:BW provides authorization profiles for a variety of roles in Business Content. To see a list of them,click .

Step 3. Click the Authorizations tab.

Step 4. Click to save the role and continue.

Step 5. Click to Change authorization data.

Step 6. Select the template S_RS_RREPU,and then click.

Note: BW provides authorization templates for a variety of roles in Business Content. S_RS_PPEPU is one of them,for query display and execution.
Step 7. The new window shows all authorizations for this role. For example,the users assigned to the R_RUN_QUERIES role can Display,Execute,Enter,Include,and Assign Calculated key figure,Query,Restricted key figure,and Template structure.
Note: If we expand other nodes,we will see other authorizations granted to this role.
To change an authorization field value,click next to the field. In our example,the reporting component Query has the activity Execute in two places. Let's remove Query from the first one.

Step 8. Deselect REP for Query,and then click to continue.

Note: S_RS_COMP is an authorization object; RSZCOMPTP is one of its fields. In this field we specify objects on which users can perform activities.
Step 9. Click to generate the profile.

Step 10. Enter a name and a description,and then click to continue.

Step 11. The status light of the Authorizations tab turns green (the red square becomes a green circle). Click the User tab to assign users to this role.

Step 12. Enter three users: one from the East region,one from the Midwest region,and one from the West region.
Click to add the authorization profile to the users' master data.

Step 13. Click to continue.

Step 14. Click to save the role.

Step 15. Notice that the status light of the User tab turns green (the red square becomes a green circle).

Result
You have created the role R_RUN_QUERIES and its corresponding authorization profile AP_R_QUERY. Also,you have assigned three users to this role. To verify that the role and assignments are correct,run transaction SU01 to display user U_WEST's master data. Under the tab Roles,review the role to which this user is assigned. Under the tab Profiles,notice the user's authorization profile.


From this example,we get an idea of how BW manages its authorization. Each role has an authorization profile. Users assigned to a particular role have all authorizations included in the authorization profile. A user can be assigned to multiple roles. The user derives his or her authorizations from the roles to which he or she is assigned.

Creating an Authorization Object to Control User Access to the InfoCube Data
Authorization objects,are the foundation of SAP authorization management. For this reason,SAP provides many authorization objects for most conceivable activities that users might perform on R/3 and BW objects. Nevertheless,in BW,we almost always need to create our own authorization objects. For example,the sales manager might decide that it is not appropriate for users in one sales region to view another region's sales data. In this case,the appropriate authorization object is not available from SAP,so we must create one by ourselves.
Before we create our own authorization object,we need to do a few things.
Prerequisites:
Step 1. Modify the InfoObject IO_SREP to make it be authorization relevant.
Open the InfoObject IO_SREP,and then select the option Authorization Relevant in the General settings block under the Business Explorer tab. Click to check the new InfoObject definition. If it is valid,click to activate the change.

Step 2. Make sure that the InfoObject 0TCTAUTHH is available.
Note: If the InfoObject 0TCTAUTHH is not available,follow the instructions in "Installing Business Content and Loading R/3 Data," to install it.

Now,we can create our authorization object.

Work Instructions:
Step 1. Log on to BW,and then either double-click Reporting Authorization Objects or run transaction RSSM.

Step 2. Enter a name,make sure the Object option is selected,and then click to create the authorization object.


The names of customer-developed authorization objects must begin with Y or Z.
Step 3. In the pop-up window,enter a description and then click to continue.

Step 4. Select IO_SREP and 0TCTAUTHH from the Authorization relevant | Objects window. Move them to the left window by clicking Click to save the changes.

Step 5. For demonstration purposes,click to save the authorization object as a local object so it will not be transported to other systems.

Note: See "Development Class," for more information on $TMP and local objects.
A status message Authorization object ZAO_SREP saved will appear at the bottom of Screen. The authorization object has been created with two fields,IO_SREP and 0TCTAUTHH.
Next,we will specify the InfoCubes to which this authorization object will apply.
Step 6. Select the Check for InfoCubes option,and then click to change the authorization object.

Step 7. Select IC_DEMOBC,and then click to save the authorization object.

Note: Only one InfoCube depends on InfoObject IO_SREP. Otherwise,more dependent InfoCubes would be listed.
Next,we need to create an authorization for each region.
Step 8. Select the option Authorization definition fr hierarchies,and then click to create an authorization.

Step 9: Enter a name for the authorization and provide other information as shown in Screen. Click to look up the available Type of authorization.
Note: Except for the name of the authorization,you can populate all fields by clicking and choosing one item from the list.

Step 10. Select 1 for Subtree below nodes,and then click to continue.

Step 11. Click to save the authorization.

Result
You have created the authorization using the newly created authorization object. We use the same method to create an authorization for the West region.

Now we can use the authorization object and the authorizations to create an authorization profile for a role. The users assigned to this role and the role created in Section can access only the East region's sales information.
Step 12. Repeat the steps from Screen to Screen to create a role called R_RUN_SREP_EAST. This time,however,click because we will use our own authorization object.

Step 13. Click to insert our authorization object.

Step 14. Enter ZAO_SREP as the authorization object,and then click to continue.

Step 15. Click to add authorizations to the Authorization for hierarchy field.

Step 16. Enter ZA_SREP_EAST,an authorization created previously,and then click to continue.

Step 17. Click to generate the authorization profile for the role.

Step 18. This message indicates that the Sales rep. ID field has no values. Click to continue.

Step 19. Enter a name and a description,and then click to continue.

Step 20. Notice that the status light of the Authorizations tab turns green. Click the User tab to assign user U_EAST to this role,and then click to add the authorization profile to U_EAST's master data.

Step 21. Repeat the steps from Screens. When they are complete,the status light of the User tab will turn green.

Result:
You have created the role R_RUN_SREP_EAST using a new authorization object. Users as signed to this role and the role created can only access the East region sales data. For example,when user U_EAST runs the query in Screen again,the user will have only two cities from which to choose Screen .


Integrating Profile Generator and BEx Browser
we learned that BEx Browser is a workbook organizer. To display query results,we use the BEx Analyzer. With Profile Generator,BEx Browser can truly serve as an information center for organizing all kinds of information resources in one place. The following procedure shows how to access a Web site and run an R/3 transaction from BEx Browser.
Work Instructions:
Step 1. Open the role R_RUN_QUERIES that was created in Section. Click the Menu tab.


Step 2. Click to create a new folder under Role menu.


Step 3. Enter a folder name,and then click to continue.


Step 4. Repeat Steps 2 and 3 to create two more folders. The names of the new folders are shown in Screen.

To add a Web address to a folder,select the Web Sites folder,and then click.


Step 5. Select the option Web address or file,and then click to continue.

Step 6. Enter your text and a Web address beginning with then click to continue.

Step 7. To define an R/3 transaction that we can launch from BEx Browser,select a folder,and then click to add the transaction code.

Note:
To run the R/3 transaction,we need to provide an RFC (Remote Function Call) destination in the Target System field. This RFC destination defines the R/3 system as a trusting system. That is,the R/3 system trusts the BW system,and selected BW users can run certain transactions or programs in the R/3 system without providing passwords.
Step 8. Enter a transaction code,and then click.

Step 9. Besides transactions,we can include R/3 reports. To do so,select a folder,and then click.

Step 10. The pop-up window shows the type of reports we can include. In this example,we do not include any reports. Click to cancel the operation and go back to the previous screen.

Step 11. Click to update the user master data,and notice that all of the tab status lights are now green.

Result
Users with the R_RUN_QUERIES role can run the R/3 transaction SM50 and access the Web site. Open BEx Browser and log on as U_EAST,then click folder Role – demo: run queries in the left panel. You will see Screen

Double-click the Work Process Overview icon. The BEx Browser opens an R/3 session. The session displays the result of transaction SM50 Screen

Double-click the awl.com icon,and BEx Browser opens the Web page using the default Web browser,Internet Explorer Screen

Summary In this,we demonstrated how to use Profile Generator to create authorization profiles and objects. We also learned how to launch R/3 transactions and access Web sites from BEx Browser.
A larger organization will likely have staff members who specialize in authorization setup issues. In some cases,such an organization's authorization staff may need assistance from a BW technical developer as well if its authorization specialist is not familiar with BW. Smaller organizations may routinely depend on BW technical developers to set up authorization profiles and objects.
Key Terms



InfoCube Design

InfoCube design not only determines business functionality,but also directly affects system performance.
In this chapter we will first look into the star schema implemented in BW. Next,we will analyze three InfoCube design alternatives for the same business scenario. Finally,we will briefly discuss two other InfoCube design techniques:
  • Compound attributes
  • Line item dimensions
Note
In BW,use of aggregates is a key technique for improving query performance. Therefore,when evaluating an InfoCube design,we must consider whether the design supports aggregates as critical criteria.
BW Star Schema
we introduced the concept of a star schema.
What does the InfoCube data model look like at the database level?
In this we will explore the relationships between database tables. They will give us a clear idea of what the star schema looks like in BW. Again,we use a step-by-step procedure.
Work Instructions:
Step 1. Run transaction SE11,enter /BIC/FIC_DEMOBC as the fact table name,and then click .
Note: From Screen,we know that /BIC/FIC_DEMOBC is the name of the fact table.
If we are interested in only the table contents,and not the table definition,we can run transaction SE16 instead.

Step 2. The Check table column lists parent tables of the fact table. Double-click /BIC/DIC_DEMOBC3 to display the sales representative dimension table.
Note: BW uses </BIC/|/BIO/>D <InfoCube name><Number starting from 1> to name dimension tables.
  • </BIC/|/BIO/>D<InfoCube name>P is for the data packet dimension. We will discuss its role in BW.
  • </BIC/|/BIO/>D<InfoCube name>T is for the time dimension.
  • </BIC/|/BIO/>D<InfoCube name>U is for the unit dimension.

Step 3. The dimension table does not have any check tables,but it has a field called SID_IO_SREP. Click to display the table's contents.

Step 4. Click to execute.

Step 5. Notice that SID_IO_SREP 11 corresponds to DIMID 23. From Screen, we know that DIMID 23 is the value of the field KEY_IC_DEMOBC3 in the first row of the table /BIC/FIC_DEMOBC. Then what does SID_IO_SREP 11 represent?

Step 6. Repeat Step 1 to display the contents of IO_SREP's SID table,/BIC/SIO_SREP. This screen shows the SID table definition. Click to display the table's contents.

Note: Here SID is Surrogate-ID,not the System ID used to name an SAP system.BW uses </BIC/|/BIO/>S<characteristic name> to name a characteristic's SID table.
Step 7. The contents of the SID table /BIC/SIO_SREP are displayed.
In this screen,we see that SID 11 corresponds to SREP01,a sales representative ID in the first record of Table.

Following the same approach,we can discover the relationships between the SID table /BIC/SIO_SREP,the master data table /BIC/PIO_SREP,and the text table /BIC/TIO_SREP. The contents of the latter two tables are shown in Screens
Step 8. Repeat Step 1 to display the contents of IO_SREP's master data table,/BIC/PIO_SREP. This screen shows the table's contents.

Step 9. Repeat Step 1 to display the contents of IO_SREP's text table,/BIC/TIO_SREP. Screen shows the table's contents.

Step 10. Repeat Step 1 to display the contents of IO_SREP's hierarchy table,/BIC/HIO_SREP. Screen shows the table's contents.
Note: Screen shows the contents of the hierarchy table,/BIC/HIO_SREP. Unlike the master data table and the text table,the hierarchy table does not link to the SID table. BW builds the hierarchy based on the information in the tables /BIC/IIO_SREP,/BIC/KIO_SREP,and /BIC/SIO_SREP.
SCREEN 7.10

Result:
Based on our discussion,we can draw a simplified star schema as shown in Figure.
BW STAR SCHEMA FOR PART I INFOCUBE DESIGN

The solid lines are the true master–detail relationships,reinforced by foreign keys. The dashed lines are relationships that are maintained by ABAP programs,but not reinforced by foreign keys.
The dashed-line relationships allow us to load transaction data even when the database does not contain any master data. Screen shows the InfoPackage used Under the Update parameters tab,notice the Always update data,even if no master data exists for the data option.

With this star schema in mind,let's look at three InfoCube design alternatives.

InfoCube Design Alternative I— Time-Dependent Navigational Attributes
In the Part I InfoCube design,IO_SREG and IO_SOFF are in a hierarchy of IO_SREP as the hierarchy's node values Screen 3.46. In this section,we will discuss an alternative by putting IO_SREG and IO_SOFF into a new IO_SREP,or IO_SREPN1,as time-dependent navigational attributes.
First,we show how to build this new design.
Work Instructions:
Step 1. In the definition of IO_SREPN1,append IO_SOFF and IO_SREG after the existing IO_SREPNM as attributes.
Make IO_SOFF and IO_SREG time-dependent by selecting the corresponding rows in the column Time-Dependent. Click to switch them from display attributes to navigational attributes.

Step 2. Enter the description Sales office for the navigational attribute IO_SOFF,and Sales region for IO_SREG.

Step 3. Click to check the InfoObject. If it is valid,click to activate the InfoObject. The time-dependent master data table /BIC/QIO_SREPN1 and the time-dependent SID table /BIC/YIO_SREPN1 are created. Their names appear in the Time-dependent master data tables block.

Step 4. Double-click the SID table /BIC/YIO_SREPN1 to reach its definition screen. Here we see two time-related fields,DATETO and DATEFROM. When we load data,we use these two fields to specify a record's valid period. DATETO is part of the key that makes a record unique. We also see two other SID fields, S__IO_SOFF and S__IO_SREG. BW uses them to link IO_SREPN1 to IO_SOFF and IO_SREG,moving IO_SOFF and IO_SREG one more level away from the fact table. This layout will negatively affect query and load performance.

Step 5. Follow the work instructions to create a new InfoCube called IC_NEWBC1. In the InfoCube definition,click to specify the navigational attributes.

Step 6. In the pop-up window,check the I/O column to activate the two attributes,and then click to continue.

Step 7. After checking,activating,and loading data into the new InfoCube,we can create a query. In the left panel,we see that Sales office (navigational attribute),Sales region (navigational attribute),and Sales representative (characteristic) all reside in the same dimension. From the query point of view,the navigational attributes are just like their characteristic,allowing for drill-down. Click to specify a key date.

Step 8. Enter 31.12.9999 as the key date,and then click to continue.

Result:
The query result shows that the Denver office is located in the West region

Create another query,but this time enter 31.12.1999,instead of 31.12.9999,as the key date (Screen). Screen displays the result of the new query,which shows that the Denver office is now located in the Midwest region.

Note: Based on our discussion, we can draw a simplified star schema as shown in Figure for this design alternative.
BW STAR SCHEMA OF ALTERNATIVE I INFOCUBE DESIGN

In Figure,/BIC/SIO_SREG and /BIC/SIO_SOFF have their own master data table and text table. These tables are not shown in the figure.
Because IO_SREG and IO_SOFF are placed one level farther away from the fact table,query performance will be poor with this design.
Because IO_SREG and IO_SOFF are hidden inside IO_SREPN1,we cannot build aggregates on IO_SREG and IO_SOFF.
Navigational attributes facilitate system maintenance. For example,if we need to reassign sales offices and sales regions,we can create new records in the master data table with corresponding valid dates.
This design,however,is not flexible enough to permit structure changes,because the levels of the sales organization are fixed.

InfoCube Design Alternative II-Dimension Characteristics
In this section,we discuss yet another design alternative. This time,we will create a new IO_SREP,called IO_SREPN2. We will treat IO_SREG and IO_SOFF as independent characteristics,just like IO_SREPN2,and put them all together in the same dimension. Figure  shows a simplified star schema for this design.
BW STAR SCHEMA OF ALTERNATIVE II INFOCUBE DESIGN

In Figure,/BIC/SIO_SREG and /BIC/SIO_SOFF have their own master data table and text table.This design methodology is known as the Dimension Characteristics method. The following steps explain how to build this new design.
Work Instructions:
Step 1. Repeat the Steps to create IO_SREPN2. It has no hierarchies.

Step 2. Create an InfoCube and include IO_SREG and IO_SOFF as characteristics.

Step 3. Assign IO_SREG,IO_SOFF,and IO_SREPN2 to the same dimension as shown in this screen. Click to check the new InfoCube. If it is valid,click to activate the new InfoCube.

Step 4. We also need to include IO_SREG and IO_SOFF in the communication structure.
Step 5. Load data into the new InfoCube and create a query.In the left panel,we see the three characteristics Sales office,Sales region,and Sales representative. They are all in the same dimension.
Step 6. As before,we specify 31.12.9999 as the key date,and run the query.

Result:
Screen shows the query result. The Denver office is listed under the Midwest region,instead of the West region,although we specified 31.12.9999 as the key date. This result arises because the sales transactions conducted by the Denver office all took place before January 1,2000 (see Table). In the data warehousing world,this query result is referred to as a yesterday-or-today scenario—the data were valid when they were generated.
In a yesterday-and-today scenario,the data that were valid yesterday and today are displayed. In our example,we would not see the Denver office data in a yesterday-and-today scenario. For further information on this scenario,refer to ASAP for BW Accelerator,"Multi-Dimensional Modeling with BW."Now we know that our new InfoCube design does not provide the two views of data that we saw earlier with the time-dependent hierarchy structure and time-dependent navigational attributes—namely,the today-is-yesterday scenario and the yesterday-is-today scenario.

Note:
From a performance point of view,this design improves upon the two earlier options,because it places IO_SREG and IO_SOFF closer to the fact table. Performance is,of course,one of the major concerns in data warehousing. Here are some guidelines for dealing with this issue:
  1. If IO_SREG and IO_SOFF data are included in the transaction data, as shown in Table, use IO_SREG and IO_SOFF as dimension characteristics instead of characteristic attributes.
SALES DATA

  1. If IO_SREG and IO_SOFF are frequently used for navigation,use IO_SREG and IO_SOFF as dimension characteristics instead of characteristic attributes.
When considering the dimension in which a characteristic should be placed,follow these two guidelines:
  1. If the characteristics,such as IO_SREG, IO_SOFF,and IO_SREPN2,have a one-to-many relationship,group them in the same dimension.
  2. If the characteristics,such at IO_MAT and IO_CUST,have a many-to-many relationship,group them in different dimensions. In some special cases,when the combinations of the relations are small,such as materials and colors,you might consider grouping them within one dimension.
Another advantage of this InfoCube design is that we can create aggregates on IO_SREG and IO_SOFF. As in Alternative I,however, the levels of the sales organization are fixed in Alternative II.

InfoCube Design Alternative III— Time-Dependent Entire Hierarchies
The original design,"Hierarchy," uses a time-dependent hierarchy structure. Although that design performs more poorly than the alternative II InfoCube design,its sales organization hierarchy is very flexible. That is,we can easily add or delete levels in the sales organization. The major drawback of the design,however,is that we cannot create aggregates on a time-dependent hierarchy structure.
This section introduces another type of hierarchy,called a time-dependent entire hierarchy,that does allow us to create aggregates. Figure  illustrates how it differs from the time-dependent hierarchy.
A COMPARISON OF TWO TYPES OF HIERARCHIES
(a)

(b)
Time-dependent hierarchy structures consist of nodes or leaves that are time-dependent. The hierarchy itself is not time-dependent.
As shown in Figure(b),time-dependent entire hierarchies consist of nodes or leaves that are not time-dependent. The hierarchy itself is time-dependent.
The following steps explain how to build the design for our example with time-dependent entire hierarchies.
Work Instructions:
Step 1. Create a new IO_SREP,called IO_SREPN3. Select the Entire hierarchy is time-dependent option.

Step 2. Select Create hierarchy from the InfoObject menu.

Step 3. Specify the valid dates,and then click to continue.

Step 4. Using the procedure given,create a hierarchy. Notice that the Denver office is placed in the West region.

Step 5. Create another hierarchy,and specify its valid dates as being from 01.01.1000 to 31.12.1999. Now the Denver office appears in the Midwest region.

Step 6. Notice the two hierarchies. Each has an associated valid date.

Step 7. Follow the procedure to create a new InfoCube,IC_NEWBC3. Follow the procedure to create a new query,IC_NEWBC3_Q01. When selecting hierarchies,we have two hierarchies.
Select one hierarchy,and click. Save and run the query.

Result:
The first query result shows that the Denver office is located in the West region.

Create another query. This time,select IO_SREPN3_H1999 as the hierarchy. The result of this query puts the Denver office in the Midwest region.

Note: The time-dependent hierarchy structure in the design and the time-dependent entire hierarchies in the new design created produce the same query results.BW hierarchies are very flexible. We can easily add or delete nodes and leaves. Likewise,we can alter the number of levels.
If the hierarchy does not change very often and is not very large,then this new design is a good one. Note,however,that its performance is not as good as that of the Alternative II InfoCube design. We can create aggregates that compensate for the loss in performance to a certain extent.

Other InfoCube Design Techniques
Before closing this chapter,let's briefly discuss two other InfoCube design techniques:
  • Compound attributes
  • Line item dimensions
Compound Attributes
Compounding entities is an idea that BW borrows from R/3 to model coexistent entities. Compound attributes requires overhead,so you should not use them unless absolutely necessary.
Screen shows that 0CO_AREA is a compound attribute of 0COSTCENTER as defined in Business Content.

Sometimes the meaning of master data depends on the source of the data. In these cases,we need to compound the characteristic with the InfoObject 0SOURSYSTEM (Source system ID).
For example,suppose a characteristic IO_HOUSE has an entry called White House. The characteristic could mean the home of the U.S. President if it comes from a government source system,or it could mean a house painted white if it comes from a home improvement Web site. To handle cases such as this one,we need to compound IO_HOUSE with 0SOURCESYSTEM to clarify the meaning of the characteristic.
The 0SOURSYSTEM InfoObject is provided with Business Content.
Line Item Dimensions:
If a dimension has only one characteristic,we can make the dimension become a line item dimension. Consider the following example. For the InfoCube described,we can create another dimension called Dim: sales transaction,and check the option Line Item as shown in Screen.

After checking and activating the InfoCube,Screen reveals that the fact table has no dimension table created for the line item dimension. The key in the fact table is the SID of the SID table. Thus the fact table links to the master data,text,and hierarchy tables with the SID table,and one middle layer for the dimension table is removed. This design improves system performance.

Note: The line item dimension derives its name from the need for detailed information reporting at the line item level. In,Operational Data Store (ODS),we will discuss another technique that allows us to build a multilayer structure for different levels of detail information reporting.
The level of detail found in a data warehouse is called its granularity. It is determined by business requirements and technology capabilities.

Summary
In this chapter,we discussed the BW star schema and analyzed three InfoCube design alternatives for the same business scenario. We also briefly discussed two other InfoCube design techniques: compound attributes and line item dimensions.
Key Terms



1 comment:

  1. Simply superb
    I had no idea about BW systems n authorization
    Now I ll pass with 90 % and above if I m given test on SAP BW Security
    Admin I understood all about BW
    Wat s bw why we use bw
    Many thanks 4 this document

    ReplyDelete