SAP BPC - Data Loading
Data loading in BPC is done with the help of Data Manager that allows you to move data in Planning and Consolidation system, copy and move data and export data from an application. Data Manager moves the data in form of packages, which allows scheduled sequences to run in the background and some can initiate other processes.
Loading Data from Source System to Cube in SAP NetWeaver BW
We can pull the data from different source system like SAP ECC system, SAP CRM, SRM using extractors. You can also load data from non-SAP sources to InfoCube in SAP BW system.
To load the data into InfoCube, go to workbench in BW system.
Transaction Code: RSA1
Data warehousing workbench will open, go to Source Systems on the left side.
Data acquisition in BW system can be done using the following methods −
- Service API
- Transfer of Flat Files
- DB Connect
- Universal Data Integration
- XML Based Data Integration
- Third party ETL tools
- Application Specific Data Acquisition
Creating a Source System
Double-click on File and enter logical system name, source system name, Type and Release.
Next is to create an InfoSource for characteristics data. Right-click on InfoSource right pane → Create Application Component.
Once you create the InfoSource, right-click on it and assign data source.
Then, right-click on Source system → InfoPackage, select master data and save.
Define Load characteristics.
Finally, create an InfoSource for transaction data. Create Updating rules and an Info Package to load transaction data.
Loading Data from SAP NetWeaver BW to SAP BPC
You can load data to BPC system from SAP BW InfoCube. With BPC Data Manager, you can use BPC code packages. You can copy or move data in different applications, and can export the data from applications. BPC packages are based on BW process chains and come with BPC installation.
Data Manager Tool is integrated with Excel. To start BPC, go to Planning and Consolidation → eTools → Data Manager.
In Data Manager Package, there are different data transfer modes −
- Aggregate Overwrite
While loading data from Info Provider, you have the following options −
BPC also supports Delta load as there are many records getting generated for each load. Delta initialization is possible with higher enhancement package and you can perform Delta load in Organize option under Data Manager Tab.
In BW system, full load is supported for Standard InfoCubes, Virtual InfoCubes, Standard and Optimized Data Store Objects DSO’s.
You can Delta load using Data Manager for Standard InfoCubes and Standard, Optimized DSO.
Next is to create a Package which contains conversion file and transformation file with variables, which is responsible to trigger a BW process chain. You can combine different packages for separate tasks in package links.
Creating Transformation File
Transformation file provides the mapping of fields from BW to BPC and is an excel file in BPC. It contains transaction data from Info Objects in BW with BPC dimensions defined in administration console.
There are three sections defined for mapping file −
Options − Contains the general settings.
Mapping − Used to assign dimension in BPC system with a source column in BW InfoObject.
Conversion − Contains the link of other Excel sheets to apply the conversion routine.
To create a Transformation File − Go to BPC Excel client. Next is to choose App set and Application where these Transformation file will be used.
Now go to Data Manager in Business Planning and Consolidation → More → New Transformation File.
You will see an excel sheet with three sections - Option, Mapping and Conversion. You don’t require to make any changes to Option, Conversion.
Go to Mapping and define Transactional data mapping from InfoObject in BW to BPC Dimension.
ACCOUNT = ZACCOUNT
TIME = ZBPCTIME
SIGNEDDATA = ZAMOUNT
Note that you can also copy an existing transformation file or update an existing transformation file by going to ‘More’ tab under Business Planning and Consolidation ribbon as shown in the following screenshot.
Creating a Conversion File
A Conversion file is used to apply the conversion rules for BPC dimension and is an Excel file. It contains three sections −
External − Contains the value from external source.
Internal − Contains the value it should be having for BPC dimension.
Formula − Defines the conversion formula applied to each record.
To create a Conversion File − Go to BPC Excel client. Choose AppSet and Application where these transformation files will be used.
Go to Business Planning and Consolidation Ribbon → More → New conversion File
You can also use other functions like - Manage conversion file, copy conversion file, etc.
Loading Master Data from Flat File into a Dimension
You can load the data in BPC dimension from source flat file like CSV, XLS. You have to first create the dimension using administration console and a connection should be established.
Go to BPC Excel → EPM tab and click on the log on the left side panel. Select the connection name, user name and password.
Go to Data Manager tab → EPM → Upload data.
Enter the name and click ‘Save’.
Create a Transformation file as mentioned above. If the number of fields are the same for source and destination, no need to use mapping.
You will only have values under Options section and rest of the fields would be blank in this Transformation file.
If you have source and target with different number of data columns, you have to define it under mapping like for “description” in the above screenshot.
Once Transformation file is created, next is to validate and process transformation. Select data type → Master data from flat file and click ‘Save’ icon.
Go to ‘DM’ tab at the top in BPC Excel → Run Package.
Click ‘Data Management’ and select import master data attribute from flat file.
Click ‘Run’ at the bottom and enter the path for data file and Transformation file as created above.
Select the dimension in which you want to upload this data and click ‘Run’. Once it is completed successfully, you can go to web portal check the data under Dimension.