cancel
Showing results for 
Search instead for 
Did you mean: 

doubt

former_member184582
Participant
0 Kudos

pls tell me the difference between a view and a derived table.and how to create.

Thankyou.

Accepted Solutions (0)

Answers (4)

Answers (4)

former_member184582
Participant
0 Kudos

thanks

Former Member
0 Kudos

If you want the universe to crate a complex query involving sub queries etc..(which universe cannot crerate). You put that SQL in a derived table. The derived table crerated will act like a view. this can be joined to other tables. Derived table is your chance of creating a view at the BO universe level and local to that particular unvierse users. Where as a view created will be visible accross all the users having access to the DB.

Performance:

When a query is run against a View it runs at the DB side --better performance

When query is run against the Derived table it runs at the BO server side.---slow performance

Former Member
0 Kudos

When query is run against the Derived table it runs at the BO server side.---slow performance

Wrong. The derived table is simply not exposed to the database server until runtime. The SQL is then fired to the database but is treated as a brand new query and won't be optimised in any way.

Former Member
0 Kudos

The main disadvantages of derived tables are:

1/ Performance - if you are not the DBA, you will not know if it will perform optimally. A view written by the dba will fall under part of the tuning maintenance plans of a good DBA

2/ Isolation - a derived table is known only to the universe designer and as such will usually be missed in any impact analysis carried out by the DBA when changing the data schema.

3/ Support - unless the designer is conscientious enough to document why they've built a derived table, you'll never know. While the same may apply to a view, there are more people exposed to the view and a better chance of someone knowing why it was created.

Derived tables have their place and it's typically where you've got no database access. It's always better to have the solution done in the database for greater visibility, performance and re-usability.

To create a view, you need to know SQL. Google will provide examples that will vary depending upon your RDBMS (Oracle, SQL Server, etc.)

To create a derived table, right-click on your universe schema and in the context menu you will see the derived table option.

Edited by: Me map on May 25, 2011 2:49 PM

amitrathi239
Active Contributor
0 Kudos

Hi,

view:

A view is a virtual table that was generated from the physical schema.

Any change in the physical table will change the data in the view.

You need to have a permission to create a view in the DB.

Derived table:

You dont need such permission. You just create derived table in the universe and use it.

And the most important advantage of derived table over the view is that the BO @functions (@variable, @prompt) can be used in the definition of derived table.

Go to the universe under the insert tab click on derived table paste the sql and parse.

Hope this helps you.

Thanks,

Amit