Update Rules

Definition

Update rules specify how the data (key figures, time characteristics, characteristics) is updated to data targets from the communication structure of an InfoSource. You are therefore connecting an InfoSource with a data target.

Use

An update rule must be specified for each key figure and the corresponding characteristics of the InfoCube. For an ODS Object, it must be specified for the data and key fields, and for an InfoObject it must be specified for the attribute and key fields.

The following update types exist:

...

       1.      No update

       2.      Addition, minimum or maximum

       3.      Overwriting (with ODS objects and InfoObjects only)

A data target can be supplied with data from several InfoSources. A record of update rules must be maintained for each of these InfoSources, describing how the data is written from the communication structure belonging to the InfoSource into the data target.

The InfoObjects of the communication structure belonging to the InfoSource are described in the update as source InfoObjects (meaning source key figure or source characteristic). The InfoObjects for the InfoCube, on the other hand, are described as target InfoObjects (meaning target key figure or target characteristic). Similarly, the InfoObjects of an ODS object/InfoObject are called target InfoObjects (therefore, target data field or target key field).

Structure

There is an update rule for each key figure of the InfoSource. This comprises the rule for the key figure itself and the current rules for characteristics, time characteristics, and units assigned to it.

InfoSource

Definition

In BW, an InfoSource describes the quantity of all the data available for a business transaction or a type of business transaction (for example, cost center accounting).

An InfoSource is a quantity of information that logically belongs together, summarized into a single unit. It prepares consolidated data for updating to the data targets. InfoSources contain either transaction data or master data (attributes, texts and hierarchies).

An InfoSource is always a quantity of InfoObjects that logically belong together in the form of the communication structure.

Use

In BW, a DataSource is assigned to an InfoSource. If fields that logically belong together exist in various source systems, they can be grouped together into a single InfoSource in BW, in which multiple DataSources can be assigned to an InfoSource.

In the BW Processing Transfer Rules, individual DataSource fields are assigned to the corresponding InfoObject of the InfoSource. Here you can also determine how the data for a DataSource can actually be transferred to the InfoSource. The uploaded data is transformed using transfer rules. An extensive library of transformation functions that contain business logic can be used here to perform data cleansing and to make the data analyzable. The rules can be applied in a simple way without code with the use of formulas.

The transfer structure is used to transfer data to the BW system. The data is transferred 1:1 from the transfer structure of the source system into the BW transfer structure.

Integration

If fields that logically belong together exist in various source systems, they can be grouped together into a single InfoSource in BW. The source system release is not important here.

If you are dealing with an InfoSource with flexible updating, then the data is updated from the communication structure into the InfoCube into other data targets with the aid of the Update Rules. InfoSources with direct updating permit master data to be written directly (without update rules) into the master data tables.

InfoSources are listed in the InfoSource tree of the Administrator Workbench under an application component.

Sources systems

Sources systems are all the data feeding pipes to the Staging Area. TYPICALLY any Transformation on the data is done after the data in its raw OR unchanged form is picked from the source systems. The further details of the Source Systems can be Data Warehouse Design & Architecture in Data Warehousing

Core source Systems

These system include the core systems mostly having well organized database, set schedules, the data being updated on online basis. Typical systems are core product manufacturing systems, accounting systems, Money Management systems, commission/Sales Compensation Systems, tightly coupled job/workflow systems.

Field OR Front-end Source Systems

These systems, include the systems, which are primarily used by theCustomer acquisition and retention staff. These include Customer service systems, Sales automation systems, leads management systems, campaign management systems.

Modeling and Analysis systems

This family of systems include budgeting, planning, forecasting, pricing and valuation type of system.

External Data

This is the data, which you receive from regulators, credit bureau, medical bureau, industry associations, market research firms, database marketing companies and other sources. This data (unlike data provided by your suppliers and Customers;which goes into your core OR field systems) follows a standard format generally governed by the supplier.

Non-Data Base/Desk top Sources

Wealth of information and critical operational data resides in the excels and MS access tables in the desktops OR local servers of organization. If you want to shock yourself, just make a study of the number of excel based applications, which have become critical part of operational delivery and reporting. The numbers could go in hundreds, if not in thousands.

Off-line Databases

As no organization data management strategy goes through a systematic & planned growth, it is possible that you might have some offline database used by the users to generate their reports. For the sake of speed, one may tend to use that as a source system. However, in a medium to long run, this may become counter productive because you will make that offline database redundant.

InfoPackage

 Definition

An InfoPackage (active object: ISIP/delivery object :SHIP) is a source-system dependent object that has the GUID as key. There is one object for each source system.

You can find additional information about this BW object under Maintaining an InfoPackage.

Structure

Key Fields of an InfoPackage (ISIP/SHIP)

Version	Key Fields	Data Fields
A (active) / M (modified) / T (Transport) - ISIP	GUID	Source System
D (Delivery /. Content) - SHIP	GUID	Source system release

As the source system does not exist in the key, but is contained within the data part, the InfoPackage objects is also delivered as a shadow table.

Example

The following example aims to show what you need to be aware of when delivering InfoPackages within partner Content.

The Content development system is connected to source system Q1. There is exactly one InfoPackage with GUID1 for this source system.

In the customer system, this InfoPackage is to be activated for several source systems with the same release status:

·        Source system X1, Release 4.0

·        Source system X2, Release 4.0

Accordingly, the InfoPackage is copied into the A version twice. The logic behind this is the same as for transfer rules (see Transfer Rules and DataSource). Conversion is carried out byRSSM_SHIP_PSEUDO_DVERSION_WIR. The copies contains GUID3 and GUID4.

When SAP delivers again, you need to know from which InfoPackage GUID3 and GUID4 are derived. This information is stored in table RSSHIPDVERS:

Table RSSHIPDVERS

LOGDPID_SH	LOGSYS	LOGDPID
GUID3	X1	GUID2
GUID4	X2	GUID2

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

BW ARCHITECTURE

     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.

1.3.1 BW Architecture

Figure 1.3 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:

            o BEx Analyzer

            o BEx Browser

        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).

    2. The middle layer, BW Server, carries out three tasks:

            o Administering the BW system

            o Storing data

            o Retrieving data according to users' requests

               We will detail BW Server's components next.

    3. 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.

        The middle-layer BW Server consists of the following components:

               

            o Administrator Workbench, including BW Scheduler and BW Monitor

            o Metadata Repository and Metadata Manager

            o Staging Engine

            o PSA (Persistent Staging Area)

            o ODS (Operational Data Store) Objects

            o InfoCubes

            o Data Manager

            o OLAP Processor

            o BDS (Business Document Services)

            o User Roles

           

                          Figure 1.3. 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 BWScheduler, 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.

3.10. Summary

In this chapter, we loaded the data described in Chapter 1 into the characteristics and InfoCube we created in Chapter 2. Figure 3.2 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.

Figure 3.2. DATA FLOW

3.9. 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

1.	Right-click Source System – demo: flat file, and then select Create InfoPackage…. SCREEN 3.66
2.	Select DataSource InfoSource Demo: IC_DEMOBC, enter a description for the InfoPackage, and then click  to continue. SCREEN 3.67
3.	Under the Select data tab, note the fields we selected in Screen 3.56. 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. SCREEN 3.68 Note 0CALDAY (Calendar day) is the characteristic in Screen 2.28 that represents the Transaction Date in Table 1.4. We also included it in the InfoSource definition.
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 fromTable 3.3. SCREEN 3.69
5.	Under the Data targets tab, select the option Select data targets, and then select the first row for our InfoCube IC_DEMOBC. SCREEN 3.70 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.
6.	Under the Schedule tab, select the option Start data load immediately, and then click  to load the data. SCREEN 3.71

3.8. 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. SCREEN 3.63
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. SCREEN 3.64
Step 3.	Accept the default update rules. Click  to check the update rules. If they are valid, click  to activate them. SCREEN 3.65 Note Section 9.2, “Preparing to Load Data into the ODS Object, Then into an InfoCube,” describes how to use ABAP to define an update rule.

3.7. Creating an InfoSource for Transaction Data

In Section 3.3, 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 1.4 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 1.1 reflects this idea, and the following procedure shows how to implement it.

3.6. 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.

3.6.1. 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. SCREEN 3.28
Step 2.	Click OK to display master data. SCREEN 3.29
Step 3.	Click OK to create a record. SCREEN 3.30
Step 4.	Enter data in the fields and click  to continue. SCREEN 3.31
Step 5.	Repeat Steps 3 and 4 to enter two more records, and then click  OK to save the data into the database. SCREEN 3.32 Result You have entered data into IO_SREG. A status message Data has been saved will appearat the bottom of Screen 3.32.                 Repeat the same procedure to enter data into IO_SOFF.                 3.6.2 Hierarchy                 Table 1.3 is actually a hierarchy. It should look like the hierarchy depicted in Figure 3.1                 when we visualize the table data.                                                                                                                                      Figure 3.1. 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 3.1 is called a time-dependent hierarchy                 structure. Section 7.4, "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.                       SCREEN 3.33                       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.                            Section 7.4, "InfoCube Design Alternative III— Time-Dependent Entire                            Hierarchies," gives an example of Entire hierarchy is time-dependent.                                                                                                                             SCREEN 3.34                       Step 3. In the Administrator Workbench: Modelling window, right-click the                       characteristic InfoObject Sales representative ID, and then select Create                       hierarchy....                       SCREEN 3.35                                                                                                                       Step 4. Enter a name and a description, and then click to continue.                       SCREEN 3.36                                                                                                                  Step 5. Right-click IO_SREP hierarchy, and then select Insert characteristic                       node....                       SCREEN 3.37                                                                                                                          Step 6. Enter IO_SREG, and then click         to continue. Here we use IO_SREG                       data to create the hierarchy nodes.                       SCREEN 3.38                       Step 7. Select all three regions, and then click     to continue.                       SCREEN 3.39                       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.                       SCREEN 3.40                                                                                                                              Step 9. Because the Denver office is already assigned to the Midwest region, we                       see this message. As noted in Chapter 1, 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.                       SCREEN 3.41                                                                                                                            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)....                       SCREEN 3.42                                                                                                                            Step 11. Enter dates in Valid from and To, and then click to continue.                       SCREEN 3.43                                                                                                                     Step 12. Repeat Steps 10 and 11 to specify valid dates for the Denver office in the                       West region.                       SCREEN 3.44                                                                                                                             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.                       SCREEN 3.46                                                                                                                            Step 15. In the Administrator Workbench: Modelling window, right-click the                       newly created hierarchy IO_SREP hierarchy, and then select Activate.                       SCREEN 3.47                                                                                                   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.