Data model cannot be executed because of an error

Youssef_B_82

Summary

Data model cannot be executed because of an error

Content

Hi,

My Data Model fails from time to time and sometimes it does work without any apparent changes.

When I open the Data model I get this error before the parameters loads "Data model cannot be executed because of an error".

I attached the message logs and the error image.

Can someone help?

thanks

Youssef

Youssef_B_82

Oracle BI Publisher 11.1.1.7.160719  (build:20160620.0157)

I have 9 LOV / Parameters

I attached the lov queries on .txt file.

Thanks for your prompt response

Venkat Thota - BIP

looks problem is with multiple selection of param values in data model , what is the bip version and post screen shots of LOV query and set up in parameter section.

Venkat Thota - BIP

Do you know how frequent the report is getting failed , the issue is with cascaded parameters (refresh parameters on change) , please check :p_bu does not have any data issues like in place of number there nay be symbols in the data. does all the parameters are fixed values from database or keep on changing ?

Youssef_B_82

Hi Venkat,

It seems to fail between 1pm and 3pm.

the scheduler was was running fine every 5 minutes and now it failed with the following error.

Job processor caused exception

[INSTANCE_ID=srv-bidev-01.1489010620834] [INSTANCE_JOB_ID=2977]::::ERROR_GETTING_REPORT_DATA::[INSTANCE_ID=srv-bidev-01.1489010620834] IOException:[INSTANCE_JOB_ID=2977] Invalid parameters requested.

oracle.xdo.servlet.data.DataException: Invalid parameters requested.

at oracle.xdo.servlet.ReportImpl.renderReportXML(ReportImpl.java:729)

at oracle.xdo.enterpriseScheduler.bursting.BurstingJobProcessor.getReportData(BurstingJobProcessor.java:502)

at oracle.xdo.enterpr

It does mentions "Invalid parameters requested". What I find strange is the fact that many reports works fine with similar data model setup and the report itself was working this morning..

What are the best practices when it comes to cascaded parameters?

Thanks

Youssef

Youssef_B_82

Update:

It's 4:15pm - Report is up and running again without no apparent changes

Job Name : test

  Job Executed Date : May 25, 2018 4:16:51 PM EDT Status : Job was successfully processed

Youssef_B_82

We use the report on demand.

When the report fails it fails before the parameters load.

I will schedule the report and hope to get information on which parameter is failing.

Thanks for the suggestion, I will update the thread with the results.

Venkat Thota - BIP

Issue might be due to cascaded parameters that some parameters depends on other. Is the scheduled report or on demand ? please gather param values when report got failed. better schedule the report and enable diagnostic log file in scheduler page when the report is failed then you will know where exactly the report is getting stopped and which param is causing problem.

Youssef_B_82

Do you know how frequent the report is getting failed --> No I don't have this statistic but. I am trying right now and it is working but unfortunately it will fail later on.

please check :p_bu does not have any data issues like in place of number there may be symbols--> BU has no symbols (only number data type and 3 options are available for single select not multiple) but the other parameters have special characters like the Department / Class / Sub-Class / Zone and Vendor prompts, many of them have one of those 5 symbols   ' . & ( )   on the description.

in the data. does all the parameters are fixed values from database or keep on changing ? It is very very rare that the description changes and the IDs always stays the same.

Thanks

Youssef

Sketz

Ivalid Parameters occure when you refer to other parameters which are null inside of another LOV.

Add NVLs or rework your parameter "hierachy"

Youssef_B_82

Hi Sven,

I do pass null to pass all values using the least function. Using native BIP setup to pass all values doesn't work since their is more than 999 different values and form my understanding if we choose "all value passed" we are limited. The only workaround I found to pass more than 999 selection is to use the least function and pass it as null, like this:

select distinct CLASS_ID || '-' || CLASS_DESCR cls

from S_DEPT_CLASS_SUBCLAS

where (least(:P_DEPT) is null or DEPARTMENT_ID || '-' || DEPT_NAME in (:P_DEPT))

and BUSINESS_UNIT_ID = (:P_BU)

ORDER BY TO_NUMBER(regexp_substr(CLS,'^[0-9]+')),LOWER(CLS)

It does work but break from time to time, if it's due to the parameters queries, what options do we have to resolve this issue?

thanks

Youssef