Skip to Content

In 2012 SAP announced the so called declustering for time management and payroll data. In March 2014 the last country versions are planned to be delivered and then declustering is available to all SAP HCM Payroll customers world-wide.

Recently I had many conversations with customers in the context of HCM on HANA and Payroll on HANA about the benefits of ‘declustering’. In this post I will try to explain what declustering is and how customers benefit.

What is a cluster?


Let me start with explaining what clustering is before talking about what declustering is. In SAP HCM modules for Payroll and Time Management employee data is stored within database tables known as cluster tables. These cluster tables (PCL1 and PCL2) store data in compressed binary (RAW format) strings. This type of data storage does not allow to access the data directly from the database layer for analytical and reporting purposes. And this is where the pain is for many customers: Running queries on large data sets of thousands of employees, with long history (and maybe even retro calculations) has not been very performing in the past. That changes now.

How does declustering work?


To use the data stored in the cluster tables, data needs to be converted from the nontransparent storage into simple transparent database table storage. This can be performed by declustering, which involves creation of new simple transparent table for each internal table in cluster table and copying the data from the cluster table to the transparent table. These tables are associated with each other through a common primary key for data retrieval or reporting purposes.

To enable declustering the HCM Declustering Tools business function must be activated. To learn more about the SAP delivered tools for declustering check this: link

How can the power be unleashed?


There are 2 ways to switch the declustering on: Generic and Customized declustering.

  • Generic Declustering: The declustering mechanism creates new transparent tables for each internal table of the payroll result. This feature allows synchronous updating of new transparent tables during the payroll run. Data archiving and data destruction for new transparent tables are also supported.
  • Customized Declustering: Select the payroll results to be declustered based on specific selection criteria. This will adjust the result tables to be declustered when certain data is not needed in the transparent tables. Country-specific lists of transparent tables/structures to be declustered are delivered as standard.

What are the advantages?


There are great advantages that come with declustering. To name a few:

  • Process or retrieve bulk data using simple SQL queries
  • Perform aggregations on the data directly at the database layer
  • Improve performance of the standard reports
  • Build highly efficient customized reports
  • Merge Payroll data with other HCM data (e.g. PA/OM data, talent data)

How do customers benefit?


To explain a bit of customer benefit around these advantages I want to share what I recently encountered. Recently I talked to a customer with 3,5billion entries in their payroll results table (RT). This customer had 75k employees and 10 years of payroll history (including retro calculation). By declustering the total size of the payroll cluster was reduced from 1Terabyte (on their traditional database) to 600GB on SAP HANA.  This means that the total size of the result table was compressed to 60% of it’s former size due to the compression that comes with SAP HANA. This alone makes the processing of data much faster. Add to this the immense reading power of SAP HANA and customers can build very powerful reports. Really unlike anything I had seen before.

On a side note: for now we need both the cluster and the tables in SAP HANA because we still store the initial payroll results in the cluster. However, the cluster does not need to be in the ‘hot-memory’ anymore. This can now also be on disks (which usually is less expensive).

Regarding the improvement of reporting on payroll results I talked to many customers with this requirement. Until now reporting was done either via the Wage Type Reporter or by loading data into SAP NetWeaver BW. This means that either the reporting was slow or it was on older data. With declustering that changes. Directly after the payroll has run the declustering is kicked off (this means real-time data). To utilize that data Virtual Data Models need to be created in the SAP HANA Modeler. One of the biggest benefits is that payroll data can now be merged with other HCM data, like PA/OM and talent management data. This makes this development crucial for value adding HR reporting and analytics. If customers want high speed ad-hoc reporting they can take advantage of the SAP Analysis tools that run directly on these Virtual Data Models. This means slicing and dicing through decades of historical payroll data in a split second. Customers only need to plug-in their BI tool. It’s that simple.

Recently I talked to a customer who had created a report with an aggregation of certain wage types per company code, personnel area and some other dimensions. The report also tracked retro-calculation. This took around 35 minutes as a Wage Type Reporter report and now due to the declustered data and SAP HANA ran in 10 seconds in their BI report. This shows that reading and processing data in SAP HANA makes a huge difference.


In conclusion, with declustering and SAP HANA payroll and time management information is now available real-time and in seconds. This allows HR departments to spend more time on analyzing the data rather than shaping and modelling it.

This means for example that a gross to net reports can now look like this, and can be instantly available after the payroll ran:

Gross net analysis.jpg

Users can immediately zoom in to different time dimensions or any other kind of dimensions. Data exploration really becomes much easier.

An analysis on social security wage spending per payroll area can now look like this:

Social wage type analysis.jpg

This allows visualizing trends and correlations in data that so far seemed impossible toanalyze. Payroll administrators can now use ad-hoc reporting tools to provide immediate reasoning behind the numbers to their management. This can be done simpler and quicker based on declustered data and with appealing and easy to use BI tooling on top.

As one customer told me “Answers can now be provided to all those questions that were not asked anymore because it was such a pain to answer them”.


How can customers start the journey?


Declustering allows storing data in transparent tables with columnar storage only on the SAP HANA system from the clustered tables. This provides two scenarios to customers that want to make use of declustering:

  • Side-by-Side: This is where a Business Suite system running on any database is connected with a separate SAP HANA box.

A precondition is that those transparent tables on the SAP HANA system are created based on the transparent tables in the Business Suite system by mechanism such as SAP Landscape Transformation (SLT).

  • Suite on HANA: Declustering allows to store data in the transparent tables on the Business Suite system directly from the clustered tables.

Thus, declustering allows managing and supporting both the SAP HANA scenario as well as the Business Suite scenario running on any database connected with a separate SAP HANA box.

One word of advice: use this to speed up your analytics and (ad-hoc) reporting, and not to build reconciliation reports because there is cool functionality coming your way in 2014!

If you need more information or if you want to share your experiences please feel free to contact me!

Best regards,

Frans Smolders

Frans.smolders@sap.com

To report this post you need to login first.

36 Comments

You must be Logged on to comment or reply to a post.

  1. Jarret Pazahanick

    Excellent blog Frans and this is some very exciting stuff that is coming for SAP Payroll customers and my assumption is that a 3rd option will be for customers to use SuccessFactors Employee Central Payroll.  Can you please confirm.

    (0) 
    1. Frans Smolders Post author

      Hi Jarret,

      The EC payroll is indeed based on the functionality we offer on-prem. Therefore, declustering becomes available as well in the cloud. There is not final date yet on when the EC Payroll moves to SAP HANA.

      For EC payroll reporting the goal is to use our SuccessFactors Workforce Analytics platform with the embedded Online Report Designer. The mock-ups I’ve put in this post are based on SAP BI tools, and therefore screens will look a bit different in our cloud offering.

      Best regards,

      Frans   

      (0) 
      1. Jarret Pazahanick

        Hi Frans

        That is excellent news for EC Payroll and a very smart strategic strategy for SAP/SF. Can you confirm that there will not be two versions/price points for customers (i.e EC Payroll on HANA and EC Payroll).

        Thanks again for the quick response.

        Jarret

        (0) 
        1. Frans Smolders Post author

          Hi Jarret,

          It’s too early to comment on that I think because there is no concrete plan yet to offer EC payroll on SAP HANA. More info will follow.

          cheers,

          Frans 

          (0) 
  2. Luke Marson

    Great blog Frans and a good level of detail on how this works technically. This will bring a lot of benefits to SAP Payroll customers, particularly around reporting and analytics. I’d also be interested to see if this will eventually extend to SuccessFactors Employee Central Payroll.

    (0) 
    1. Frans Smolders Post author

      Hi Luke,

      Thanks for the kind words! I just replied to Jarret’s question which is similar:

      The EC payroll is indeed similar to the functionality we offer on-prem. Therefore, declustering becomes available as well in the cloud. There is not final date yet on when the EC Payroll moves to SAP HANA.

      For EC payroll reporting the goal is to use our SuccessFactors Workforce Analytics platform with the embedded Online Report Designer. The mock-ups I’ve put in this post are based on SAP BI tools, and therefore screens will look a bit different in our cloud offering.

      Best regards,

      Frans   

      (0) 
  3. Jyoti Sharma

    Hi Frans,

    This is super exciting news, and you have explained the declustering mechanism well. No more wait time with the Wage Type reporter, Yes!

    In addition to Jaret and Luke’s suggestions, I think something to consider for future will be if there is any possibility to decluster the Time and Attendance data that EC payroll customers are receiving from providers like Kronos and WorkForce Software.

    Warm Regards,

    Jyoti

    (0) 
    1. Frans Smolders Post author

      Hi Jyoti,

      Thanks for the kind words. Can you please elaborate a bit more on your use-case? Do you mean that Kronos and Workforce Software provide data in clusters or do mean that you load it into the EC payroll via the RPTIME?

      cheers,

      Frans

      (0) 
    1. Frans Smolders Post author

      Hi,

      the Payroll already runs on HANA! Declustering is key to have performing reporting and it’s key for future developments. In addition, there are deliveries planned for reconciliation functionality and UX renovations.

      I’ll keep you posted!

      cheers,

      Frans 

      (0) 
  4. Demis Backx

    Hello Frans,

    Very clearly explanation. This might help a lot of customers. I think that this functionality also helps customers to start to make use of archiving of their clusters. I experience that SAP customers have fear to remove cluster data out of their database into the archive. Now their are able to keep it availble for reporting, the data can be moved on to cheaper data carriers.

    (0) 
  5. Pradyp Parakala

    Hi Frans

    Very Interesting, want to see in real how does this work. can u let us more more in brief about what system landscapes will help us to achieve for HCM HANA.

    Cheers πŸ˜‰

    Pradyp

    (0) 
  6. Suma Rammohan

    Hi Frans,

    Great Blog !!! Was waiting to know about the optimizations in HCM area of Suite on HANA.

    Have a quick query around migration to Suite on HANA.

    In case of Suite on HANA migration projects, when a HR customer (HCM solution on normal database) chooses to migrate to Suite on HANA, post migration, will the Payroll and Time results stored earlier in PCL1 and PCL2 clusters automatically will now be moved to transparent tables in HANA system (owing to the fact that HANA system will no longer have pooled/cluster tables ? If this is true , it would not be required to use SAP HCM De-clustering Tools right ?

    With Payroll / time data now available in transparent tables in the Suite on HANA system, it would now allow to process or retrieve HR data using complex SQL queries and perform aggregations on these results right at the database layer through HANA Live.

    Regards,

    Suma

    (0) 
    1. Frans Smolders Post author

      Hi Suma,

      You’re right in your assumption. And there are 2 options to transfer the declustered data into:

      1. suite on HANA;

      2. side-car approach in which the customer has a seperate SAP HANA box in which declustered will be replicated.  

      cheers,

      Frans

      (0) 
      1. Chandrakant Mohanty

        Hi Frans ,

        After reading this blog I understand that after migration, the Payroll result will be stored both in cluster and transparent table in the suite on HANA.

        If my understanding is correct I would be keen to know why the data needs to be stored in cluster and transparent tables?

        Regards,

        Chandrakant

        (0) 
        1. Frans Smolders Post author

          Hi Chandrakant,

          Your assumption is correct. Reason is that we can not step away from cluster technology for the payroll. That would mean that we completely recode the payroll and there are too many dependencies that hinder that for now. Nevertheless, we need data in declustered tables to enable speed and smart solutions for payroll validation and reconciliation. Hence the current need for both. In the future that might change, but for the interim this is the way to go.

          best regards,

          Frans

          (0) 
          1. Paul Worland

            Sorry to dig up an old response….

            Presumably then on a HANArised system with declustering performed, the old transactions such as PC_PAYRESULT and the Wage type reporter (PC00_M99_CWTR) still work as they used to?

            Further, custom code that reads the pay results (Spinifex easy reporting tools come to mind here) will also continue to work, albeit not with the performance gains they used to have?

            (0) 
            1. Eric Pendergrass

              I have a similar question.. it is stated in this blog that declustering would provide performance benefits to the existing wagetype reporter, etc, then it is also said that the payroll process is still using the cluster tables because of the amount of work it would take to recode it to use the new transparent tables.

              Does that imply that if declustering is switched on, the existing reporting tools in a suite on HANA have been updated and are capable of using the new faster data source?

              The environment I am currently working with is suite on HANA and the declustered tables are present in the system but are not populated (switched off.) We are considering the benefits of switching it on before going live so that we have all the data in place. It would be good to know about whether or not performance benefits will exist immediately or not.

              Thanks!

              (0) 
              1. Frans Smolders Post author

                Hi Eric,

                I did not mean that the declustering brings advantages to the wage type reporter in it’s current form. With declustering we can come to better results (mainly in relation to speed). Unfortunately we did not make any changes to wage type reporter. We use declustering for SAP Payroll control center add-on. That replaces to a large extend the need for the wage type reporter.

                Best regards,

                Frans

                (0) 
  7. James Barron

    Hi Frans,

    Thanks for the great article.

    I’m curious how much of the performance improvement is HANA vs using flat tables. e.g. may well be “only” 5x faster from using flat declustered tables on non-HANA database but 200+ times faster declustered & HANA.

    I realize the bulk of the phenomenal improvement will be HANA but many customers may be very happy indeed with say infoset queries on the declustered tables (with the better output options, PA data etc etc) even if “merely” say 5x quicker than WT reporter. i.e. something they can have NOW without waiting for HANA, BI… to come thru many companies glacial upgrade cycles.

    Thanks,

    james

    (0) 
    1. Frans Smolders Post author

      Hi James,

      I fully understand and agree. It’s hard to say what performance will be. What we know is that the data size on a normal DB will grow between factor 2-5.

      However, when customers chose to limit the amount of tables (e.g. huge tables like CRT/TCRT in the US would simply blow up the data base if declustered, without adding much value) and time dimensions (and with that data size) that they want to ‘decluster’ for they can already experience the value immediately!

      And getting started with the declustering is really relatively simple and low cost. So it’s easy to try out what works best for each customer.

      best regards,

      Frans

      (0) 
      1. James Barron

        Hi Frans,

        Have access to a HANA playpen with declustering done – intend to do some testing as time allows.

        First question though is I can see a table

        P2RX_WPBP_INDEX “HRDCD HELPER TABLES FOR WPBP” (dict below)

        1 .INCLUDE  

        2 MANDT MANDT C 6

        3 .INCLUDE  

        4 DCT_PERNR P_PERNR N 16

        5 DCT_SEQNR CDSEQ N 10

        6 DCT_ROWID HRDCT_ROWID N 10

        7 RT_APZNR APZNR X 1

        8 WPBP_APZNR APZNR X 1

        P2RX_WPBP populates but no records in P2RX_WPBP_INDEX. I can’t find any info on WPBP_INDEX anywhere but from the structure wonder if it’s intended to be an index of FPPER/INPER back to immediate previous FPPER (similar to PCALAC is to PPOIX).

        An index like this could dramatically improve the reads needed to calc the FPPER – last FPPER delta diffs (hence my interest).

        (On the other hand it maybe more likely just some attempt to put a short index on the largish WPBP – less interested)

        Do you have any idea where P2RX_WPBP_INDEX fits into the scheme of things? I can’t work out if its an orphan, red herring, something in development or what.

        Cheers,

        james

        (0) 
        1. James Barron

          ps: I just put together an infoset in the decluster tables on a playpen that has had WPBP & RT declustered. (took about 4 hours but I kind of do know what I’m doing πŸ˜† )

          Running in a slow sandpit reporting PERNR FPPER LGART on /101 for 13722 person/FPPER (i.e. 13722 rows)

          SAP Query on decluster tables 1:08 (with full RT / WPBP lowest line level 1:22)

          Wagetype Reporter 4:21 (comparable WPBP detail not possible)

          Running for all wagetypes in cust range (0000-9999)

          SAP Query on decluster tables 1:13 + 13 sec transfer app to ALV so 1:26

          Wagetype reporter – I got bored watching for it to finish at 12 minutes so don’t have exact time but it did run so somewhere over12min but less than the 20min process timeout.

          There is a bit more code to go into the SAP Query to be strictly comparable (auth checks for 0008 & payresults + some tweaking on delta diffs) – say +25% to the SAP Query times quoted above – but we’re still looking at something that runs 3 to 10 times quicker then Wagetype Reporter (& another factor again quicker then the third party reporting tool this client has) while delivering far more detail than WT Reporter – I’d say that’s worth having!

          Basically the advantages of an intelligently written program running on flat tables rather than clusters i.e. I’m sure these relative run time advantages would hold true regardless of HANA etc.

          Cheers,

          james

          updated:

          with the index tables now allowing full SQL select Infoset Query (aka Ad Hoc) is 15 to 50+ times faster than WT reporter on identical data (the range is the commonality of the selected wagetype eg /101 occurs all results so 15x diff but reporting across a payroll on an uncommon wagetype can be 50+ as SQL SELECT directly accesses the rows but WT reporter still has to read & unpack & loop on RT for each result)

          Some of the data required to be declustered is missing though – not production ready yet πŸ™

          (0) 
    1. Frans Smolders Post author

      Hi Aamod,

      Currently the payroll and the time cluster are declustered. Unfortunately PCL1 isn’t. Can you tell me more about the use case, so that I can try to advise in it?

      cheers,

      Frans

      (0) 
  8. Bhanu Prakash

    Hi Frans Smolders,

    In Side By Side scenario:

    after Declustering  PCL2 table does transparent tables created in HCM system running on DB likes oracle/DB or else in HANA ?

    if transparent tables created in conventional DB’s then should we use SLT to replicate them into HANA? or else should we need create secondary DB connection in HCM system to HANA ?

    how does it works ..?

    Thanks

    Prakash

    (0) 
    1. Fabian Guenther

      Hi Prakash,

      when using the side-by-side approach (HCM system running on conventional DB, secondary HANA DB) you should avoid filling the transparent tables in the HCM system. On a conventional DB they become pretty big, and you should have very good reasons to nevertheless fill them (and if you do so, use SLT to replicate their contents to HANA if needed there, too). In HANA, they are even smaller than PCL2 because of compression and no need for indices.

      However, there are two feasible options to fill them in HANA only. For both options, you of course need to create the tables in HANA – either manually or using SLT. To actually fill them, you can do the following:

      1. Run the declustering initial load report RPCDCT_INITIAL_LOAD with configured secondary DB connection after finishing the payroll run.
      2. Use the declustering capabilities of SLT (configure the corresponding PCL2 relid for replication but configure SLT in a way that the replicated PCL2 records are put into the transparent tables instead).

      Option 1 comes out of the box and is typically integrated into the payroll process for one period – just fill the tables asynchronously after the payroll run.

      Option 2 requires more sophisticated SLT configuration but has the advantage that each change of a PCL2 record is nearly immediately available on HANA, and you don’t need an explicit additional process step to perform the declustering.

      Which option you take depends on the way how you want to use the transparent tables. If you are fine with starting the initial load report after each payroll run I would suggest option 1. Such a start can be automated for sure using some process control tools like the payroll process manager (“HR process workbench”).

      The best option of course is running the HCM system directly on HANA, then you can use the synchronous declustering during the payroll run (which I would not suggest when using the secondary connection to HANA only), you don’t have the complexity with the data replications and you can take advantage of further optimizations.

      Best regards

      Fabian

      (0) 
      1. Sami EL BALILI

        Hi Mr. Ghuenter,

        I´,m EL BALILI SAMI From University Dortmund in Germany.

        Thank you for this interesting Dicussion but i have a question for you:

        I have a programm that´s read only Personal Data from a PCL1 and PCL2 clusters. Normaly i need Makros to read this Data. But in SAP HANA there is no Clusters. Hier is my Test Code:

        REPORT  zz_elbalili_cluster_test.

        TABLES: pernr,

                pcl1,                        “Zeit-Cluster

                pcl2.                        “Abrechnungs-Cluster

        INCLUDE rpc2cd00.                    “Cluster CD Data-Definition

        INCLUDE rpc2rdd0.                    “Cluster RD Data-Definition

        INCLUDE rpppxd00.                      “Data-Definition Puffer PCL1/PCL2

        INCLUDE rpppxd10.                    “Common Part Puffer PCL1/PCL2

        INCLUDE rpppxm00.                    “Pufferverwaltungsroutinen

        DATA: gv_pabrp    TYPE pnppabrp,

              gv_pabrj    TYPE pnppabrj,

              gt_res      TYPE STANDARD TABLE OF payde_result,

              gs_res      TYPE payde_result,

              gs_rt       TYPE pc207.

        GET pernr.

        *—– 1. Import

          cd-key = pernr-pernr.

          rp-imp-c2-cu.          “automatische Umstellung durch SAP oder manuelle Anpassung?

          CHECK  rp-imp-cd-subrc EQ 0.

          LOOP AT    rgdir

             WHERE fpper EQ pn-begda(6)

             AND   srtza EQ ‘A’.

            rx-key-pernr = pernr-pernr.

            UNPACK rgdir-seqnr TO rx-key-seqno.

            rp-imp-c2-rd.       “automatische Umstellung durch SAP oder manuelle Anpassung?

            IF rp-imp-rd-subrc EQ 0.

              LOOP AT rt WHERE lgart GE ‘0000’.

                WRITE:/001 ‘Import’,

                       010 pernr-pernr,

                       020 rt-lgart,

                       030 rt-betrg,

                       050 rt-anzhl.

              ENDLOOP.

            ENDIF.

          ENDLOOP.

        *—– 2. FB

          gv_pabrp = pn-begda+4(2).

          gv_pabrj = pn-begda(4).

          CALL FUNCTION ‘HR_GET_PAYROLL_RESULTS’ “automatische Umstellung durch SAP oder manuelle Anpassung?

            EXPORTING

              pernr      = pernr-pernr

              pabrj      = gv_pabrj

              pabrp      = gv_pabrp

              actual     = ‘A’

            TABLES

              result_tab = gt_res

            EXCEPTIONS

              no_results = 1

              OTHERS     = 4.

          CHECK sy-subrc EQ 0.

          LOOP AT gt_res INTO gs_res.

          ENDLOOP.

          CHECK sy-subrc EQ 0.

          SKIP.

          LOOP AT gs_res-inter-rt INTO gs_rt

                                  WHERE lgart GE ‘0000’.

            WRITE:/001 ‘FB’,

                   010 pernr-pernr,

                   020 gs_rt-lgart,

                   030 gs_rt-betrg,

                   050 gs_rt-anzhl.

          ENDLOOP.

          SKIP 3.

        Should i do any modifications in this code?

        Thanks.

        (0) 
  9. Barin DESAI

    Good blog. One question however. If I have a customer who is already running payroll on clustered environment and then if I try to decluster RT because of specific requirements then what is the impact on existing payroll and other related integrations of modules with payroll.

    My assumption is none however wanted to check.

    (0) 
  10. Edgar Valverde

    I am currently implementing PCC for a company in Mexico, the entire process is working ok for me, but I have problem running the policies, check the configuration is correct,Β Someone can help me with any comments or hint for check this problem, the error in the instance of PCC is “not execute instance found” “Stop Execution”

    (0) 

Leave a Reply