‘Antijoin’ – New join type in InfoSet – SAP BW 7.3

Exploring InfoSet

InfoSets are logical InfoProviders that logically join data and provide this data for BI queries. This can be created based on InfoCube, DSO and Master Data InfoObject.  All the BEx and OLAP services are available (authorizations, texts, variables, hierarchies, calculated key figures) except navigational attributes of InfoSet characteristics.  The base concept behind InfoSet is join condition wherein data from different InfoProviders are collected at run time and produced in a single row.

When to use InfoSet:

·         To join required data from basic InfoProviders

·         To allow BEx Reporting on a DataStore object without turning the BEx Reporting indicator on

·         To evaluate time dependencies (for example, join time dependent master data InfoObjects)

·         To be able to create self joins and left outer joins

·         To  be able to create Antijoin (Functionality provided in version 731)

Join concepts:

·         Inner join: A record can only be in the selected result set if there are entries in both joined tables

·         Left outer join: If there is no corresponding record in the right table, the record is part of the result set (fields belonging to the right table have initial values)

·         Antijoin: Only those results are displayed which are present in other InfoProvider.

·         Temporal join: A join is called temporal if at least one member is time-dependent.

·         Self join: The same object is joined together

I have explained below the joins available in version 731. In the following example, I have taken an InfoCube and a DSO to create an InfoSet.

InfoCube contains Employee Number, Name of Employee and Salary of Employee. The data in InfoCube is as follows:

DSO contains Employee Number, Name of Employee and Age of Employee. The data in DSO is as follows:

Steps to create an InfoSet:

1.    1.  Right click on InfoArea. Select ‘Create InfoSet’.

1.   2.   Enter the required entries.  Select the InfoProvider to start with. Press ENTER. Here we have selected InfoCube as first InfoProvider for InfoSet.

1.  3.    We get the following screen. Here we can decide to choose the fields that have to be included in InfoSet. We have included all fields of InfoCube for InfoSet.

1.   4.   Now we have to choose the second InfoProvider for InfoSet.  Search for the required DSO.

1.  5.    After selecting the DSO, select only the required fields from DSO and connect InfoCube and DSO based on Employee Number. Connect both with inner join.

1.   6.   Check, Save and Activate InfoSet. Now check data of InfoSet. We get the following result set:

Since the above created InfoSet is having Inner join, it contains the record only for those employee number (Common field) which are present in InfoCube and DSO both.

InfoSet with Left Outer Join:

1.       Now we edit the InfoSet and change the join type to ‘Left Outer Join’ as given below:

2.       Select Left Outer Join. Check, Save and Activate InfoSet. Now check the data. We get the following result set:

Since it is Left Outer join, so all the data from left entity i.e. InfoCube is displayed here.  Employee Number 4 is present in cube, so it’s salary is displayed. Since Age is coming from DSO and employee number 4 is not present in DSO, so, Age is not displayed.

InfoSet with Antijoin:

Case 1: Antijoin applied at DSO

1.   1.   Now we edit the InfoSet and change the join type to ‘Antijoin’ at DSO side as given below:

1.    

1.  2.    After selecting join type as Antijoin at DSO side, all the fields at DSO side become inactive as shown below:

1.   3.   Check, Save and Activate InfoSet. Now check data of InfoSet. We get the following result set:

Important point to note here is that only those records are displayed which are present only InfoCube.

Case 2: Antijoin applied at InfoCube

1.    1.  Now we edit the InfoSet and change the join type to ‘Antijoin’ at InfoCube side as given below:

1. 

1.   2.   After selecting join type as Antijoin at DSO side, all the fields at DSO side become inactive as shown below:

1.   3.   Further we have an option to select unchecked fields of DSO. We select all fields here. Now activate InfoSet and display data. We find following Result set.

We find that only those records are displayed which are present only in DSO.

Order of InfoProviders matters in InfoSet in case of Left Outer Join:

If we create an InfoSet starting with DSO and connecting to InfoCube using inner join or Antijoin, then results are similar to the above scenario. But if we use join type as ‘Left Outer Join’ then we counter some different result. I am going to explain this scenario below:

1.   1.  Create an another InfoSet taking same InfoCube and DSO but starting with DSO, and join type is left outer join, we get below screen.

Our expected output would be like below:

Employee Number	Name of Employee	Salary of Employee	Age of Employee
1	ABC	40,000	30
2	LMN	75,000	55
3	XYZ	35,000	25
5	ASD	0	28

1.    2.    Check, Save and Activate InfoSet. Now check data of InfoSet.

We expect the above result because employee number 1, 2 and 3 are common in InfoCube and DSO both, and employee number 5 is present in DSO (Left entity).

But we get the following result set:

Here we find the results only from InfoCube. Whichever fields are selected in InfoSet design from InfoCube, those get displayed. No data from DSO is displayed.

1.    3.  Select all fields of InfoCube as shown below:

1.   4.   Check, Save and Activate InfoSet. Now check data of InfoSet.

Reason for this is left outer join cannot be applied at InfoCube side. At InfoCube side, we can apply either inner join or Antijoin.

Important points to remember:

           1. If we want to apply Antijoin on an InfoProvider, second InfoProvider must have Inner join.

           2. At least one InfoProvider must have Inner join.

                 3. At InfoCube side, left outer join cannot be applied.

                 4. We should avoid taking many InfoProviders in a single InfoSet.

                 5. We should avoid Left outer type as it degrades the performance.