What is Derived Table in SAP BusinessObjects

How to Implement Derived Table

• Open/Import a universe.

• Click Insert -> Derived Tables OR right click on an empty space in the universe canvas and click derived Tables.

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

• 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

• Click "Check Syntax". If the SQL gives error, debug it. If the SQL parses correctly, click OK.

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

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

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.