The chemical industry faces the legal requirement that products must be compliant with the respective regulations. These regulations can be country specific (e.g. chemical classification under GHS) or industry specific (e.g. classification under IFRA for the fragrance industry) or both (e.g. country and industry specific regulations for the food/flavor industry). Before a product can be sold, the selling company has to ensure that it is compliant to all applicable regulations. Therefore, the following statement can be made: The faster the compliance status of a product has been assessed, the faster the product can be sold!

What are our Customer’s Needs?

In order to accelerate their business, our customer belonging to the fragrance and flavor industry decided to bring compliance checks and calculations to SAP HANA and link it to the SAP EHS database, where all needed data to retrieve a compliance status of a product can be maintained.

This step not only improves performance and safeguards the product compliance in general, but also enables the company to perform legal requirement checks on the fly during product development or in case of specific customer requests.

Main Challenge: Performance

The SAP EHS database of the customer contains almost 600’000 products and approximately 50’000 raw materials. Additionally, most products have a deeply nested product structure – a product can consist of hundreds of ingredients.

Prior to retrieving the overall compliance status of a product, each single ingredient needs to be checked as well. This results in a quite complex calculation algorithm since product hierarchy must be strictly obeyed.

Hence, it is obvious that the workload for the calculation engine is quite high, especially, when it comes to the automated process of re-calculation of the whole product portfolio. In case there is a change of an ingredient (e.g. classification change) or raw material (e.g. composition change) all affected products have to be determined and re-calculated. Thus, one of the main challenges was to implement the calculation in a highly performing way.

The History of Our Project

The project started in 2011 for the fragrance division and 2013 for the flavor division. Fragrance went live end of 2012, while the project for flavor is still ongoing (go live expected to take place beginning of 2015).

Important to note, that this time period also included the proof of concept and specification phase – the compliance check requirements were not in line with the available standard functionality (SERC Content and EHS Expert). In addition an SAP HANA prototype was built together with the Performance Insight Optimization (PIO) team.

This prototype revealed the power of HANA for even quite complex calculation procedures and convinced the customer to continue with HANA and agreed on a Customer Development (CD) project. The project itself has been conducted by team members of EHS Consulting, CD and PIO.

The Fragrance Division has been running the new solution for more than a year and the first positive results can be already measured: sales volume and profit increased in 2013.

Our Conclusion

Even though the implemented checks / calculations have been designed on customer specific requirements (and are not available as a standard delivery), this project proved that SAP HANA is far more than an analytical reporting engine. It can be used as a powerful calculation engine! Therefore, we believe that the concept of this project can be re-used for basically every company using SAP EHS and dealing with big data volume in order to establish an automated process for EH&S compliance calculations. It will not only speed up the daily work but the business itself as well.

Authors: Göril Möschner and Manuel Knäple

To learn more about how SAP HANA Services can help you to eliminate barriers to fast, informed decisions, please visit us online: http://www.sap.com/services-support/svc/in-memory-computing.html

To report this post you need to login first.

17 Comments

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

  1. Christoph Bergemann

    Hello Göril

    this topic of use of SAP HANA has been discussed in other threads/blogs etc. to a certain extent. Looking now to this nice document my interpretation is still this:

    SAP will go on in use of EHS expert for normal chemical industry; no general SAP HANA based approach similar to EHS expert is planned on long term; development, if needed, could be done by SAP using the  CD approach based on the results of this project. But why is the interest of chemical industry so “low” to get a SAP HANA “rule set” based product from SAP as a “Standard” package? There are more and more regulations to consider. There is more and more the need to calculate fast and robust “if then else” scenarios (e.g. exchanging raw materail A by B; what happens?)

    Can you may be provide additional information here?

    On the top:

    To my knowlegde this SAP HANA based approach is only for calculating something; doing analytic research (e.g. like what will happen if) but no data is “written” back to SAP EHS (or is this done?) so that e.g. later the caculated data could be used in safety data sheet generation of label generation or…

    Thanks for sharing this information with EHS community.

    C.B.

    (0) 
    1. Goeril Knauff-Moeschner Post author

      Hi Christoph

      Easy answer first: yes, the data is written back to EHS.

      The requirement of our customer was that compliance documents are generated on demand displaying results calculated by HANA. Therefore, we have implemented for the Fragrance division the following scenario:

      If a user requests a compliance document (e.g. certificate on naturalness) for a chosen product, this request is linked to the check “product must be 100% natural”). This check is then calculated by HANA and the result is written back to EHS. Then document is generated (WWI) and issued to the external document management and the user will receive it via email. Around 20 certificates are handled in such a way.

      For the flavor division a slightly different approach may apply, depending on requested result to be printed. In some cases the HANA result is not stored in EHS but delivered during final report generation – means a parameter symbol is filled.

      However, the implemented compliance checks / calculations on HANA differ from the content calculated by EHS expert and delivered by standard rule sets. Thus, data needed for MSDS are still retrieved by this functionality.

      Concerning your question about standard delivery: As far as I know you are right – no general SAP HANA based approach similar to EHS Expert is planned so far. I am not involved in product development and thus, don’t know which kind of requirements are made by other chemical companies. My guess is that not many chemical companies are dealing with so many products and ingredients and therefore EHS Expert (rule set calculations) might not be the limiting factor concerning performance. However, I think I can also make the statement that our customer (mentioned in this blog) would appreciate “SAP HANA rule sets” delivered as a “standard package” as well. 

      (0) 
      1. Christoph Bergemann

        Dear Goeril

        thanks for this very informative information. In a different blog the SAP HANA topics was discussed as well. There it has been explained that the data extraction from SAP EHS is done using ETL process ( Determing Chemical Compliance with SAP HANA in Product Development

        In this blog no information was provided how the data is coming back to EHS. Do you use in your scenario RFC, ALE or which technique is used to write back data to EHS? There are a lot of reasons why I am interested in the answer.

        a.) e.g. the whole scenario is worth to think about to use it as the EHS performance as is “bad”; Ok SAP has improved the EHS Expert Server process (highly welcome) but reading data in EHS is “expensive” from performance point of view

        b.) if you write back the data to EHS the data in EHS could be “blogged” by a user in EDIT mode; how do you handle this situation in your concept?

        c.) and coming back to the WWI topic: is the process designed in such a way that it is nearly  “automatic”?; that means if the data is written back to EHS is there a subsequent “automatic” procss trigger which regenerated th WWI documents or is the generation of the WWI documents a “manual” task

        d.) and on the top I am interested in “error” message handling. E.g. the “rule set” in HANA needs some data; if this data is not available how is this handled in the process?; does the “user” get feedback about calculation which are successful done and those which aren’t sucessfull and user  (e.g as a log) is getting feedback regarding the reason of the problem?

        e.) is the whole process triggered only by user interaction or there the “on top” functionality to execute is as a job”night” job (e.g. by specifying a hit lits of specification for which the calculation should run)?

        I hope that you are able to provide some feedback regarding my questions.

        C.B.

        (0) 
        1. Goeril Knauff-Moeschner Post author

          Hi Christoph

          In our scenario EHS and HANA are linked with SLT (System Landscape Transformation). If I remember correctly, the advantage here was, that if a user hits the “save button” in CG02 data is directly replicated to HANA. Furthermore, ADBC (ABAP Database Connector) is used to connect EHS to the HANA database. Since I am not the expert here, I will leave it up to Manuel to elaborate this more in detail. Same for error messages and “lock issues”. In general, an error message (data cannot be written back or data is missing or connection is lost) is displayed (pop-up check dialogue screen) or sent back (external document management system).

          Concerning WWI:

          Yes, it is an automated process. The only manual interaction for sure needed is that the user requests the document in the external DMS. After retrieving the doc request HANA calculates, then data is written back to EHS, WWI generation starts and report is shipped (sent to the external DMS). There might be reasons that the report is not automatically shipped (e.g. manual review and release or rejection required) or even aborted (e.g. check result was not compliant). In this case this information is sent as message to the external DMS to inform the user. But for the automated WWI generation and report shipment we re-use a functionality which has been built already during Product Safety implementation for MSDSs.

          Concerning trigger:

          The HANA calculation is triggered either by manual activity (user decides which product(s) and which calculations/check) or automatically. The automated calculation will take place whenever the system detects a relevant change – either on the product or on a compliance object. I don’t know exactly in which time frames the jobs are running, but I assume it something like every 15 or 30 minutes. But Manuel can help out here as well for sure =)

          He will be back in the office tomorrow and will pick up the open questions then.

          For now, I do hope that my answer is sufficient as a first feedback!

          Goeril

          (0) 
          1. Manuel Knaeple

            Hi Christoph,

            as indicated already above by Göril we use HANA in a “side-by-side” scenario. This means, that the main persistency of all the EHS data is still a legacy DB and only the relevant data required for the compliance check functions is replicated to HANA. For this purpose we utilize SAP SLT (System Landscape Transformation) as ETL tool which is capable to identify changes in the EHS master data and replicate them to the HANA DB within a few seconds.

            In order to trigger the compliance checks implemented in HANA as database procedures we make use of ADBC (ABAP Database Connectivity) which is an API for the Native SQL Interface.

            Data-locks caused by a user who is currently editing an object in EHS are not an issue since we either only display the results obtained from HANA in a “result screen” or we save the results in Z-Tables for further reference which the user technically cannot edit resp. lock. So this means it is not like the EHS Standard feature “Secondary Data Determination” where the results were written back to the specification but we have our own persistency for the results.

            Regarding the error handling we have to distinguish two cases:

            In case of technical errors, e.g. some unexpected issue in HANA, we receive an Exception that is mapped to a meaningful message which is shown to the user via a pop up after one has triggered the compliance calculation.

            In case of missing data resp. in general in case of master data maintenance issues it is clearly defined what shall happen in each case. Example: In case that not all required information are maintained on certain objects involved in the compliance check we obtain the IDs of these objects in a separate error table from HANA. So if this error table is empty we now that everything went fine. Else we display the “incomplete objects” in the result screen to make the user aware.

            Kind regards,
            Manu

            (0) 
            1. Christoph Bergemann

              Dear Manuel

              I can only say “wow”… 🙂 😎 Thanks for your explanations. I have now a better picture about the high level scenario used etc.

              There is only two “slight” conflicts left there i hope you can clearify may be my misinterpretation of the solution:

              Goeril wrote:

              Yes, it is an automated process. The only manual interaction for sure needed is that the user requests the document in the external DMS. After retrieving the doc request HANA calculates, then data is written back to EHS, WWI generation starts and report is shipped (sent to the external DMS)

              And you wrote:

              . So this means it is not like the EHS Standard feature “Secondary Data Determination” where the results were written back to the specification but we have our own persistency for the results


              Question now: Data is read in EHS pushed ot SAP HANA calculated; goes back to an “own presistency” layer and then WWI process is started. But with which data? Data from EHS or data from EHS and from the persestency layer together or is the calculated data not used for WWI generation?


              Second topic:

              After rereading the whole article and your comment may be I did not really understood the source of data used for calcxulaiotn in SAP HANA.


              It has been explained by you:

              This means, that the main persistency of all the EHS data is still a legacy DB and only the relevant data required for the compliance check functions is replicated to HANA.


              Goeril wrote:

              The SAP EHS database of the customer contains almost 600’000 products and approximately 50’000 raw materials


              So may assumption has been:  any data used by SAP HANA is stored in SAP EHS and retrieved from SAP EHS;  Did I misintepret this? or what is the meaning of “legacy DB” ?


              C.B.

              (0) 
              1. Manuel Knaeple

                Dear Christoph,

                to be precise we save the results coming back from HANA in Z-Tables as mentioned already and in addition, in order to enable the generation of documents via WWI, we save the data in a specification in EHS of a certain specification type which is “hidden” for the user, means the user is not supposed to edit and lock these kind of specifications. WWI is pulling then the data from these specifications.

                The 600.000 products and the 50.000 raw materials are stored in EHS which technically runs on a normal DB like Oracle or MaxDB or whatever (this is what I mean with “legacy db”). To enable the compliance checks we have replicated all relevant data into HANA, means a sub-set of all the data stored in EHS does co-exist in HANA. Every change on this sub-set in EHS  will be replicated to HANA.

                Hope this clarifies.

                BR Manu

                (0) 
                1. Christoph Bergemann

                  Dear Manuel

                  once again “wow” 😎 . Very clever use of SAP standards and options ! 😎 :

                  Congratulation to you and the team in preparing such a nice scenario. This hides “complexity” and the user does not realize that WWi document is generated by hidden data

                  Really clever solution; indeed.

                  C.B.

                  (0) 
      2. Michael Schmalfeldt

        Hi Goeril,

        regarding your comment:

        “My guess is that not many chemical companies are dealing with so many products and ingredients and therefore EHS Expert (rule set calculations) might not be the limiting factor concerning performance.”

        We are also a CLEO customer and are using SAP EH&S Expert for many years. We are not happy at all with the performance of the Expert tool. We are using MULTI CALLs (combined Expert rule sets) and for a single product the runtime is around 2 minutes (!!). For us the performance of the Expert tool DEFINTELY is the limiting factor. Especially when mass updates for e.g. 2000 products have to be done.

        However, I think I can also make the statement that our customer (mentioned in this blog) would appreciate “SAP HANA rule sets” delivered as a “standard package” as well.

        We couldn’t agree more…

        Kind regards, Michael

        (0) 
        1. Christoph Bergemann

          Dear Michael

          a.) EHS classic has been enhanced with some OSS notes regarding performance. To use EHS Expert rules first the data need to be selected. Here EHS is not perfect in regards of performance. Perfomance relevant OSS notes are coming up from time to time. Check your system (and may be check: Performance improvements – Specification Workbench )

          b.) Regarding EHS Expert as well some enhancement is possible may be check as well: Overview: SAP EHS Management in context of Enhancement Package / Support Package

          I can agree to your general statement. Chemical companies would like to have “general” riuleset so that you can run them (as a cascade if necessary) to consider changes in data. Therefore a further success factor for use of rule sets is the “set up of them”; that means do you would like to use “small” ones; block like, or global (e.g. calculate only OEL, calculate only MSDS/SDS content or calculate all includting status of registration etc.). In most cases any of the three (or more scenarios) is the best choice as now the user can select what he/she would like to execute

          C.B..

          PS: and many companies are asking for tools to check “if then scenarios” withotu writing back data to databasse; .e.g. what would happen if I exchange raw material A by B in regards of e.g. classification/labeling etc. stuff; this in clused as well the “there used list” scenario (here as well EHS performance is not the “best”)

          (0) 
        2. Goeril Knauff-Moeschner Post author

          Hi Michael

          Sorry for the late reply, but I was on vacation…

          Actually, Christoph has provided already all the information which I could have given you – even the better one, because I was not aware that there is a SCN document compiling all the available notes…

          However, I do understand your comments and your concerns. And believe me, I raise this topic (rule sets on HANA) from time to time, whenever possible. But my influence here is very limited. You as a customer could have much more influence by raising this topic to your SAP contact person and / or participate in the so-called co-innovation groups – where product management is in direct contact to the participating companies. In those groups companies can directly address what they want / need and thus, can have influence on the functionalities delivered by the next release(s).

          Kind regards

          Goeril

          (0) 
  2. David Beck

    Hi Göril. Hi Manuel.

    Having a little bit of an insight into the amount of data you were facing, the complexity of the product structures and compositions, and also the requirements on all the different checks that need to executed and combined to get the right result, I am really impressed by the success story of this project.

    Congratulations and “Chapeau!”

    (0) 
    1. Goeril Knauff-Moeschner Post author

      Hi David

      Thanks a lot!

      No need to tell you that without Michael Bockelmann, Andreas Chudalla, Yujie Ren, you (all EHS consulting), Mirko Luedde (PIO) and Mirco Stern (CD) this project would not have been that successful!

      (0) 
        1. Goeril Knauff-Moeschner Post author

          Hi Sethu

          Since they are a SAP reference customer, I think it is possible to share this article with Colgate. Concerning the call, I will send a separate email to you including the account manager who should drive this.

          (0) 

Leave a Reply