30270: An internal error occurred while calling the 'processDPCommands' API

[Former Member]

Hellow there,

I have a WebI report that is currently working fine. However, when I add two new measure objects to it, the report doesn't run. If I try to schedule the report, I get the following message:

30270: An internal error occurred while calling the 'processDPCommands' API. (Error: WIS 30270) BOException

When I try to build the report from scratch and run the report query within Webi, the report doesn't show the results and all I can see in the message statue pane at the bottom of the page is the following message: "Retrieving data".

If I copy the SQL of the query before running it in WebI and run it in TOAD, the query takes about 37-38 seconds to run.

I am using Business Objects Enterprise XI Release 2. The report is accessing Oracle database release 10.2.0.3.0.

Can you please help?

Thanks,

Alaa

[Former Member · Accepted solution]

I wonder if this is Universe related, since that is one difference between running the SQL in Toad vs. through webi.

Try clearing your document and universe caches. Caches are located in your filesystem somewhere around:

C:\Program Files\Business Objects\BusinessObjects Enterprise 11.5\Data\[SYSTEM]\storage

(this varies depending on how you deployed your system and what OS you're running). Rename the storage directory to storage.bak and create a new 'storage' in it's place.

Does your report run fine without the two extra measure objects? Can you describe the measures in more detail? (are they calculated from other measures? or are the values pulled directly out of the database?)

[Former Member · Accepted solution]

Thank you Tony for your reply. I renamed the storage folder and created a new one in its place but that didn't help. I still receive the same error.

Those new measure objects are based on the database directly. Here is their syntax:

Measure Object1: NVL(SUM(Z_ROME_COLL_REQ_FACT_BY_ECP_VW.INBOUND_REQ_AMOUNT), 0)

Measure Object2: NVL(SUM(Z_ROME_COLL_REQ_FACT_BY_ECP_VW.OUTBOUND_PROCESS_AMOUNT), 0)

Here is the generated SQL for the query that doesn't include the new measure objects:

SELECT

ROME_COUNTERPARTY_SUMMARY_VW.COUNTERPARTY_NAME,

SUM(Z_ROME_COUNTERPARTY_EXP_VW.TOTAL_EXPOSURE * Z_ROME_EXCHANGE_RATE_FACT_VW.EXCHANGE_RATE),

SUM(Z_ROME_COUNTERPARTY_EXP_VW.C4 * Z_ROME_EXCHANGE_RATE_FACT_VW.EXCHANGE_RATE),

SUM(Z_ROME_COUNTERPARTY_EXP_VW.C7 * Z_ROME_EXCHANGE_RATE_FACT_VW.EXCHANGE_RATE),

SUM(Z_ROME_COUNTERPARTY_EXP_VW.UNSECURED_EXPOSURE * Z_ROME_EXCHANGE_RATE_FACT_VW.EXCHANGE_RATE),

SUM(Z_ROME_COUNTERPARTY_EXP_VW.LOW_TOTAL_EXPOSURE * Z_ROME_EXCHANGE_RATE_FACT_VW.EXCHANGE_RATE),

SUM((NVL(Z_ROME_COUNTERPARTY_EXP_VW.COLLATERAL_CASH_HELD, 0) +

NVL(Z_ROME_COUNTERPARTY_EXP_VW.COLLATERAL_LOC_HELD, 0) +

NVL(Z_ROME_COUNTERPARTY_EXP_VW.COLLATERAL_OTHER_HELD, 0)) * Z_ROME_EXCHANGE_RATE_FACT_VW.EXCHANGE_RATE),

MAX(Z_ROME_LIMIT_EVENT_VW.LIMIT_AMOUNT_CAD),

MAX(CONCAT (Z_ROME_LIMIT_SUMMARY_VWCPT.LIMIT_AMOUNT, LOWER (SUBSTR (Z_ROME_LIMIT_SUMMARY_VWCPT.CURRENCY, 1, 1)))),

Z_ROME_TIME_PERIOD_SUMMARY_VW.TIME_PERIOD_ID,

Z_ROME_TIME_PERIOD_SUMMARY_VW.TIME_PERIOD,

Concat('Report Currency: ', Z_ROME_EXCHANGE_RATE_FACT_VW.CURRENCY),

CONCAT('Create Date: ', TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH:MI AM')),

COUNTERPARTY_RATING.RATING_NEXEN,

ROME_BOT_CHILD_RELATIONS_VW.CHILD_BOT_PATH,

CONCAT('Report Date: ', TO_CHAR(Z_ROME_EVENT_SUMMARY_VW.AS_OF_DATE, 'DD-Mon-YYYY')),

Z_ROME_LIMIT_EVENT_VW.LIMIT_AMOUNT_ID

FROM

ROME_COUNTERPARTY_SUMMARY_VW,

Z_ROME_COUNTERPARTY_EXP_VW,

Z_ROME_EXCHANGE_RATE_FACT_VW,

Z_ROME_LIMIT_EVENT_VW,

Z_ROME_LIMIT_SUMMARY_VW Z_ROME_LIMIT_SUMMARY_VWCPT,

Z_ROME_TIME_PERIOD_SUMMARY_VW,

Z_ROME_EVENT_SUMMARY_VW,

(

SELECT R.COUNTERPARTY_ID AS COUNTERPARTY_ID,

MAX (CASE

WHEN R.RATING_NAME LIKE 'S%'

THEN R.RATING_VALUE

ELSE NULL

END) AS RATING_S_AND_P,

MAX (CASE

WHEN R.RATING_NAME LIKE 'M%'

THEN R.RATING_VALUE

ELSE NULL

END) AS RATING_MOODYS,

MAX (CASE

WHEN R.RATING_NAME LIKE 'D%'

THEN R.RATING_VALUE

ELSE NULL

END) RATING_DBRS,

MAX (CASE

WHEN R.RATING_NAME LIKE 'N%'

THEN R.RATING_VALUE

ELSE NULL

END) AS RATING_NEXEN

FROM Z_ROME_RATING_SUMMARY_VW R

WHERE R.LAST_UPDATE = 1

GROUP BY R.COUNTERPARTY_ID

) COUNTERPARTY_RATING,

ROME_BOT_CHILD_RELATIONS_VW,

Z_ROME_CALC_METHOD_SUMMARY_VW,

Z_ROME_SCENARIO_SUMMARY_VW,

Z_ROME_EXP_PERSPECT_SUMMARY_VW,

Z_ROME_ENTITY_TYPE_VW

WHERE

( Z_ROME_COUNTERPARTY_EXP_VW.COUNTERPARTY_ID=ROME_COUNTERPARTY_SUMMARY_VW.COUNTERPARTY_ID )

AND ( Z_ROME_COUNTERPARTY_EXP_VW.CALC_METHOD_ID=Z_ROME_CALC_METHOD_SUMMARY_VW.CALC_METHOD_ID )

AND ( Z_ROME_COUNTERPARTY_EXP_VW.EXPOSURE_PERSPECTIVE_ID=Z_ROME_EXP_PERSPECT_SUMMARY_VW.EXPOSURE_PERSPECTIVE_ID )

AND ( Z_ROME_COUNTERPARTY_EXP_VW.SCENARIO_ID=Z_ROME_SCENARIO_SUMMARY_VW.SCENARIO_ID )

AND ( Z_ROME_COUNTERPARTY_EXP_VW.PERIOD=Z_ROME_TIME_PERIOD_SUMMARY_VW.TIME_PERIOD_ID )

AND ( ROME_COUNTERPARTY_SUMMARY_VW.COUNTERPARTY_ID=COUNTERPARTY_RATING.COUNTERPARTY_ID(+) )

AND ( Z_ROME_COUNTERPARTY_EXP_VW.BOT_ID=ROME_BOT_CHILD_RELATIONS_VW.CHILD_ID )

AND ( Z_ROME_COUNTERPARTY_EXP_VW.EVENT_DIM_ID=Z_ROME_EVENT_SUMMARY_VW.EVENT_DIM_ID )

AND ( Z_ROME_EXCHANGE_RATE_FACT_VW.EVENT_DIM_ID=Z_ROME_COUNTERPARTY_EXP_VW.EVENT_DIM_ID )

AND ( Z_ROME_EXCHANGE_RATE_FACT_VW.PERIOD=Z_ROME_COUNTERPARTY_EXP_VW.PERIOD )

AND ( Z_ROME_LIMIT_EVENT_VW.COUNTERPARTY_ID(+)=Z_ROME_COUNTERPARTY_EXP_VW.COUNTERPARTY_ID )

AND ( Z_ROME_LIMIT_EVENT_VW.BOT_ID(+)=Z_ROME_COUNTERPARTY_EXP_VW.BOT_ID )

AND ( Z_ROME_LIMIT_EVENT_VW.LIMIT_ALLOCATION = 'Direct' )

AND ( ROME_COUNTERPARTY_SUMMARY_VW.COUNTERPARTY_ID=Z_ROME_ENTITY_TYPE_VW.COUNTERPARTY_ID )

AND ( Z_ROME_LIMIT_SUMMARY_VWCPT.BOT_ID(+)=Z_ROME_COUNTERPARTY_EXP_VW.BOT_ID )

AND ( Z_ROME_LIMIT_SUMMARY_VWCPT.CP_ID(+)=Z_ROME_COUNTERPARTY_EXP_VW.COUNTERPARTY_ID )

AND ( Z_ROME_LIMIT_SUMMARY_VWCPT.LIMIT_ALLOCATION = 'Term' )

AND ( ROME_COUNTERPARTY_SUMMARY_VW.CP_STATUS_ID = 4 )

AND

(

( Z_ROME_CALC_METHOD_SUMMARY_VW.NAME = 'Total 60 Day Exposure' )

AND

( Z_ROME_SCENARIO_SUMMARY_VW.SCENARIO_NAME = 'ACTUAL' )

AND

( UPPER(Z_ROME_EXCHANGE_RATE_FACT_VW.CURRENCY) = UPPER(@Prompt('Select Report Currency','A','Exchange Rate\Data Exchange Currency','MONO','CONSTRAINED')) )

AND

( Z_ROME_EXP_PERSPECT_SUMMARY_VW.NAME IN ( 'Normal', 'Reverse') )

AND

( ROME_COUNTERPARTY_SUMMARY_VW.EXTERNAL_TYPE = 'External' )

AND

( Z_ROME_ENTITY_TYPE_VW.ENTITY_TYPE = 'Counterparty' )

)

GROUP BY

ROME_COUNTERPARTY_SUMMARY_VW.COUNTERPARTY_NAME,

Z_ROME_TIME_PERIOD_SUMMARY_VW.TIME_PERIOD_ID,

Z_ROME_TIME_PERIOD_SUMMARY_VW.TIME_PERIOD,

Concat('Report Currency: ', Z_ROME_EXCHANGE_RATE_FACT_VW.CURRENCY),

CONCAT('Create Date: ', TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH:MI AM')),

COUNTERPARTY_RATING.RATING_NEXEN,

ROME_BOT_CHILD_RELATIONS_VW.CHILD_BOT_PATH,

CONCAT('Report Date: ', TO_CHAR(Z_ROME_EVENT_SUMMARY_VW.AS_OF_DATE, 'DD-Mon-YYYY')),

Z_ROME_LIMIT_EVENT_VW.LIMIT_AMOUNT_ID

Here is the generated SQL for the query that includes the new measure objects:

SELECT

ROME_COUNTERPARTY_SUMMARY_VW.COUNTERPARTY_NAME,

SUM(Z_ROME_COUNTERPARTY_EXP_VW.TOTAL_EXPOSURE * Z_ROME_EXCHANGE_RATE_FACT_VW.EXCHANGE_RATE),

SUM(Z_ROME_COUNTERPARTY_EXP_VW.C4 * Z_ROME_EXCHANGE_RATE_FACT_VW.EXCHANGE_RATE),

SUM(Z_ROME_COUNTERPARTY_EXP_VW.C7 * Z_ROME_EXCHANGE_RATE_FACT_VW.EXCHANGE_RATE),

SUM(Z_ROME_COUNTERPARTY_EXP_VW.UNSECURED_EXPOSURE * Z_ROME_EXCHANGE_RATE_FACT_VW.EXCHANGE_RATE),

SUM(Z_ROME_COUNTERPARTY_EXP_VW.LOW_TOTAL_EXPOSURE * Z_ROME_EXCHANGE_RATE_FACT_VW.EXCHANGE_RATE),

SUM((NVL(Z_ROME_COUNTERPARTY_EXP_VW.COLLATERAL_CASH_HELD, 0) +

NVL(Z_ROME_COUNTERPARTY_EXP_VW.COLLATERAL_LOC_HELD, 0) +

NVL(Z_ROME_COUNTERPARTY_EXP_VW.COLLATERAL_OTHER_HELD, 0)) * Z_ROME_EXCHANGE_RATE_FACT_VW.EXCHANGE_RATE),

MAX(Z_ROME_LIMIT_EVENT_VW.LIMIT_AMOUNT_CAD),

MAX(CONCAT (Z_ROME_LIMIT_SUMMARY_VWCPT.LIMIT_AMOUNT, LOWER (SUBSTR (Z_ROME_LIMIT_SUMMARY_VWCPT.CURRENCY, 1, 1)))),

Z_ROME_TIME_PERIOD_SUMMARY_VW.TIME_PERIOD_ID,

Z_ROME_TIME_PERIOD_SUMMARY_VW.TIME_PERIOD,

Concat('Report Currency: ', Z_ROME_EXCHANGE_RATE_FACT_VW.CURRENCY),

CONCAT('Create Date: ', TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH:MI AM')),

COUNTERPARTY_RATING.RATING_NEXEN,

ROME_BOT_CHILD_RELATIONS_VW.CHILD_BOT_PATH,

CONCAT('Report Date: ', TO_CHAR(Z_ROME_EVENT_SUMMARY_VW.AS_OF_DATE, 'DD-Mon-YYYY')),

Z_ROME_LIMIT_EVENT_VW.LIMIT_AMOUNT_ID,

NVL(SUM(Z_ROME_COLL_REQ_FACT_BY_ECP_VW.INBOUND_REQ_AMOUNT), 0),

NVL(SUM(Z_ROME_COLL_REQ_FACT_BY_ECP_VW.OUTBOUND_PROCESS_AMOUNT), 0)

FROM

ROME_COUNTERPARTY_SUMMARY_VW,

Z_ROME_COUNTERPARTY_EXP_VW,

Z_ROME_EXCHANGE_RATE_FACT_VW,

Z_ROME_LIMIT_EVENT_VW,

Z_ROME_LIMIT_SUMMARY_VW Z_ROME_LIMIT_SUMMARY_VWCPT,

Z_ROME_TIME_PERIOD_SUMMARY_VW,

Z_ROME_EVENT_SUMMARY_VW,

(

SELECT R.COUNTERPARTY_ID AS COUNTERPARTY_ID,

MAX (CASE

WHEN R.RATING_NAME LIKE 'S%'

THEN R.RATING_VALUE

ELSE NULL

END) AS RATING_S_AND_P,

MAX (CASE

WHEN R.RATING_NAME LIKE 'M%'

THEN R.RATING_VALUE

ELSE NULL

END) AS RATING_MOODYS,

MAX (CASE

WHEN R.RATING_NAME LIKE 'D%'

THEN R.RATING_VALUE

ELSE NULL

END) RATING_DBRS,

MAX (CASE

WHEN R.RATING_NAME LIKE 'N%'

THEN R.RATING_VALUE

ELSE NULL

END) AS RATING_NEXEN

FROM Z_ROME_RATING_SUMMARY_VW R

WHERE R.LAST_UPDATE = 1

GROUP BY R.COUNTERPARTY_ID

) COUNTERPARTY_RATING,

ROME_BOT_CHILD_RELATIONS_VW,

Z_ROME_COLL_REQ_FACT_BY_ECP_VW,

Z_ROME_CALC_METHOD_SUMMARY_VW,

Z_ROME_SCENARIO_SUMMARY_VW,

Z_ROME_EXP_PERSPECT_SUMMARY_VW,

Z_ROME_ENTITY_TYPE_VW

WHERE

( Z_ROME_COUNTERPARTY_EXP_VW.COUNTERPARTY_ID=ROME_COUNTERPARTY_SUMMARY_VW.COUNTERPARTY_ID )

AND ( Z_ROME_COUNTERPARTY_EXP_VW.CALC_METHOD_ID=Z_ROME_CALC_METHOD_SUMMARY_VW.CALC_METHOD_ID )

AND ( Z_ROME_COUNTERPARTY_EXP_VW.EXPOSURE_PERSPECTIVE_ID=Z_ROME_EXP_PERSPECT_SUMMARY_VW.EXPOSURE_PERSPECTIVE_ID )

AND ( Z_ROME_COUNTERPARTY_EXP_VW.SCENARIO_ID=Z_ROME_SCENARIO_SUMMARY_VW.SCENARIO_ID )

AND ( Z_ROME_COUNTERPARTY_EXP_VW.PERIOD=Z_ROME_TIME_PERIOD_SUMMARY_VW.TIME_PERIOD_ID )

AND ( ROME_COUNTERPARTY_SUMMARY_VW.COUNTERPARTY_ID=COUNTERPARTY_RATING.COUNTERPARTY_ID(+) )

AND ( Z_ROME_COUNTERPARTY_EXP_VW.BOT_ID=ROME_BOT_CHILD_RELATIONS_VW.CHILD_ID )

AND ( Z_ROME_COUNTERPARTY_EXP_VW.EVENT_DIM_ID=Z_ROME_EVENT_SUMMARY_VW.EVENT_DIM_ID )

AND ( Z_ROME_EXCHANGE_RATE_FACT_VW.EVENT_DIM_ID=Z_ROME_COUNTERPARTY_EXP_VW.EVENT_DIM_ID )

AND ( Z_ROME_EXCHANGE_RATE_FACT_VW.PERIOD=Z_ROME_COUNTERPARTY_EXP_VW.PERIOD )

AND ( Z_ROME_LIMIT_EVENT_VW.COUNTERPARTY_ID(+)=Z_ROME_COUNTERPARTY_EXP_VW.COUNTERPARTY_ID )

AND ( Z_ROME_LIMIT_EVENT_VW.BOT_ID(+)=Z_ROME_COUNTERPARTY_EXP_VW.BOT_ID )

AND ( Z_ROME_LIMIT_EVENT_VW.LIMIT_ALLOCATION = 'Direct' )

AND ( ROME_COUNTERPARTY_SUMMARY_VW.COUNTERPARTY_ID=Z_ROME_ENTITY_TYPE_VW.COUNTERPARTY_ID )

AND ( Z_ROME_LIMIT_SUMMARY_VWCPT.BOT_ID(+)=Z_ROME_COUNTERPARTY_EXP_VW.BOT_ID )

AND ( Z_ROME_LIMIT_SUMMARY_VWCPT.CP_ID(+)=Z_ROME_COUNTERPARTY_EXP_VW.COUNTERPARTY_ID )

AND ( Z_ROME_LIMIT_SUMMARY_VWCPT.LIMIT_ALLOCATION = 'Term' )

AND ( Z_ROME_COLL_REQ_FACT_BY_ECP_VW.COUNTERPARTY_ID=Z_ROME_COUNTERPARTY_EXP_VW.COUNTERPARTY_ID )

AND ( ROME_COUNTERPARTY_SUMMARY_VW.CP_STATUS_ID = 4 )

AND

(

( Z_ROME_CALC_METHOD_SUMMARY_VW.NAME = 'Total 60 Day Exposure' )

AND

( Z_ROME_SCENARIO_SUMMARY_VW.SCENARIO_NAME = 'ACTUAL' )

AND

( UPPER(Z_ROME_EXCHANGE_RATE_FACT_VW.CURRENCY) = UPPER(@Prompt('Select Report Currency','A','Exchange Rate\Data Exchange Currency','MONO','CONSTRAINED')) )

AND

( Z_ROME_EXP_PERSPECT_SUMMARY_VW.NAME IN ( 'Normal', 'Reverse') )

AND

( ROME_COUNTERPARTY_SUMMARY_VW.EXTERNAL_TYPE = 'External' )

AND

( Z_ROME_ENTITY_TYPE_VW.ENTITY_TYPE = 'Counterparty' )

)

GROUP BY

ROME_COUNTERPARTY_SUMMARY_VW.COUNTERPARTY_NAME,

Z_ROME_TIME_PERIOD_SUMMARY_VW.TIME_PERIOD_ID,

Z_ROME_TIME_PERIOD_SUMMARY_VW.TIME_PERIOD,

Concat('Report Currency: ', Z_ROME_EXCHANGE_RATE_FACT_VW.CURRENCY),

CONCAT('Create Date: ', TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH:MI AM')),

COUNTERPARTY_RATING.RATING_NEXEN,

ROME_BOT_CHILD_RELATIONS_VW.CHILD_BOT_PATH,

CONCAT('Report Date: ', TO_CHAR(Z_ROME_EVENT_SUMMARY_VW.AS_OF_DATE, 'DD-Mon-YYYY')),

Z_ROME_LIMIT_EVENT_VW.LIMIT_AMOUNT_ID

One thing to note, the first time I added those two measure objects, the universe was generating two sql statements and joining them to retrieve the results. So in the universe, I created a context which included all the necessary joins to include all objects in one query.

[Former Member · Accepted solution]

I didn't spend too much time on it, but nothing stood out as being a problem. Maybe you can remove some of those objects from the query to simplify it to see if there's something specific about those measures working in the context.

Also, I found this kbase on 30270 errors on processDPcommands.

Cause

This error message occurs because the parameters for the universe connection used by the query are set incorrectly.

Resolution

To resolve the error message

1. Open the universe causing the error in Designer.

2. Click File > Parameters. The Universe Parameters window appears.

3. Click Edit under the Definition tab. The Connection Wizard appears. Verify that the correct information and data source are selected.

4. Click Next. The Perform a test window appears.

5. Click Test Connection to ensure the server is responding.

6. Click Next. The Advanced Parameters window appears.

7. Select the Keep the connection active during whole session option.

8. Click Next. The Custom Parameters window appears.

9. Click Finish > Finish.

HTH

[Former Member · Accepted solution]

Thanks, Tony.

I figured the problem with scheduling the report. It was the prompts. For some reason, scheduling the report with the default prompt values was causing the error. I had to select those same prompt values again and then schedule the report for it to work.

This is described in BO Note 1183642. Thanks again for your input.

[Former Member]

Answer to this error is provided by BO Note 1183642.

[Former Member]

Can you please tell me where I could get hold of BO Note 1183642?

Edited by: Anna-Marie Smith on Aug 14, 2008 3:20 PM

Never mind. I found it.

[Former Member]

Can you please give me the link to "BO Note 1183642". Many thanks.