BI Publisher 12c Error : Failed to load xml in Data model (view data)

Question

Summary

Content

Hi All,

I am trying to create data model using simple sql query (configured JDBC connection) and when I am clicking the view data ,am getting error as "failed to load XML " due to this I am unable to create report and was not able to click save as sample data.

I am to see data in query builder for the same query but in view data I am unable to see xml (I selected 5 rows)

Please help me to resolve the issue

Vamsi Rampalli · Answer

Hi Thomas , Have checked with other user (who is having sysdba previleges) , still I am unable to view data and getting failed to load xml error. Please help me to resolve this issue. Below is the view engine log information: - - - values for processing.. [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]XDO_USER_NAME->c_bchapp [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]XDO_USER_ROLES->BIAdministrator, BIConsumer, BIAuthor [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]XDO_USER_UI_LOCALE->en [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]XDO_USER_UI_ORACLE_LANG->US [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]XDO_USER_REPORT_LOCALE->en_US [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]XDO_USER_REPORT_ORACLE_LANG->US [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]XDO_DASHBOARD_EXPR->null [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]FUSION_SECURITY_ENABLED->false [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]FUSION_ESS_APPLSESSION_ID-> [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]TimeZone Details: User Preference time zone selected:sun.util.calendar.ZoneInfo[id="Africa/Casablanca",offset=0,dstSavings=3600000,useDaylight=true,transitions=100,lastRule=java.util.SimpleTimeZone[id=Africa/Casablanca,offset=0,dstSavings=3600000,useDaylight=true,startYear=0,startMode=2,startMonth=2,startDay=-1,startDayOfWeek=1,startTime=7200000,startTimeMode=0,endMode=2,endMonth=9,endDay=-1,endDayOfWeek=1,endTime=10800000,endTimeMode=0]] Java SimpleDateFormat with no timezone set:2018-01-16 10:27:05.033-080022 JVM Java.Util.Timezone:sun.util.calendar.ZoneInfo[id="US/Pacific",offset=-28800000,dstSavings=3600000,useDaylight=true,transitions=185,lastRule=java.util.SimpleTimeZone[id=US/Pacific,offset=-28800000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=2,startDay=8,startDayOfWeek=1,startTime=7200000,startTimeMode=0,endMode=3,endMonth=10,endDay=1,endDayOfWeek=1,endTime=7200000,endTimeMode=0]] Java SimpleDateFormat with JVM timezone:2018-01-16 10:27:05.033-080022 FND Cononical Date Format with JVM timezone:2018-01-16T22:27:05.236-08:00 [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]Writing Data ... [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]Number of queries :1 [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]Returning Final Query from QueryParser: select /* QUERY_SRC('datamodel: _PCLS_BI_CCL_TEST_DATA_xdm,dataset:a') */ "CHECKLIST_GROUP"."VERSION" as "VERSION", "CHECKLIST_GROUP"."ACTIVE" as "ACTIVE", "CHECKLIST_GROUP"."CREATED_BY" as "CREATED_BY" from "CCL"."CHECKLIST_GROUP" "CHECKLIST_GROUP" [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]DataSet Name:a: DB Fetch Size: 100 [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]DataSet Name:a: SQL Query Timeout: 500 [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]DataSet Name:a: SQL Query :a: SELECT "VERSION", "ACTIVE", "CREATED_BY" FROM (select /* QUERY_SRC('datamodel: _PCLS_BI_CCL_TEST_DATA_xdm,dataset:a') */ "CHECKLIST_GROUP"."VERSION" as "VERSION", "CHECKLIST_GROUP"."ACTIVE" as "ACTIVE", "CHECKLIST_GROUP"."CREATED_BY" as "CREATED_BY" from "CCL"."CHECKLIST_GROUP" "CHECKLIST_GROUP" ) PRUNED_a [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]Complete process Data Structure .. [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]Executing AFTER REPORT triggers... [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]Data Generation Completed... [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]Total Data Generation Time: 0.0 seconds [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]Total Estimated XML Data size: 571 [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:] Start Datamodel diagnostic details.. ====================================================================== Datamodel name: _PCLS_BI_CCL_TEST_DATA_xdm Processing mode: online Calling module: StringList: 2 Cache SQL Rowset: false Allow DB Modifications: false Is DM Customized: false Output format: 1 Auto db Fetchsize enabled: true Default db Fetchsize : 20 SQL Query Timeout : 600 Olnine SQL Query Timeout : 500 Offline SQL Query Timeout : 3600 XML data size limit: 314572800 XML Sample data size limit: 1048576 Processed with Scalable mode enabled: true Track xml size enabled: true SQL Session Trace enabled: false SQL Session Trace File: SQL Query Pruning enabled: true Is Layout Elements passed: false Layout Elements size: 0 Webservice timeout: 1800 Debug Mode: false Estimate XML DataSize: false Generate Output XML: true DataSet Name:report Number of elements:0 Total Query Execution time (ms):0 DataSet Name:Parameters Number of elements:0 Total Query Execution time (ms):0 DataSet Name:Triggers Number of elements:0 Total Query Execution time (ms):0 DataSet Name:a DataSet Type:sql DataSet DB fetch size:100 DataSet Query Timeout:600 Total Number Of Times Query Executed:1 Total Number Of Rows Processed:5 Number of elements:3 Total Query Execution time (ms):14 ------------------------------------------------------------------------ Total Number of Datasets: 4 Total Number of Elements: 3 Total Parse Time (ms): 0 Total Used Memory at Start: 1.17 GB Total Used Memory after Parsing: 1.17 GB Total Used Memory after Processing: 1.18 GB Total Query Execution Time: 14 mseconds Total Data Generation Time: 16 mseconds Total XML Data Size Generated: 586 Processing Exceptions: ====================================================================== End Datamodel diagnostic details.. [180116_10270005][DATA PROCESSOR ID:1573961815][SCH JOB INFO:]Removed Session thread :Thread[[ACTIVE] ExecuteThread: '18' for queue: 'weblogic.kernel.Default (self-tuning)',5,Pooled Threads] : for id:ZJRbekGUZ11516170425072 ]]>

Vamsi Rampalli · Answer

Yes Thomas , I also noticed that error. But my doubt is whether this permission error related to DB or BI publisher side ?? Because I am able to run the query in same DB and see the data in DB (Using Sql developer).

Why this error coming in BI publisher ? Could you please explain me .

Thomas Dodds · Answer

[180115_11150016][DATA PROCESSOR ID:1704132772][SCH JOB INFO:]XMLGEN:Exception while processing datamodel...:_PCLS_BI_BI_Publisher_Sample_Data_Model___CCL_xdm

[180115_11150016][DATA PROCESSOR ID:1704132772][SCH JOB INFO:]Process Cancelled Stage:false

[180115_11150016][DATA PROCESSOR ID:1704132772][SCH JOB INFO:]XMLPGEN:java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges

ORA-01031: insufficient privileges - Database is telling you you are trying to do something without having the correct privileges associated with your login credentials