We usually do the Infoprovider selection based on user input to improve Query performance when the underlying Multiprovider has many Infocubes with huge data.
There would be a scenario where 2 BEx queries on the same multiprovider connected via RRI needs to do the Infoprovider selection at runtime, but the problem is the second(receiver) query don’t have any user inputs. So the second query needs to select the infoprovider based on user input from the first(Sender) query.
So let’s see how we can handle this. There would be many approaches, but i came across only this one.
To store the First query variable values temporarily in the INDX table and the same can be utilized in the second one.
The reason for using INDX table:- This table is available in all SAP systems, so no need to create any new objects for this purpose.
Here for example, I have a Multiprovider with 2 cubes. If date >= 19.10.2012,use cube 1, else cube 2. I just took the simple logic here.
Date variable 1 :- Customer exit type, user input
Infoprovider provider variable :- Customer exit, no user input
In I_step 2:- Logic written for the infoprovider selection based on user input date.
* had included the another date variable as well here which is from Second query.
In I_Step 3:- Logic for writing the date variable values in INDX table.
the values are stored with an indicator,so that the second query can identify this value based on the same indicator.
here i used indicator as User ID and System date.It can be changed according to requirements.
On executing the query, you can see an entry created in the table.
Ok now done with the first query logic. now moving to second query:-
Second query :-
date variable 2 :- customer exit, no user input
same infoprovider variable(can be used different as well)
** Suppose we are using bookmarks on the second query, we need to add the date variable in default values as well,to make use of this logic when running bookmarks.
In I_step1:- logic to read INDX table entries and fill with this date variable
(not the complete code)
so used the same indicator to read the database table entry and populated the date variable of the second query.
Done with the logics.
Only one cube had accessed.
Same thing when accessed from another cube:-
**The database table entries can be cleaned up later on ,may be end of User session or end of the day or weekly once.
And this concept can be used for any logic between 2 queries, not only for Infoprovider selection.
Hope I made the concept clear and there could be different approaches as well. Please share your thoughts on this, if we could have approached different way.