How to use view in universe ?

[Former Member]

HI,

   How can i use view created in database in the  universe . One approach i know is using of derived tables.is there any other way ? Also please explain how view is better than derived table ?

Thanks in advance

-Regards

  B

[Former Member]

Hi,

Views can be added to the Universe in the same way as you do to Insert other tables from the database.

Please refer the below link for advantages of using derived tables:

http://www.dwbiconcepts.com/reporting-a-analysis/25-business-objects/89-bo-derived-table.html

Views are a better option to use in Universe than derived tables. Derived tables will reduce the performance. But again it depends on your requirement. If you wan to create any prompts, then derived table is the option. Otherwise it advisable to use database views.

Regards,

Santhosh

[Joe_Peters]

As far as a universe is concerned, there's no difference at all between a database view and a table. You add views to a universe in the same way as a table, and once in the universe, they are indistinguishable from tables.

A derived table serves a similar function as a view -- it is a "virtual table" that is based on predefined SQL. 

Advantages of a derived table over a view:

• The SQL of derived tables can be changed directly from within Designer.
• Derived tables can contain @prompt() or @variable() calls to create a parameterized table.

Advantages of a database view over a derived table:

• The database (Oracle, anyway) will ensure that the objects a view is based on remain valid, and will mark the view as unusable if not.
• The database will compile the view when it is created.  With a derived table, its SQL may have to be compiled every time the query is run.
• A view can be shared among multiple database users, and may be used by multiple universes, or query tools other than BO.
• A view can incorporate database-level security.

[Former Member]

Thanks for the reply ..

performance wise which is better view or derived table.

Also please explain views vs Materalized views

-Thanks

   B

[Joe_Peters]

You might notice a slight improvement gain with a view vs. derived table; though in theory the database should optimize them the same way. 

Materialized views are a different beast.  Rather than being dynamic like a view, they are a physical representation -- imagine doing a "create table mytable as select * from myview".  Try a Google search on "materialized view" for more info.