SAP Universe Designer - Using Derived Tables

Using Derived table, you can limit the data returned from a table in Universe. Derived table are created based on SQL query at Universe level and can be used as logical table while creating Universe.

Following are the advantages of using Derived table in the Universe −

• Derived tables can be used to perform complex calculations

• They are used to reduce maintenance of database summary fields. Instead of using aggregate tables in Universe, you can use derived tables to return same data.

Adding a Derived Table to Universe

To add a derived table to Universe, navigate to Insert → Derived Tables.

This will open Derived table dialog box. You need to enter Derived table name and SQL query to select data in derived table. You can also make use of tables and columns along with Operators and Functions to create expression for derived table.

The derived table appears in the schema with the physical database tables and you can build the objects on derived tables.

You can click on check syntax button to validate SQL Expression.

Editing a Derived Table

To edit a derived table, you need to right-click the table in UDT schema and select Edit Derived Table from the shortcut menu as given below −

Deleting a Derived Table

In Universe schema pane, select the derived table that you want to delete, and use delete key to delete the table.

You can also create a nested derive table which is derived from an existing derive table. To create nested derived table, you need to enter SQL expression for the nested derived table based on a derived table. You can also make use of @DerivedTable function as a reference to the derived table in your nested derived table. This function @DerivedTable(Derived_table_name) is included in the functions catalog in the Derived Tables editor.

You can also rename a derived table, and new name is updated in all other derived tables that reference it. To rename, select Derived table and right click Rename Table.