Execute a BW query by excluding values from another BW query.

Execute a BW query by excluding values from another BW query. Bhushan Suryavanshi  Business Card Company: Bombardier Aerospace Posted on Aug. 07, 2011 02:12 AM in ABAP, Analytics, Enterprise Data Warehousing/Business Warehouse

Motivation

In SAP BW, usually there is no easy way of performing set operations (union, intersection, outer joins etc.) of two queries. This is usually a manual activity. Basically, post processing using two query results is difficult. Infosets can be used in some scenarios but these need additional modelling steps and not as flexible. This blog illustrates how we can fetch values from one query and then exclude these selected values from another query.

Business Example

Using technical content, you want to find out all infocubes that exists in the system but with no data being fetched from them over the last two years (via queries say).

Let X = {A1, A2, .. AN} be a query that delivers all the cubes in the system.

Let Y = {A1, A2, A3} be the query that delivers all the cubes in use over the last two years i.e. some data was fetched from them.

You want to find Z = X - Y = {A4, A5 .. AN}.

Steps

1) In query X, create a customer exit variable of type Selection option on characteristic A.

 

2) Write the customer exit coding in FUNCTION EXIT_SAPLRRS0_001 include ZXRSRU01. We calculate the variable values set at i_step=1. (How customer exit variables are processed is out of scope of this blog)

 

3) Code sample

Initialization of the request object (Every query execution is represented in BW via a request object).

DATA:

l_r_request TYPE REF TO cl_rsr_request,

l_s_compkey TYPE rszcompkey.

*** Initialization with query Y to be executed from within the ABAP code ***

l_s_compkey-objvers = rs_c_objvers-active.

l_s_compkey-infocube = 'INFOCUBE'.

l_s_compkey-compid = 'QUERY_Y'.   *** executing query Y

l_s_compkey-comptype = rzd1_c_comptp-report.

 

4) Get the internal query id for QUERY_Y

* Get the compkey

CALL FUNCTION 'RRI_REPDIR_READ'

CHANGING

c_s_repkey = l_s_compkey

EXCEPTIONS

entry_not_found = 1.

IF sy-subrc <> 0.

MESSAGE s792(brain) WITH l_s_compkey-compid.

EXIT.

ENDIF.

 

5) Create the Request object

CREATE OBJECT l_r_request

EXPORTING

i_genuniid = l_s_compkey-genuniid

EXCEPTIONS

OTHERS = 1.

 

 6) Call the query Y from within ABAP code

* get the query definition

CALL METHOD l_r_request->get_initial_state

IMPORTING

e_t_dim = l_t_dim

e_t_mem = l_t_mem

e_t_cel = l_t_cel

e_t_atr = l_t_atr

e_t_con = l_t_con

e_t_fac = l_t_fac

e_t_prptys = l_t_prptys

EXCEPTIONS

x_message = 8

OTHERS = 1.

 

* Set the request

CALL METHOD l_r_request->set_request

EXPORTING

i_t_dim = l_t_dim

i_t_mem = l_t_mem

i_t_cel = l_t_cel

i_t_atr = l_t_atr

i_t_con = l_t_con

i_t_fac = l_t_fac

i_t_prptys = l_t_prptys

EXCEPTIONS

no_processing = 1

x_message = 8.

 

* read data

CALL METHOD l_r_request->read_data

IMPORTING

e_warnings = l_warnings

EXCEPTIONS

no_processing = 1

x_message = 8.

  

*close the request

l_r_request->p_r_olap->FREE( ).

 

7) After fetching the results, assign the result set elements {A1, A2, A3} as exclude 'E' to the exit variable.

* Get the text table from the output handle of the request

ASSIGN l_r_request->n_sx_output-text->* TO <l_th_text>.

loop at <l_th_text> into l_s_txt_n.

clear l_s_range.

l_s_range-low = l_s_txt_n-CHAVL_EXT.

l_s_range-sign = 'E'. *** Excluding

l_s_range-opt = 'EQ'.

append l_s_range to e_t_range.

endloop.

......

e_t_range contains the variable values which will finally be submitted to OLAP to fetch the results of query X excluding values of Y i.e. X - Y.

Thus, in this way you can perform other set operations simply in the customer exit coding itself.

 

 P.S. For more details on executing BW queries from within ABAP, please refer: http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/43db5ee1-0701-0010-2d90-c3b991eb616c

Bhushan Suryavanshi   is a SAP BI Analyst at Bombardier Aerospace.

---

출처 : http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/25643%3Futm_source%3Dtwitterfeed%26utm_medium%3Dtwitter%26utm_campaign%3DFeed%253A+SAPNetworkWeblogs+%2528SAP+Network+Weblogs%2529