Performance Issue due to Expensive MDX Statements in SAP BPC 11, version for SAP BW/4HANA
It has been observed that, there are Customers with initial release of SAP BPC 11, version for SAP BW/4HANA (BPC 11), faced performance issues while running BPC reports, triggering some BPC calculations even though they have SAP BPC 11, powered by SAP HANA.
The root cause of the issue could be anything. Whereas this post is intended to provide the solution, if the performance issue due to some Expensive MDX statements as explained below in details.
Follow below steps to Check whether issue is due to MDX statements or not :
Check 1 : Go to ‘DB02′ T-code -> in the left hand pane expand ‘Performance’ – > Select ‘Expensive Statements‘, as shown below
then you will see Expensive MDX statements as shown below
Check 2 : Check BPC traces using T-code ‘UJSTAT‘ (Make sure value of Model Level Parameter BPC_STATISTICS is set to “ON”). When you check details of respective statistics, you will find ‘Execute SQL to Do HANA MDX Query’ step is taking majority of time.
Solution to Follow :
Step 1 : Implement below SAP Notes (These Notes belongs to different release. So kindly check Support Package Details and check which NOTE is relevant for your system)
- 2826225 – Supporting BPC HANA MDX to read data from HCPR
- 2901302 – Performance downgrade for HANA MDX in BPC 11.0/11.1
- 2985116 – Performance downgrade when ENABLE_MDX_ON_HCPR switched on
- 3031680 – ENABLE_HANA_MDX cannot be enabled after apply note 2985116
Please make sure you followed the sequence mentioned above, as they are interdependent. After applying above notes please follow below Manual Steps
- Execute UJHANA_REFRESH_VIR_CUBE program for the Model to regenerate the necessary metadata.
- Maintain Model level parameter ‘ENABLE_MDX_ON_HCPR“ to ‘X’
Still if you have the same issue, then follow below manual steps:
Step 1 : Make sure the Global Level Parameter ‘ENABLE_QUERY_CACHE‘ is already Disabled, by changing the value to Blank.
Step 2 : Make sure the Model Level Parameter ‘ENABLE_MDX_ON_HCPR‘ is already Disabled, by changing the value to Blank.
Step 3 : Execute “UJXO_CLEAN_CACHE” Program
Step 4 : Execute “UJXO_CLEAN_DIM_CACHE” Program
Step 5 : Execute “RSR_MULTIPROV_CHECK” Program for the Models by selecting their respective Composite Provider name and make sure you have selected 3 Check Boxes as highlighted in below image.
Step 6 : Execute “RSDDB_LOGINDEX_CREATE” Program for the BPC Model by mentioning their respective Composite Provider as shown below.
Step 7 : Execute “UJA_REFRESH_DIM_CACHE” Program for All dimensions of BPC Environment.
Step 8 : Execute “UJHANA_REFRESH_VIR_CUBE” program for BPC Model.
Step 9 : Make sure you Enabled the Model Level Parameter ENABLE_MDX_ON_HCPR for the BPC Model by changing value to ‘X’.
Step 10 : If you have disabled the Global Level Parameter ‘ENABLE_QUERY_CACHE‘ in Step 1, then make sure you Enabled this parameter by changing value to ‘X’.
Post following the above steps, the issue related to the expensive MDX statements while executing any report or calculation will be not observed further.
To make sure, execute the Reports / Calculations post performing the above steps, you will not find any expensive statements as explained in Check 1.
Additional Details on HANA MDX :
For further understanding on HANA MDX and Composite Provider in SAP BPC 11, version for SAP BW/4HANA, kindly refer “HANA MDX and Composite Provider in SAP BPC 11, version for SAP BW/4HANA” Article by William Yu