Oracle Analytics Publisher Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Analytics Forums› Oracle Analytics Publisher Forum

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How do I view the HTML output of a Oracle BI Publisher report with 700 thousand rows?

Received Response
16
Views
1
Comments
Ngoc Phung
Ngoc Phung Rank 3 - Community Apprentice
in Oracle Analytics Publisher Forum

Hi everyone,

I'm currently using BI Publisher embedded in Oracle Analytics Server 2024. I have a report that uses 2 parameters: P_BRANCHES and P_DATE
For P_BRANCHES = 'ALL' (all the branches), the report will return all the values for the column BRANCH_NAME and the remaining columns, which is 700 thousand rows plus in total.

The rtf template of this report was made by BI Publisher Desktop in Words and the template uses GROUP BY BRANCH_NAME (in Table Wizard), then the sum for each column of each BRANCH_NAME is calculated in a row at the end of every BRANCH_NAME.

The sheer amount of rows and the calculation of each BRANCH_NAME make the report run extremely slowly if someone chooses P_BRANCHES = 'ALL'.
I was able to export the .xlsx file in about 20 minutes and the file was 49,8 MB, but the HTML output took forever to run.

Is there any practical way for me to make the report run faster when I choose P_BRANCHES = 'ALL' and HTML as the output? If not, please suggest a different approach to fetch the output for the case P_BRANCHES = 'ALL' faster.

Tagged:

Answers

  • RVohra
    RVohra Mod

    There is no practical way to make an HTML BI Publisher report with ~700k rows perform well. As per my understanding, BI Publisher loads the entire XML data set into memory then applies grouping and calculations in the XSLT layer and then renders every row for HTML output and it is all going to be CPU and memory intensive.

    I would recommend, instead of grouping in the RTF, pre-aggregate in the query some think like:

    SELECT
    branch_name,
    SUM(amount) AS total_amount,
    SUM(qty) AS total_qty
    FROM <your table name>
    WHERE (:P_BRANCHES = 'ALL' OR branch_name IN (:P_BRANCHES))
    GROUP BY branch_name

    Alternatively think of two-mode Report

    P_BRANCHES Value

    Behavior

    Specific branch(es)

    Detail report

    ALL

    Summary-only report

    How to Implement

    • Use an IF condition in the RTF:
    <?if:P_BRANCHES='ALL'?>  <!-- Summary layout --><?else?>  <!-- Detailed layout --><?end if?>
    • For ALL:
      • Show one row per branch
      • Use SQL aggregation
    • For specific branches:
      • Allow detail rows

    I hope you review these approaches and will be able to come up with the solution that works well. Thanks