on 05-25-2011 10:29 AM
pls tell me the difference between a view and a derived table.and how to create.
Thankyou.
thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.