Restrict query execution to specific InfoProvider

[sukdev_koner2]

 

Author Bio: 

Sukdev Koner is SAP Certified consultant in NetWeaver BI and SAP HANA. He is working as BW/HANA Architect with 12 years of experience in this field.  He has implemented multiple end-to end solution implementations for SAP BI Analytics in industry sectors like Pharmaceutical, FMCG, Utility and Power and Insurance/Consulting.

 

Purpose and Target Audience:

This blog would be helpful to those who had tried several performance optimizations in SAP BI system like Old Data deletion from PSA/Change Log, Compress BW Info Cubes, Partitioning the Info Providers. Still for specific queries (on top of Big Cubes), execution time is fairly big.

 

Background:

SAP BI Multi provider works as union operator and  is used to combine the data from objects in a Multi Provider. Here, the system constructs the union set of the data sets involved; all the values of these data sets are combined.

A Multiprovider can consist of different combinations of the following Info Providers: Info Cube, Data Store object, Info Object, Info Set, Virtual Provider, and aggregation level.

SAP guide line is to create all queries on top of Multi Provider (some case on top of Info Set) even if query is based on a single Info Provider.

 

Problem Statement:

When you execute a query on top of a Multi Provider, its search and read all the underlying Cubes/DSOs in parallel. But still all the records in all the cubes are searched at least once.  For our example, there are total of 10 billion records.

If your cube is partitioned by Time characteristics like 0CALDAY or 0FISCPER, then it might only search the specific Cube or Cubes, when you run query for time characteristics.

But if, your Cube is not partitioned and there is no scope for that in PRODUCTION environment. Or say you want to search/filter your Report based on non-Time characteristics, say by Material, Customer or Profit Centers.

 

Solution:

If we can make the query execution to a specific cube (based on user choice in Query input selection), then performance and response time will be good.

We can follow the below method to see a good performance boost in terms of execution time and memory/CPU usage (even if you have HANA as Data base or have BWA).

Say you have a DSO – ZDSO_O01. It has 10 billion of records for last 10 years. Total no of Profit centers (0PROFIT_CTR) in DSO is 9. (30001, 30002 …. 30009).

 

First create three cubes on top of that DSO. Say ZCUBE_C01, ZCUBE_C02 and ZCUBE_C03.

Load the 3 cubes with below DTP selections:

ZDSO_O01 -> ZCUBE_C01 (Profit Center = 30001, 30002, 30003)

ZDSO_O01 -> ZCUBE_C02 (Profit Center = 30004, 30005, 30006)

ZDSO_O01 -> ZCUBE_C03 (Profit Center = 30007, 30008, 30009)

 

Create a Multi Provider ZMP_M01 on top of the three cubes. Go to Query Designer and create the BEx Query.

 



 

Business user wants to execute the query for the Profit Center for which he/she has authorization access.  So query will be executed one Profit Center at time.

 

Create a query on top of Multi provider and create following two variables as below:

1. CUBE_VAR :               Type Customer Exit, Single Value


2. PROFIT_C_V :               Type Single Value; User Input Ready

 

Purpose of that Exit Variable is to restrict query execution to a specific Info Cube.





 

Go to Transaction Code SMOD.

Type Enhancement as ‘RSR00001’ and select ‘Component’ and ‘Change’. Double Click on Include ‘ZXRSRU01’ and write below code.


 

Actual Code:

IF i_step = 2.

CASE i_vnam.

WHEN 'CUBE_VAR'.  "Userr Exit Variable for 0INFOPROV

READ TABLE i_t_var_range INTO l_var_range
WITH KEY vnam = 'PROFIT_C_V'. "Profit Center Variable - Ready for User Input

IF sy-subrc EQ 0.

lv_profit_ctr = l_var_range-low.

********** Restriction for Cube 1 **************
IF lv_profit_ctr EQ '0000030001' OR
lv_profit_ctr EQ '0000030002' OR
lv_profit_ctr EQ '0000030003'.
l_s_range-low =  'ZCUBE_C01'.
ENDIF.

********** Restriction for Cube 2 **************
IF lv_profit_ctr EQ '0000030004' OR
lv_profit_ctr EQ '0000030005' OR
lv_profit_ctr EQ '0000030006'.
l_s_range-low =  'ZCUBE_C02'.
ENDIF.

********** Restriction for Cube 3 **************
IF lv_profit_ctr EQ '0000030007' OR
lv_profit_ctr EQ '0000030008' OR
lv_profit_ctr EQ '0000030009'.
l_s_range-low =  'ZCUBE_C03'.
ENDIF.

**************
l_s_range-sign = 'I' .
l_s_range-opt = 'EQ'.

APPEND l_s_range TO e_t_range.
ENDIF.

ENDCASE.

ENDIF.

 

 

Conclusion:

Execute the BEx Query in RSRT and you can see in debugging that based on Profit Center selection, it’s choosing only one Info Cube. Other two Cubes are not searched at all.

So performance is better and CPU usage and Memory Read, I/O throughput is improved.

 

What's Next:

I would hope the readers would be able to implement this solution and let me know how much reporting time has been improved. Please reach out to me in case you run into issues while implementing this.

I would be publishing more blogs in near future on other performance optimization initiatives in SAP BW and HANA space.