Introduction:
This blog covers Audit log tracking in BPC system in a very innovative way. Its relevant for SAP BPC Embedded 10.1 built on SAP BW on HANA.
Basic knowledge of BW IP, HANA and programming is required to understand the concept.
Audit log information is very crucial when we talk about planning system. Its highly necessary to identify what values are changed/added by whom and when. its required for end users, business super users to monitor those changes. In any project there can be a requirement to track the audit log. Current blog will help the BPC developer to build such kind of solution.
in earlier release (BPC 10.0) auditing can be enabled for a model. The model creates internally infocube in BW backend. But in BPC embedded model is linked to BW ADSO (planning enabled). Standard way of auditing is not applicable. So here comes the solution which I have explained below.
Main Content:
Problem Statement:
In the below scenario budget planning data gets saved to the ADSO through AO input sheet by the business user. There is possibility that same data can be amended/modified by another user through input sheet. Also for some regions user sends the file to IT team which is loaded through DTP to the same ADSO.
From auditing point of view user needs to track the changes based on
- Data added/modified using AO input sheet
- Data loaded through DTP from the file
User requirement is to have a common solution which can track above both combining and display in a report with all delta changes.
In standard SAP there is no provision to enable auditing combining both of the cases where BPC model is built on planning enabled ADSO and BW IP is used as planning solution.
Solution:
I have explained below the process how to handle this situation.
Note: there is no changes required for the planning ADSO also there is no additional dimension needs to be added. No need to have separate audit ADSO.
This solution Its based on the Request TSN (REQTSN). For every change there is a request number created. That can be used to identify the changes sequentially (with delta changes for keyfigures). its applicable for both planning data added through input sheet as well as for data loads.
Design
Create a new Z table containing below fields:
- ADSO name
- Request Number
- Username
- Date
- Time
Make ADSO name and REQTSN as the keys as shown below.
I have explained below both the scenarios how to capture the information.
Step 1 (Capture the changes from AO input sheet)
- When planning data is saved from input AO sheet, there is a standard code that gets executed before planned buffer is saved to ADSO.
- I have identified the place where I can capture the request ID getting saved to the ADSO.
- I created the enhancement spot(with enhancement framework) to plug-in my code into SAP standard.
- I captured the userid who is running the AO report and triggering the change to planning buffer.
- Identified the place where I can capture the cube name which is getting updated in the request.
- Finally stored the relationship between all to new Z table including current date and time. Used the conversion exit to convert into correct time-time (UTC+8)
- the detailed design is explained in the below picture.
Step 2 (Capture the changes from the data load)
- When the data is getting loaded through DTP, there is a set of code which gets executed before loading the records to ADSO.
- I identified the place where I can capture the information about the ADSO and request number.
- updated the information to new Z table created before together with current date and timestamp.
- I have enabled only the for the required planning ADSO. That logic is put in the code. The reason is there may be so many ADSO getting through data load in the system and we need only for our planning ADSO.
- shown below the enhancement spot and logic.
Step 3 (Reporting on the audit log)
- The hana table for the new Z table is available under SAPABAP1 schema.
- I created a SQL view containing above view with tables (USR21, ADRP, ADR6) to capture user info (e.g. username, email, etc.)
- I have defined the ADSO name as input parameter(I_ADSO) for the SQL view. So that it can be used for any ADSO where we need this mechanism.
/********* Begin Procedure Script ************/
BEGIN
var_out = select M.ADSONM, M.REQTSN, M.UNAME,
M.DATUM, M.UZEIT, P.NAME_TEXT,
R.SMTP_ADDR, M.REQTSN AS REQTSN_N
FROM "SAPABAP1".<table name> AS M
LEFT OUTER JOIN "SAPABAP1".USR21 AS U
ON M.UNAME = U.BNAME
LEFT OUTER JOIN "SAPABAP1".ADRP AS P
ON U.PERSNUMBER = P.PERSNUMBER
LEFT OUTER JOIN "SAPABAP1".ADR6 AS R
ON U.PERSNUMBER = R.PERSNUMBER
AND U.ADDRNUMBER = R.ADDRNUMBER
where m.ADSONM = I_ADSO;
END /********* End Procedure Script ************/- Created a hana calculation view containing above SQL view and hana view of planning ADSO.
- Created join between these two based on REQTSN.
- So system can easily identify user and timestamp for the record which got updated in the planning ADSO.
- Here the input parameter for ADSO name also is passed.
- Attached the above hana view to composite provider.
- Created BW query on the composite provider. Kept all ADSO characteristics in free characteristics.
- Kept all the necessary fields in selection which will be used.
- Created AO sheet over the BW query.
- It was able to display the content with all changes to the key figure values together with username, date and timestamp.
Conclusion:
Now you just learnt how to create a solution in BPC embedded to track the audit log changes based on request number concept. You can find the benefits of this as mentioned below.
- This report can allow the display at any granularity based on the fields selected from planning ADSO. Even at each request number level
- You can easily track the changes done by data load as well as through input sheet for the same set of characteristics.
- all the key figure changes(Delta) is displayed with respect to each and every changes performed by user.
Looking forward for reading your comments. I hope you will find this blog useful.
- SAP Managed Tags:
3 Comments
