In this article we are explaining How to use Base_UserDefined transform step by step with an example of  Replacing Special characters from the

input field(Salary).

Base_UserDefined Transform

This transform provides an interface to do anything that you can write Python code to do. You can use the User-Defined transform to create new records and data sets, or populate a field with a specific value, just to name a few possibilities.

  1. Create Input File and place a Base_UserDefined transform of Data Services and link the transform with the file .

             

  1. Open Base_UserDefined  in the Input tab of the transform, drag and drop Input column of the input schema to the Input Schema Column Name so as to map with the Transform Input Field Name .

         

3.  Check the Options tab and Click on Edit Options Button to customize the transform.

             

  1. Check Per record , then select Python Expression Editor and Click on Launch Python Editor Button to open the Python Editor .

       (Option :- Per Record:- To deal all record independently ,Per collection:- Build a group of records on basis of  Break group conditions like Base_match transform in this example we will go with Per record option and in next Topic we will proceed with Per collection option )

         

  1. Python Editor:- a.) I/O Field :- Insert the Input/Output fields according to requirement by  Right clicking on particular option Input Fields / Output Fields then click on Insert give the field name and length of field .  b.) Python API :- Python functions GetField to get the input field value and SetField to set output field value.  c.) Editor :- Write your Python code  here .

  (For more details about code refer any Python Book) 

              

  1. On the Output tab select the fields of interest.

7.  Result :-

Please find the input and the output dataset generated as screenshots below:

In Part-2 I will explain used Python code in detail

To report this post you need to login first.

24 Comments

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

    1. Scott Broadway

      Not sure I want my ETL/DQ tool sending me a text message.  Yeah it looks cool in a demo, but why?  In fact, I’m kind of wary of DS being allowed to send emails.  I’d much rather just have DS be able to initiate a workflow request in a ticket system.  Put whatever notification mechanisms you need into the ticket system and allow the end user to customize them.  Don’t hard-code that kind of notification in DS. 

       

      And, better yet, if you need that kind of notification from DS?  Have DS call a web service function.

       

      I like Python as much as the next guy, but I don’t want to see Data Services as a glorified front end for Python.

      (0) 
  1. kamal kumar Post author

    Thanks Scott and Mikhail  for showing interest in the topic.

     

    I want to add some things before going with User Defined transform …

    1. First analyse your requirement

    2. Look for the existing functions of the tool.

    3. If not exist go with Custom function or User defined Transform.

     

    like Mikhail shared the link in which they are sending the mail, for that BODS providing the existing function

    mail_to(recipients_list, subject, message, number_of_trace_lines, number_of_error_lines)

     

     

    Following are  Python features which we can use in User_defined transform these are mentioned in help book itself..

     

    The software has its own Python module that contains five classes:

    • FLDataCollection class
    • FLDataManager class
    • FLDataRecord class
    • FLProperties class
    • FLPythonString class

    Each of these classes has one or more methods.

     

    Third-party Python libraries

    To ensure that your Python expressions run correctly, make sure that all third-party python libraries are in the appropriate dynamic library path for your operating system so that the dependencies are resolved. If you find that a Python library is not working correctly, update the library path (LD_LIBRARY_PATH for Solaris and Linux, LIBPATH for AIX, and SHLIB_PATH for HP) in the environment where the AL_JobService is installed, and restart the job service.

     

    Regards,

    Kamal

    (0) 
  2. Mikhail Budilov

    The software has its own Python module that contains five classes:

    • FLDataCollection class
    • FLDataManager class
    • FLDataRecord class
    • FLProperties class
    • FLPythonString class

    Each of these classes has one or more methods.

     

     

    Not so match as required, but i hope sap will add more methods, properties, etc.

    (0) 
  3. Samual Krish R

    Good Post. But I have a question does the output of the python variable is always datatyped to varchar 255.

    If not so is there a way to increase the variable length.

    Could you tell me how to store data in global variable of a particular job?

    (0) 
  4. Neha Khetan

    Hi Kamal,

     

    I was trying the User_Defined transform as given in the above article.

     

    But i have some issues in using this transform.I am unable to figure out.Plz help me.

     

    1)In the Options tab,Edit Options button is non clickable.Is there any setting to use it.

    2)In the Output tab,STD_Sal check box is not showing in the Mapped_Name.

     

    I am attaching 2 screenshot for better understanding.User_Defined.jpgUser_Defined2.jpg
    Thanks in advance.

     

    Neha Khetan

    (0) 
    1. Venkata Ramana Paidi

      Hi Neha,

       

      The problem is not with User defined transform.   Problem is here your windows pop up disabled or  something is stopping to open .

       

      Try to check the Match transform edit option button also . If you are facing same issue   try to disable the antivirus and try.

       

      This type of problems we face in match, user defined and associate transform  as having edit options.

       

      see the below SAP notes.

       

      1925789 – Click on the Edit Options button in the Match transform and nothing happens, no pop up window appears – Data Services

       

      Cause

      The antivirus software running on the box is not allowing the popup to occur.

      Resolution

      Disable the antivirus software or at the least exclude the Data Services install directory from it.

       

       

       

      Thanks & Regards,

      Ramana.

      (0) 
      1. Neha Khetan

        Hi Venkata,

         

        I have tried with Match transform also and Edit Options is not working.

        As i am working in my ofc i cant disable the antivirus.

         

        Is there any other workaround?

         

        Regards,

        Neha Khetan

        (0) 
          1. Terry Chang

            I suggest a generic solution for this antivirus problem – Sometimes antiviruses are set to block programs, ports etc. automatically. Go in the settings of your AV and try to change that setting. Make it ask for user action instead. When you run your SAP stuff, your AV will detect something and then you can allow that thing to run. The downside is that other programs in your computer will also cause such warnings from your AV. To prevent that, remember which “SAP things” you allowed your AV to run. Then, add those things to the list of exceptions in your AV (AV settings or firewall settings) and switch back to automatic mode.

             

            Simple analogy – The guard (AV) at your house (computer) will shoo away anyone (software, external computers) who SEEMS suspicious. Tell the guard to show you
            the face of all these people and then you decide which one is to be allowed. Your were
            expecting your friend (SAP) to come home and have free access to your living room.

            The guard now shows you his face and you tell him to remember it (Adding exceptions to Virus Scanner or Firewall). After allowing your buddy inside, you tell the guard should go back to automatically shooing away anyone who looks strange, even if its your mom-in-law. Don’t add an exception for her

            (0) 
  5. Joshua Blythe

    If you really get into working with python in these user defined transforms, something handy to use if you’re using complex data structures…  (dictionary,multi-dimensional lists) is the pickle module.  You open a file handle and can write the contents of the dictionary to a file, then open it up from outside of DS to work on. 

     

    import cPickle

    OH = open(r’\\Dsvcs1\repository\user\jblythe\wms_work\dctOrder.dat’,’wb’)

    cPickle.dump(dctOrder,OH,2)

    OH.close()

     

    to import it to your desktop python…

     

    import cPickle

    IH = open(r’C:\Users\jblythe\Desktop\dctOrder.dat’,’rb’)

    dctOrder = cPickle.load(IH)

    IH.close()

     

    this makes debugging python code for use in DS a piece of cake.

    (0) 
    1. Neha Khetan

      Thank You for your explanation.As i am very new to this tool I was only trying to use the User Defined transform.

       

      Neha Khetan

      (0) 
  6. Terry Chang

    I wonder how SAP can continue to provide such a complicated, rudimentary, junk ETL tool. SSIS is far easier to learn and use compared to this monstrosity.

    This tutorial is excellent, but it also shows how SAP is so complex.

    SSIS coding – Drag script task onto screen, choose VB or C#  and start coding in that language. Do ANYTHING you like. Simple.

    SAP – Read above tutorial and do several steps. Do ANYTHING ??? Maybe not.

    I wonder why SAP does not make it like SSIS. Looks like the word “intuitive” is missing from SAP’s dictionary.

    (0) 
  7. Swetha N

    is there a possibility of coding in any other language? like the same BODS scripting language?

    learning python will add an other overhead.

    (0) 
  8. Swetha N

    Hi all,

    1) why are we accessign the input field SALARY as (u’SALARY’) here?

    2) and what does below line this mean ? is it a list creation or something?

    dct[u’SALARY’]

     

    3) while i was trying out the same example in my designer, i tried to drag the input field into editor window, but it just comes as SALARY.. no errors while validation, but it gives an error that SALARY field not found while execution.

     

    i did an online tutorial on Python, but still having difficulty in understanding the code used in above example.

     

    is there any tutorial available for python codding syntax inside BODS?

     

    4) also how do you evaluate whether user-defined-transform or custom-function for any scenario?

    (0) 
    1. Venkata Ramana Paidi

      Hi Swetha,

       

      If you want to use user defined transform , you should know about python language.  User defined transform supports only python language.

       

      Coming to custom function vs user defined transform.  Custom transform , we will use when we need any iteration type of logic etc…  But you cannot achieve everything using the custom transform that  python does.  Using custom transform , we can connect social media like twitter, facebook etc.  Mean you can utilize the python language features here but this is not possible in the custom transform. Custom transform scripting is not a programming language.

       

      You can find a lot of online courses for basic python. You should learn lists,tuples, dictionaries to write decent user defined transform code.

       

      Here dct means dictionary.  u means Unicode conversion of the field.  We should convert Unicode that’s why we used u.

       

      Thanks & Regards,

      Venkata Ramana Paidi

      (0) 
      1. Swetha N

        thanks Venkata.

        I have started on the python online tutorials.

         

        but one question is on whether/how we can enable intellisense for custom coding in BODS ? By this I mean the syntax errors highlighted, typing a dot next to module bringing a drop down of the available functions to choose from, errors highlighted in color etc while coding ? we have this feature in most programming languages and would be very helpful.

         

        but now the validation would not really catch most of the errors and I tend to write each line of code and run the job without any intellisense in case of errors.

        (0) 
        1. Venkata Ramana Paidi

          Hi Swetha,

           

          I agreed with you. There is no IntelliSense for User defined transform like other program languages IDE’s . We will know when we are executing the job only.

           

          You can check your code in the Python IDE then write in the user defined transform.

           

          Thanks & Regards,

          Ramana.

          (1) 

Leave a Reply