I noticed that there were a lot questions and confusion about how to use parameter ENABLE_HANA_MDX in BPC. Here I would like to list some common questions and give my personal opinion. Feel free to add your comment.
Disclaimer: This article only presents my personal opinion and does not any team or organization. There could be some wrong or outdated statement. Please refer to latest SAP standard document for the final answer.
Q1: What is the criterion to make your BPC system as a “BPC on HANA” system?
According to BPC 10.0 installation guide (https://websmp207.sap-ag.de/~sapidb/011000358700000483522011E), you need:
- First of all, you need HANA as the DB for BPC system.
- You have to install SAP HANA Component for Planning and Consolidation HANABPC
- Run program BPC_HANA_MIGRATE_FROM_10 in transaction code SE38. This step will create some additional HANA hierarchy table/view and SQL scripts used by the HANABPC component.
- Turn on parameter ENABLE_ACCELERATOR or ACCELERATOR_ON to “X” or “Y”.
With these settings, your system is already “BPC powered by HANA”. Some functions and query interfaces have been moved from ABAP layer to HANA layer to get better performance.
So HANABPC and parameter ENABLE_ACCELERATOR/ACCELERATOR_ON is the key for "BPC on HANA", not parameter ENABLE_HANA_MDX. Do not be confused on this concept.
Q2: Highlighted performance gain from BPC on HANA
- In general, with HANA DB BPC report and data manger get much better performance compared to other DB, especially for aggregated query in BPC, like YTD query, Finance summary report
- Much better performance on data loading
Simple test on a UJBR restore process (5 million records) shows 10X+ performance gain on HANA.
BPC on HANA: 3-4 seconds to fill a request with 75k data
BPC on other DB: 20-30 seconds to fill a request with 50K data
- 10X+ faster on lite optimizations jobs.
In BPC on traditional DB, customer needs to run frequent lite optimizations jobs to compress the Info cube and update DB statistics/indexes to keep good performance.
One of the customer’s lite optimizations jobs on other DB took 40 minutes but took 35 seconds in our internal BPC on HANA system. Lite optimizations in BPC on HANA do not require the index and roll up steps. The only step executed in HANA version is compression of duplicate record from different requests. In HANA, there is almost no performance drag if you forget to run Lite optimizations jobs.
- No need to roll up the data to aggregate or BWA.
In a planning solution on other DB, data is frequently updated so the new data will not leverage the aggregate or BWA until you roll up the data.
- More items will be added later.
Q3: What is the benefit of having HANA MDX for BPC system?
According to SAP Note “1904344 - Leverage HANA MDX for Planning and Consolidation 10.0 NW”, with support of HANA MDX, BPC can have improvements/enhancements with below area
- Report acceleration
- Member formula calculation acceleration
- Support nested member formula as well as hierarchy aggregation
- Possible write-back optimization on HANA. Please also refer to note 1902743.
With parameter ENABLE_HANA_MDX, BPC will push the MDX parsing/execution from BW ABAP engine to HANA MDX engine.
- Report acceleration
Please be aware that MDX query interface is only one type of interface in BPC.
If your BPC query is not a MDX query then there should be no change from using HANA MDX. For example, a query which only includes the base members will not use MDX interface in BPC.
- Member formula calculation acceleration
From my test, one of the reports shows 30X performance improvement with very complex member formulas existing in multiple dimensions. As member formula is customized objects, the performance gain could be very different.
- Support nested member formula as well as hierarchy aggregation
If you upgrade your BPC system to CPMB 801 SP09 (SAPK-80109INCPMBPC) and also implement SAP Note SAP 2049535, there will be no “255 characterizes” limitation to maintain your member formulas in BPC web admin center. So even without HANA MDX, you can just extend your nested member formulas in BPC web admin.
Q4: Will parameter ENABLE_HANA_MDX solve my performance issue of BPC report?
It depends. Parameter ENABLE_HANA_MDX may not be able to help you. First of all you need to understand what would be the root cause for your performance issue. If your BPC report has complex VBA code and most of the runtime is spent on the Excel side, parameter ENABLE_HANA_MDX will not help at all. Or if you have a very large BPC report which retrieves too many records and bottleneck is on the network/excel refresh, then parameter ENABLE_HANA_MDX cannot help.
ENABLE_HANA_MDX is not a magic finger to address all your performance issues. A runtime breakdown and ABAP trace of expensive BPC query will help understand the potential performance gain from ENABLE_HANA_MDX.
Q5: What is the limitation of HANA MDX for BPC?
For limitations and restrains, please refer to:
SAP Note 1723925 – “SAP HANA MDX: General constraints released”
SAP Note1904344 - Leverage HANA MDX for Planning and Consolidation 10.0 NW
SAP Note 2134881 - Wrong results or query execution failures in MDX prior to SAP HANA Database Revision 93
For example, you may receive UJO exception due to HANA MDX syntax error. Some common mistakes are listed below. You can check the definitions for related Measure Formula and Member Formula. Please refer to the document 'Instruction on Measure Formula.docx' in the attachment to solve this issue.
Item level in BW MDX Engine: [LEVEL00]
Item level in HANAMDX Engine: [LEVEL 00]
One limitation I would like to highlight is that you may not able to run multi-hierarchy queries unless you’re on SAP HANA SP9.
.
Q6: Common issues with ENABLE_HANA_MDX and possible workaround?
- SAP Note 2121950 - HANABPC 10: YTD Measure Showing Incorrectly as Null (Blank)
For example, the value for 2015.02/YTD should be 1 and value for 2015.04/YTD should be 3. But these YTD measures are blank when Periodic is null.
| Account | Measure | 2015.01 | 2015.02 | 2015.03 | 2015.04 |
|---|---|---|---|---|---|
| Expense | Periodic | 1 | 2 | ||
| Expense | YTD | 1 | 3 |
Fixed in HANA Rev 92 and later.
Possible temporary workaround if you only have 1-2 key reports having this issue:
- Use Excel to calculate the YTD.
- Fill all the null values on Periodic with 0. In this case you can not run zero compression on your BPC infocube.
- For some specific report, it is possible to use a customized YTD measure to get correct value. Please contact BPC HANA MDX expert for more information.
- Push the YTD calculation logic into member formula if your report only contains few key member formulas and YTD measure.
- Use SQE query BADI to calculate YTD result.
- The usage of several hierarchies on the same dimension in an MDX statement is not supported.
This limitation is documented in SAP Note "1723925 - SAP HANA MDX: General constraints". Final fixed will be in HANA Rev 92 and later.
Possible temporary workaround if you only have 1-2 key reports having this issue:
- Create one large hierarchy which include all dimension members and make sure all the dimension members are from one hierarchy.
- Asymmetric tuple expression in a query and it fails with an error and get a wrong result (Please refer to SAP Note 2062555 and 2134881 )
For example:
Denominator: ( [ACCOUNT_0],[PRODUCT_0])
Numerator: ([ACCOUNT_0],[PRODUCT_0],[COMPANY_0],[DATASOURCE_0])
Denominator has 2 dimensions and Numerator has 4 dimensions. If the two tuples don't have the same dimensions, then each missing dimension is implicitly added via <dimension>. <hierarchy>.CurrentMember.
Possible temporary workaround:
Add missing dimensions to make tuple expression symmetric.
- SAP Note 2122679 - Memberformula result member returns no data
If user put several member formulas together and display them in one report, in the MDX query, the SOLVE_ORDER will be incorrect which causes the final result generated even some of the member formulas not executed yet.
Possible temporary workaround:
Default SOLVE_ORDER is 5. So you can try to add a different SOLVE_ORDER for different member formula. For example, ”SOLVE_ORDER=3” for formula 1 and “SOLVE_ORDER=7” for formula 2.
Q7: How much is the effort to use HANA MDX for BPC?
Before you want to use HANA MDX for BPC solution, please review these limitations and evaluate the benefit before they try to use ENABLE_HANA_MDX. So using HANA MDX is not a one click effort. Make sure your team has a deep understanding on HANA MDX and BPC query interface. You also need follow the steps below for a complete test.
- Have a complete unit test for all reports and data managers.
Some report or data manager may get error when using HANA MDX as they may not be supported by HANA MDX.
- Have a complete performance test for most important reports and data managers to make sure there is no deterioration in performance.
For example, SAP Note “2037142 - Performance improvement for report with nested member formulas” has addressed the performance degrade issue when there are many nested member formulas.
- Schedule a volume test to verify the sizing of HANA DB.
Using HANA MDX will push more workload from BPC ABAP layer to HANA DB layer. HANA MDX engine is able to leverage multiple threads to improve the performance. So memory and CPU requirement are higher for HANA DB. The standard BPC sizing guide may not present the extra resource required by HANA MDX.
The memory and CPU usage is depending on the complexity of your BPC MDX query. So it is not easy to use a standard formula to calculate the extra sizing for HANA MDX.
Q8: How to trouble shooting HANA MDX for BPC?
Please refer to SAP Note 1958603 - Analyze HANA MDX problems in Planning and Consolidation 10.0 NW. The most important step is to get MDX statement of BPC query and directly reproduce the MDX issue in HANA DB.
You can use program UJHANA_MDX_TEST instead of MDXTEST to test MDX query on HANA DB.
When ENABLE_HANA_MDX parameter is turned on or off, the system will check the member formula/ measures/script logics if all these objects can be supported in HANA MDX. If you get error in this step, you can use CL_UJHANA_MODEL_MDX_CHECKER for trouble shooting and debug.
Q9: Where can I find the generated HANA View for my BPC Applicaiton?
- Run program BPC_HANA_INFO_DETAIL you will get a list of all HANA view for hierarchy tables.
These hierarchy tables/views will be recreated when you turn on parameter ENABLE_HANA_MDX.
- Program UJHANA_REFRESH_VIR_CUBE will execute steps below:
- Drop the existed HANA view
- Generated new OLAP view
- Generated hierarchy view
- SAP Managed Tags:
4 Comments
| User | Count |
|---|---|
| 3 | |
| 2 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
