cancel
Showing results for 
Search instead for 
Did you mean: 

Derived table in univ's

Former Member
0 Kudos

Hello,

Plz Ans me-

What is Derived table, where it is using derived table? advantages& Di- advantages derived table?

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hai nanip123 ,

Derived table is the combination of mltiple table columns. The different table colums are mapped into one table is called as a "Derived Table" ...

The derived table is increase the Query perfomence. In the derived table backend the select statement is by default build among all the multipe tables..

By using of derived table the advantages are more than compare to disadvantages.

All the best,

Praveen

Former Member
0 Kudos

Hi,

One of the disadvantage is the maintenance of derived tables in case of back end DB table changes.

Ex:if the column names gets changed in the DB table then it needs to be modified manually in the derived table level.

Other one is the performance of the reports (may be adhoc), for example you have the derived table with t1,t2,t3 tables and with some complex functions etc and you have created some objects out of it. When you create a report by using the columns of one particular table say t1 then still it will process the entire derived table then gets the results. It is also difficult for the adhoc users to understand the logic behind the result set

Former Member
0 Kudos

Hi,

One of the disadvantage is the maintenance of derived tables in case of back end DB table changes.

Ex:if the column names gets changed in the DB table then it needs to be modified manually in the derived table level.

Other one is the performance of the reports (may be adhoc), for example you have the derived table with t1,t2,t3 tables and with some complex functions etc and you have created some objects out of it. When you create a report by using the columns of one particular table say t1 then still it will process the entire derived table then gets the results. It is also difficult for the adhoc users to understand the logic behind the result set

Former Member
0 Kudos

but how do i use a derived table in universe ?

Answers (5)

Answers (5)

Former Member
0 Kudos

could any body give examples for derived table with detail?

Former Member
0 Kudos

Hi Vijendhar,

I found the below simple example,

Suppose my Database table has following Columns
-Empno
-Empname
-Managerno

Report Requirement is like:
report should display Emp name and its corressponding manager name.

Based on this reqquirement, i need to design Universe.

My steps would be:
Looking at the requiement, i would go for Derived table, having name Manger.

Derived Table(Manager) would be having following syntax.

select distinct a.EmpNo,b.EmpName from Emp a,Emp b
where a.ManagerNo=b.EmpNo

Joining already existing table with derived table

Create class Manager and Object Manager Name, Source for this object would be
Derived_table.EmpName that is manager. EmpNo. Here EmpNo is coming from Derived table syntax.

Now my universe is ready.

As per the report requirement i can create report.

Regards,

Mitesh Joshi

Former Member
0 Kudos

Hi Joshi.

Nice example .but i have question can we use distinct keyword in this example?? ...with out also can we get result ...?.for your ex with out derived table aslo we ll get result i think so ....i tried but no issue in result .. can u help me if i am wrong

Regards

Narasimhulu

Former Member
0 Kudos

Hi Ramesh,

Keep it simple : Derived table are

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.

All the best,

Veerabh.

Former Member
0 Kudos

Hi,

Please refer the below link for advantages and disadvantages of derived tables:

http://www.dwbiconcepts.com/reporting-a-analysis/25-business-objects/89-bo-derived-table.html

Hope it helps

Regards,

Santhosh

Former Member
0 Kudos

Derived tables are views maintained within the universe rather than the database.

As such, the positive is that they can support prompts and are fully controlled by the universe designer.

The major down side is performance - the DBA will not necessarily know of the existence of the derived table and as such would not keep the database tuned to perform well with it whereas a good DBA would look at the performance of views on a regular basis.

It's much better to push the code back to a physical table from a performance and maintenance point of view and is certainly a long term strategy that should be undertaken with all derived tables and non-materialised views.

amitrathi239
Active Contributor
0 Kudos

Hi,

Derived tables are nothing else but InLine views (with the one additional benefit of being able to use @prompt syntax in a derived table) and as such do not contain any data, everything is calculated on the fly during query execution, meaning: whenever you refresh a report.

Derived tables are tables that you define in the universe schema. You create objects on them as you do with any other table. A derived table is defined by an SQL query at the universe level that can be used as a logical table in Designer.

Derived tables have the following advantages:

u2022 Reduced amount of data returned to the document for analysis. You can include complex calculations and functions in a derived table. These operations are performed before the result set is returned to a document, which saves time and reduces the need for complex analysis of large amounts of data at the report level.

u2022 Reduced maintenance of database summary tables. Derived tables can, in some cases, replace statistical tables that hold results for complex calculations that are incorporated into the universe using aggregate awareness. These aggregrate tables are costly to maintain and refresh frequently. Derived tables can return the same data and provide real time data analysis.

Derived tables are similar to database views, with the advantage that the SQL for a derived table can include BusinessObjects prompts.

Thanks,

Amit

Former Member
0 Kudos

Hi amit thanks for your reply on derived tables it is clear. Can you please provide me with a scenario when we the derived tables.