2 Replies Latest reply: Jul 23, 2014 11:08 AM by NRB RSS

    Re:opaque view and materialized view

    NRB

      Hi Experts,

       

       

      I want little bit clarification regarding opaque view and materialized view in OBIEE physical layer

      -> what is the main difference  between opaque view and materialized view in the RPD point of view we have to create this in RPD level or DB level.

      -> If i want to list out the opaque view's and materialized view's  in the RPD how i have to do.

      -> how an opaque view will degrade the performance and how an materialized view will improve the performance in report level.

      -> In which scenarios we will use opaque view and materialized view (If possible with one scenario for better under standing).

       

       

      Thanks in advance.

        • 1. Re: Re:opaque view and materialized view
          rmoff

          I want little bit clarification regarding opaque view and materialized view in OBIEE physical layer

          -> what is the main difference  between opaque view and materialized view in the RPD point of view we have to create this in RPD level or DB level.

          An opaque view is a SQL statement defined in the physical layer, that OBIEE will use whole in building a SQL query. In a way it's no different from a database view, except it is defined in OBIEE only. Optionally, you can deploy an Opaque view, which pushes its definition into the database. Read more about it here: Working with Physical Tables, Cubes, and Joins - 11g Release 1 (11.1.1)

          A materialized view (MV) is a database concept where the results of a SQL statement are physicalised as if a table, and so as far as OBIEE is concerned a MV is just another physical table

          -> If i want to list out the opaque view's and materialized view's  in the RPD how i have to do.

          To find opaque views, use the Admin Tool "Query Repository" function. Search for Physical Tables, and set up a filter on Type = Select

          Screenshot: https://www.evernote.com/shard/s16/sh/5c843e47-bff1-4d4b-b847-be98f3a2ec09/cf060a6cd9805f687fa0b5b46f3f0020

           

          To find materialized views you'll need to query your database's catalog (eg select object_name, object_type from user_objects where object_type = 'MATERIALIZED VIEW';) and compare it to the list of tables in your RPD

           

          -> how an opaque view will degrade the performance and how an materialized view will improve the performance in report level.

          A materialized view is materialized, that is, the results are already stored by the database. That generally means it will perform better because all the joins and any aggregations etc will have been resolved in advance.

          • 2. Re:opaque view and materialized view
            NRB

            Hi Rmoff,

             

            Thanks for your response and inputs.

             

            Thanks,