Search

Custom Search

Sunday, October 20, 2013

Derived Table in SAP Business Objects Universe Designer

 

Overview:

Derived table is one of the features provide by SAP business objects universe designer. It is a logical table created on the semantic layer level [Universe] and will be executed at run time. This is different from physical table as physical table store data and can be manipulated by DDL and transactional statement. While in Derived table it acts like database view.

 

How to create and implement Derived tables:

  • Open universe designer and right click on any empty space in the right ban (physical layer) OR go to insert menu and select derived table
  • Type a proper name for your derived table.
  • Write the SQL select statement that will be used to define your derived table in: “Enter SQL Expression Area”
  • You can use “Table & Columns” panel in the bottom left corner to make it easier for you when selecting the required column in your derived table definition.
  • You can select a derived table from derived table panel to create a nested derived table.
  • You can select the required operator from the operator panel in your calculated fields.
  • Check syntax of your derived table definition and resolve SQL errors if any.
  • Click ok to complete your derived table.
  • Now you will be able to see your derived table in the physical layer and you can start use it as a normal table (Join, Create objects,…etc)

Derived table Rclick

Derived table Insert Menu

 

Using Prompt in derived table:

You can use a prompt in the definition of derived table. When you use any object related to this derived table it will prompt the end user to enter the required values. You can use prompt in the where clause as well as in calculated column definition.

 

Nested Derived Table:

Nested derived table is a derived table created on top of another derived table. This is a new feature in BO XI 3.0 and onward. You will use one derived table to define the other one. Normally we use nested derived tables if we want to simplify the design when have a very complex business logic. In that case we will build small derived tables and we will start using them in the bigger one. Please note that you can create as many derived tables as you wish but you only allowed to nest up to 20 level of derived table.

 

List of derived tables:

for very large universe it will be impossible to manage your derived tables if you don’t have a centralized place to access them. List of derived table window will give you that function. to access it click on tools menu, then select “List of derived tables…”. you can edit remove or add derived tables from that window.

Tools list of derived table

List of Derived tables

 

Derived table best practice, advantages & disadvantages:

  • Write your derived table name in capital letters and without spaces. You can use “_” as a separator between words. Example: AB_EXAMPLE_TABLE_DRV
  • Use “_DRV” as a suffix to your derived table name to segregate between derived tables and other tables (Aliases & physical tables)
  • If you have a permission to create a DB view, then it is better to use it and imported to your physical layer.
  • Use derived tables in the following cases:
    • If you need to use a prompt inside your table.
    • If you have a complex logic to be implemented and you can do it from report level.
    • If you have one big lookup table and you want to create specific mini lookups.
  • Always check syntax before creating derived table.
  • Give calculated columns a meaningful name as it will be displayed in the table view.
  • Use nested derived tables when you have very complex business logic. This will make your universe more readable and understandable by other developers.
  • You may encounter a bad performance when using derived table as it is a logical table and there is no data stored in it. You can create an index or adjust table space or do any performance enhancement on your derived table. But on the other hand you can be carful by optimizing the performance for the SQL select statement used in the derived table definition.
  • Add SQL comments in the definition of your derived table to explain your derived table and make it easier for other developers to know why this derived table was created. You can also describe business logic and column definition.
  • Remember this constrain: Nesting derived table is limited to 20 level .

Derived Table Examples:

Derived table:

Drived Table # 1

Nested Derived Table:

Nested Derived table

Derived table with prompt:

Prompt in Derived Table