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

Mounika Ved

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

Joel Acha

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

Hi Joel,

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

Thanks,

Mounika

[Deleted User]

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 Acha

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.

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

Mounika Ved

Thank you Christian.

Mounika Ved

Hi Joel,

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

Thanks,

Mounika