Skip to Content

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.

image

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.

image

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:

image

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:

 image

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:

image

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:

image

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:

image 

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:

image

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:

image

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:

image

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:

image

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.

image

 

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.

To report this post you need to login first.

23 Comments

You must be Logged on to comment or reply to a post.

  1. sanjay sachithanandham
    Wow can’t wait to see this product in market,

       Amazing blog pravin. You almost covered all the common possible scenario in data load. I am wondering how other keyfigures values like Quantity and price are handled in single Keyfigure model.Do we have to follow Account based model by creating another dimension called KFY and have member value Qty to store quantity and PRC for storin Price.

    Thanks,
    sanjay.

    (0) 
    1. Kavyashree B K
      Hi sanjay,

      The key figure scenario can be solved by creating another staging BW info provider with single KF struture. transformation can be created using Rule groups to acheive this to laod the data to staging BW info provider.and data can be loaded from staging BW info provider using  data manager wihtout any transformation needed.

      (0) 
  2. LOKESH NANDULA
    Excellent blog Pravin. Thanks.

    Some tips on how to handle the concept of characteristic relationships, data slices and Multiprovider in BPC 7.0NW will be very useful.

    regards,
    Lokesh Nandula

    (0) 
  3. Tintu K S
    Hi ,
    This is an excellent blog explaining all the benefits and limitations of each method. But I have few queries.
    Firstly in the blog for running BPC process chains within custome process chain, we have used a XML transformation file. While here we have used a transformation and a conversion file. Why is this different. And how do we specify the conversion in case of custome process chain method, as we are using XML file.

    Secondly, To load data directly from BW InfoProvider to BPC Cube, the last method explained how can we automate this using process chain. Can this also be included in custom chains.

    Any help would be greately appreciated.

    Regards,
    Tintu

    (0) 
  4. Kavyashree B K
    Hi Pravin,

    I want to know that, while loading data from BW info provider to BPC using data manager, does the structure of BW info provider should match the BPC cube struture? does BW info provider need to have all info objects of length 20 and Char datatype?

    (0) 
    1. Pravin Datar Post author
      Hi Kavyashree,
      The structure need not match but you need to use conversion files to convert the source data to 20 char dimension members in BPC.
      (0) 
  5. Albert Mas
    Hi Pravin,

    I think this an excellent and interesting blog. I have some doubts about the third method.

    When I design a transformation file with more than one Round, when you validate or execute it, it only loads data belonging to the last Round, not all the Rounds. How can I load all data belonging to all Rounds? In this way, it would allow me to reduce the number of the Transformation Files.

    On the other hand, could you explain, if it is possible, an example about how to automate loading data from BW InfoProvider to BPC Cube, creating an only custom process chain, that works with multiple Transformation files. It would allow me to load data from several transformation files with an only package.

    Thanks in advance,
    Albert

    (0) 
  6. muge turnacigil
    Hi Pravin,
    Thank you for this excellent blog. I followed your instructions and loaded data succesfully. At the end of the process , the data manager log detail seems like that:
    Task name Append Load:
    Reject Count: 0
    Submit Count: 35473
    Application: Inspection. Package Status: SUCCESS
    But i am not able to bring data to my reports and also i can not see loaded data on my BPC cube directly. Is there another step for activating data or something like that? Thanks.

    Regards,
    Muge.  

    (0) 
    1. Pravin Datar Post author
      Hi Muge,
      There is no step to activate the data. The data should be availalble immediately. One way out to recheck the data is to view the data in the infocube (through the BW way – transaction listcube)
      Regards
      Pravin
      (0) 
  7. Anand Kumar
    Hello Pravin,

    You have covered all the points. I have one doubt :

    I have understood from the blog that we can load only full load.

    Please let me know how to load delta through the Data Management Pakage.

    Thanks.

    With regards,

    Anand Kumar

    (0) 
    1. Pravin Datar Post author
      Hi Anand,
      At present it is full load. For delta load, you can have a DSO before you load the records into BPc and every time after loading into BPC you can clear the DSO. Please note that this is a BW solution/work around to handle deltas and not a BPC solution.
      (0) 
  8. GUILLAUME PETIAUX
    Excellent job Pravin,
    I’m really interested in the third method, but I can’t find the package “LoadData” (load data from infoprovider) in the list available. Did you made something special to make it available ?
    (on my side the closer package available seems to be “LoadInfoProvider” and it needs to be “called” from the target cube (in current view), and since it’s not a BPC cube, it’s not available in the list)
    Thanks in advance for your help.

    Guillaume P.

    (0) 
  9. Vitor Ramalho
    Very Nice Blog Praven.

    I have a question regarding data transfer from BPC NW 7.5 to BW cube.
    Wich choices I have to do this? Do I have to use files?

    Best regards,
    Vitor Ramalho

    (0) 
    1. Pravin Datar Post author
      Thanks Vitor. You have a couple of choices here. Either you can export the data as a flat file as you said and then load it to BW cube or export using a badi to BW cube.
      (0) 
  10. Victor Figueroa
    Hi, i try load data from 0PROFIT_CTR to BPC reading PDF “How To Import Master Data and Hierarchies into BPC 7.5 from SAP NW BW”.

    Then use this assing:

    *MAPPING
    ID=ID
    RESPONSABLE=0RESP_USER
    BPC=*IF(ID(2:2)=*STR(I) then *STR(I);*IF(ID(2:2)=*STR(D) AND ID(4:4)=*STR(Z) then *STR();*STR(D)))
    PAIS=*IF(ID(1:1)=*STR(C) then *STR(CHILE);*STR(PERU))

    In Validate get Error for Field BPC : The number in parentheses is incorrect, check your formula

    *IF(ID(2:2)=*STR(I) then *STR(I);*IF(ID(2:2)=*STR(D) AND ID(4:4)=*STR(Z) then *STR();*STR(D)))

    This line working good :

    PAIS=*IF(ID(1:1)=*STR(C) then *STR(CHILE);*STR(PERU))

    Examples value for 0PROFIT_CTR is :

    CD1Z0101
    CD101C01
    PI1CA001
    CI1CA001

    I look sintaxis and is good number in parentheses.

    I test this :

    BPC=*IF(ID(2:2)=*STR(D) AND ID(4:4)=*STR(Z) then *STR();*STR(D))

    And GET error :

    *IF formula is not valid, check your formula

    Maybe problem is in “AND” stament ?

    (0) 
  11. Brian Schwind
    Pravin,
    Very nice article. Is there any way that we can make the Transformation File dynamically change the SELECTION criteria without user input?
    For example, say we only want to load the previous months data using the using the source of the TIME dimension from BW, could we make the selection “smart” enough to automatically do this?  Or would we have to resort to either a manual change of the Transformation file or use of a Delta in BW?
    (0) 

Leave a Reply