Services
Education
SAP Delivery Framework
SAP Implementation
SAP BPC Implementation
SAP BOBJ Implementation
SAP BW/BI Implementation
SAP ABAP Implementation
SAP FICO Implementation
Hyperion Implementation
 
Events Registration
Contact us for you're On Job/ Remote Support
 
Data Manager End-To-End Implementation in SAP BPC NW 7.5
Using 1. Data Files, 2. Conversion Files, 3. Transformation File
Complex Data Load Scenario Analysis
A SAP BPC NW 7.5 Solutions
 
Table of Contents


 

Business Scenario

In a real time business scenario, it's common for the business users to load data into BPC applications. In addition to using the input schedules, users can also bulk load the data from flat files using BPC Data Manager.

BPC data manager offers a simple user interface easy for the business users to understand. Both transaction and master data can be imported using Data Manager. Data Manager can support many different scenarios including data manipulation using default logic and integration with Process Chains. However, default logic and Process Chain integration can be one time setup after which business users can run or schedule the data load without being concerned about the technical details behind it.

Back to top
Document Objective

This guide will provide all the steps necessary to load transaction data into a BPC application. It covers 5 different scenarios in which the data loads can happen.

There are other scenarios not covered by this guide. They can be explored once the Fundamentals given in this guide are understood.

Implemented Business Scenarios:

  • Scenario 1: GL Data load with 1 Transformation and 1 Conversion File
  • Scenario 2: GL Data load with 1 Transformation and 2 Conversion Files
  • Scenario 3: GL Data load with 1 Transformation and 1 Conversion File with Multiple Sheets
  • Scenario 4: GL Data load with 1 Transformation and 1 Conversion File with Formula
  • Scenario 5: GL Data load with 1 Transformation and 1 Conversion File with instruction to skip records

 

See below for the logical flow diagrams:

 

 

Back to top
Prerequisites/Assumptions
  • Necessary Dimensions and applications are already created
  • Appropriate security settings have been configured
  • Target data region does not have any data just to make the verification easier
  • EVDRE input schedule already created to verify data load for the target data region
Back to top
Implementation Steps

Steps listed below apply to all the scenarios with minor adjustments.

  • Create Data file
  • Upload data file
  • Preview data file
  • Define conversion file. Save under /COMPANY/CONVERSIONFILES folder
  • Define transformation file
  • Validate and save (or process) the transformation file
  • Run data package
  • Run "Import Transaction data" data package
  • Select the appropriate Transformation File and the data file
  • View log
  • Verify thru EVDRE report that the load is completed successfully

 

How to log into BPC Excel:

All the exercises in the guide are performed via BPC Excel. See below the screenshots on how to log in. There is more than one way to log in:

Via web interface
 
Step 1: Launch BPC web to access the Interface for Excel
 
 
Step 2: Enter User ID and password in the popup screen
 

Step 3: In the BPC web interface, click on “Available Interface” and then “Interface for Excel”
 
 
Step 4: Select appropriate Appset and Application and click OK
 

Step 5: Enter User ID and password in the popup screen. Click “Finish”.
 

You will see the following screen (Home screen of BPC Excel Interface)
 

Via BPC Admin Console:
 
Step 1: In the BPC Admin Console, click on “Available Interfaces” and then select “Interface for Excel” to access BPC Excel.
 
 
Step 2: Select appropriate Appset and Application and click OK
 

Step 3: Enter User ID and password in the popup screen. Click “Finish”.
 

You will see the following screen (Home screen of BPC Excel Interface)
 
Back to top
Conversion File Fundamentals

Conversion file is useful in converting external data from the flat files into system recognizable format. It's also used to perform calculations on the incoming data before being stored in the system as signed data.

See the three screenshots in order below for navigation to access/create a conversion file in BPC Excel:

Step 1: Select "Manage Data" under "Available Task Categories" to access available data management tasks
 
 
Step 2: Click on "Maintain Conversions" to see conversion file tasks
 

Step 3: Depending upon the need, any of the different conversion tasks can be selected.
 
 
Conversion file is an Excel file that has three columns External, Internal and Formula. External column has the member value expected from the data file. Internal column has the member value as stored in the BPC database. Formula column can be used for any data manipulation before being stored as signed data. Please refer to Fig 4 for a basic conversion file format.

Step 4: Define conversion rules
 

Step 5: After definition, it can be validated and saved.
 
Back to top
Transformation File Fundamentals
Transformation file is useful in instructing the system how to read the data from a data file and also connects the conversion file with appropriate dimensions or signed Data.

See the steps three screenshots in order below for navigation to access/create a transformation file in BPC Excel:

Step 1: Select "Manage Data" under "Available Task Categories" to access available data management tasks
 

Step 2: Click on "Maintain Transformations" to see the transformation file tasks
 
 
Step 3: Depending upon the need, any of the different transformation tasks can be selected.
 
 

Transformation file has three sections: Options, Mapping and Conversion.

Step 4: Define Transformation file
 
Transformation File Options:

  • FORMAT: Indicates if the data file is delimited or fixed or variant (Required)
  • DELIMITER: Indicates the type of delimiter (comma, tab, space, text character)(Required)
  • HEADER: Indicates if the data file has a header (Required)
  • AMOUNTDECIMALPOINT: Allows to enter non-period decimal point
  • SKIP: No. of lines to skip at the top (Required)
  • SKIPIF: Skips a line in the data file if it begins with a given text string (Required)
  • VALIDATERECORDS: Validates mapping and maps data (Required)
  • CREDITPOSITIVE: Reverses sign in case of LEQ and INC accounts (Required)
  • MAXREJECTCOUNT: If validating records, the no. of rejected records at which stop processing (empty string means 500, some postive number, 0r -1) (Required)
  • ROUNDAMOUNT: Indicates amount of decimal values to round
 

Mapping Section:

MAPPING section defines how data is mapped to the BPC database. It is also used for mapping if the header in the data file is different from the BPC database. In the screenshot above, mapping section indicates the position of the string to be read from the data file for a dimension member value.

Conversion Section:

Connects the dimension/signed data with the conversion file

After completing the transformation file, it can be validated and saved.

Step 5: After definition, transformation file can be validated and saved.
 
Back to top
Scenario 1: Data Load with 1 Transformation and 1 Conversion File
Step 1: Create Data file
 
Create data file with necessary dimension and signed data information. Please note it can be delimited or fixed.
 

Step 2: Upload Data file
 
Click "Manage Data" to access available BPC data tasks.

 
Select "Upload data file" option to save the data file in the application server

 

Select appropriate data file from the local machine in the source file field and the desired server destination in the Destination file field.

 
Step 3: Preview Data file
 
Uploaded data file cab be viewed using the “Preview Data File” option.

Select appropriate file for verification

Preview of the data file

 
Step 4: Define conversion file
 
Select Manage Data as previously mentioned to access data tasks.

Select "Maintain Conversions" to access conversion file tasks

 
Select appropriate option to create/update/copy conversion file.

 
Define conversion file

After definition, conversion file can be validated and saved.

 
Note that the conversion file should be stored directly under “CONVERSIONFILES” folder in the server.

 

In the screenshot below, note how the Time dimension ID is converted into a different format using the conversion file. IFP_Time member value in the system is "2009.JAN" but the data file has "0109" thus requiring conversion.

 
Step 5: Define transformation file
 
Select "Manage Data" as previously mentioned to access data tasks.

Select "Maintain Transformation" to access transformation tasks

 
Select appropriate option to create/update/copy transformation file

Define transformation file by providing appropriate information under each section as needed.

 
Step 6: Validate transformation file
 
After definition, transformation file can be validated and saved.

During validation process, the appropriate data file can be selected.

 
See below for different actions performed by the system to validate the transformation file.

Step 7: Run Data Package
 
Select “Manage Data option again.

 
Select “Run a package” to execute the appropriate DM data package

Step 8: Run "Import Transaction Data" Package
 
Need to select the "Import Transaction data" data package since transaction data needs to be imported into the BPC application

Step 9: Select the appropriate Transformation File and the data file
 
In the next screen, select the data file and transformation file just defined from the server. Note that there are options to merge or replace the existing data. Also, you can choose to run the default logic during the package execution.

Choose appropriate options regarding work status and run mode.

 
Step 10: View log
 
Log can be viewed by selecting the “View Status” button.

Choose a line item and click on Detail button to view the details of the log.

 
View log details.

 
Step 11: Verify thru EVDRE report that the load is completed successfully

Refresh the pre-defined EVDRE report to verify that the data load was successful.

 
See below for the logical flow in scenario 1.

Back to top
Scenario 2: Data Load with 1 Transformation and 2 Conversion Files
Step 1 thru 3: Refer to Scenario 1
 
First three steps (create/upload/preview) are the same as scenario 1 except that two dimension members in the data file will need conversion. In this example, both Entity and Time dimension members need conversion.

Step 4: Define 2 Conversion Files
 
Select “Manage Data” to view data tasks.

Select “Maintain Conversions” to access conversion file tasks

 
Select appropriate option to create/update/copy conversion file

 
Note both IFP_Entity and IFP_Time members need conversion. Following same process above, two conversion files need to be created.

 

Create data file as follows. Note that both IFP_Time and IFP_Entity dimension members need conversion.

 
Once the Conversions are defined, they can be validated and saved.

 
Make sure to save the conversion files directly under “CONVERSIONFILES” folder in the server.

 
Step 5: Define transformation file
 
This step is the same as scenario 1 except that two conversion files need to be mapped.

Steps 6 thru 11 are the same as scenario 1. Note that two different conversion files helped in finding the right data region to update.

See below for the logical flow in scenario 2.

Back to top
Scenario 3: Data Load with 1 Transformation and 1 Conversion File with multiple sheets
First three steps (create/upload/preview) are the same as scenario 1 & 2.

Step 4: Create one conversion file with multiple sheets using the same procedure as step 4 in scenario 2.
 

Note the conversion rules are defined in the same files but in multiple sheets.

 
 
Step 5: Define transformation file
 
This step is the same as scenario 1 & 2 except that two sheets in the same conversion file need to be mapped.

Note that conversion sheet names need to be mapped correctly.

 
Steps 6 thru 11 are the same as scenario 1. Note that one conversion file with two different sheets helped in finding the right data region to update.

See below for the logical flow in scenario 3.

Back to top
Scenario 4: Data load with 1 Transformation and 1 Conversion File with formula

All the steps in this scenario are the same as scenario 1 except inclusion of a formula in the conversion file and usage of "Amount" keyword in the transformation file for conversion mapping.

In the conversion file below, "*" indicates all members. It can be member specific as well. In this case, the signed data will be increased by 10%.

Under the conversion section of the transformation file, “Amount” keyword needs to be specified as follows:

After the data load, signed data for all the three records increased by 10%.

See below for the logical flow in scenario 4

Back to top
Scenario 5: Data load with 1 Transformation and 1 Conversion File with skipping instruction
All the steps in this scenario are the same as scenario 1 except for inclusion of skipping instruction in the conversion file.

Conversions file with instruction to skip records with a specified string "Product1":

 
In this example, the data record with “Product1” string will be skipped. In this case, the first record will be skipped.

 

Note that all the records except for the first one were processed.

 
See below for the logical flow in scenario 5