All of the documentation and cases I could find refer to the benefits of Declustering when on a HANA box. However, many customers are not at the stage of being on HANA yet, so the question I had was; What benefits can Declustering bring on a standard (i.e. Oracle DB) ECC system?
Setup
The initial setup required for declustering on a non-HANA system is the same as on HANA. It consists of activating the framework, flagging which tables to decluster, doing the initial conversion of existing results, and then activating the declustering to occur ongoing. These are simple steps detailed in note 1774918.
Also, it’s important you bring in the latest declustering updates. Specifically note 1826671 is important as it brings in reference tables required to pick up the correct entries in WPBP and also enable quicker access to retro results.
Skillset required
The below skillsets are necessary to be able to implement (and use) declustering effectively to be of value for businesses:
- Payroll functional knowledge of cluster structure (off-cycles, retros, splits etc)
- ABAP programming skills
- SQL skills
Database storage
In a non-HANA system declustering the major payroll tables (WPBP, RT, BT, a couple of country specific tables) consumed roughly the same amount of space as PCL2, i.e.
Declustered results + PCL2 results ≈ 2 * PCL2 results
Performance
The comparisons I have here are timings on purely how long it takes to get to the dollar figure result. These results are from ABAP programs.
The non-HANA system is from a customer with approximately twice the employee volume of the HANA system. Both are large employee volumes.
Non-HANA system:
| | Report |
Item | Request | WT reporter time taken (seconds) | Declustered Query (seconds) | Percentage Speed improvement |
1. | Net Pay for Payroll Area 01 | 1,461 | 4 | 36,425% |
2. | Net Pay for Personnel Area A | 195 | 20 | 875% |
3. | Salary for Personnel area B | 210 | 22 | 855% |
HANA system:
| | Report |
Item | Request | WT reporter time taken (seconds) | Declustered Query (seconds) | Percentage Speed improvement |
1. | Net Pay for Payroll Area 01 | 67 | 13 | 415% |
2. | Net Pay for Personnel SubArea A | 4 | 1 | 300% |
3. | Salary for Personnel SubArea B | 4 | 1 | 300% |
Items 1, 2 and 3 are the same query / code for both systems (though slightly different selection criteria given the nature of each customer) with the exception of item 1. This item reads the BT table for the non-HANA system and the RT table for the HANA system as the HANA customer doesn’t have their BT table declustered. Therefore comparing item 1 between the 2 customers is not appropriate here, it is useful for explaining behaviour on the non-HANA system though.
Reasons for differences in performance
The reason for the difference in performance within the non-HANA system for the above 3 tests is that declustering speed there seems to be proportional to how many items you are selecting (the WHERE statement in your SQL) and how many tables you are reading to get those items.
For example, request item 1 above (Net Pay for Payroll Area 01) for the non-HANA system is a very simple request as the report only needs to look at 2 tables to get the information and we don’t have to worry about retros (BT table is the A result only), hence the very fast results. However, items 2 and 3 above needs to look at 4 tables and restrict within those (as well as take into account retros) to get the desired result, this takes a lot longer to run.
The HANA system doesn’t hit these same constraints, and the speed in that system is more proportional to the amount of data being read, i.e. the more data the longer it takes to run. The HANA system performance does not seem to be dependent on the complexity of the SQL statement like the non-HANA system is.
This dynamic should be taken into consideration when scoping the benefits of declustering for your customer(s).
The gaps / limitations or declustering – things to be aware of before embarking on an implementation
First and foremost, declustering is just a method of storing information. It’s not a toolset. Implementing declustering doesn’t make anything you have now run any faster. Declustering stores information in a way where it can be accessed quicker and (from a technical point of view at least) easier.
If you want to use declustering (which I believe you really should), the question then becomes how? There are no out of the box (that I’m aware of) back end ABAP programs that will read it. You have to build tools to use it.
If you want to use it for reporting, then there is ancillary information a business user will want outside of what you can get from the payroll results. Employee name or the text for the personnel area for example. Therefore you have to not just get the figures from the declustered tables, you’ll need to lookup the other required information to make it fit for purpose for a business user.
Your basic options are:
- Build something to read from it in ECC (Wagetype reporter does not read from it)
- Use the toolsets SAP provides and create your code and SQL statements to read from it. The key toolsets SAP provide are; the Payroll Cockpit, Payroll Control Center and BI
Declustering does not alter your posting results. So if you report from PPOIX, PPOPX etc for reconciling payroll to your GL then your performance under a declustered system will not change for that.
There are some other blogs around that have excellent information on the technical aspects of declustering. This one by Gabriel is particularly useful:
http://scn.sap.com/community/erp/hcm/blog/2014/07/04/new-analytic-capability-of-payroll-results-via-...
Summary
As can be seen from the test results, significant performance advantages can be achieved by moving to a HANA system, but should that not be on the radar for your organisation in the immediate future you can still leverage the declustered toolset to significantly improve performance within your current system constraints. There additional database requirements to use it, however this is not an insurmountable impost given the performance improvement.
