Thursday, 27 February 2014

What is Derived Table in Universe Designer?

02:51  XtremeProgrammer86  No comments

A Derived Table is a logical table created in Business Objects Universe using an SQL query. A derived table in a universe is analogous to a view created in database with a SELECT statement.

How to Implement Derived Table


  1. Open/Import a universe.
  2. Click Insert -> Derived Tables OR right click on an empty space in the universe canvas and click derived Tables.
  3. Write the SQL statement which is to be used for the derived table in the "Enter SQL Expression" area. Name the derived table with an appropriate name.
  4. If the SQL of the derived table contains any calculation, enter a valid alias for that column. e.g. SELECT calculated_column AS CAL_COL FROM TABLE
  5. Click "Check Syntax". If the SQL gives error, debug it. If the SQL parses correctly, click OK.
  6. The Derived Table can be seen in the universe canvas with columns as named in the SQL query. These columns can be dragged to the "Universe Window" (on left side of the canvas) to create Objects of the derived table.
  7. Link the derived table with the database tables if required using appropriate joins and contexts.

Advantage of using Derived Tables

  • It can be created using an SQL statement with complex expressions, joins and prompts which are not possible to create in BO Universe using normal approach.
  • It acts just like a normal table in the universe and can be used to join with other database table and derived tables present in the universe.
  • Multiple level of nested dervied tables can be created, in which one derived table is used in another derived table query.
  • Since a derived table is based in a universe and is not dependent on any DDL, there’s no need for any interaction from the DBA or ETL team for its creation.
  • Changing the structure of the derived table is as easy as changing the SQL statement it is made up of.

Disadvantages of using Derived Tables

  • Derived tables do not store data and hence every time a report using this table is run, the whole SQL query is executed. This may cause poor performance of the report.
  • If the database tables used in the derived tables are huge and the query is complex, it may cause memory issues on the server.
  • Since the derived table is a logical table, indexes cannot be used for faster data retrieval.

 

Posted in:

0 comments:

Post a Comment