Loading transactional data from any infocube to BPC Application in BPC7NW

[pravin_datar]

Business Planning and Simulation version for Netweaver successfully leverages the Netweaver infrastructure and we can use the data stored in infocubes in the Enterprise Data Warehouse in BPC7NW. In this blog we will discuss what tools and techniques we can use to get the transactional data stored in any infocube in BW into BPC Application using Data Manager in BPC7NW.

Options available for cube to cube data load:

There are many options available to the users of BPC7NW to get transactional data from a BW cube to BPC Application. Here are some of them:

1. We can export the transactional data from any infocube in the form of flat file and then use Data Manager in BPC7NW to load that data from flat file to BPC Application. The advantage of this option is that it is relatively very simple to administer and very flexible to adapt since we are dealing with a flat file when it comes to importing data into BPC Application. The limitation of this option is that here we are really not leveraging the BW platform. If we have to load data from any other non-BW source, we can use exactly the same approach  - export data from that external data source to a flat file and then import that flat file into BPC. So in essence, in this option we are treating BW infocube as any other external data source. There is another ostensible limitation that this option may portray - that this process can not be automated. If we have to get a flat file export from an infocube and then import that flat file into BPC, then it may appear that there has to be a handoff from the flat file export to the import of flat file. However we can overcome this limitation, if we want to, by creating a custom process chain that in turn executes the process chain to import the flat file data into BPC Application. You can read more about that at Running BPC process chains within non-BPC process chains
2. We can leverage BW Extraction, Transformation and Loading (ETL) tools to transfer transactional data from any infocube to the infocube used by BPC Application. BPC Application in BPC7NW generates its own infocube in its namespace. We can use standard BW ETL techniques to transform and load the data to the cube used by the BPC Application. (You can read more about BPC namespace at A reservation of a different kind – why, what and how of BPC namespace). This option also is valid one and it indeed leverages the BW platform and also this can very well be automated if we desire to do so. Further this option can handle deltas very efficiently since we can use the delta handling mechanisms in BW. Lastly, using the ETL toolset is a proven, scalable and very robust technique to handle data within BW. However this option is also beset with several limitations. Firstly, infocube for the BPC Application will need to be switched to loading mode while loading it using ETL and then switch it back to planning mode after loading is complete. This means that during that time, it will not be available for planning through BPC front end. So the ETL load has to coordinated with the BPC users to avoid any potential mishaps like the automated process switching the BPC cube to loading mode when a planner is in the middle of updating his/her plan. Secondly, in BPC7NW, we can maintain validations to validate the data written to BPC Application so that invalid records are not written and data integrity is maintained.  If we use BW ETL, it will bypass this validation mechanism completely and there is a risk of invalid records being written to BPC application. The validation mechanism will not check the records that have already been written. Thirdly, the BPC audit logs will not be updated if we use BW ETL since it won't invoke any BPC audit functionality. Fourthly, the data will always be additive - for example if we have 10 in the cube and then write 100, the result will always be 110. This is just a consideration rather than a limitation. Finally, if and when the BPC user executes ‘full optimize' for the BPC Application, BPC may end up generating a totally new cube which is more optimized than the previous one. In that case, all the ETL work that was done for the previous cube will be dropped from the new cube. At present, the ‘full optimize' option does not automatically inherit the ETL configuration done on the earlier BPC cube. The building blocks for the ETL like the datasource, infosource etc will still be there in BW but they would be required to be linked again to the new cube.
3. This brings us to the third option which we will discuss in much more detail. This option is to use the Data Manager in BPC7NW to load transactional data from any Infoprovider in BW to the BPC Application. This option overcomes almost all the limitations enumerated above since this is executed from within BPC itself. This can very well be scheduled from within Data Manager or by invoking the Data Manager process chain from a custom process chain.  So let us see how exactly we should go about realizing this.

Creating transformation file:

Before we run the data manager package to load data, we should prepare the transformation and conversion files. An example of a transformation file that we can use to load data from other infoproviders is given below.

Please note the mapping section. The dimension names in the BPC Application are mapped to the corresponding characteristics from the Infoprovider from where we want to load the data. Please note that as far as the BPC dimension names are concerned, we are using the dimension names (and not the technical names of the BW characteristics corresponding to those dimensions) whereas when we map them to the Infoprovider characteristics, we must use the technical names of the BW characteristics. For example, the TIME dimension in BPC is mapped to 0FISCPER characteristic in BW. Here TIME is the BPC Dimension name whereas 0FISCPER is the technical name of the BW characteristic in the source Infoprovider. Also please note the mapping for AMOUNT. The AMOUNT is mapped to the technical mane of the key figure in the source Infoprovider. So in this case, ZMAOUNT01 is the technical name of the key figure in the source Infoprovider.

In this regard, please note that the source BW Infoprovider can have multiple key figures. If so, we can choose only one key figure in a transformation file since our BPC cube has only one key figure. If for any reason, you have a situation where you have to get data from two or more key figures, you can use multiple transformation files and run the data manager package multiple times with each transformation file. However in that case, please note that the data from all those key figures will end up in the same single key figure in the BPC cube.

What if there is no corresponding characteristic in the source Infoprovider for a dimension in BPC? What if we want to have a single default value in the data load regardless of what exists in the source Infoprovider? Well, in that case, we can use the keyword *NEWCOL in the transformation file for the corresponding BPC dimension. Please see the example of the transformation file below.

Here for the category dimension, we are forcing the default value of FORECAST in the data load. Similarly for the dimension P_DATASRC, even if there is no corresponding BW characteristic in the source Infoprovider, it is OK - we can always pass the default value through our transformation file.

What if we want to load only a subset of the data from the source Infoprovider? In that case, we can enter our selection in the transformation file. Please see the example of the following transformation file:

Please note that in the OPTIONS section, we have entered a selection to select data from only two characteristic values C2000 and C1000 of the characteristic ZACCT01. Hence we are selecting only a subset of the source Infoprovider. You can enter selections from multiple characteristics here in the selection and thus load data from a specific data slice in the source from Infoprovider.  

Creating conversion file:

In addition to the transformation file, we can have conversion files and refer them in the transformation files. This is necessary if the master data values in the BPC dimension in the BPC Application (dimension members) and the characteristics in the source Infoprovider (characteristic values) are different. Those conversion files we can refer in the transformation file as shown below:

 

Here, the conversions for TIME dimension are read from the file ZBICGR01TIME.XLS. That conversion file can have the mapping between internal (BPC) time dimension members and external (0FISCPER) values. That conversion file is shown below:

Here ? is used as a wildcard so that we don't have to write conversion for each year. This conversion file can work for any year. Another example of the conversion file for account is shown below:

In addition to writing such conversion files for BPC dimensions, we can write the conversion for AMOUNT if necessary. For example, if we want to change the data coming over for a particular dimension member during the load, we can write the formula in the conversion file for AMOUNT as shown below:

 

So in this case, the data for account CE0004220 will be increased by 10% during the load.

Validating the transformation file:

After we prepare the transformation and conversion files, the next step would be to get the transformation validated with the source Infoprovider. BPC7NW gives additional options for validation as shown below:

We should select the option 'transaction data from infocube' and enter the technical name of the Infoprovider. Please note that though the option here reads ‘transaction data from infocube' it works with DSO also in the same way.

The validation then can validate our transformation and conversion files against the data in the source Infoprovider and gives us the result as shown below:

Running the data manager package:

Once we have our transformation files validated, we are in a good shape to run our data manager package to load the data from the source Infoprovider. There is a delivered process chain and data manager package to load data from Infoprovider as shown below:

If we run this package, we can enter the technical name of the source Infoprovider and the transformation file and schedule the load. Upon completion, we can get the success message in the data manager package status as shown below:

Loading data from other appsets:

So far we have seen how we can use the data manager features to load data from any Infoprovider in BW to BPC Application. This raises another question. Can we use the same technique to load data from one BPC application to another BPC application in the same Appset or for that matter from any application in any other Appset within BPC7NW? The answer is an emphatic ‘yes'. We can treat the BPC application in the same or other appset as just another BW cube with characteristics and key figure. The only consideration is using this approach to load data from other applications in other appsets is that we have to maintain the /CPMB name space technical names of the characteristics of those dimensions in the transformation file against the dimension names of the target application and while running the package, we have to enter the /CPMB namespace technical name of the infocube pertinent to the source BPC application. An example of the transformation file used for such data transfer is shown below.

Thus we can see that we can effectively leverage the Netweaver platform to transfer the transactional data from one infocube to another using Data Manager package.