on 08-18-2011 5:58 AM
Hello,
Plz Ans me-
What is Derived table, where it is using derived table? advantages& Di- advantages derived table?
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
could any body give examples for derived table with detail?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.