Management may have to do Audit on the data coming from / loaded into different applications. In this blog post i will share some of my experience how we did that.
Here we are pushing the FI/CO Actual data from BW Info cubes (Finance, Controlling, Internal Orders,ECCS – Consolidation & Cost Center Accounting) into BPC relevant models which are defined by the Business.
This is to confirm the data we are pushing from BW to BPC is right and so that we can explain to the Internal Audit team if something goes wrong at the data perspective any point of time.AsActual data plays an important role in the BPC side (Budgeting, Forecasting &Planning) and to do various EPM reports in comparing against Actual vs forecast vs plan vs budgeting its a good practice to have a control report to make sure the input data from BW is always right when entering into BPC.
BW – 7.31 SP 09
CPMBPC – 801 SP 08
BOBJ – 4.0
- BW Info Cubes are identified which relates to the respective BPC Models
- Data strategy has been defined how to handle the actual data i.e Data Transformations,Logic & Conversions.
- Data Manager packages are created based on the Data Strategy and been loaded into BPC models.
- Business Objects – Crystal Report is required
- BW Process chain is running per the schedule
- BPC Data Manager Packages are scheduled to run adhoc.
- Crystal reports to be scheduled after the DM packages successful completion of their load into the BPC Models.
Below is the design and functional flow of the BW – BPC Control report.
The example below is taken for One Finance Cube from BW and relevant BPC Model .
- BEx query should be created with rows/columns and Key figures on top of the BW Info Cube.
- Bex Query on BW Info Cube should have the global filter , filtered to “Actual”
- BEx query on top of the BPC – Model which is /CPMB/*** Cube at the BW backend level should be created with the similar query structure of the BW InfoCube Bex query with the difference of Signed data and KF respectively.
- Bex Query on BPC should have the global filter , filtered to “Actual”
- The Dimensions and Info objects in both queries should be the same ie the info objects which feeds the actual data to the Dimensions in BPC.
- Create a Crystal report out of these two Bex to show only the difference ,when there is a mismatch between the signed data and KF with respective to the Dimensions and Info objects in both the queries. This is the reason we should have exactly the same structure in both the queries so that each dimension from BPC Model query will be linked to the Info objects in the BW infocube query.
- Create a Alert in the Crystal report and include the email id’s and schedule it .
- Crystal report should be scheduled after the DM packages schedule. May be there should be a specific schedule window when the DM’s will run on the BPC side.
- When there is a difference the Crystal report will send email to the list included in your Alert setup
These kind of reports can be developed to compare and reconcile for Dollars amounts , Tons, SKF’s which is pushed from BW to BPC Environment….
Hope this gave a brief overview of how to do Control reports/Audit reports between BW and BPC.
In a similar approach we did and automated the control reports for ECC vs BW for all of the SAP ECC/BW business areas which are playing an important role in Enterprise Analytics part of an Organization.
P.S : This option may not be useful if the full optimize is done quite frequently as the base cube will get restructured and the query redo would be needed.