Info Set ( Join )
Info Set is a Virtual Provider. Info Sets allow you to analyze the data in several Info Providers by using combinations of master data-bearing characteristics, Info Cubes and Data Store objects. The system collects information from the tables of the relevant Info Providers. If you are joining large sets of data from the master data or from DSO objects, SAP recommends that you use an Info Set. This improves performance as fewer temporary tables are required and the join is executed in the database itself.
Joins are 4 types
: 1) Left outer Join
: 1) Left outer Join
2) Right outer Join.
3) Temporary Join...based on any Date field.
4) Equal Join
Inner Join:
In case of inner join there should be an entry in all the tables use in the view.
Outer Join:
With the use of outer join you can join the tables even there is no entry in all the tables used in the view. Inner join between table 1 and table 2, where column D in both tables in the join condition is set the same:
Table 1 Table 2
Inner Join
---- ---- ---- ---- ---- ---- ---- ---- ----
A B C D D E F G H
---- ---- ---- ---- ---- ---- ---- ---- ----
a1 b1 c1 1 1 e1 f1 g1 h1
a2 b2 c2 1 1 e1 f1 g1 h1
a4 b4 c4 3 3 e2 f2 g2 h2
---- ---- ---- ---- ---- ---- ---- ---- ----
Left outer join between table 1 and table 2 where column D in both tables set the join condition: Table 1 Table 2
A B C D D E F G H
a1 b1 c1 1 1 e1 f1 g1 h1
In case of inner join there should be an entry in all the tables use in the view.
Outer Join:
With the use of outer join you can join the tables even there is no entry in all the tables used in the view. Inner join between table 1 and table 2, where column D in both tables in the join condition is set the same:
Table 1 Table 2
Inner Join
---- ---- ---- ---- ---- ---- ---- ---- ----
A B C D D E F G H
---- ---- ---- ---- ---- ---- ---- ---- ----
a1 b1 c1 1 1 e1 f1 g1 h1
a2 b2 c2 1 1 e1 f1 g1 h1
a4 b4 c4 3 3 e2 f2 g2 h2
---- ---- ---- ---- ---- ---- ---- ---- ----
Left outer join between table 1 and table 2 where column D in both tables set the join condition: Table 1 Table 2
A B C D D E F G H
a1 b1 c1 1 1 e1 f1 g1 h1
a2 b2 c2 1 3 e2 f2 g2 h2
a3 b3 c3 2 4 e3 f3 g3 h3
a4 b4 c4 3 --- ---- ------------
Left Outer Join
a4 b4 c4 3 --- ---- ------------
Left Outer Join
A B C D D E F G H
a1 b1 c1 1 1 e1 f1 g1 h1
a2 b2 c2 1 3 e2 f2 g2 h2
a3 b3 c3 2 4 e3 f3 g3 h3
a4 b4 c4 3 --- ---- ------------
-----------------------------------------------------
A B C D D E F G H
------------------------------------------------------
a1 b1 c1 1 1 e1 f1 g1 h1
A B C D D E F G H
------------------------------------------------------
a1 b1 c1 1 1 e1 f1 g1 h1
a2 b2 c2 1 1 e1 f1 g1 h1
a3 b3 C3 2 NULLNULLNULLNULLNULL
a4 b4 c4 3 3 e2 f2 g2 h2
What makes difference between Inner Join & Left Outer Join
Inner join returns only the matching records from both tables.Left outer join returns complete details of left table which are matching with right table and non matching records also.
The data that can be selected with a view depends primarily on whether the view implements an inner join or an outer join. With an inner join, you only get the records of the cross-product for which there is an entry in all tables used in the view. With an outer join, records are also selected for which there is no entry in some of the tables used in the view.
The set of hits determined by an inner join can therefore be a subset of the hits determined with an outer join.
Database views implement an inner join. The database therefore only provides those records for which there is an entry in all the tables used in the view. Help views and maintenance views, however, implement an outer join.
Temporal Join
Join containing at least one time-dependent characteristic For example, a join contains the following time-dependent Info Objects (in addition to other objects that are not time-dependent).
InfoObjects in the join Valid from Valid to
Cost center (0COSTCENTER) 01.01.2009 31.05.2009
Profit center (0PROFIT_CTR) 01.06.2009 31.09.2009
Where the two time-intervals overlap, means the validity area that the Info Objects have in common, is known as the valid time-interval of the temporal join.
Temporal join Valid from Valid to
a3 b3 C3 2 NULLNULLNULLNULLNULL
a4 b4 c4 3 3 e2 f2 g2 h2
What makes difference between Inner Join & Left Outer Join
Inner join returns only the matching records from both tables.Left outer join returns complete details of left table which are matching with right table and non matching records also.
The data that can be selected with a view depends primarily on whether the view implements an inner join or an outer join. With an inner join, you only get the records of the cross-product for which there is an entry in all tables used in the view. With an outer join, records are also selected for which there is no entry in some of the tables used in the view.
The set of hits determined by an inner join can therefore be a subset of the hits determined with an outer join.
Database views implement an inner join. The database therefore only provides those records for which there is an entry in all the tables used in the view. Help views and maintenance views, however, implement an outer join.
Temporal Join Join containing at least one time-dependent characteristic For example, a join contains the following time-dependent Info Objects (in addition to other objects that are not time-dependent).
InfoObjects in the join Valid from Valid to
Cost center (0COSTCENTER) 01.01.2009 31.05.2009
Profit center (0PROFIT_CTR) 01.06.2009 31.09.2009
Where the two time-intervals overlap, means the validity area that the Info Objects have in common, is known as the valid time-interval of the temporal join.
Temporal join Valid from Valid to
Valid time-interval 01.03.2009 31.05.2009
You define an Info Set via the characteristic PROFIT Center, which contains the responsible person (RESP) as the time-dependent attribute and the characteristic COSTCENTER that also contains person responsible as a time-dependent attribute.
These characteristics contain the following records:
Profit Center Responsible Person DATEFROM* DATETO*
BI A 01.01.2009 30.06.2009
BI B 01.07.2009 31.12.9999
Cost Center Profit Center Responsible Person DATEFROM* DATETO*
4711 BI X 01.01.2009 31.05.2009
4711 BI Y 01.06.2009 31.12.2009
4711 BI Z 01.01.2009 31.12.9999
If both characteristics are used in a join and connected via PROFITCENTRE, it is not true that all six possible combinations are valid for the above records. Instead only the following four:
PROFITC RESP Cost Center Profit Center Responsible Person
BI A 4711 BI X (01.01.2009-31.05.2009)
BI A 4711 BI Y (01.06.2009-30.06.2009)
BI B 4711 BI Y (01.07.2009-31.12.2009)
BI B 4711 BI Z (01.01.2009-31.12.9999)
Equal Join
A join condition determines the combination of records from the individual objects that are included in the resulting set. Before an Info Set can be activated, the join conditions have to be defined in such a way (as equal join condition) that all the available objects are connected to one another either directly or indirectly.
An Equal Join is possible only with Same Values Technical Requirement is such way that both the Values has same Data Type & Length for Equal Join.
Equal Join
Table1 Table 2
Equal Join
X ------------------- X
You define an Info Set via the characteristic PROFIT Center, which contains the responsible person (RESP) as the time-dependent attribute and the characteristic COSTCENTER that also contains person responsible as a time-dependent attribute.
These characteristics contain the following records:
Profit Center Responsible Person DATEFROM* DATETO*
BI A 01.01.2009 30.06.2009
BI B 01.07.2009 31.12.9999
Cost Center Profit Center Responsible Person DATEFROM* DATETO*
4711 BI X 01.01.2009 31.05.2009
4711 BI Y 01.06.2009 31.12.2009
4711 BI Z 01.01.2009 31.12.9999
If both characteristics are used in a join and connected via PROFITCENTRE, it is not true that all six possible combinations are valid for the above records. Instead only the following four:
PROFITC RESP Cost Center Profit Center Responsible Person
BI A 4711 BI X (01.01.2009-31.05.2009)
BI A 4711 BI Y (01.06.2009-30.06.2009)
BI B 4711 BI Y (01.07.2009-31.12.2009)
BI B 4711 BI Z (01.01.2009-31.12.9999)
Equal Join
A join condition determines the combination of records from the individual objects that are included in the resulting set. Before an Info Set can be activated, the join conditions have to be defined in such a way (as equal join condition) that all the available objects are connected to one another either directly or indirectly.
An Equal Join is possible only with Same Values Technical Requirement is such way that both the Values has same Data Type & Length for Equal Join.
Equal Join
Table1 Table 2
Equal Join
X ------------------- X
