Oracle Communities

Oracle Analytics and AI

Child Item

Materialized View - Accessing through OBIEE

Leslie H

Please forgive me if I have missed this in previous discussions:

I am currently on the 11.1.1.9.0 Version of OBIEE, and am a novice in the system.

My team has created a Materialized View for me in the RMI Database.

They have enabled Query Rewrite, and it is now my job to get OBIEE to read this view instead of the base view.

I have tried to follow the Oracle documentation for Opaque Views in the RPD file, but I have had no success.

I have tried adding hints to the OBIEE query itself, and am still unsuccessful -- such as... /*+REWRITE(<view,view>)*/ --

Does anyone have a resource to help with this process? I have searched Oracle Docs, Discussions here, as well as Google, and I am finding it difficult to get a clear picture of what I am doing incorrectly.

Please let me know if I need to provide more information to help answer this question.

Thank you in advance for your time.

Find more posts tagged with

Accepted answers

All comments

[Deleted User]

Have you tried simply importing the object?

You don't have to write an opaque view in the RPD. Just use the object as it is in the DB.

Robert Angel

Hi,

Christian is (as always) completely correct.

Your snapshot from the MV will be seen as no different to a table, just import it in the usual way into the physical layer, join it to the appropriate tables (aliasing first for best practice) and then remap the columns onto the business model to replace all fields with the new 'table' and when you are done delete the original table from the logical table source.

Job done.

Thomas Dodds

1. import the object (as already counseled)

2. reserve opaque view for teporary/POC work (there's a serious performance consideration present in this technique)

3. if you have to hint ANYTHING in OBIEE your data design is deficient (I have been handed RPDs with hinting that has crippled performance)

Leslie H

Hi Christian,

Yes, I have imported the object without deploying the Opaque View.

I feel like I am missing a step though, maybe it is the steps Robert outlined below - create alias, remap columns, etc. I will re-try. Thank you for your response.

Robert Angel

Hi Leslie,

from memory I think there is also a wizard in the rpd that lets you change all occurences of tableX to tableY (Or MatViewY in your case)

[Deleted User]

The steps outlined by Robert are the normal modelling stepsin the physical layer in order to make things "usable" as in - it's the basis for the logical star you'll build on top of it in the logical layer.

If you just have your MV imported as a physical layer object you will simply join it to its relevant relationships in terms of facts and dimensions and then drag things over into the BMM layer.

Alternatively - what Robert is hinting towards - if you have things mapped already you can use the wizard to replace things. Tools -> Utilities ->

In here you then specify which object to replace with which. In your case you'd replace the whole "table" (which could be your opaque view) with your MV.

Robert Angel

re: Hints - I tend to be a busy man, hence some of my helps are laconic bordering on one word.

Also, I figure this is a help forum, not a training forum, so I expect foundation OBIEE skills as a minimum, and where this is obviously not the case will advise OBIEE by Example and other trainings, free or otherwise.

No offence intended or implied to any specific individual or individuals present or absent.

[Deleted User]

Tell me about it...