Most of the time, foreign currency exchange (FX) rates are maintained in SAP ERP Central Component (SAP ECC). Usually the same rates are used in SAP Business Planning and Consolidation (BPC) for planning and consolidation applications.

The BPC administrator has to extract the FX rates on a periodic basis from SAP ECC to the rate application. This is usually a manual process with some degree of automation based on a custom solution. These solutions require you to log on to an external system (i.e., SAP ECC or SAP NetWeaver BW) to trigger the extraction. They involve additional activities to transform and move the data to a state in which you can load it to the BPC rate application. (See the sidebar “Three Custom Solutions and their Drawbacks” for a description of them.)

The BPC version of SAP Netweaver as of now does not have a feature or an automated tool that can help load exchange rates from SAP ECC system or SAP Netweaver BW. In this article, I use the SAP Netweaver BW system as a source to extract the exchange rates. The exchange rates in SAP NetWeaver BW are replicated from SAP ECC on a daily basis using a job-based ABAP program, RSIMPCURR. This keeps the data in SAP Netweaver BW synchronized with SAP ECC.

Instead you can use a custom method I devised that allows you to automate the extraction of FX rates from SAP ECC in real time. You can trigger, automate, and monitor the extraction within the BPC Administrator console, thus relieving the BPC administration from a manual process and reducing the chances of manual errors.

My custom solution uses table TCURR in SAP NetWeaver BW, which is synchronized with SAP ECC on a daily basis. The solution uses an SAP NetWeaver BW virtual InfoCube and a standard BPC Data Manager package to load data to the rate application in BPC. It requires SAP NetWeaver BW 3.1C or above and BPC 7.5 or above.

This solution simplifies the process by eliminating:

• The mechanism to extract data from table TCURR to a flat file or an SAP NetWeaver BW InfoProvider
• The need to transform data for loading into the rate application
• Moving flat files or storing data in an SAP NetWeaver BW InfoProvider
• Logging into SAP ECC or SAP NetWeaver BW to manually trigger the load

It also addresses the business need to synchronize the FX rate data either periodically or on demand, and the need to monitor the extraction of data.

Click the video clip below to see my complete solution in action.

I provide step-by-step instructions to create my custom solution.

Custom Solution Details

The custom solution consists of the following:

Extract Data from Table TCURR in Real Time

Before you can create the virtual InfoCube that extracts data in real time from table TCURR in SAP NetWeaver BW, you need to understand the structure of table TCURR, which is shown in Table 1.

Table 1

The structure of table TCURR

The important fields to note are KURST, FCURR, TCURR, GDATU, and UKURS.

The fields FFACT and TFACT define the ratio of the from currency and to currency. They play an important role when you translate small currency that does not have the hundredth denomination, such as Chilean pesos (CLP) and the Hungarian forint (HUF). Typically, table TCURR looks like what you see in Table 2. The exchange rate type is month end (ME), with the to currency is the U.S. dollar (USD).

Table 2

Data from table TCURR

As you can see, field GDATU looks like a number even though it is a date. This is because the date is maintained in an inverted format. To get to the actual date, you subtract the number from 99,999,999. Therefore the actual date is 99999999 – 79888768 = 20111231 (yyyymmdd) — or December 31, 2011.

In SAP NetWeaver BW, a periodic job is scheduled to synchronize table TCURR with SAP ECC. To do so, go to transaction Administrator Workbench (RSA1), source system. Right-click the ECC source system and choose Transfer Exchange Rates. Specify the Exchange rate types and changed since date and mode as update Exchange rates.

You can check the job in the SAP NetWeaver BW system with ABAP program RSIMPCURR.

Since BPC does not support loading the data directly from a table, you need to expose the data from the table in a form in which BPC can read data. An SAP NetWeaver BW InfoProvider would be the best fit. BPC standard Data Manager package BPC: Load InfoProvider data with selection UI (/CPMB/LOAD_INFOPROV_UI) allows you to provide restrictions on what data needs to be extracted. It also provides the capability to do further transformation and conversion using transformation and conversion files.

An SAP NetWeaver BW virtual InfoCube with services can expose data from table TCURR in real time. Since the size of the data is not big, the performance for querying data from an InfoCube is not an issue.

A virtual InfoCube with services is an InfoCube that does not physically store its own data in SAP NetWeaver BW. The data source is a user-defined function module. You have a number of options for defining the properties of the data source more precisely. Depending on these properties, the Data Manager provides services to convert the parameters and data.

You also can use a virtual InfoCube with services if you want to display data from non-SAP NetWeaver BW data sources in SAP NetWeaver BW without having to copy the data set into the structures. The data can be local or remote. You can also use your own calculations to change the data before it is passed to the OLAP processor.

When you create an InfoCube, you can specify the type. If you choose virtual InfoCube with services as the type, an extra Detail button appears on the interface. This button opens an additional dialog box, in which you define the services with the following settings:

• Setting 1 – Functional Module (Mandatory). Enter the name of the function module that you want to use as the data source for the virtual InfoCube. There are different default variants for the interface of this function module.
• Setting 2 – Support for Selection Condition (Optional). Select options for converting and simplifying the selection conditions. You do this by selecting the Convert Restrictions option check boxes. For my solution, I leave them unchecked. These conversions only change the transfer table in the user-defined function module. The result of the query is not changed because the restrictions that are not processed by the function module are checked later in the OLAP processor. You can select among these options:

-   No restrictions: If you select this option, no restrictions are passed to the InfoCube.

-   Only global restrictions: If you use this option, only global restrictions (FEMS = 0) are passed to the function module. Other restrictions (FEMS > 0) that are created (e.g., by setting restrictions on columns in queries) are deleted.

-   Expand hierarchy restrictions: If you choose this option, restrictions on hierarchy nodes are converted into the corresponding restrictions on the characteristic value.

• Setting 3 – Data Format and Data Transport (Optional).

-   Pack the RFC. This option packs the Remote Function Call (RFC). It packs the parameter tables in BAPI format before the function module is called and unpacks the data table that is returned by the function module after the call is performed. Since this option is only useful in conjunction with an RFC, you have to define a logical system that is used to determine the target system for the RFC.

-   SID support. If the data source of the function module can process SIDs, then select this option. If this is not possible, the characteristic values are read from the data source and the Data Manager determines the SIDs dynamically. In this case, wherever possible, restrictions that are applied to SID values are converted automatically into the corresponding restrictions for the characteristic values.

-   Internal format (key figures). In SAP systems, a separate format is often used to display currency key figures. The value in this internal format is different from the correct value in that the decimal places are shifted. You use the currency tables to determine the correct value for this internal representation. If this option is selected, the OLAP processor incorporates this conversion during the calculation.

Loading the FX Rates Data in BPC

Now that you understand the table TCURR structure and the capability to use a virtual InfoCube with services, let’s see how the rate application in BPC is structured.

A rate application has the following mandatory dimensions:

• R_ACCT is an account type dimension and stores details about the different types of rate (e.g., historical, average, end of period)
• R_ENTITY is an entity type dimension and stores the type of rate value source (e.g., whether the rate is entered directly in BPC or is a calculated rate)
• InputCurrency is a currency type dimension and stores each applicable local currency (e.g., EUR, USD, CHF)
• Time and category dimensions are of the type category and time type, respectively, and are usually shared across other planning and consolidation applications
• SignedData is a key figure holding only numbers; the combination of dimensions defines the value of a signed data key figure

The rates are maintained with reference to the reporting currency, so if you define USD as the reporting currency, the typical entry in rate application looks like Table 3. Category defines the version to which the rate is applicable.

Table 3

Data from rate application in BPC

Now let’s look at the BPC Data Manager Package, which you use to load data from the virtual InfoProvider to the rate application in BPC.

BPC provides a standard Data Manager Package BPC: Load InfoProvider data with selection UI (/CPMB/LOAD_INFOPROV_UI). You use it to load data from an SAP NetWeaver BW InfoProvider with selections on dimensions.

The Data Manager Package requires the technical names of the InfoProvider and selections, which provide the list of the characteristics on which restriction can be set. The Data Manager also requires transformation files for providing the mapping of the characteristic from an InfoProvider to BPC dimensions and a conversion file if needed.

Verify the FX Rates Data in BPC

The data load through the InfoProvider can be verified by creating a quick report on top of the rate application in BPC. You can compare it against the data in the virtual InfoCube.

Creating the Custom Solution

I use a copy of the APSHELL appset (APSHELL_BK) in BPC, version for SAP NetWeaver to develop the solution. You start by creating the following components.

Create a virtual InfoCube for exposing the FX rates data from an SAP NetWeaver BW table. This step involves the creation of the characteristics and key figures in Table 4.

Table 4

Structure of the FX rates virtual InfoCube

Then you create the virtual InfoCube based on the functional module as shown in this video demo clip:

Create the functional module referenced in the SAP NetWeaver BW virtual InfoCube. The function module acts as a data source and reads the data from table TCURR in SAP NetWeaver BW when the InfoCube is queried for data. The function module also takes care of the transformation of data from the table to the InfoObject format.

Use the ABAP code provided via this link for the function module for the virtual InfoCube. This video clip below shows how to create the functional module:

Create the BPC Data Manager packages. Load the data using the standard Data Manager package BPC: Load InfoProvider data with selection UI with the technical name /CPMB/LOAD_INFOPROV_UI.

Execute the Data Manager package, providing the selections for extracting data from the SAP NetWeaver BW InfoCube with transformation and conversion files. On successful completion of the Data Manager package, verify the number of records loaded.

Lastly, verify the extracted data in the BPC rate application by creating a simple report.

The contents of the transformation and conversion files are provided via this link. This video clip shows you how to create BPC Data Manager packages and how to load data from the virtual InfoCube rate application.

A Review of Your Results

Upon successfully creating the FX rates InfoCube, functional module, and Data Manager package, you have a solution to automate the extracting of FX rates from your SAP ECC system via SAP NetWeaver BW to BPC. You can run the package links on demand or schedule them.

This custom solution also provides insight on functionality offered by virtual InfoCubes with services in SAP NetWeaver BW, and how these can be used to provide real-time data if the size of the data is small, such as with exchange rate data. Virtual Infocubes do not have technical limitations on the size of data; if data is large, the InfoCube needs more time to display it and takes up more memory consumption.

Ashish Narway

Ashish Narway is an SAP BusinessObjects Planning and Consolidation, version for SAP NetWeaver architect, certified SAP NetWeaver BW Solution consultant, SAP financial and controlling solution consultant, and Project Management Professional (PMP) with more than 13 years of experience. In his tenure with Tata Consultancy Services, Limited, he has successfully led planning and consolidation and SAP NetWeaver BW implementations, as well as upgrade and support projects for large, multinational companies in the high tech, industrial machinery, construction, telecommunications, and manufacturing Industry industries.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.