Payroll Declustering in a non-HANA world
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:
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.
Good article Steve and important to note that PCC RDS content contains reports that run off the declustered data. That said, hoping that SAP/SF will rewrite the large and ALWAYS used Wage Type Reporter and Payroll Recon to do the same.
I agree with Jarret, some OOB tools for utilizing this would be great. We are ourselves about to embark on upgrading our ERP HCM system to EhP7, but we are not (yet) migrating to HANA. We have been relying on third-party tools to read payroll results for the kinds of reporting you describe, and declustering has been in the backs of our minds as something to look at as a way of simplifying that. Now, reading about these performance gains as well, I think we'll want to look at it a bit closer. Thanks for the analysis!
Thanks Jarret and Matt,
The PCC RDS does contain code that runs off declustered data, however a challenge with what has been released is that it doesn't cover several key cases. I've discussed this with the SAP development (and RDS) team and hope they will extend the templates they provide to cover more areas.
Specifically, the gaps in the current RDS are:
1 - The SELECT statements that read the declustered results don't use table P2RX_EVAL_PERIOD & P2RX_WPBP_INDEX. These tables were only released in note 1826671 (i.e. after the RDS was released). This means the SELECT statements are not as fast they could be when using these new tables. Additionally you may have issues with reading wagetypes that don't have WPBP splits (i.e. technical wagetypes) and could end up with doubled figures in your reports due to this if you need to use any fields from WPBP.
2 - None of the SELECT statements I saw in the RDS took into account retros, i.e. they only looked at the A result, not the P result. This means the standard functionality of the WT reporter where it gives the difference of wagetype figures for retro periods is not an example provided in the RDS. This is a very important aspect for users so we had to write our own code to cater for this.
3 - No accounting for off-cycle processing. The RDS doesn't have any checks that can be run to read an off-cycle pay. Off-cycles are generally far less in volume that the main pay, but for now if you were to use the RDS you would probably want to take off-cycle processing out of scope for use by the PCC / RDS.
I also hope SAP will release an updated WT reporter & their standard reports that will read from declustered results, however I've not been able to find anything out in that regard yet.
Also, as an FYI, I've found that the wagetype reporter I've built to read from declustered results works faster when written with OpenSQL rather than native SQL. You may already be aware that it would be faster, but just though I'd let people know just in case they didn't.
Thanks for the additional comment Steve and I have a call with the PCC RDS after the upcoming go-live (Oct) and will be sure to bring up some of the points about but also anything else I find. Fun to be on the bleeding edge isnt it 🙂
Yes it is, helps to make life interesting 🙂
Also, just got confirmation that "Test" Payrolls for concurrent employment should be available from November this year too, so that will be another exciting aspect to get going.
Important points for us to keep in mind, especially as both retros and offcycles are a fact of life for us each and every month (not to mention Concurrent Employment, which I see from your bio is something you're familiar with -- we're a school district, so we have a lot of CE). Our business rules around things like substitute teachers and so on practically guarantee that there will be retro activities in every payroll cycle, and our variety of union agreements likewise build in offcycle payrolls every month for the small handful that use a different calendar.