Skip to Content

doubt

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

Thankyou.

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • Posted on May 25, 2011 at 09:43 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 25, 2011 at 12:49 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 25, 2011 at 03:41 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      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.

  • Posted on May 26, 2011 at 07:06 AM

    thanks

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.