on 2021 May 19 2:41 AM
Hi Friends,
Business case - data moves from one model (multiple audittrails) to another model (a single audit trail) using destination app. This existing logic works as intended. New requirement is to move data for a single auditrail of source model to the destination model, without overwriting/clearing the existing records in the destination model, something like an append. (special scenario only)
Existing logic
*XDIM_MEMBERSET CATEGORY = ACTUAL
*XDIM_MEMBERSET TIME = %curmonth%
*XDIM_MEMBERSET AUDITTRAIL = BPC_FLAT,BPC_PCT,BPC_JRNL
*XDIM_MEMBERSET RPTCURRENCY = USD, LC
*DESTINATION_APP = Finance
*WHEN CATEGORY
*IS ACTUAL
*REC(FACTOR=1, AUDITTRAIL=BPC_DISTRIBUTION)
*ENDWHEN
New logic, we just need to pass BPC_JRNL entries to finance as top-side adjustments, is there a way to append the records in the destination model instead of overwrite. With this logic, the total value in audit trail bpc_distribution, gets overwritten by bpc_jrnl.
Request clarification before answering.
In your script you are trying to aggregate AUDITTRAILs
Do it in 2 steps:
1. Aggregate AUDITTRAILs in the initial model to some member. TEMP_AUDIT
2. Copy data to Finance model with lookup:
*LOOKUP Finance
*DIM FIN:AUDITTRAIL=BPC_DISTRIBUTION
*ENDLOOKUP
*XDIM_MEMBERSET CATEGORY =ACTUAL
*XDIM_MEMBERSET TIME = %curmonth%
*XDIM_MEMBERSET AUDITTRAIL = BPC_FLAT,BPC_PCT,BPC_JRNL
*XDIM_MEMBERSET RPTCURRENCY = USD, LC
*WHEN CATEGORY
*IS *
*REC(FACTOR=1, AUDITTRAIL=TEMP_AUDIT)
*ENDWHEN
*XDIM_MEMBERSET AUDITTRAIL = TEMP_AUDIT
*DESTINATION_APP = Finance
*WHEN CATEGORY
*IS *
*REC(EXPRESSION=%VALUE%+LOOKUP(FIN), AUDITTRAIL=BPC_DISTRIBUTION)
*ENDWHEN
P.S. If BPC_DISTRIBUTION is empty in the original model you can also use it instead of of TEMP_AUDIT.
In the script you can also clear this AUDITTRAIL in initial model.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Append is a bad idea in general. You can run append only once, second run will be accumulated with previous. It's better to create another audittrail and write to it.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you Vadim for your reply.
This special script will be use very sparingly whenever there is major restatement. And specific instruction will be put on the script as well as the package for admins who have permissions to run.
As a workaround I had to use a manual file upload to the destination model with Append package. But file creation, and all the other steps that are involved with the existing script, had to replicated on the manual file (example, filling up values in the dimension, which don't exist in the source etc).
Destination model does not need a split and this data gets pushed further into Consolidation model from finance. So we dont want disturb the entire data flow.
User | Count |
---|---|
6 | |
2 | |
2 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.