Skip to Content
Though, I have stated in my first blog that questions about long texts in SAP BW almost disappeared, Long texts in SAP BW: Modeling one more appeared just recently. This time the load of long text was conducted from R/3 system. How to deal with this issue is explained here. What will be said is applied to all source systems (including DB Connect) except flat files. In case of loading long description with 240-character length we have four fields in the datasource of the source system. The first one bears the whole long description (AAA, BBB, CCC and DDD abbreviations designate the parts of the long description, 60-characters length each). The other three, with 60-character length, may contain any information (shown as XXX, YYY and ZZZ). Content of these fields is not important, they are simply placeholders. What is important is the length of the fields. image After uploading data from the datasource, in BW information goes to transfer structure. Physically, it is so called PSA table. Information is copied without any changes, 1:1. image During further load from PSA table to data target, the start routine in transfer rules works out first. In our case it cuts out the long description in peaces of 60-symbol each and places them into corresponding fields (infoobjects) of the communication structure. Actually, incoming from PSA table XXX, YYY and ZZZ information is replaced by BBB, CCC and DDD on the fly. image Update rules of data targets take these peaces of the long description and place them in their long texts. So, in general, the datasource should supply a field with the original long text and several fields with 60 characters length (placeholders). If you have such a datasource, then dealing with it is obvious. If you have not, create it. The easiest way to do it is to create a generic datasource with a table or view.

VIEW Creation (SE11 t-code)

For demonstration purposes I found a table AQGTQ with 255-symbol TEXT field. For a placeholder I used the 80-symbol TEXT field of AQGTS table. It doesn’t matter that the length of this field is not 60 characters. What matters is the fact that this length is greater than 59 characters. For simplification I cut out the long description in two peaces, not four. I choose mentioned above two fields during a view creation and named them as TEXT1 and TEXT2 in the view definition. image In order to decrease the number records to be extracted from R/3, I joined the tables with the common key INDX.

Generic Datasource Creation (RSO2 t-code)

I chose an application component and created view. image The length of the field INDX is NUMC 5 and I set ‘Selection’ option for this field in the datasource. image Since our ZGROUP_x infoobjects have NUMC 2 type, this selection will allow me to restrict the records to be transferred to BW by their key.

Further Steps

I generated the datasource in R/3. Replicated it in BW. Found the infosource I used for load long description from flat files. Assigned to it my R/3 system as a source system and chose the datasource I just created. image In transfer rules I copied ZGROUP_1, 0ADDR_LINE1 and 0ADDR_LINE2 names from the left frame into blank fields corresponding to INDX, TEXT1 and TEXT2 fields. Since my datasource didn’t provide the value for language field 0LANGU, I placed in transfer rule for this field a formula which returned the logon language. image Transfer rules for the last two fields remained unassigned. It doesn’t matter. In the start routine in transfer rules I placed the following code. Notice, that it is the same code that I used before save that the number of modified fields is less and field names in transfer structure are different. image In the Data Selection tab of an infopackage I restricted transferred records by the INDX value form 1 to 99. image The list of data targets was changed. image The rest is absolutely the same as in case of load from a flat file and has been described in the first blog. About workbooks formatting see the blogs: Long Texts in SAP BW: Displaying in BEx Analyzer. Introduction to Excel Workbooks Formatting. Part I. Long Texts in SAP BW: Displaying in BEx Analyzer. Introduction to Excel Workbooks Formatting. Part II.

To report this post you need to login first.

13 Comments

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

  1. Sankar Kumar
    Hello 🙂 Eugene Khusainov,
    How r u ?

    We are facing this issue, like in the MS SQL some fields are varchar(600) even varchar(2000). But the data is not so long as the length assigned. Say, 79 Chars is the Max for the field Varchar(600), what do u suggest in this scenario ?

    Thanks and Regards….
    Sankar Kumar
    +91 98403 47141
    ksankar@kaar.in
    http://www.kaar.in

    (0) 
    1. Hi Sankar,

      In your case the datasource to be brought to BW is to be based on a view. If you are certain about maximum number N of characters used, in the srcipt file creating the view use a function like LEFT (depends on the particular SQL version) to bring just the left N symbols.

      Best regards,
      Eugene

      (0) 
      1. Sankar Kumar
        Once again Thanks !!! for the idea, we could create multiple view fields for one table field. Say if its varchar(240) then 1 st calculate the LENGTH of that field in the whole table and take the maximum. So we can decide how many View fields to create in the view.

        Generating Data Sources will have the maximum view fields for that particular long field. Using SUBSTRING function we can split the data in MS SQL  itlself. Need not write any routine in BW side.

        Just create IOs and load the data….

        How about this idea ? do you feel this is better ?

        (0) 
        1. Sankar,

          Certainly, if you can cut out the long description directly in the source system, it’s the best approach.
          You’ll don’t need any routines in BW. Just load these peaces to long texts of infoobjects.

          Best regards,
          Eugene

          (0) 
          1. Sankar Kumar
            Hey Eugene,
            Can i post a Technical Article if it works ? I started the job. What you would suggest ?

            I require one more information from you. From which source systems we will face this problem ? coz we are dealing with MS SQL only as for now.

            Thanks & Regards….
            Sankar
            ksankar@kaar.in
            http://www.kaar.in

            (0) 
            1. Hi Sankar,

              I assumed that you are working with non-SAP system through DB Connect. Is it correct?

              Do you want to write an article on how to create views in the source system? Or something else?

              Anyway, you’d better contact the SDN Team: sdn@sap.com with this question.

              Best regards,
              Eugene

              PS. I don’t get your question: “From which source systems we will face this problem?”.

              (0) 
              1. Sankar Kumar
                Hi Eugene,

                Yes, you are correct. We are working on MS SQL with DB Connect.

                Here it is…. what i have planned to do

                creating an application using webdynpro to check the MS SQL table for the varchar fields and identify the field(s) length exceeding 60 Chars. Then it will automatically create the view for the table or tables, splitting the fields which are above 60 chars into view fields. So the View in MS SQL will match with BW requirement.

                Hope i am clear…. any suggessions ?
                Sankar

                (0) 
                1. Sankar,

                  In general, it looks OK except one clarification.
                  Are going to create views for ALL long fields or just for some selected ones?

                  Best regards,
                  Eugene

                  (0) 
                  1. Sankar Kumar
                    Hello 🙂 Eugene,
                    How r u ? Believe i am a bit late.

                    I have asked our webdynpro people to create an application which will automatically check the table and create the views accordingly for all the fields.

                    Let me explain with an example,
                    If the MS SQL table contains and length as below
                    mat_no | mat_name | mat_desc | mat_usage
                    10       25         100        240

                    then the view generated will be like this
                    MAT_NO | MAT_NAME | MAT_DESC1 | MAT_DESC2 |
                    10       25         60          40        

                    MAT_USAGE1 | MAT_USAGE2 | MAT_USAGE3 | MAT_USAGE4
                    60           60           60           60

                    Is this fine ?

                    Best Regards….
                    Sankar Kumar

                    (0) 
                    1. Hi Sankar,

                      It’s OK with your late reply. It’s not really late.

                      Your view is very good.
                      Just rememebr that you is to create two sets of infoobjects: for description and usage.
                      Modify a comm structure accordingly. I think as placeholders in the comm struc you can use 0ADDR_LINE0 – 0ADDR_LINE7.
                      Insert your infoobjects as data targets and in the update rules map the fields appropriately.

                      I don’t see any problems with implementation.

                      Best regards,
                      Eugene

                      (0) 
                      1. jimi ogun
                        Eugene

                        If you have the long description in the PSA (coming from SAP source), would there be an option to create start routines that would supply the newly created infoObjects? Is this an easy way out instead of creating views in the source system?

                        (0) 
                        1. Hi Jimi,

                          Actually, the long text in PSA was what I work with and mentioned in the 1st part:

                          Long texts in SAP BW: Modeling – Follow Up

                          Depending on placeholders presence in the transfer structure (PSA) we have two options:

                          1. Placeholders (with 60 chars length) are present. -> Use either a start routine or routines in transfer rules.

                          2. Placeholders are absent or don’t have the length required. -> You may use routines in TRs only.

                          Best regards,
                          Eugene

                          (0) 
                          1. V K
                            Hi Eugene,
                            Thanks for this nice blog. this really helps.
                            Is the logic mentioned above applicable for Long text(around 200 chars) from SAP R/3 source as transaction data while loading to infocube?

                            Cheers,

                            Best Regards,
                            vk

                            (0) 

Leave a Reply