Symptom
Further Performance Improvement for EPM Add-in Report on BPC HANA by leveraging report cache
Other Terms
Performance, HANA, JSON, Cache
Reason and Prerequisites
JSON Encoding/decoding and request/response transformation will introduce big overhead for large query on BPC HANA.
Solution
Please apply this note or upgrade to HANABPC SP02.
Please note this note will NOT improve the web report performance and can ONLY be installed/applied on BPC HANA.
After the installing the note or upgrade to HANABPC SP02, you need also enable the cache in IMG by the following steps.
1. Goto IMG ( t-code: SPRO ) and open "SAP Reference IMG"
2. Goto "Planning and Consolidation|Configuration Parameters|Set Global Parameters"
3. New a Parameter "ENABLE_QUERY_CACHE" and Set its value as "X"
4. Save the changes
As the cache will use ABAP EXPORT/IMPORT buffer, please ensure it is adjusted to the appropriate. You can monitor it through ST02 to see if it is overused, if yes, please adjust the parameter rsdb/obj/buffersize correspondingly.
Please note this note will NOT improve the web report performance and can ONLY be installed/applied on BPC HANA.
After the installing the note or upgrade to HANABPC SP02, you need also enable the cache in IMG by the following steps.
1. Goto IMG ( t-code: SPRO ) and open "SAP Reference IMG"
2. Goto "Planning and Consolidation|Configuration Parameters|Set Global Parameters"
3. New a Parameter "ENABLE_QUERY_CACHE" and Set its value as "X"
4. Save the changes
As the cache will use ABAP EXPORT/IMPORT buffer, please ensure it is adjusted to the appropriate. You can monitor it through ST02 to see if it is overused, if yes, please adjust the parameter rsdb/obj/buffersize correspondingly.
------------------------------------------------------------------------
|Manual Pre-Implement. |
------------------------------------------------------------------------
|VALID FOR |
|Software Component CPMBPC SAP Business Pl...|
| Release 801 SAPK-80101INCPMBPC - SAPK-80101INCPMBPC |
------------------------------------------------------------------------
1. Create Domain UJ_CACHE_TYPE
1) Goto transaction SE11
2) Select "Domain" and enter "UJ_CACHE_TYPE" in the corresponding field
3) Click "Create"
4) Fill in the following information
Data Type: CHAR
No. Characters: 2
Decimal Places: 0
Description: "BPC: Cache Type"
5) Save and Activate it ( Set target package UJ0 )
2. Create Data Element UJ_CACHE_TYPE
1) Goto transaction SE11
2) Select "Data Type" and enter "UJ_CACHE_TYPE" in the corresponding field
3) Click "Create" and select type as "Data Element"
4) Fill in the following information
Domain: UJ_CACHE_TYPE
Description: "BPC: Cache Type"
Field Label (Medium/Long/Heading): Cache Type
5) Save and Activate it ( Set target package UJ0 )
3. Create table UJ0_CACHE_STAT
1) Goto Transaction SE11
2) Select "Database Table" and enter "UJ0_CACHE_STAT" in the corresponding field
3) Click "Create"
4) Enter description as "BPC: Cache Status"
5) Goto "Delivery and Maintenance" tab and fill in the following information
Delivery Class: A
Table View Maint.: Display/Maintenance Allowed with Restrictions
6) Goto "Field" tab and fill in the tables as below
Field |Key|Initial| Data Element
________________________________________________
MANDT | X | X | MANDT
CACHE_TYPE | X | X | UJ_CACHE_TYPE
SERVER | X | X | SYHOST
CACHE_ACTIVE| | | UJ_FLG
7) Click "Technical Setting" and fill in the following information
Data Class: APPL0
Size Category: 0
Buffering: Buffering switched on
Buffering Type: Fully Buffered
8) Save and Activate it ( Set Target Package "UJ0" and Enhancement Category "Cannot be enhanced" )
4. Create table UJXO_QUERY_BUF
1) Goto Transaction SE11
2) Select "Database Table" and enter "UJXO_QUERY_BUF" in the corresponding field
3) Click "Create"
4) Enter description as "BPC: Shared Buffer for Query Object"
5) Goto "Delivery and Maintenance" tab and fill in the following information
Delivery Class: A
Table View Maint.: Display/Maintenance Allowed with Restrictions
6) Goto "Field" tab and fill in the tables as below
Field |Key|Initial| Data Element
________________________________________________
MANDT | X | X | MANDT
RELID | X | X | INDX_RELID
APPSET_PREFIX | X | X | UJ_APPSET_PREFIX
APPL_PREFIX | X | X | UJ_APPL_PREFIX
LANG | X | X | SYLANGU
USER_ID | X | X | SYUNAME
REQUEST_HASH | X | X | HASH160
SRTF2 | X | X | INDX_SRTF2
CLUSTR | | | INDX_CLSTR
CLUSTD | | | BLOCKLRAW
7) Click "Technical Setting" and fill in the following information
Data Class: APPL1
Size Category: 9
Buffering: Buffering not allowed
8) Save and Activate it ( Set Target Package "UJXO" and Enhancement Category "Cannot be enhanced" )
5. Create structure UJXO_S_CACHE_ENTRY
1) Goto Transaction SE11
2) Select "Data Type" and enter "UJXO_S_CACHE_ENTRY"
3) Click "Create" and select type as "Structure"
3) Enter description as "BPC: Cache Entry"
4) Fill in the following lines in the "Components" tab
Component | Type Category | Component Type
________________________________________________
APPSET | TYPE | UJ_APPSET_PREFIX
APPL | TYPE | UJ_APPL_PREFIX
USER_ID | TYPE | SYUNAME
5) Save and Activate it ( Set Target package "UJXO" and Enhancement Category "Cannot be enhanced")
6. Create Lock Object EUJXO_CACHE_ITEM
1) Goto Transaction SE11
2) Select "Lock Object" and enter "EUJXO_CACHE_ITEM" in the corresponding field
3) Click "Create"
4) Enter Description as "BPC: Lock for Query Entry"
5) Goto "Tables" tab and fill in the following information
Name: UJXO_S_CACHE_ENTRY
Lock Mode: Write Lock
6) Goto "Lock Parameter" tab and fill in the tables as below
Lock Parameter| Table | Field
__________________________________________________
APPSET | UJXO_S_CACHE_ENTRY | APPSET
APPL | UJXO_S_CACHE_ENTRY | APPL
USER_ID | UJXO_S_CACHE_ENTRY | USER_ID
7) Save and Activate it ( Set target package UJXO )
7. Add messages UJXO_META_EXCEPTION020
1) Goto Transaction SE91
2) Enter Message Class "UJXO_META_EXCEPTION" and click "Change"
3) Set the following text on Message 020
Internal Error in Query Cache
4) Save the change
8. Add table entry V_UJ0_EVENTS
1) Goto Transaction SM30
2) Enter "V_UJ0_EVENTS" in "Table/View" field and click "Maintain"
3) Click "New Entries" and insert the following 2 ones
Event ID | Event Description
______________________________
7011 | Read Query Cache
7012 | Run cached query
4) Save the changes
------------------------------------------------------------------------
|Manual Pre-Implement. |
------------------------------------------------------------------------
|VALID FOR |
|Software Component CPMBPC SAP Business Pl...|
| Release 800 SAPK-80007INCPMBPC - SAPK-80007INCPMBPC |
------------------------------------------------------------------------
1. Create Domain UJ_CACHE_TYPE
1) Goto transaction SE11
2) Select "Domain" and enter "UJ_CACHE_TYPE" in the corresponding field
3) Click "Create"
4) Fill in the following information
Data Type: CHAR
No. Characters: 2
Decimal Places: 0
Description: "BPC: Cache Type"
5) Save and Activate it ( Set target package UJ0 )
2. Create Data Element UJ_CACHE_TYPE
1) Goto transaction SE11
2) Select "Data Type" and enter "UJ_CACHE_TYPE" in the corresponding field
3) Click "Create" and select type as "Data Element"
4) Fill in the following information
Domain: UJ_CACHE_TYPE
Description: "BPC: Cache Type"
Field Label (Medium/Long/Heading): Cache Type
5) Save and Activate it ( Set target package UJ0 )
3. Create table UJ0_CACHE_STAT
1) Goto Transaction SE11
2) Select "Database Table" and enter "UJ0_CACHE_STAT" in the corresponding field
3) Click "Create"
4) Enter description as "BPC: Cache Status"
5) Goto "Delivery and Maintenance" tab and fill in the following information
Delivery Class: A
Table View Maint.: Display/Maintenance Allowed with Restrictions
6) Goto "Field" tab and fill in the tables as below
Field |Key|Initial| Data Element
________________________________________________
MANDT | X | X | MANDT
CACHE_TYPE | X | X | UJ_CACHE_TYPE
SERVER | X | X | SYHOST
CACHE_ACTIVE| | | UJ_FLG
7) Click "Technical Setting" and fill in the following information
Data Class: APPL0
Size Category: 0
Buffering: Buffering switched on
Buffering Type: Fully Buffered
8) Save and Activate it ( Set Target Package "UJ0" and Enhancement Category "Cannot be enhanced" )
4. Create table UJXO_QUERY_BUF
1) Goto Transaction SE11
2) Select "Database Table" and enter "UJXO_QUERY_BUF" in the corresponding field
3) Click "Create"
4) Enter description as "BPC: Shared Buffer for Query Object"
5) Goto "Delivery and Maintenance" tab and fill in the following information
Delivery Class: A
Table View Maint.: Display/Maintenance Allowed with Restrictions
6) Goto "Field" tab and fill in the tables as below
Field |Key|Initial| Data Element
________________________________________________
MANDT | X | X | MANDT
RELID | X | X | INDX_RELID
APPSET_PREFIX | X | X | UJ_APPSET_PREFIX
APPL_PREFIX | X | X | UJ_APPL_PREFIX
LANG | X | X | SYLANGU
USER_ID | X | X | SYUNAME
REQUEST_HASH | X | X | HASH160
SRTF2 | X | X | INDX_SRTF2
CLUSTR | | | INDX_CLSTR
CLUSTD | | | BLOCKLRAW
7) Click "Technical Setting" and fill in the following information
Data Class: APPL1
Size Category: 9
Buffering: Buffering not allowed
8) Save and Activate it ( Set Target Package "UJXO" and Enhancement Category "Cannot be enhanced" )
5. Create structure UJXO_S_CACHE_ENTRY
1) Goto Transaction SE11
2) Select "Data Type" and enter "UJXO_S_CACHE_ENTRY"
3) Click "Create" and select type as "Structure"
3) Enter description as "BPC: Cache Entry"
4) Fill in the following lines in the "Components" tab
Component | Type Category | Component Type
________________________________________________
APPSET | TYPE | UJ_APPSET_PREFIX
APPL | TYPE | UJ_APPL_PREFIX
USER_ID | TYPE | SYUNAME
5) Save and Activate it ( Set Target package "UJXO" and Enhancement Category "Cannot be enhanced")
6. Create Lock Object EUJXO_CACHE_ITEM
1) Goto Transaction SE11
2) Select "Lock Object" and enter "EUJXO_CACHE_ITEM" in the corresponding field
3) Click "Create"
4) Enter Description as "BPC: Lock for Query Entry"
5) Goto "Tables" tab and fill in the following information
Name: UJXO_S_CACHE_ENTRY
Lock Mode: Write Lock
6) Goto "Lock Parameter" tab and fill in the tables as below
Lock Parameter| Table | Field
__________________________________________________
APPSET | UJXO_S_CACHE_ENTRY | APPSET
APPL | UJXO_S_CACHE_ENTRY | APPL
USER_ID | UJXO_S_CACHE_ENTRY | USER_ID
7) Save and Activate it ( Set target package UJXO )
7. Add messages UJXO_META_EXCEPTION020
1) Goto Transaction SE91
2) Enter Message Class "UJXO_META_EXCEPTION" and click "Change"
3) Set the following text on Message 020
Internal Error in Query Cache
4) Save the change
8. Add table entry V_UJ0_EVENTS
1) Goto Transaction SM30
2) Enter "V_UJ0_EVENTS" in "Table/View" field and click "Maintain"
3) Click "New Entries" and insert the following 2 ones
Event ID | Event Description
______________________________
7011 | Read Query Cache
7012 | Run cached query
4) Save the changes
No comments:
Post a Comment