What is Derived table in BO Universe?

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.

DT1Add Derived table Name in Capital Letters and keep “_” (under Score) between Characters, so that it’s easy to identify.

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.
DT2How 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.
DT3You can also view the list of available derived tables in universe. Tools –List of Derived tables.

DT4DT5Below are advantage and disadvantages of using Derived tables.

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

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

Advertisements

3 thoughts on “What is Derived table in BO Universe?

  1. It’s hard to find your page in google. I found it on 20 spot,
    you should build quality backlinks , it will help you to get more
    visitors. I know how to help you, just type in google – k2 seo
    tricks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s