BIP reports on OBIEE dashboard - very slow — Oracle Analytics

Oracle Analytics Cloud and Server

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

BIP reports on OBIEE dashboard - very slow

Received Response
625
Views
13
Comments

Summary

BIP reports on OBIEE dashboard - very slow

Content

Hey,

I have a couple of BIP reports which are very slow when rendered on the OBIEE dashboard and when I do a view report.

1. the data model brings back 200 records in less than 3 seconds

2. The query also brings back 500 records in less than 3 seconds in the DB

Not sure why it would become slow when i render the report as view report or on the OBIEE dashboard with prompts.

the maximum number of records returned by the query is only 60 k. the dates for which I am running the qeuery brings back only 12k records.

Please suggest any configurations that i might need to check or anything that could help me get through this issue.

OBIEE version - 12.2.1.1

BIP 12c

DB - Oracle 12c

Regards

Kriparam

«1

Answers

  • Sherry George
    Sherry George Rank 7 - Analytics Coach

    Take the query generated (which brings back 12k records) and run from a db client like sql dev and see how long it takes.

    Also you haven't posted how much time 12k records take in OBIEE and 300 or 500 records in not comparable to 12k.

  • Venkat Thota - BIP
    Venkat Thota - BIP Rank 7 - Analytics Coach

    also apply below configurations

    To Improve Performance it is good to have the value for the following parameters set to True under the report level:

    -Use BI Publisher's XSLT processor

    - Enable scalable feature of XSLT processor

    -Enable XSLT runtime optimization

  • Venkat Thota - BIP
    Venkat Thota - BIP Rank 7 - Analytics Coach

    1) xml data retrieval time in data model and data retrieval time in sql developer are almost same , both will use jdbc connection.

    2) response time will definitely vary based on output records , lower records lower time and more records more time ,so for 500 records and 12k and 60k response time will be different.

    3) what is time taken to execute for 500 records ,5k and 12k records in obiee dashboard and in BI Publisher?

    3) try input filters values with different output records and compare.

    4) please post bipublisher.log for 12k records which contains data model execution time and report formatting execution time.

    Thanks

  • Maraprik
    Maraprik Rank 4 - Community Specialist

    Hey sherry,

    thanks for responding.

    The query brings when run for the same dates, comes back in 3 seconds. I am under the impression that, it kind of paginates and brings only 500 records at a time(please correct me if I am wrong) as it says records 1-500 at the bottom.

    To see the exact time, i export the data to a file from the DB, it completes the export in close to one minute. this is for 12421 records.

    I am sorry but i may be totally wrong with my assumptions.

    In OBIEE it takes about 6 minutes to come back .

    please let me know if I can provide further information.

    Regards

  • Maraprik
    Maraprik Rank 4 - Community Specialist

    Thanks for the response. I have changed the parameters.

    500 records in OBIEE dashboard takes less than a minute

    5k and 12k records in obiee dashboard and in BI Publisher - 3 minutes and 7 minutes

    I have changed the parameters u have specified. i'll keep you posted. thanks again.

  • Venkat Thota - BIP
    Venkat Thota - BIP Rank 7 - Analytics Coach

    Problem is with single report or or all reports ?

    have verified all the server configuration properties same in both systems (old and new system)?

    Run detailed log for a specific report that causing problem and get execution time for sql from log and compare this with sql execution plan from sql developer or Toad.

    Try to find where which part of portion consuming more time ? (data execution or formatting the output).

    Thanks

  • Emmanuel Tchamna
    Emmanuel Tchamna Rank 2 - Community Beginner

    Hi,

    Has the BI Publisher template slowness been resolved. I 'm facing a similar situation wherein the BI Publisher template itself takes a long time (OBIEE 12.2.1.2) to generate the RTF/Excel option to export the BI Publisher reports.

    These reports were on 11.1.1.6 and had no issue, we migrated to .3 release on 12c and the following BI Publisher properties values have been set to true:

    -Use BI Publisher's XSLT processor

    - Enable scalable feature of XSLT processor

    -Enable XSLT runtime optimization

    However, the wait time before the template renders still takes 5-7 minutes. We have build a new BI Publisher report in 12c to test but the delay is no longer present, we are looking for a fix as we have several BIP reports that were migrated and rebuilding each report would be very time consuming.

    Any help would be very welcome.

    Thanks,

  • Venkat Thota - BIP
    Venkat Thota - BIP Rank 7 - Analytics Coach

    Any Update?

  • Venkat Thota - BIP
    Venkat Thota - BIP Rank 7 - Analytics Coach

    Looks Like its bug ,IMG_1826.jpg

  • Emmanuel Tchamna
    Emmanuel Tchamna Rank 2 - Community Beginner

    The issue is not with the actually report execution or time it takes to run. The problem we are experiencing after migrating to 12.2.1.2 is the BI Publisher Template. as per the first image, there is no immediate template populated and users must wait 7 minutes while bi server pools the template before they can open the PDF. The first image below is a BI Publisher report that goes through the RPD model.

    same BIP report.png

    The below image executes the RTF template as soon as you click on the dashboard. The pooling is instantaneous, however this is the new build BIP report that uses JDBC connection.

    BIP template correct.png

    I'm not sure as to why it worked fine in 11.1.1.6 and delays in 12c. Any thoughts?

    Thanks