What Is Derived Table.

Derived tables are not real tables at the Database level , but are the selection of few columns in the Table with some aggregations, specific to the report.

This feature is available in BO 6.5 and later versions.

Sel a1,a2,a3,max(a4)

from a ,

(Sel b1, b2, max(b3) as B3, Min(B4) as B4 from B group by b1,b2) as BX

where BX.B1= a.a1
group by a1,a2,a3
order by a1,a2,a3

The (Sel b1, b2, max(b3) as B3, Min(B4) as B4 from B group by b1,b2)  in the above query is a derived table.

The same definition has to be added in the Universe – by going into Insert >> Derived Tables.

Type the Query – parse it and the new table inserted can be used to any type of join / Contexts etc in the Universe.

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.

Add Comment
0 Answer(s)

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.