Thursday, March 4, 2010

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
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
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

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
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

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

4 comments:

Steve said...

Hi..it is so nice of you to share such an informative things..thanks for sharing and keep going on..

Regards,
Pavan Kumar GRS.

Unknown said...

hi i raj ,, iam sap consulatnt its very nice blog .blog has effective information on sap-bi

Unknown said...

hi i raj ,, iam sap consulatnt its very nice blog .blog has effective information on sap-bi

Unknown said...

Very informative article. This is usefull for many people who are reading it.
Learn Master Data Loading Attributes and Text Actual cost of this course is 3,200/- But now Kuncham Software Solutions Pvt ltd is giving you Free access. Limited period offer Hurry-up Enroll Now at Udemy.

Hurry-Up Enroll Now and get Free Access to the Entire Course