Long texts in SAP BW: Modeling Follow Up
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. 
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. 
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. 
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. 
The length of the field INDX is NUMC 5 and I set ‘Selection’ option for this field in the datasource. 
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. 
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. 
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. 
In the Data Selection tab of an infopackage I restricted transferred records by the INDX value form 1 to 99. 
The list of data targets was changed. 
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.
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
www.kaar.in
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
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 ?
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
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
www.kaar.in
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?".
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
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
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
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
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?
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
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