Executing a stored procedure using custom BPC task

[Former Member]

Hi,

I have written a script logic. its is validated and saved to create the LGX file. When i test this logic from logic debugger, it is working fine. but, when i create a data manager package with Logic task, it is failing (runs infinitely). I have included the modifyscript variable to enable run time assignment. In the advanced tab of the data manager package in modify mode, i have specified the the following settings.

PROMPT(SELECTINPUT,%SELECTION%,,"Select the members to INSERT","ACCOUNT,CATEGORY,ENTITY")

TASK(lt,LogicMode,1)

TASK(lt,LogicFile,SP_TEST_BPC.LGX)

TASK(lt,RUNMODE,1)

'TASK(lt,Selection,%SELECTION%)

TASK(lt,FORMULASCRIPT,"*RUN_STORED_PROCEDURE=SP_TESTBPC('%ACCOUNT_SET%', '%CATEGORY_SET%','%ENTITY_SET%')")

.LGX contains

*RUN_STORED_PROCEDURE=SP_TESTBPC('%ACCOUNT_SET%','%CATEGORY_SET%','%ENTITY_SET%')

i used TASK(lt,LogicFile,SP_TEST_BPC.LGF) also and tried, this also fails.(runs infinitely)

Need help on this issue

Regards,

Vivek S

[Former Member]

Hi,

The problem is with your advanced logic.

You can refer to the default DM package. Use the same advanced logic. Your advanced logic should point to your script logic only and not to your stored procedure. Your script logic will invoke the stored procedure.

Hope this helps.

[Former Member]

Hi Nilanjan,

I am calling the .LGX file in the script. not the stored procedure. the formulascript line is commented.

Regards,

Vivek

[Former Member]

Hi,

Now, the package is getting executed successfully but it takes around 40 mins to do it. in the log file it says

"Time to run stored procedure:.1 sec.

call 1 completed and data posted in 2,124.8 sec.

Run completed in 2,124.9 sec."

The stored proc inserts 1 row into the respective table.

I do not understand what it is doing for 2124 secs when it is executing the SP in 1 sec.

Script details used in the data manager task:

PROMPT(SELECTINPUT,,,,%ACCOUNT_DIM%%CATEGORY_DIM%%ENTITY_DIM%)

TASK(lt,USER,%USER%)

TASK(lt,APPSET,%APPSET%)

TASK(lt,APP,%APP%)

TASK(lt,SELECTION,%SELECTIONFILE%)

TASK(lt,LogicMode,1)

TASK(lt,LogicFile,SP_TEST_BPC.LGF)

TASK(lt,RUNMODE,1)

Any help to solve this is much appreciable.

Regards,

Vivek

[radharamansingh_rajkumar]

Hi Vivek,

Just limit the dimension member in the package which can be hardcoded instead giving all the dimension member.

Perfomance will increase considerably.

Regards

Raman

[Former Member]

Hi,

In your advanced script above, some other line has been commented.

I would suggest you to copy the advanced script of default DM package and just change the script file name. Please refer to the default DM package.

Hope this helps.

[Former Member]

Hi,

when executing the pkg, it will prompt to select the account, category and entity. i am choosing only one value in each. so it is only 1 set of values passed to the stored procedure. Do i still have to restrict members explictitly in the pkg.

Regards

Vivek

[Former Member]

.

Edited by: Vivek_s90 on Jun 8, 2010 10:55 AM

[Former Member]

.

Edited by: Vivek_s90 on Jun 8, 2010 10:55 AM

[Former Member]

.

Edited by: Vivek_s90 on Jun 8, 2010 10:54 AM

[Former Member]

Hi Nilanjan,

In my latest reply, i have put the script used. i have taken it from the sample pkg - execute formulas.

regards,

Vivek

[Former Member]

Hi,

What I noticed was the scope of your script logic. Right now, the script is running for all the records in the fact tables. Try to reduce the scope. You have prompt statements for 3 dimensions. Use them in the script as well.

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

.

.

.

Like the above statement, restrict the other 2 dimensions also.

Hope this helps.

[Former Member]

Hi,

In continuation to my testing, what i found was, when i include material dim (Having 50000 members) in the prompt and select 1 material and execute the pkg, it executes in 3 secs.

It seems even if we want to work on data with account, entity and category as filters, it starts validating all the dimension of the application irrespective of the fact that it is necessary for the logic.

is this a limitation to specify some default values to all the dimensions of the appln even if they are not needed for the stored procedure to run(even if SP is not dependant on those dims).

Regards,

Vivek S

[Former Member]

Hi Vivek,

The execution depends on the scope of the logic. If there is no scope defined, then it will run for all the members. So, its important to define the scope if any (you can use the xdim_memberset for defining the scope).

Hope this helps.

[Former Member]

Hi Nilanjan,

I specified *XDIM_MEMBERSET PRODUCT=<Value> in the .LGF file before the *Run_STOREDPROCEDURE script. But still it is taking around 40 mins.

I also tried *XDIM_MAXMEMBERS PRODUCT=1000

tried with *XDIM_MEMBERSET PRODUCT=<Value>

tried with *SCOPE_BY ACCOUNT,CATEGORY,ENTITY

*LOGIC_BY ACCOUNT,CATEGORY,ENTITY

*LOGIC_MODE=2

still no improvement

Regards,

Vivek S

[Former Member]

Hi,

Issue resolved

Regards,

Vivek

[Former Member]

Hi,

Issue resolved

Regards,

Vivek