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:

1.PNG

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

2.PNG

Steps to create an InfoSet:

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

3.PNG

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

4.PNG

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.

5.PNG

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

6.PNG

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.

7.PNG

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

8.PNG

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:

9.PNG

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

10.PNG

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:

11.PNG

1.    

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

12.PNG

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

13.PNG

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:

13.PNG

1. 

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

14.PNG

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.

15.PNG

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.

16.PNG

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:

17.PNG

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:

18.PNG

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

19.PNG

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.

To report this post you need to login first.

26 Comments

You must be Logged on to comment or reply to a post.

  1. Bala Krishna Gadekari

    Dear Nitesh,

    Its a nice document . well explained with examples.

    Coul dyou please eloberate the below points which you mentioned at the end.

    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.

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

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

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

    BeBest Regards,

    KKrishna.

    (0) 
    1. Nitesh Kumar Post author

      Dear Bala,

      Thank you for liking this document. Explanations for these points are below:

      1. To apply inner join on at least 1 InfoProvider is MUST in the design of InfoSet. Since we are planning to apply Antijoin at 1 InfoProvider, join type for second is mandatory as inner join.

      2.  To apply inner join on at least 1 InfoProvider is MUST in the design of InfoSet.

      3. At InfoCube side, we can apply either inner join or Antijoin.

      4. Since Infoset follows the join concept, it degrades the performance if handled with many InfoProviders.

      5. When we apply inner join, only matched data for both Infoproviders are checked. In case of Antijoin, volume of data decreases more. But in case of left outer join, volume of data increases as it gives the matched data as well as some additional data. So, in left outer join, performance decreases.

      Hope it clarifies your queries.

      Regards,

      Nitesh

      (0) 
      1. Bala Krishna Gadekari

        Dear Nitesh,

        Thanks for your explanation.

        With reference to the point5, Inner join is more performance effected join when compared to the left outer join.

        In case of Inner join always the join condition is performed, even though if we wont check any field from right table unlike Lefet Outer join.

        Correct me if am wrong.

        Best Regards,

        Krishna.

        (0) 
        1. Nitesh Kumar Post author

          Dear Krishna,

          Cheking the fields at right table only decides whether that field will be displayed in the result or not.

          Either we apply inner join or Left outer join, join condition is always performed irrespective of its type.

          You can understand it in other way. Result set of inner join is always a subset of Left outer join. So, since left outer join results a bigger outcome, it decreases the performance. Hope I clarified the things. Please revert in case of any further query.

          Regards,

          Nitesh

          (0) 
    1. Nitesh Kumar Post author

      Dear Suman,

      Thank you so much for your compliments.

      I will try my best to come up with some real time scenarios on more topics.

      Regards,

      Nitesh

      (0) 
  2. berna consultant

    hi Nitesh,

    a very good document for infoset.

    However, I am not still clear on the left outer join scenerio.

    Is it possible to show the records in DSO (which is not available in infocube) with left outer join ?

    I am looking for an answer, since I am in need of a reporting where I need to work on the non sales products.

    appreciate your reply.

    cheers,

    berna

    (0) 

Leave a Reply