on 04-17-2016 4:05 PM
Hi Experts,
We noted that when importing a data file it deletes the input form data for the same category entity and time.
In the NW version there's also an AuditID (Datasource) segmentation for import, that allows a user to input to forms in one AuditID and and import data to another such that input form data is not deleted. This allows the users to constantly re-import their data (after corrections) without losing their input form data.
Any ideas for a solution in the MS version?
One manual solution is to import to another category (e.g. ACTUAL2) and then create a specific copy package from ACTUAL2 to category ACTUAL where the input form data resides, copying only the specific AuditID. This creates another step for the users.
Maybe automate the above option and add to the default script a copy statement that copies the above a when auditid = INPUT (the AuditID of the data) and not affecting the input form AuditID level.
Any other ideas?
The current setup assumes that the user will only enter input form data after completing the data import. This is not always the case. There are several import iterations.
Thanks in advance
Eyal
Hi Roberto, and Arnold
Is there another option to change the import Data Manager package such that the combination of replace and clear will be CATEGORY ENTITY TIME and AUDITID?
I see a variable %CLEARDATA% in the Data Manager script.
Can we populate that or similar and obtain the desired result? If so what's the syntax?
Thanks in advance.
Eyal
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Eyal,
sorry but you have not other options, the %CLEARDATA% variable is used to send 0 or 1 for the dumpload task TASK(LOAD,CLEARDATA,%CLEARDATA%) where 0 means the "Merge data value" option and 1 means "Replace & clear" option, see please this old thread Copy Package Replace & Clear clears all data, the data will always be deleted for same entity, category and time.
You have to use a *cleardestination to restrict data deletion, this is the better choice, other possibilities *runallocation o *REC with factor=0.
Regards
Roberto
Hi,
So if that's the case, can you assist me a little further regarding modifying the CLEAR package to restrict to a specific TIME ENTITY CATEGORY and AUDITID?
Below is the script,
1) How do we limit the parameters?
2) How do we choose all members of a dimension?
3) How do we allow the users to select CATEGORY ENTITY AND TIME?
4) Where is the below inserted?
Reviewed the help after your explanation - script would include:
*CLEAR_DESTINATION
*DESTINATION AUDITID=INPUT
*DESTINATION CONSOSCOPE=G_NONE
*DESTINATION CURRENCY=LC
*DESTINATION FLOW=F99,PL99
*DESTINATION INTERCO = ALL MEMBERS?? PARENT I_ALL ??
*DESTINATION CUSTOM_DIM1= ALL MEMBERS ??
*DESTINATION CUSTOM_DIM2= ALL MEMBERS ??
DEFAULT CLEAR SCRIPT
'DEBUG(ON)
PROMPT(SELECTINPUT,%SELECTION%,,"Select the members to CLEAR",%DIMS%)
PROMPT(RADIOBUTTON,%ENABLETASK%,"Do you want to clear comments associated with data regions in BPC?",1,{"Yes","No"},{"1","0"})
PROMPT(RADIOBUTTON,%CHECKLCK%,"Select whether to check work status settings when deleting comments.",1,{"Yes, delete comments with work status settings","No, do no delete comments with work status settings"},{"1","0"})
INFO(%TEMPFILE%,%TEMPPATH%%RANDOMFILE%)
TASK(EXPORT_ZERO,Environment,%Environment%)
TASK(EXPORT_ZERO,Model,%Model%)
TASK(EXPORT_ZERO,USER,%USER%)
TASK(EXPORT_ZERO,FILE,%TEMPFILE%)
TASK(EXPORT_ZERO,SQL,%SQLDUMP%)
TASK(EXPORT_ZERO,DATATRANSFERMODE,2)
TASK(LOAD CUBE,Environment,%Environment%)
TASK(LOAD CUBE,Model,%Model%)
TASK(LOAD CUBE,USER,%USER%)
TASK(LOAD CUBE,FILE,%TEMPFILE%)
TASK(LOAD CUBE,DATATRANSFERMODE,4)
TASK(LOAD CUBE,DMMCOPY,0)
TASK(LOAD CUBE,PKGTYPE,0)
TASK(LOAD CUBE,CHECKLCK,%CHECKLCK%)
TASK(CLEAR COMMENTS,Environment,%Environment%)
TASK(CLEAR COMMENTS,Model,%Model%)
TASK(CLEAR COMMENTS,USER,%USER%)
TASK(CLEAR COMMENTS,DATATRANSFERMODE,0)
TASK(CLEAR COMMENTS,SELECTIONORFILE,%TEMPFILE%)
TASK(CLEAR COMMENTS,ENABLETASK,%ENABLETASK%)
TASK(CLEAR COMMENTS,CHECKLCK,%CHECKLCK%)
BEGININFO(%SQLDUMP%)
select %FACTDIMS%,0 as SIGNEDDATA FROM ( SELECT %FACTDIMS%,0 as SIGNEDDATA FROM TBLFACT%Model% WHERE %SELECTION% UNION ALL SELECT %FACTDIMS%,0 as SIGNEDDATA FROM TBLFACTWB%Model% WHERE %SELECTION% UNION ALL SELECT %FACTDIMS%,0 as SIGNEDDATA FROM TBLFAC2%Model% WHERE %SELECTION%) as ZeroTable group by %FACTDIMS% OPTION(MAXDOP 1)
ENDINFO
Thank you again.
Eyal
Hi Eyal,
you can use a task that launch an .lgf file
like
DEBUG(ON)
PROMPT(SELECTINPUT,,,"Please select category, entity and time",%CATEGORY_DIM%%ENTITY_DIM%%TIME_DIM%)
TASK(Execute_formulas,USER,%USER%)
TASK(Execute_formulas,APPSET,%APPSET%)
TASK(Execute_formulas,APP,%APP%)
TASK(Execute_formulas,SELECTION,%SELECTIONFILE%)
TASK(Execute_formulas,LOGICFILE,%APPPATH%\..\AdminApp\%APP%\MyScript.LGF)
TASK(Execute_formulas,RUNMODE,1)
TASK(Execute_formulas,LOGICMODE,1)
in the MyScript.LGF you can ad
*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%
*XDIM_MEMBERSET TIME=%TIME_SET%
*XDIM_MEMBERSET ENTITY=%ENTITY_SET%
*INCLUDE CLEARDESTINATION.LGF (INPUT,...,...) // add all other parameters here
--
and in the CLEARDESTINATION.LGF file
something like
*SELECT(%ACCOUNTS%,"ID","ACCOUNT","your select condition")
*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%
*XDIM_MEMBERSET TIME=%TIME_SET%
*XDIM_MEMBERSET ENTITY=%ENTITY_SET%
*XDIM_MEMBERSET ACTIVITY=<ALL> // all members selected
*XDIM_MEMBERSET AUDITID=%P1% // first parameter, same for other dims for second param as %P2%, etc.
*XDIM_MEMBERSET ACCOUNTCM=%ACCOUNTS% // extraction by select
// it is also possible to scope using MDX (see help)
*CLEAR_DESTINATION
*DESTINATION ENTITY=%ENTITY_SET%
*DESTINATION TIME=%TIME_SET%
*DESTINATION CATEGORY=%CATEGORY_SET%
*DESTINATION AUDITID=%P1%
*DESTINATION ACTIVITY=<ALL>
*DESTINATION ACCOUNTCM=%ACCOUNTS%
*WHEN AUDITID
*IS %P1%
*REC(AUDITID="%P1%",FACTOR=0)
*ENDWHEN
*COMMIT
You can use this script if you have several scripts that should be cleared with same scope except params.
Regards
Roberto
Hi Roberto,
Thanks for the guidance.
I setup and tested and ran into some issues.
Below please find the DM and 2 scripts I used:
Receiving a message right after executing the DM package “Selection scope is not defined”.
Steps:
I made a copy of the clear.dtsx Data Manager package used the scripts and received the message.
Seems like the issue is passing a parameter via the DM package so I modified the Data Manager scripts - marked in blue
B) Please see the Script Logic below.
C) Another step I performed was to create one 1 script logic containing both of both selection and clear destination and hard coded the account dimension value as a troubleshooting step. Again, looks like the script didn’t even make it that far.
Again, appreciate your help….
Thanks
Eyal
Result of the DM package:
Total Step: 3
Export_Zero: completed in 0 sec
Load Cube: completed in 0 sec
Clear Comments: Failed in 0 seconds
Clear: completed in 0 sec
[Selection]
--------------------------------------------------------------
(Member selection)
Category: ACTUAL
Entity: 1000
Time: 2014.DEC
ENABLETASK = Yes
CHECKLCK = Yes
[Message]
--------------------------------------------------------------
Selection scope is not defined
Looks like the Clear comments is failing – so I added back the marked DM lines noted below
2 DM Package
DEBUG(ON)
PROMPT(SELECTINPUT,,,"Please select category, entity and time",%CATEGORY_DIM%%ENTITY_DIM%%TIME_DIM%)
‘ Comment: Added back because the DM was failing on the clear comments section
PROMPT(RADIOBUTTON,%ENABLETASK%,"Do you want to clear comments associated with data regions in BPC?",1,{"Yes","No"},{"1","0"})
‘ Comment: Added back because the DM was failing on the clear comments section
PROMPT(RADIOBUTTON,%CHECKLCK%,"Select whether to check work status settings when deleting comments.",1,{"Yes, delete comments with work status settings","No, do no delete comments with work status settings"},{"1","0"})
TASK(Execute_formulas,USER,%USER%)
TASK(Execute_formulas,APPSET,%APPSET%)
TASK(Execute_formulas,APP,%APP%)
TASK(Execute_formulas,SELECTION,%SELECTIONFILE%)
TASK(Execute_formulas,LOGICFILE,%APPPATH%\..\AdminApp\%APP%\Clear_input_MD.LGF)
TASK(Execute_formulas,RUNMODE,1)
TASK(Execute_formulas,LOGICMODE,1)
‘ Comment: Added back because the DM was failing on the clear comments section
TASK(CLEAR COMMENTS,ENABLETASK,%ENABLETASK%)
‘ Comment: Added back because the DM was failing on the clear comments section
TASK(CLEAR COMMENTS,CHECKLCK,%CHECKLCK%)
Scripts:
// PART 1 SELECTION FOR CLEAR BASED ON DATA MANAGER PACKAGE SELECTION
// CLEAR_INPUT_MD.LGF
*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%
*XDIM_MEMBERSET TIME=%TIME_SET%
*XDIM_MEMBERSET ENTITY=%ENTITY_SET%
*INCLUDE CLEARDESTINATION_MD.LGF (INPUT,G_NONE,LC) ) //add all other parameters here
Script #2
// CLEARDESTINATION_MD.LGF
//*SELECT(%ACCOUNTS%,"ID","ACCOUNT","your select condition")
*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%
*XDIM_MEMBERSET ENTITY=%ENTITY_SET%
*XDIM_MEMBERSET TIME=%TIME_SET%
*XDIM_MEMBERSET AUDITID=%P1%
*XDIM_MEMBERSET CONSOSCOPE=%P2%
*XDIM_MEMBERSET CURRENCY=%P3%
*XDIM_MEMBERSET FLOW =<ALL>
*XDIM_MEMBERSET INTERCO =<ALL>
*XDIM_MEMBERSET MIGZAR =<ALL>
*XDIM_MEMBERSET TC =<ALL>
*SELECT(%ACCOUNTCM%,"ID","ACCOUNT","[FINANCIAL_ACCT] = 'Y'")
//*XDIM_MEMBERSET ACCOUNT = 411_01 - for test purposes
// first parameter, same for other dims for second param as %P2%, etc.
//*XDIM_MEMBERSET ACCOUNTCM=%ACCOUNTS% // extraction by select
*CLEAR_DESTINATION
*DESTINATION CATEGORY=%CATEGORY_SET%
*DESTINATION ENTITY=%ENTITY_SET%
*DESTINATION TIME=%TIME_SET%
*DESTINATION AUDITID=%P1%
*DESTINATION CONSOSCOPE=%P2%
*DESTINATION CURRENCY=%P3%
*DESTINATION FLOW =<ALL>
*DESTINATION INTERCO=<ALL>
*DESTINATION MIGZAR=<ALL>
*DESTINATION TC=<ALL>
*DESTINATION ACCOUNT=%ACCOUNTCM%
*WHEN AUDITID
*IS %P1%
*WHEN CONSOSCOPE
*IS %P2%
*WHEN CURRENCY
*IS %P3%
*REC(AUDITID="%P1%",CONSOSCOPE="%P2%",CURRENCY="%P3%",FACTOR=0)
*ENDWHEN
*ENDWHEN
*ENDWHEN
*COMMIT
Hi,
One more iteration -- Added the hard code for clear comments and work status- task ran successfully but each step ran in 0 seconds and there were was no affect.
TASK(CLEAR COMMENTS,ENABLETASK,%ENABLETASK%,1)
TASK(CLEAR COMMENTS,CHECKLCK,%CHECKLCK%,1)
Any help would be greatly appreciated.
Regards,
Eyal
Hi Eyal,
try first without clearcomment, this should work
DEBUG(ON)
PROMPT(SELECTINPUT,,,"Please select category, entity and time",%CATEGORY_DIM%%ENTITY_DIM%%TIME_DIM%)
TASK(Execute_formulas,USER,%USER%)
TASK(Execute_formulas,APPSET,%APPSET%)
TASK(Execute_formulas,APP,%APP%)
TASK(Execute_formulas,SELECTION,%SELECTIONFILE%)
TASK(Execute_formulas,LOGICFILE,%APPPATH%\..\AdminApp\%APP%\Clear_input_MD.LGF)
TASK(Execute_formulas,RUNMODE,1)
TASK(Execute_formulas,LOGICMODE,1)
Scripts:
// PART 1 SELECTION FOR CLEAR BASED ON DATA MANAGER PACKAGE SELECTION
// CLEAR_INPUT_MD.LGF
*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%
*XDIM_MEMBERSET TIME=%TIME_SET%
*XDIM_MEMBERSET ENTITY=%ENTITY_SET%
*INCLUDE CLEARDESTINATION_MD.LGF (INPUT,G_NONE,LC) ) //add all other parameters here
Script #2
// CLEARDESTINATION_MD.LGF
*SELECT(%ACCOUNTCM%,"ID","ACCOUNT","[FINANCIAL_ACCT] = 'Y'")
*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%
*XDIM_MEMBERSET ENTITY=%ENTITY_SET%
*XDIM_MEMBERSET TIME=%TIME_SET%
*XDIM_MEMBERSET AUDITID=%P1%
*XDIM_MEMBERSET CONSOSCOPE=%P2%
*XDIM_MEMBERSET CURRENCY=%P3%
*XDIM_MEMBERSET FLOW =<ALL>
*XDIM_MEMBERSET INTERCO =<ALL>
*XDIM_MEMBERSET MIGZAR =<ALL>
*XDIM_MEMBERSET TC =<ALL>
*XDIM_MEMBERSET ACCOUNT=% ACCOUNTCM %
*IGNORE_STATUS
*CLEAR_DESTINATION
*DESTINATION CATEGORY=%CATEGORY_SET%
*DESTINATION ENTITY=%ENTITY_SET%
*DESTINATION TIME=%TIME_SET%
*DESTINATION AUDITID=%P1%
*DESTINATION CONSOSCOPE=%P2%
*DESTINATION CURRENCY=%P3%
*DESTINATION FLOW =<ALL>
*DESTINATION INTERCO=<ALL>
*DESTINATION MIGZAR=<ALL>
*DESTINATION TC=<ALL>
*DESTINATION ACCOUNT=%ACCOUNTCM%
*WHEN AUDITID
*IS %P1%
*WHEN CONSOSCOPE
*IS %P2%
*WHEN CURRENCY
*IS %P3%
*REC(AUDITID="%P1%",CONSOSCOPE="%P2%",CURRENCY="%P3%",FACTOR=0)
*ENDWHEN
*ENDWHEN
*ENDWHEN
*COMMIT
Regards
Roberto
Hi Roberto,
I found a simpler solution: https://scn.sap.com/thread/748632
This allows us to continue using the Import DM package without having multiple tasks.
Thank you for your ongoing help.
Regards
Eyal
Hi Eyal,
yes your right the clear option inside the package deletes all the data for the same category entity and time.
If you need to filter better the data to delete as Arnold wrote you can add a package that contains a *CLEAR_DESTINATION restricted with your definded scope.
Regards
Roberto
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Eyal,
For one of our clients we have created a specific clear package, that the user can run prior to a re-import. It is set to only clear imported data (specific datasource). The import then uses the add/replace rather than the clear option.
You could even add these two into a package link to be run together (one after the other).
BR,
Arnold
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
14 | |
4 | |
2 | |
1 | |
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.