senthil

• Jun 30th, 2006

 

hi

    it is similar to views,it is mainly used to restrict the datas

MuthuKumar

• Jul 21st, 2006

 

Can u pls give me the exact defination and examples ?

Senthil Raj

• Aug 3rd, 2006

 

we cont use correlated subquery in BO 6.5( Im not sure with other version).

In such cases we use Derived tables. 

pratip

• Jan 10th, 2007

 

respected all

Actually am a BO XI beginner ,so have stuck in  the basic concept of derived table..if any one explain it for me,i ll be grateful to them,and plz explain in which context i should use the derived table.

thankyou

pratip

Vineela

• Apr 26th, 2007

 

Derived tables are not real tables at the Database level , but are the selection of few columns in the Table with some aggregations, specific to the report.

This feature is available in BO 6.5 and later versions.

Sel a1,a2,a3,max(a4)

from a ,

(Sel b1, b2, max(b3) as B3, Min(B4) as B4 from B group by b1,b2) as BX

where BX.B1= a.a1
group by a1,a2,a3
order by a1,a2,a3

The (Sel b1, b2, max(b3) as B3, Min(B4) as B4 from B group by b1,b2)  in the above query is a derived table.

The same definition has to be added in the Universe - by going into Insert >> Derived Tables.

Type the Query - parse it and the new table inserted can be used to any type of join / Contexts etc in the Universe.

kiran

• May 15th, 2007

 

Derived tables is an option in Business Objects Tool(Business Intelligence) from version 6.5.

sunil

• Jun 3rd, 2007

 

For Derived table, i am having one scenario.
My Database table is having following Column
-Empno
-Empname
-Managerno

my 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 like this:
1.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

2. Joining

2. 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 repoer requirement i can create report.

So Bottom line is Derived table plays a major role in this scenario.

goutham438

 Profile Answers by goutham438 

• Jul 31st, 2011

 

derived table is the table which is created in the universe itself

we create the new variable using variable editor in webi or deski in the report panel using a function but it is used for one single report. if we create the variable using derived table using universe itself it can be used for multiple reports and multiple universe's

pujitha

• Sep 14th, 2011

 

Derived tables are tables that you can define in the universe schema. You create objects on them as you do with any other object.

venkat

• Oct 31st, 2011

 

Derived tables can be defined by an sql query at the university level that can be used as a logical table in designer.
It is not physical table in universe schema.The advantage of this derived table is reduced amount of data returned
to the document for analysis.

lionskashif

 Profile Answers by lionskashif 

• Aug 30th, 2012

 

Derived Table is a Virtual and Dynamic table in schema. It is not a Physical table but Act like a Physical table.
why we make that? We can create/store most usable Dimension in it to reuse frequently to access the data.
It saves your time to retrieve the data and giving you better performance in Universe and Reporting side.

cheers.....lionskashif

vyankatesh valakate

• Oct 19th, 2012

 

Derived tables can define the suquery of a Table. derived table has a query in the form of Logical tables in designer to accomplish task by writting query in query panel.

vishwanath_bhambure

 Profile Answers by vishwanath_bhambure 

• Oct 18th, 2013

 

Derived tables are somewhat like Views in the database. we can write select statement to create the such table which contains more than one table and join conditions, filter conditions in their query.

This tables can be used with other normal database tables by joining them. Normal tables in the universe structure points to the one table of physical database. But derived tables can be made by using many tables existed in the physical database.

sudheerv2k

 Profile Answers by sudheerv2k 

• Nov 25th, 2013

 

Advantages of derived table:

It can be created using an SQL statement with complex expressions, joins and prompts which are not possible to create in BO Universe using normal approach.
It acts just like a normal table in the universe and can be used to join with other database tables and derived tables present in the universe.
Multiple levels of nested derived tables can be created, in which, one derived table is used in another derived table query.
Since a derived table is based in a universe and is not dependent on any DDL, there’s no need for any interaction from the DBA or ETL team for its creation.
Changing the structure of the derived table is as easy as changing the SQL statement it is made up of.
Disadvantages of derived table:

Derived tables do not store data and hence every time a report using this table is run, the whole SQL query is executed. This may cause poor performance of the report.
If the database tables used in the derived tables are huge and the query is complex, it may cause memory issues on the server.
Since the derived table is a logical table, indexes cannot be used for faster data retrieval.

bharath

• Oct 21st, 2014

 

actually derived tables are database view only...... like aggregate tables(materialized views) but aggregate tables are created in ETL developers in database leval but Derived tables are created by bo developers in universe level..... created use of custom sql.........

Praveen Guntuka

• Oct 31st, 2014

 

Derived table is like view in SQL. We can insert multiple columns from multiple tables into it. We can write complex code to fetch the user required data. These tables cannot holds data, whenever we use the objects from Derived table then it will hit the database and brings the data. we can use this tables to avoid clumsiness in universe schema. instead of using multiple tables we can insert required fields in Derived table and we can use that in Universe

Akhil Rawat

• Nov 12th, 2014

 

Derived Tables are the tables which are created in the Universe. These are basically used for the databases which are not accessible by the users. One can create Derive Table using the name of the columns which the user wants to access.

Raju

• Jul 8th, 2015

 

Derived table we can use in universe design tool, it is a table by using sql sytax we will create it derived table, main we can create this table by using multiple data providers..