• Business Scenario
• Document Objective
• Prerequisites/Assumptions
• Implementation Steps
• Conversion File Fundamentals
• Transformation File Fundamentals
• Scenario 1: Data Load with 1 Transformation and 1 Conversion File
• Scenario 2: Data Load with 1 Transformation and 2 Conversion Files
• Scenario 3: Data Load with 1 Transformation and 1 Conversion File with multiple sheets
• Scenario 4: Data load with 1 Transformation and 1 Conversion File with formula
• Scenario 5: Data load with 1 Transformation and 1 Conversion File with skipping instruction

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.

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:

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)

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.

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.

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.

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.

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.

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