Import File Deletes the Input Form Data

[Eyal_Feiler]

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

[former_member234894 · Accepted solution]

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

[former_member186498 · Accepted solution]

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

[former_member234894 · Accepted solution]

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

[former_member186498 · Accepted solution]

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

[former_member234894 · Accepted solution]

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

1. 1 DM Result:

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

[former_member234894 · Accepted solution]

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

[former_member186498 · Accepted solution]

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

[Eyal_Feiler · Accepted solution]

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

[former_member186498]

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

[Eyal_Feiler]

Thanks Roberto and Arnold.

Thank you for the quick response.  

I was discussing the point with a colleagure this morning.

This is what we will setup.

Will keep you posted.

Regards,

Eyal

[Former Member]

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