Pulling data from different BPC Model
BPC Data PULL mechanism
Across Model data copy
Applies to: SAP BPC 10 NW/MS
Summary
This Article will explain how we can pull data from different model of an environment using standard script logic.
Author: Obaidullah Shaikh
Company: Alexion Business services
Created on: 28 November 2014
Author Bio
Obaidullah Shaikh is a SAP BW/BPC/IP/BO Associate manager with Alexion business services.
Table of content
Introduction
This article addresses the copying data from one model to another using Push and Pull mechanism. SAP has given a standard function called DESTINATION_APP for pushing data from one model to another model of an environment.
But we can also pull data using the same function by making some change in Data manager package.
This function will be working as it is but we will be calling this from target
model instead of source model.
Script Logic in Source Model
Create the DESTINATION_APP script logic in source model with all the required parameters. The DESTINATION_APP keyword allows you to write the results of calculations to a different application.
Here, we have two models with same dimensional structure, So we have written a simple DESTINATION_APP logic without any other key word. We are just copying data from source model to target model with the change of Category version.
DESTINATION_APP Keywords
- If destination application shares only some of the dimensions of the original application then, the missing dimensions can be dropped from the original records with the instruction:
*SKIP_DIM= {dimension name}[,{dimension name},…]
When the original application has dimension that are not found in the destination application, the SKIP_DIM keyword is mandatory.Validation cannot pass without the SKIP_DIM keyword.
Multiple dimension names can be supplied to the instruction separated either by commas or by multiple SKIP_DIMinstructions entered on separate lines.
- If the destination application has dimensions that do not exist in the original application, these can be added to the passed records,using the instruction:
*ADD_DIM {dimension name}={value}[,{dimensionname}={value},…]
Multiple dimension names and values can be supplied to the instruction separated either by commas or by multiple ADD_DIM instructions entered on separate lines.
- The keyword RENAME_DIM can be used, to change name of one or more dimensions. The syntax is:
*RENAME_DIM {dimension name}={value}[,{dimension name}={value},…]
This instruction can be used when data is to be written into an application where a dimension is named with a different ID. Multiple dimension names and values can be supplied to the instruction separated either by commas or by multiple RENAME_DIM instructions entered on separate lines.
DM Package in Target Model
Create a DM Package
Data Manager is a Business Planning and Consolidation (BPC) module that helps you move data in to BPC, as well as copy or move data within and across applications. In addition to allowing you to move and copy data, Data Manager supports mapping and complex transformations of data. It also allows us to trigger the Script logic (Calculation engine) to do any kind calculation.
Assign process chain and Task type. Click on Modify script.
In the DM Package script where you mention the script logic, you need to mention the Source Model name instead of parameter and Source model script logic.
Conclusion
Since package is created in Target model, user can only that from Target but that package will trigger the script created in Source model. That means even though system works like push mechanism but that is transparent for the end user.
Use RUNLOGIC_PH Badi to launch script from different model - it's the correct and flexible solution!
Vadim
I know that, but that is not part standard BPC functions. you need to import some TR etc.
This is simple and easy way of doing the same using standard BPC function.
RUNLOGIC Badi is used for years 🙂 With this badi you can arrange chain of scripts to be launched in different cubes!
In the described scenario, for example it's better to do the following:
1. Launch script in target with some scope as user input.
2. Clear data in target for the scope!!! REC(EXPRESSION=0)
3. Using RUNLOGIC_PH badi launch script in source to push data to target
4. Some additional calculations can be done in target after RUNLOGIC execution.
Vadim
I agree RUNLOGIC is being used for years, but it is still not a part of standard BPC functions, you need to implement that as per the How to guide.
Here, I am trying to expose the hidden BPC functions or tricks that any one can use.
I know you are aware of all these tricks 🙂 but not every BPC developer.
I have to repeat - the proposed trick is limited and not applicable in most cases:
The requirement in 99% of cases is not to simply copy data from one cube to another, but to clear the target scope before copy! If not - then in case of second execution of copy to the same data region - some records can be not overwritten by the new ones. It means that you have to execute clear and copy sequentially in the same package! And RUNLOGIC is a perfect solution. User will enter scope members only once and the script will do the rest.
Vadim
I don't understand why we need to clear data, I have implemented the same without any issue.
I can run this as many time as I want without clearing the data in the target.
BPC always overwrites on the same combination.
RUNLOGIC is to trigger any other model's script logic and that's what This document also explaining.
Test case:
1. User entered value for Account1 - 100
2. Run copy package with DESTINATION_APP script. It will copy this value to another cube.
3. User entered value for Account1 - 0 to clear the amount.
4. At some moment the Light Optimize with zero elimination was run on the source cube. Zero record for Account1 will be deleted.
5. Run copy package with DESTINATION_APP script. No record - no changes in destination cube! 100 will remain in destination - ERROR!
Vadim
We have package link that can be used to run a clear script package in target model before running DESTINATION_APP.
But this is something very rare in case Planning or forecasting whereas consolidation it is possible.
See, This is another way of doing the same thing. I have not mentioned that RUNLOGIC is wrong or its not right way 🙂 . I am giving another option that might help in some cases.
With package link don't you need to answer prompts twice?
if needed, then yes.
clearing data every time is also not good idea, because it will impact the performance or light optimization process specially when customer is doing Rolling forecast.
The scenario you mentioned will never come if process is designed properly. because After approval of process owner, data should be locked and then copied to another model.
Another thing, the light optimization should be leaving at least 2 to 3 days request uncompressed as a best pratice
If you copy data to another model only once after the data in the source cube is permanently locked then it's not an issue! But in the real life data copy can happen more then once in case of some corrections etc... And in this case the source cube scope will be unlocked, data entry corrections will be done and the second copy without clearing data can produce incorrect results.
In our system we have to report for different shareholders with different set of P&L lines. We have the main forecasting cube with the account structure for one shareholder. And we use the second cube with different accounts for the second shareholder. Data is transferred using script logic from main cube to the second cube with DESTINATION_APP and translation matrix stored in account dimension property. Very often we have to repeat copy between cubes. And I can provide many other examples...
With data clearing before copy you are 100% safe 🙂 By the way, even standard copy package within single cube supports this approach (unfortunately for the fixed list of dimension types)
Vadim
I don't understand of forecasting daily. Forecasting is a process that should be closed before reporting to higher management. once it is done and approved, nobody is going to change that number.
You may copy different lines of P&L at different time every line should get copied once they are approved. that way you can copy as many time as you want.
I am again repeating the same thing, it is another option which can also used in some scenarios.I don't understand the purpose of this debate.
Dear Vadim,
How do I setup the following? Can you provide a sample?
1. Launch script in target with some scope as user input.
2. Clear data in target for the scope!!! REC(EXPRESSION=0)
3. Using RUNLOGIC_PH badi launch script in source to push data to target
4. Some additional calculations can be done in target after RUNLOGIC execution.
I have two models: SOURCE_MODEL and TARGET_MODEL. Source model has approved plans and forecasts for TIME (2015.01 to 2015.12) and VERSION (PLAN01). I would like to clear the target model and then copy PLAN01 for 2015 from source model.
Cassio
Hi Cassio
Please, open a new discussion.
Vadim