- SAP Universe Designer Tutorial
- SAP Universe Designer - Home
- SAP Universe Designer - Introduction
- SAP Universe Designer - Object Types
- SAP Universe Designer - User Interface
- Creating, Editing and Saving
- Creating Parameters
- Creating Schemas
- Table Browser
- Using Derived Tables
- Using Formula Bar
- Universe Building
- List of Values (LOVs)
- OLAP Data Sources
- Using Connection Wizard
- Creating Calculated Measures
- Aggregate Awareness
- Deploying Universe
- Managing Access of Universe
- User and Groups
- UDT vs. IDT Comparison
- unv File to unx File
- Job Responsibilities
- SAP Universe Designer Resources
- SAP Universe Designer - Quick Guide
- SAP Universe Designer - Resources
- SAP Universe Designer - Discussion
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.
