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.

    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.

    Derived Table SQL Expression

  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

    Derived Table Check Syntax

  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.

    Derived Table in Universe

  7. Link the derived table with the database tables if required using appropriate joins and contexts.

    Derived Table Joined with other table

Advantages of derived table:

  • 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 tables and derived tables present in the universe.
  • Multiple levels of nested derived 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 derived table:

  • 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.

Points to remember:

  • Use Derived Tables as a last resort since it slows down the execution of reports.
  • Use only when an urgent (but temporary) fix is required and ETL implementation of this fix will take time.
  • Use only when there’s a requirement for a prompt to be embedded in the table structure.
  • Use only when the BO universe methods are incapable of creating an implementation with complex joins and calculations.


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.