on 07-14-2008 10:22 PM
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
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?)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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.
Answer to this error is provided by BO Note 1183642.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
96 | |
11 | |
11 | |
10 | |
9 | |
8 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.