Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Johannes Bosman
    Convert OTBI analysis / report to BI Publisher report
    Topic posted May 29, 2018 by Johannes Bosman, tagged BI Publisher, Fusion Financial reporting, OBI Answers, OBIEE Answers, OTBI, Reports, Tip 
    1878 Views, 8 Comments
    Title:
    Convert OTBI analysis / report to BI Publisher report
    Summary:
    Convert OTBI analysis / report to BI Publisher report
    Content:

    At our company the OTBI reports are running slow and long.

    I am tasked to convert OTBI analysis / reports to BI Publisher reports for our OpTax department, with no idea how.

    Could anyone maybe assist, point me in the right direction.

    Thanks in advance.

    Regards.

    Chris

    Comment

     

    • Shakher Sharma

      If your OTBI analysis are running slow then please raise an SR with Oracle and see if they can help you with the performance improvement.

      To answer your question, there are two ways to create BI Publisher report using an existing Analysis.

      First, you can create a Data Set in a data model based on Oracle BI Analysis, as shown in Step 1 in the attached image. It will ask you to browse and select your existing analysis that you wish to use as a data source. However, this method may not give you any improvement performance-wise since the Analysis will be executed first to fetch the data, and this is the Analysis itself with which are you facing performance issues.

      Second, you can get Physical SQL generated by the Analysis, and then use SQL Query as data source while creating the Data Set ( Step 2 in attached image). You will get a box to paste your SQL query and you should select correct DB connection in Data Source. Following are the steps to get Physical Query:

      • Run you analysis.
      • Click on Administration
      • On Administration page, under Session Management section, click on Manage Sessions hyperlink.
      • On Manage Sessions page, you will find two tables; the first one will be Sessions tables listing all the sessions, and the other one will be Cursor Cache table listing all Cursors. In Cursor Cache table, you will have to browse and look for your session. Use Sort By LOV on right-hand side, at the top of the second table to sort all session by Creation Time Descending and identify the latest session executed from your account. Information column will show you the path of the analysis for the session in context.
      • Once you have identified the session, click on View Log Hyperlink to open the log.
      • Go to the bottom of the page and start browsing upward. You should be able to see a Physical SQL that will start with "WITH" statement. Copy this SQL. This SQL works perfectly in Data Model. The only problem is that since it is machine generated, it is hard to understand. You should enclose this SQL in another SELECT C1, C2, C3... FROM (   Physical SQL goes here ). It will make your job easier. You should then run the data model, view data, try to make sense what column ( C1, C2... ) repersents which value so that you may create readable aliases for these columns in outer SELECT.

       

    • Richard Liu

      I'm curious which Product Family/Specific product area is the OTBI reports running slow.  Supply Chain? Financials? Procurement? Which product area?

    • Veronica Dumitriu

      Johannes, what Subject Areas are the OTBI reports built on?

    • Adolfo Costantino

      Thanks for infos... One question: Where is "View Log Hyperlink" section?

       

      Thanks,

      Adolfo

      • Veronica Dumitriu

        You'd have to be logged in as BI administrator:

         

        • On Administration page, under Session Management section, click on Manage Sessions hyperlink.
        • On Manage Sessions page, you will find two tables; the first one will be Sessions tables listing all the sessions, and the other one will be Cursor Cache table listing all Cursors. In Cursor Cache table, you will have to browse and look for your session. Use Sort By LOV on right-hand side, at the top of the second table to sort all session by Creation Time Descending and identify the latest session executed from your account. Information column will show you the path of the analysis for the session in context.
        • Once you have identified the session, click on View Log Hyperlink to open the log.
    • Omar Basaad

      Hi ,

      We followed the step provided above,

      Difficult to simplify the the script from machine.

      As an example , please find attached file.

      Is there any way to get the query of OTBI analysis in simple way.

      Regards