Derived table is not a physical in database however its logical table created in Business Objects Universe using SQL. Derived table can be considered like views in database where its structure is defined using SELECT statement.
Create Derived table in universe Designer:
Either goes to Insert – Derived table or right click in the universe Structure and click Derived table, the new Derived table editor will open.
Then write your required SQL into the SQL Expression section and check the syntax. Make sure you are providing some meaningful column name in the Derived Table SQL, which will be used in the Object Select section.
How to Check Derived table list?
Derived tables will have different look in the universe structure compared to normal tables and views. Derived tables are highlighted in blue color and will display as shown below.
You can also view the list of available derived tables in universe. Tools –List of Derived tables.
• SQL Statements with complex joins, expressions and conditional prompts can be used to create derived tables when those are not possible in BO universe through normal tables.
• Derived table will reduce the amount of data returned to the document for analysis.
• Derived table will reduce the maintenance effort of database tables. 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.
• Derived tables can be used as a normal table in the universe and do join with other tables.
• Many levels of nested derived tables can be created using @derivedTable function. Example, You have Derived table DRV_SALES and want create another derived table by using DRV_SALES, then create “Select * from @DerivedTable(DRV_SALES)”. Nesting derived table is limited to 20 levels.
• To Update / Change the derived table structure in the universe is very easy.
• You can merge data from different table which is not possible using normal in universe using underlying data sources.
• You can embed prompts in derived table definition.
• Derived table cause poor performance of the report because the derived table do not stored the data and it will execute whole SQL each report run.
• Derived tables may cause memory issue on the server, if the database tables used in the derived tables are huge and the query is complex.
• Index cannot be created using derived table since it’s a logical table.
Thinks to Remember:
• Use only when you need complex joins and calculations between tables in the BO universe then go with Derived table.
• Derived table option should be selected when you have an urgent fix and ETL/ DB implementation of the fix will take more time.
• If you have an requirement for a prompt to be embedded in the table structure then go with Derived table.