Overview: Working with a bursting BIP report to be sent out to X number of recipients. The bursting job never completes and stays in ‘Job processor completed’ status indefinitely. Looking at the job details, N number of recipients’ reports are created and delivered. M number, however, are stuck in ‘Rendering Report Document’ statuses. The BIP job never completes.
Analysis: Looking into the issue. It is not clear what is causing this issue. Is it a memory issue? Is it stuck threads? Is there something wrong with the bursting definition query?
First course of action is to look at log files, specifically bipublisher.log, and bump up debug tracing level within BIP log configuration. To enable this:
- Login into Enterprise Manager
2. In left pane, expand the folder ‘Application Deployments’
3. Two options based on install
a. Simple Install — right click on bipublisher(AdminServer)
b. Enterprise Install– right click on bipublisher(bi_server1, bi_server2,…)
4. From the pop-up menu, choose Logs-> Log Configuration
5. In the right pane, open the log configuration window, showing the ‘Log Levels’ tab
6. At the bottom of page, tick the checkbox ‘Persist log level state across component restarts’
a. This ensures that the changed log level will persist across OBI restarts
7. Change the Oracle Diagnostic Logging Level to TRACE:32(FINEST)
8. Click Apply
The log files did not show anything before and after extended debugging. So the log files were not helpful.
I looked at both the memory and threads and did not see anything that would suggest this was an issue. I broke down the data set and bursting SQL query to one of the users/recipients that were having an issue. That sample user always had an issue! So by breaking nit down to test a smaller sample set and down to the one sample problematic recipient helped me isolate and make a decision that it was not memory or thread related.
I then evaluated the bursting SQL query definition. Thinking it must be it. But wait, nothing was wrong, as many others seemed to get their reports created and sent. So it must not be the bursting SQL query definition.
I then ran the report by itself for the test user having issues. That ran and produced a document! So what gives!? It can run by itself;but, bursting has issues.
I did notice in the stand alone run of the report two graphs were not being produced and just showing the running clock. Hmmm…
I looked at the dataset for that one recipients data. All looked good. Example:
<DATA_DS> <G_1> <NAME>FOO</NAME> … <ADDR>123 ELM ST…</ADDR> </G_1>
</DATA_DS>
I looked at a comparable dataset for another user/recipient that ran fine. Here it is! Example:
<DATA_DS> <G_1> <NAME>FOO</NAME> … <AMOUNT>100.00</AMOUNT> … <ADDR>123 ELM ST…</ADDR> </G_1>
</DATA_DS>
The problematic users where missing a few elements in their data set rows. Like, the AMOUNT element/attribute in the examples above. So, this causes the BIP report engine to freak out and churn forever. Specifically, this was causing the gauge objects, within the BI Publisher Layout (XPT) format, to churn and cause this issue. Churn meaning showing the running clock symbol forever if running the report separate or the ‘Rendering Document’ status in bursting mode. So, if any data elements are null in the data set that are being referenced in the XPT report format for gauges, it is a problem!
The XPT (BI Publisher Layout) format does not have or allow conditional sections, like RTF format. The RTF format is a more flexible format in that regard; but I had to use the XPT format for this particular circumstance.
Now, as I specified in the first version of this document I thought it was due to the fact that the null values were not being added as tags and thus were missing. But that was not the case. I found out that there is a setting under the data model->properties that will allow the data set results to add the XML tags/elements for any null values. This setting is a checkbox with label: ‘Include Empty Tags for Null Elements’. Checking this box will produce the XML elements if they are null, for example:
<DATA_DS> <G_1> <NAME>FOO</NAME> … <AMOUNT/> … <ADDR>123 ELM ST…</ADDR> </G_1>
</DATA_DS>
I believe that the BIP XPT format should allow null elements or at least allow a setting default values for the gauge objects so that this error/issue would no longer exist. At the very least, throw an error or warning giving a clue to the issue.
Resolution/Summary:
Ensure that all data elements (aka columns) from the data set query return a value and not NULL , especially for metrics that will be used in gauge objects in the XPT format. My particular work around was to put in a NVL( ) in the aggregate functions to ensure they do not result in a NULL and default to a 0. For my particular issue, the data set SQL had an aggregate function which was returning NULL. Example:
SELECT
…
MAX(AMT) as AMOUNT
FROM
…
I just needed to modify accordingly and viola. The bursting jobs will complete and send to all recipients with out issue or get stuck.
SELECT
…
MAX(NVL(AMT,0)) as AMOUNT
FROM
…
