Combine the data of 3 DSOs into 1 DSO then follow by Infocube

[Former Member]

I am new in SAP BI system. There is some help which I need the expertise in this area.

Let me brief a bit of my current scenario as shown below.

Initial objective, I have 3 diff. standard DSO which I want to display all the information of each DSO to the InfoCube, but somehow it is not showing the result which we require. Now I am trying to use an alternative way to solve it by using 3 DSO to 1 DSO then follow by transformation to InfoCube. But the result still showing the same as below.

DSO_1		DSO_2					        DSO_3	
F1	F2	F3	F4	F5	F6	F7	        F8	        F9
12	BUP002							
							        SAP_0000000002	B
							        SAP_0000000002	C
							        SAP_0000000002	J
							        ZINDUSTRY	CONSUMER PRODUCTS
		10		3	7	tests.com		
			11					

The above show the result from 3 DSO to 1 DSO (merge).

DSO_1		DSO_2					        DSO_3	
F1	F2	F3	F4	F5	F6	F7	        F8	        F9
12	BUP002	10	11	3	7	tests.com	SAP_0000000002	B
							        SAP_0000000002	C
							        SAP_0000000002	J
							        ZINDUSTRY	CONSUMER PRODUCTS

The above is the actual result I wish to see on the 1 DSO (merge) or InfoCube.

Thank you in advance. ^.

Kind Regards,

Ivan Young

[Former Member · Accepted solution]

You should have common fields present in-between these 3 DSO's to merge the records.

May be you can just try this.

try to create a Infosource on top of these 3 DSO and have all the fields from 3 DSO's defined in the Infosource.

Create three transformations between Infosource and 3 DSO's

And then create transformation between Merge DSO and the infosource.

Just try this and check if it works...

--- Thanks...

[Former Member · Accepted solution]

Thank you for the tips. I will try it now.

Do you know how to use any ABAP code to solve this solution? (just an additional optional solution for me to try out)

Cheers,

Ivan Young

[Former Member · Accepted solution]

Hello.

I have never create a InfoSource before. How do I transfer the data from the InfoSource to the merge DSO? I try to use DTP but it seem DTP do not have the option to choose InfoSource as source to the target merge DSO.

Thank you.

Cheers,

Ivan Young

[Former Member · Accepted solution]

Hi,

You must need some common field or logic where you can say this record from DSO1 should merge with this record from DSO2.

[Former Member · Accepted solution]

I think this will not work out, though you have Infosource Inbetween, you need to create separate DTP's for 3 DSO's used in the Infosource.

Do you have common fields present in these 3 DSO's??? If yes then did you try to create Infoset on top of these DSO's ??

--- Thanks...

[Former Member · Accepted solution]

Hello BI experts.

My current data flow is InfoPackage -> DS -> Transformation to DSO -> DTP to standard DSO -> transformation to merge DSO -> DTP to Merge DSO -> transformation to InfoCube. Apply for all the 3 DSO. (this was before creating the InfoSource recommend by Rookie-Bi)

After creating InfoSource for the merge DSO it would be InfoPackage -> DS -> Transformation to DSO -> DTP to standard DSO -> transformation to InfoSource -> transformation to Merge DSO. (which I'm stuck here).

The DSOs are standard DSO of 0BP_CUST, 0BP_REL and 0ATR_DS01. Which 2 of the DSO have the same key field of 0BPARTNER and 1 of the DSO also carrying the same key field of 0BPARTNER but it was separate into two part 0BPARTNER1 and 0BPARTNER2 (0BP_REL). I'm using the 0BPARTNER as my key to show all the related data to the merge DSO somehow it did not show the result which I want (refer to the first post).

I'm assuming my another step should be creating the InfoSet for the 3 standard DSO. (correct me if I'm wrong.)

Have to say sorry in advance as I might ask some stupid question.

Cheers,

Ivan

[Former Member · Accepted solution]

Hi Ivan ,

your requirement is quite simple .the basic thing is you must have one field common so that you can have records populated correctly .

for ex .

An employee_no is key and if we have three DSO having employee address ,salary details ,education details then we will use employee_no as key to find which detail belongs to which employee .

DSO1 DSO2 DSO3

Emp no f1 f2 Emp no f3 f4 Emp no f5 f6

DSO4

Empno f1 f2 f3 f4 f5 f6

So here in your case you need to find some field which is common in all DSO (key )

then you can just create 3 transformation from

DSO1 ->DSO4

DSO2->DSO4

DSO3->DSO4

and will load them via DTP's for each Transformation .so here DSO4 will have data of all 3 DSO

1. Using ABAP is not good idea as it will reduce performace but if you want to do all loads via one Transformation only then you can do this .

2. Even to write ABAP code you need to read DSO data's with respect to some common key field.

3. Its easy to find key field and if you want help in writing ABAP code for same let me know but i will suggest that you ho via Transformations .then you just nee to map fields from each DSO to DSO4 along with Key field and It will automatically load data as per your requirement .

hope this will be helpful .

Regards,

Jaya Tiwari

[Former Member · Accepted solution]

Hi, Jaya Tiwari.

Thank you for the detail explanation.

Nevertheless, I did try on this method. But due to the data from the 3 DSO have multiple row for each CustomerNO, in the DSO4 it will only populate one single row with all the data from the 3 DSO which it is overwrite with the next row of record.

Let me give an example for my scenario.

Result from 3 DSO to DSO4.

DSO1                            DSO2                                        DSO3
Customer ID      Role           Contact Person    Resp. Person   CP Tel.    Item      Item_Purchase
CUS01            Sold-to        Con3              Res6                      ItemB     ItemB-EFG

Result which I want from 3 DSO.

DSO1                            DSO2                                        DSO3
Customer ID      Role           Contact Person    Resp. Person   CP Tel.    Item      Item_Purchase
CUS01            Prospect       Con1                             +01234     ItemA     ItemA-ABC   
                 Sold-to        Con3              Res6                                ItemA-XYZ
                                                                            ItemB     ItemB-EFG

From the "Result from 3 DSO to DSO4" somehow the following data from another row will overwrite the initial row (as Customer ID is the key). I wish to show only 1 row of data which consolidate all the related information to the CUS01 from the 3 DSO as show in the second result.

And also would like to thank you on the few point which you had pointed out to me. As least now I know by using ABAP coding it will reduce the performance.

I believe you have a better solution to my doubt.

Cheers,

Ivan

Edited by: Ivan Young on Jun 7, 2011 4:06 PM

Edited by: Ivan Young on Jun 7, 2011 4:13 PM

[Former Member · Accepted solution]

Hi Ivan ,

I think you want this kind of output :

DSO1

Customer_ID Role

CUS01 Sold-to

CUS02 bill-to

CUS03 Sold-to

CUS04 Sold-to

DSO2

Customer_ID Contact Person Resp_Person CP Tel.

CUS01 Con1 Con3 +01234

CUS02 Con1 Con3 +01234

CUS03 Con1 Con3 +01234

DSO3

Customer_ID Item Item_Purchase

CUS01 ItemB ItemB-EFG

CUS01 ItemC ItemC-EFG

CUS01 ItemD ItemD-EFG

CUS02 ItemD ItemD-EFG

Dso4

Customer_ID Role Contact Person Resp_Person CP Tel. Item Item_Purchase

CUS01 Sold-to Con1 Con3 +01234 ItemB ItemB-EFG

CUS01 Sold-to Con1 Con3 +01234 ItemC ItemC-EFG

CUS01 Sold-to Con1 Con3 +01234 ItemD ItemD-EFG

CUS02 bill-to Con1 Con3 +01234 ItemD ItemD-EFG

CUS03 Sold-to Con1 Con3 +01234

CUS04 Sold-to

In this case your data must be getting overwrite because only customer id is key .

You can solve this in 2 ways

1.You need to change key and { Customer_ID,Item,Item_Purchase } should be key in DSO4 so that more than one record can come and then map

2.At Start Routine you read DSO3 table and populate reocrds in DSO4 but still you need to include

{ Customer_ID,Item,Item_Purchase } in key so that unique record can be identified based on these 3 not only based on Customer_ID .Otherwise you will get error at load time .

Hope this will be helpful .

Regards,

Jaya Tiwari

[Former Member · Accepted solution]

Good day, Jaya.

Again thank you for the explanation.

I did as what you had explained to me. I use routine on all the require field to populate data from another 2 DSO. When it come to transformation, the data for Customer_ID, Item, and Item_Purchase show the correct data unfortunately the data for Role, Con. Person, and Resp. Person did not show the data correctly. It will only show the same data.

Should I create Role, Con. Person, and Resp. Person to key as well? as it is multiple data as well? Furthermore, I'm not sure my ABAP coding in the Start Routine and routine is correct or wrong.

Thank you.

Ivan

[Former Member · Accepted solution]

Thank you for all the value information to me.

Manage to find a solution myself.

Thank you.

[former_member199448 · Accepted solution]

Could you please share your solution?

Thanks

[former_member199448 · Accepted solution]

Hello Tiwari,

I have a question related to your solution 2:

Why are you only choosing Customer_ID, Item, Item_Purchase as keys?

What about contact person & Role?

Another question:

By merging multiple DSOs into a new merged DSO should we have to use all keys coming from DSOs in the new merged DSO? or can we just select some object as keys?

Is there any SAP Doc which explains it?

Thank you.

[Former Member · Accepted solution]

Am also looking answer for the same...

[Former Member · Accepted solution]

Whats the solution you found out? Pls share

[former_member182467 · Accepted solution]

yo man....hw did u resolve it....give us the solution bro.

[Former Member]

very informative