How do I...Mimic Replacement Path with Query using Customer Variable Exits?

[david_schuh]

Hello SDN:

We are on BW 3.5 SP16.

Our business need is to retrieve a set of values to use as filter values. Much like the often cited "top 5 products" problem in Query replacement examples.

Our problem is performance: If the user does not enter any values in the replacement path query the replacement path query returns many (10000) rows. You can imagine what this does to initial query performance. The user must be free to use the query this way and we must have the FUNCTIONALITY of replacement path. We have been directed to improve performance.

A little experimenting leads us to believe that we could mimic replacement path functionality in the customer variable exits, executing a SELECT statement inside the customer exit based on user input to bring back the value set.

- If no user variables entered DO NOT execute internal select.

- If user variables entered execute SELECT (usually with small return set) and populate our user exit variables.

Resulting in a nice performance improvements.

Specifically;

There are 3 user enterable variables related to this problem

These variables are ready for input, optional entry and selection option.

lets call them

VXMNG (manager)

VXDM (division manager)

VXGM (group manager)

What I have working:

Customer variable exits.

Using i_step=3 case we can capture the user's variable settings from i_t_var_range.

Use these values and FREE_SELECTIONS_RANGE_2_WHERE to build, execute and retrieve our values from a dataprovider.

Now I have the values.

Now comes the complicated part.

We must discard the USER ENTERED VXMNG, VXDM and VXGM variable values

(i.e. do not use as filter values).

And replace VXDM and VXGM with nothing (empty-do not use as filter)

And replace VXMNG with values retrieved from our select statement.

Execute the query

How do I get my internally selected filter values to work with the query. Nothing I've tried works so far. Have studied and tried many examples found on SDN but none that I've seen quite address this challenge.

The standard copy into e_t_range does not seem to behave as I expected (I have many questions about how e_t_range works...not the least of which is how does it know to which variable/characteristic it is associated with).

I've been deep searching and reading SDN and SAP notes for about a week and do not find threads which directly address this situation.

We are also exploring the possibility of conditional replacement path execution.

(different topic subject="How do I...Conditionally control Replacement Path Query execution?")

Many thanks for any help

David Schuh

[former_member184494 · Accepted solution]

David ,

I am unable to understand your question , let me try and rephrase it ....

You have 3 customer exits running and the related variables are optional entry. You are facing a problem when the user does not enter any value and the query returns the whole set . For this you want to have an internal select which does a select statement only if the user inputs something or does nto execute the internal select.

Questions :

Is this the expected behavior ?

Is this the expected behavior for all three variables ?

Are these variables single entry or multiple entry or range?

Is this query on the web through Portal or on Bex ?

Possiblities you could try :

1. Cache the full query and see if it improves performance

2. I think you can catch the user entry within the user exit and accordingly choose to run or not run the select statement.

Please correct me if I haveassumed something that was not meant to be. and also please do not post the same post twice ... did not know which one to answer to ...

Arun

[david_schuh · Accepted solution]

Arun;

Thank you for your response.

Our current situation is that we are using a replacemtne path query to populate variable values in the main query(s).

If users do not enter any variable values for the replacement path query variables, the replacement query returns a large value set. This results in a significant performance hit while the OLAP engine processes these pre-query results into the main query. I have verified this using TX ST05 and empirically (execution times are long when the variable processing is used. Execution time is much shorter when filtering using the navigation block (no variable processing)).

So our goal is to mimic the functionality of Replacement Path Query using customer exit variables. In this way we can control whether the pre-query is used or not.

To directly answer your questions:

Yes, we have 3 variables with customer exit processing.

Yes, we can determine when to execute the internal select statement.

The problem is getting those values returned by the internal select to be used by the OLAP engine as filter values for one of the variables.

All three variables can be left blank by the user.

All three variables are defined as Selection Option (full range of user choices, single, multiple, range, include or exclude) Entry Optional and Ready for input.

The querys are web and BEx, not through portal at this time.

1) Caching the full query will not help us as the performance degradation stems from processing a prequrey result set the replacement path that is too large. Which is why we are trying to mimic the behaviour with customer exit.

2) Yes we can and do have this part working. It is using the results from the conditionally executed internal select statement that is our difficulty at this time.

Thank you very much for trying to follow what is perhaps too long a description. I will try to regroup and distill it to its essence.

My appologies about the double subject. I had two different posts and accidentally left the subject the same.

Thank You

David Schuh

Here is some of the essential code used:

IF i_step = 3.

[snip]

*...Convert the range table to a where clause (WBS)

CALL FUNCTION 'FREE_SELECTIONS_RANGE_2_WHERE'

EXPORTING

field_ranges = lt_tab_range

IMPORTING

where_clauses = lt_where_wbs.

IF sy-subrc EQ 0.

*...Prepare WHERE CLAUSE

READ TABLE lt_where_wbs

INTO ls_where_wbs "with key tablename = '/bic/zviswbs'

INDEX 1.

IF sy-subrc EQ 0.

*...select statement for zviswbs

SELECT /bic/zllwbs FROM /bic/pzviswbs

INTO TABLE l_t_zllwbs

WHERE (ls_where_wbs-where_tab)

AND objvers EQ 'A'.

ENDIF.

ENDIF.

DESCRIBE TABLE l_t_zllwbs LINES sy-tfill.

IF sy-tfill GE 0.

CLEAR intern_range.

intern_range-vnam = 'VXWBSMNG'.

intern_range-iobjnm = 'ZLLWBS'.

intern_range-sign = 'I'.

intern_range-opt = 'EQ'.

LOOP AT l_t_zllwbs.

intern_range-low = l_t_zllwbs-/bic/zllwbs.

APPEND intern_range TO i_t_var_range.

ENDLOOP.

LOOP AT i_t_var_range INTO loc_var_range

WHERE vnam = 'VXWBSMNG'.

CLEAR l_s_range.

l_s_range-low = loc_var_range-low.

l_s_range-high = loc_var_range-high.

l_s_range-sign = loc_var_range-sign.

l_s_range-opt = loc_var_range-opt.

APPEND l_s_range TO e_t_range.

ENDLOOP.

ENDIF.