Integrating data from SAP ERP into SAP BusinessObjects Planning and Consolidation may be likened to using a European hair dryer in the US. First of all, the European plug does not fit into the US outlet, so you need a converter. Once connected the European dryer underperforms as it needs 240 volts while the US runs on 110 volts — thus you need to transform the 110 volts into 240 volts to achieve full performance.

You can plug SAP NetWeaver BW and SAP BusinessObjects Planning and Consolidation, version for the Microsoft platform together using the open hub service and Microsoft SQL Server Integration Services (SSIS). SAP NetWeaver BW and SAP BusinessObjects Planning and Consolidation Data Manager can take over the role of the transformer to ensure that the SAP data loaded into SAP BusinessObjects Planning and Consolidation is correct and reflective of what the user expects.

You have two challenges to overcome to load SAP data into SAP BusinessObjects Planning and Consolidation:

• Connect SAP ERP and SAP BusinessObjects Planning and Consolidation
• Transform the data to load it into SAP BusinessObjects Planning and Consolidation

We describe techniques to load data from SAP NetWeaver BW into SAP BusinessObjects Planning and Consolidation. Although we focus on SAP BusinessObjects Planning and Consolidation, version for the Microsoft platform, most of the conversion tricks equally apply to the version for SAP NetWeaver. The information in this article applies to SAP BusinessObjects Planning and Consolidation 5.x and 7.0.

Automatic Data Loads

This section depicts the steps to connect SAP ERP and SAP BusinessObjects Planning and Consolidation, version for the Microsoft platform to automate the data loads from SAP ERP via SAP NetWeaver BW into SAP BusinessObjects Planning and Consolidation. The SAP ERP to SAP NetWeaver BW data loading is well documented and covered by SAP Business Content, so our focus is on the integration of SAP NetWeaver BW and SAP BusinessObjects Planning and Consolidation, version for the Microsoft platform. Figure 1 shows the high-level data flow, which is as follows:

• SAP ERP data flows into SAP NetWeaver BW via standard SAP Business Content extractors
• SAP NetWeaver BW data is transformed into an SAP BusinessObjects Planning and Consolidation-compatible format
• The data is pushed out into a flat file using SAP NetWeaver BW open hub service
• The SSIS package picks up the file and transfers it to SAP BusinessObjects Planning and Consolidation
• SAP BusinessObjects Planning and Consolidation Data Manager loads the data into the SAP BusinessObjects Planning and Consolidation application

Figure 1

Data flow from SAP ERP via SAP NetWeaver BW into SAP BusinessObjects Planning and Consolidation

By creating an open hub service destination in SAP NetWeaver BW, you can export your data out of SAP NetWeaver BW into a .csv flat file, copy it to a server that is accessible for SAP BusinessObjects Planning and Consolidation, and then pick it up using the SAP BusinessObjects Planning and Consolidation SSIS import package. The import package runs in SAP BusinessObjects Planning and Consolidation Data Manager (Figure 2).

Figure 2

The open hub destination to transfer SAP NetWeaver BW data to a .csv file

After the SAP NetWeaver data has been loaded to an application server that is accessible from SAP BusinessObjects Planning and Consolidation, the data can be picked up using an SSIS package. The SSIS package loads it to the SAP BusinessObjects Planning and Consolidation server, where the SAP BusinessObjects Planning and Consolidation Data Manager can pick it up (Figure 3).

Figure 3

SSIS package to pick up the data file for the load into SAP BusinessObjects Planning and Consolidation

SAP BusinessObjects Planning and Consolidation Data Manager converts and loads data from a flat file. It requires a transformation file (Figure 4). The transformation file contains general information about the structure of the input file, such as whether there is a header line, how the data is delimited, and what field is stored in which column. For values in the input file that cannot be directly copied into SAP BusinessObjects Planning and Consolidation, the transformation file links to a conversion file. The conversion file maps external to internal formats (e.g., 2009001 from 0FISCPER in SAP NetWeaver BW would be mapped to 2009.JAN, the internal date format in SAP BusinessObjects Planning and Consolidation).

Figure 4

SAP BusinessObjects Planning and Consolidation transformation file calling a conversion file for time format conversion

When you follow the process we described, your data can flow out of SAP NetWeaver BW via the open hub service. Then, SSIS picks up the file and SAP BusinessObjects Planning and Consolidation Data Manager loads the file into your SAP BusinessObjects Planning and Consolidation application. SAP ERP is now connected to SAP BusinessObjects Planning and Consolidation via SAP NetWeaver BW.

Overcome Common Transformation Challenges

After the systems are connected, the next challenge is to overcome the format conversion necessary to load data. In general, the format of data flowing from SAP ERP extractors into SAP NetWeaver BW is different from the format that SAP BusinessObjects Planning and Consolidation expects. One example is that SAP BusinessObjects Planning and Consolidation has only one key figure per record (known as the generic key figure model), while SAP NetWeaver BW can have multiple key figures in one record (key figure-based model). Therefore, the question of data compatibility between SAP NetWeaver BW and SAP BusinessObjects Planning and Consolidation arises: “What are the typical major transformation challenges you are likely to encounter when loading data from SAP NetWeaver BW into SAP BusinessObjects Planning and Consolidation? What techniques in which system are available to overcome these challenges?”

Here are three major areas of technical conversion and transformation for loading data from SAP NetWeaver BW into SAP BusinessObjects Planning and Consolidation:

• Matching the generic one key figure model in SAP BusinessObjects Planning and Consolidation
• Data format challenges

• Unit of measure harmonization

Matching the Generic One Key Figure Model

One major difference between SAP BusinessObjects Planning and Consolidation and SAP NetWeaver BW is the strict account-based model (i.e., the generic key figure model) in SAP BusinessObjects Planning and Consolidation with only one key figure. SAP NetWeaver BW on the other hand can contain multiple key figures per record in an SAP NetWeaver BW InfoProvider.

For example, you can store data in SAP NetWeaver BW using three key figures per one record, as shown in Table 1.

Table 1

Source: key figure model in SAP NetWeaver BW

In SAP BusinessObjects Planning and Consolidation, you have to split this up into three records using different accounts to represent each key figure (Table 2).

Table 2

Target: account-based model in SAP BusinessObjects Planning and Consolidation

SAP NetWeaver BW demands different key figures for financial amounts and quantities, while SAP BusinessObjects Planning and Consolidation needs to have both combined in one key figure. SAP NetWeaver BW has standard functionality called rule groups that allow you to transform a key figure model to an account-based model without any ABAP coding. You can create rule groups in the SAP NetWeaver BW workbench when defining transformations (data transfer processes [DTPs]). They allow you to create multiple records in SAP NetWeaver BW from one input record. Let’s look at a more detailed example to illustrate the use of rule groups.

Rule Groups: Business Scenario

Imagine a car vendor stores its revenue in a currency key figure called ZREVENUE, and its Cost of Goods Sold (COGS) in a key figure called ZCOSTS in an SAP NetWeaver BW InfoCube (Table 3). In addition, the InfoCube contains a third key figure called 0QANTITY of type Quantity, which stores the number of cars sold. SAP BusinessObjects Planning and Consolidation only has one key figure, so how do you get three SAP NetWeaver BW key figures, which are of types quantity and currency amounts, to fit into SAP BusinessObjects Planning and Consolidation? In addition, there is only one SAP BusinessObjects Planning and Consolidation unit field, the report currency dimension RPTCURRENCY. SAP NetWeaver BW has different units for different key figures, such as 0UNIT for quantities and 0CURRENCY for amounts.

Table 3

SAP NetWeaver BW data in a key figure model

In our example, we create one data record in the standard rule group transferring ZREVENUE into 0AMOUNT, and a second record in the Cost rule group transferring ZCOSTS into 0AMOUNT. Different constant accounts in the account InfoObject enable you to distinguish between the two (Figure 5). The account is set to a constant in each rule group (here to REVENUE).

Figure 5

Rule groups in SAP NetWeaver BW transformation for conversion to account based model

Now, how do you handle different key figure types? For SAP BusinessObjects Planning and Consolidation, you need to transfer the quantity key figure to your one key figure as well. However, 0AMOUNT is a key figure of type currency in SAP NetWeaver BW (i.e., you cannot write any quantity value into it). Therefore, in the quantity rule group of transformation, you transfer the source quantity 0QUANTITY into the quantity key figure ZBPC_QTY. In the end routine, you transfer 0AMOUNT and ZBPC_QTY into a characteristic ZBPCAMNT, which is of type CHAR (Figure 6).

Figure 6

Currency and quantity key figures are copied into the CHAR characteristic ZBPCAMNT

So what about the RPTCURRENCY dimension in SAP BusinessObjects Planning and Consolidation that has to contain both the currency values (i.e., USD and GBP) from the original revenue and cost records, and the unit of measure (UOM), such as eaches (EA), from the original quantity record? You cannot write EA into 0CURRENCY, nor should USD be stored into 0UNIT.

Therefore, create another CHAR InfoObject ZBPCUNIT and set the currency and UOM value to a constant in each rule group (Figures 7 and 8). However, instead of writing USD and GBP, enter LC for local currency. This accounts for the way SAP BusinessObjects Planning and Consolidation carries out currency translation. Each SAP BusinessObjects Planning and Consolidation entity stores a property with a local currency (e.g., GBP) in the SAP BusinessObjects Planning and Consolidation entity dimension. By entering LC into the RPTCURENCY dimension, SAP BusinessObjects Planning and Consolidation automatically understands that for this particular entity LC means GBP by looking at the currency property of the corresponding entity. It uses the LC value for the translation to the controlling area currency. All these transformations result in the dataset in Table 4.

Figure 7

If the currency type is local currency, enter LC in the SAP BusinessObjects Planning and Consolidation unit

Figure 8

For quantities, the SAP BusinessObjects Planning and Consolidation unit is set to a constant (here EA)

Table 4

Result of key figure to account-based model with different unit values in the SAP BusinessObjects Planning and Consolidation unit

Overcome Data Format Challenges

Transforming SAP NetWeaver BW data into the SAP BusinessObjects Planning and Consolidation one key figure model as described in the previous section is usually not enough to ensure an error-free load of SAP NetWeaver BW data into SAP BusinessObjects Planning and Consolidation. Several format conversions may be required to allow data to flow into SAP BusinessObjects Planning and Consolidation including the following:

• Data signage conversion
• Date format conversion
• Year/month concatenation
• Definition of constant values
• Placeholder values for empty fields
• Removal of special characters from alphanumeric SAP ERP material numbers
• Removal of leading zeroes: alpha conversion

It is best practice to create new SAP NetWeaver BW InfoObjects for the converted data in SAP BusinessObjects Planning and Consolidation format.

Data Signage Conversion

After converting the SAP NetWeaver BW key figure model to the SAP BusinessObjects Planning and Consolidation account-based model, you might encounter the next issue: negative key figures in SAP NetWeaver BW. Let’s assume the COGS values in our example are stored as negative values. The SAP NetWeaver BW internal format of ZCOSTS is <value>- (i.e., the minus sign is a suffix). That is, -40,000 is stored as 40,000- (Table 5). As such it is transferred into the CHAR InfoObject ZBPCAMNT. SAP BusinessObjects Planning and Consolidation however expects -40,000 (i.e., the sign as a prefix). Otherwise SAP BusinessObjects Planning and Consolidation runs into a load error.

Table 5

Source: Internal key figure signage format in SAP NetWeaver BW

Therefore, you need to ensure that the values are changed to the SAP BusinessObjects Planning and Consolidation key figure format. This involves some simple ABAP coding in SAP NetWeaver BW, which puts the minus sign (-) from the right to the left side of the amount in the CHAR field ZBPCAMNT (Table 6).

Table 6

Target: SAP BusinessObjects Planning and Consolidation key figure signage format

The signage conversion is implemented in a function module that you call in your end routine when combining currency and quantity key figures into one CHAR field. The function module contains the logic shown in Figure 9.

Figure 9

Function module that converts signed data into an SAP BusinessObjects Planning and Consolidation format

Date Format Conversion

The SAP BusinessObjects Planning and Consolidation TIME dimension expects the date in the format YYYY.MON (e.g., 2009.MAY). However, SAP ERP and SAP NetWeaver BW store the date in the following format: YYYYMM (i.e., 200905), as shown in Table 7.

Table 7

Source: Internal time format in SAP NetWeaver BW

It is easy to convert an external format stored in one field to the SAP BusinessObjects Planning and Consolidation format using a conversion file, which is referenced in a transformation file, as shown in Figure 4 and 10. The resulting time format after the conversion is shown in Table 8.

Figure 10

SAP BusinessObjects Planning and Consolidation conversion file for the time format conversion

Table 8

Target: SAP BusinessObjects Planning and Consolidation time format

Year/Month Concatenation

What happens if an external value is stored in several fields instead of one? For example, let’s assume that externally the date is stored in two fields, YEAR and MONTH (Table 9).

Table 9

Source: Internal time format with year and month in separate fields

The SAP BusinessObjects Planning and Consolidation TIME dimension expects the date in the format YYYY.MON (e.g., 2009.JAN). As you have just seen, it is easy to convert an external format stored in one field to the SAP BusinessObjects Planning and Consolidation format using a conversion file. However, if the date is stored in two separate fields, the conversion file concept does not work by itself because the conversion file maps only one external field to one internal value in SAP BusinessObjects Planning and Consolidation. The solution is the concatenation capability in SAP BusinessObjects Planning and Consolidation transformation files (Figure 11).

Figure 11

Transformation file using two concatenated input fields and a conversion file to map the time dimension

The syntax *col(3)+*col(5,1:2) concatenates 2009 and 05 from the above example into the value 200905. The value 200905 can be passed to a conversion file, which then converts it to the value 2009.MAY. 

Definition of Constant Values

Unlike SAP NetWeaver BW in which initial values are not a problem, SAP BusinessObjects Planning and Consolidation requires a value in every field of the record. This leads to the assignment of constant values in SAP BusinessObjects Planning and Consolidation. One example is the SAP BusinessObjects Planning and Consolidation DataSrc dimension that you can use to track where data comes from. When loading data from SAP ERP Cost Center Accounting (CCA) (Table 10) into SAP BusinessObjects Planning and Consolidation, you can track its origin by assigning the constant CCA to the SAP BusinessObjects Planning and Consolidation DataSrc dimension (Table 11).

Table 10

Source: Data from SAP ERP CCA

Table 11

Target: Data containing a constant CCA as an identifier of its source

There are two options for assigning constants:

• SAP NetWeaver BW transformations
• SAP BusinessObjects Planning and Consolidation transformation files

If you assign the constant in our SAP NetWeaver BW transformation, you need to create an InfoObject in SAP NetWeaver BW to store that information (e.g., ZBPC_SRC) as shown in Figure 12.

Figure 12

Assign the constant CCA to the DataSrc InfoObject in the transformation rule

Alternatively, in SAP BusinessObjects Planning and Consolidation, transformation files enable the user to define new fields that do not exist in the original source data and fill them with constants. In our example, the SAP BusinessObjects Planning and Consolidation CCA DataSrc value should be CCA. In the example in Figure 13, the code DataSrc=*NEWCOL(CCA) creates a new column for the DataSrc dimension and assigns the value CCA to it.

Figure 13

The transformation that creates a new DataSrc column with the constant value CCA

Placeholder Values for Empty Fields

The previous topic of constant values is closely related to the topic of placeholders. As discussed in the previous section, in SAP BusinessObjects Planning and Consolidation database tables, a dimension member can never be empty. This can be a problem if the source data field contains only values for a few, but not all, records. For example, one common reporting requirement in operational expenditure (OPEX) planning is to see costs per cost center broken down by internal order where applicable. However, not every single record carries an internal order value (Table 12).

Table 12

Data records from CCA with and without an internal order value

SAP ERP CCA data can be sourced from the CCA extractor Cost Centers: Actual Costs - Line Items (0CO_OM_CCA_9). The extractor contains an auxiliary account InfoObject 0AUXACCVAL, which contains the internal order information from SAP ERP CCA (if existent). To pass the internal order information on to SAP BusinessObjects Planning and Consolidation, you create an SAP BusinessObjects Planning and Consolidation InfoObject in SAP NetWeaver BW and fill it with the internal order value. You need to cut off leading zeroes before the data transfer and apply the function module CONVERSION_EXIT_ALPHA_OUTPUT (Figure 14).

Figure 14

Fill the internal order with a placeholder value when it is empty

For data records from CCA without internal order values, you need to fill the internal order InfoObject with placeholders (e.g., NO_ORD), as shown in Tables 13 and 14.To fill the internal order InfoObject with the value from CCA or a placeholder value such as NO_ORD, you can use a simple IF statement in a routine in the update rules.

Table 13

Source: CCA data with and without an internal order

Table 14

Target: SAP BusinessObjects Planning and Consolidation internal order value and placeholder for empty internal order

Removal of Special Characters from Alphanumeric ERP Material Numbers

SAP BusinessObjects Planning and Consolidation dimension IDs do not accept certain special characters such as a dash or parentheses. These special characters, however, can be found in SAP ERP source data. A prominent example of an object that often contains special characters is the alphanumeric material number in SAP ERP, which is stored in the SAP NetWeaver BW InfoObject 0MATERIAL to represent the stock keeping unit (SKU), as shown in Table 15.

Table 15

Source and Target: BW and SAP BusinessObjects Planning and Consolidation material number

In SAP BusinessObjects Planning and Consolidation, the member IDs of the PRODUCT dimension may be the technical keys of 0MATERIAL in SAP NetWeaver BW. To obtain the material numbers for SAP BusinessObjects Planning and Consolidation without any special characters, you have to remove or replace them, copying the values to a specific InfoObject (e.g., ZBPC_MAT) that you want to transfer into SAP BusinessObjects Planning and Consolidation.

To replace any special characters that SAP BusinessObjects Planning and Consolidation does not accept, you can apply a custom function module in the SAP NetWeaver BW transformation rules. This function module uses a simple ABAP REPLACE statement (Figure 15 and 16) to replace characters that are invalid in SAP BusinessObjects Planning and Consolidation with permitted ones.

Figure 15

Custom function module to replace special characters and delete leading zeroes

Figure 16

Call the custom function module in SAP NetWeaver BW transformation rules to replace special characters

Removal of Leading Zeroes: Alpha Conversion

SAP NetWeaver BW InfoObjects can contain leading zeroes, which may not be desired in SAP BusinessObjects Planning and Consolidation. For example, the SAP NetWeaver BW customer InfoObject 0CUSTOMER may contain leading zeroes. You can delete leading zeroes in SAP NetWeaver BW using the standard function module CONVERSION_EXIT_ALPHA_OUTPUT by calling it in a routine of a transformation rule (Figure 17).

Figure 17

Call function module CONVERSION_EXIT_ALPHA_OUTPUT to delete leading zeroes in the customer number

To ensure that leading zeroes are not automatically reinserted in SAP NetWeaver BW, you should deactivate the ALPHA routine in the InfoObject. Figure 18 shows an example of an InfoObject in which the conversion routine ALPHA is not selected.

Figure 18

SAP BusinessObjects Planning and Consolidation customer in SAP NetWeaver BW without the ALPHA conversion routine

Unit of Measure Harmonization

When material sales are recorded in SAP ERP sales and distribution (SD) they are recorded in the unit of measure (UOM) in which they are sold — for example, eaches, boxes, pallets, or cartons. To make information comparable, you may need to report quantities in a harmonized way — for example, in the base unit of measure (BUOM). You might have this question: “If I sold 10 boxes and 30 eaches of one material, have I sold more or less than another subsidiary that sold 5 boxes and 60 eaches of the same material?”

Unit harmonization may also be necessary when conducting market share analysis comparing your data against external market data. If you want to use a standardized unit of measure in SAP BusinessObjects Planning and Consolidation, then you can use SAP NetWeaver BW standard unit conversion functionality. It applies the conversion rates from SAP ERP material master data. This kind of conversion is hard to do in SAP BusinessObjects Planning and Consolidation itself because no standard functionality is available to pull the material master conversion information from SAP ERP into SAP BusinessObjects Planning and Consolidation.

As standard unit of measure conversion, SAP NetWeaver BW uses unit conversion types. Unit conversion types are defined in transaction RSUOM in SAP NetWeaver BW. If, for example, your BUOM is eaches (EA), you can define a conversion type accordingly (Figure 19 and 20). You can call this conversion type in the transformation rules to convert all sales of a specific material into eaches to allow easy comparisons. In the UOM tab of transaction RSUOM you define the source UOM and the target UOM.

Figure 19

BUOM conversion type reading conversion information from 0MATERIAL

Figure 20

Conversion to eaches (EA) in conversion type

After defining a conversion type, you can call it in a transformation in transaction RSA1. In the transformation rule for the quantity, set the unit to be derived to from Conversion and call the corresponding conversion type (Figure 21). For more detail on UOM conversion in SAP NetWeaver BW, see the SAP Help section “Quantity Conversion (New).”

Figure 21

UOM conversion in the transformation of 0QUANTITY

Note that technical load automation does not deliver the integration of the business semantics within your data. For your project to be successful, both technical and semantic data integration need to be addressed during the design and construction of your SAP BusinessObjects Planning and Consolidation solution. For comprehensive coverage of the semantic challenges of SAP data integration into SAP NetWeaver BW see the article “Integrate Your SAP Data into SAP BusinessObjects Planning and Consolidation.”