Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

Using Oracle Flashback Data Archive (FDA) Tables data for reporting in OBIEE

Received Response
1
Views
6
Comments
Mounika Ved
Mounika Ved Rank 1 - Community Starter
edited Aug 13, 2024 5:27PM in Oracle Analytics Cloud and Server

Hi,

We need your help in implementing one solution.

We are trying to use Oracle Flashback Data Archive (FDA) Tables data for reporting in OBIEE.

We need to use  "AS OF <TIMESTAMP>" while querying the FDA table as we might have to show the data in table as of a particular Timestamp.

In RPD, we tried using Opaque view to achieve this and the Timestamp value is passed through Request/Session variable. But there are performance issues with using Opaque view.

Could you please help us with better way of doing this without using Opaque view/Materialized view.  We need to report Live data.

Thanks,

Mounika

Answers

  • Joel
    Joel Rank 8 - Analytics Strategist

    I don't believe that the use of Opaque views is your underlying cause of the performance issues that you're facing. Flashback queries, in most instances don't make use of Oracle's query optimisation.

  • Mounika Ved
    Mounika Ved Rank 1 - Community Starter

    Hi Joel,

    Thank you. But is there any other way to implement this other than using Opaque view?

    Thanks,

    Mounika

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    I'm with Joel. The performance issue points to a more basic problem than just "an opaque view".

    Directly doing analytics against such data stirages is a questionable approach at best if the data source itself hasn't first been verified for sufficient performance.

  • Joel
    Joel Rank 8 - Analytics Strategist

    Hi Mounika

    I've never done this before so I can't say for certain if the following will work and it all depends on whether or not the BI Server can interpret the FDA ASOF syntax.

    You can try adding the ASOF syntax in the "WHERE" clause section of the Logical Table source in the BMM layer.

    pastedImage_1.png

    If that doesn't work then you'll be left with the Opaque View as your only other option in my opinion.

  • Mounika Ved
    Mounika Ved Rank 1 - Community Starter

    Thank you Christian.

  • Mounika Ved
    Mounika Ved Rank 1 - Community Starter

    Hi Joel,

    Thank you for the suggestion. I will try out and see if that is possible.

    Thanks,

    Mounika