Combining Sales and Billing ODS

[Former Member]

I am working on 2LIS_11_VAITM & 2LIS_13_VDITM and have taken them into individual ODS (Sales & Billing). I have taken Sales Doc and Sales Item as key fields. In the next stage I have combined Sales and Billing ODS into a single ODS with Sales Doc and Sales Item as key field. But when I checked the data, the billing doc is not showing all the relevant items e.g

Sales Doc Sales Item Billing Doc BillItem

10006765 10 3300000016 12

The Billing doc is having two more items like 10, 11 along with 12. The result is showing only the last item of the billing doc. And this is wrong for all the other doc nos. Why ?? Could you throw some light on this???

[Former Member]

Hi Paolo and Anil, thanks for your inputs. I have tried both the options mentioned by you.

Option 1: For both Sales & Billing ODS, I have taken 0DOC_NUMBER, 0S_ORD_ITEM, 0BILL_NUM, 0BILL_ITEM as key fields as suggetsed by you and some of the data fields as billing qty, sales qty, net price, invoice net price etc. In the query result set, if I take the sales details and billing details, the sales details are coming in extra row with hash though I am getting the sales doc no & item no. The key figure values are coming in extra row with hash. Is it required to write some routine for billing doc & item in the final ODS as from sales ODS I am not getting any billing doc & item, it is coming from only the billing ODS. Please let me know. If so what kind of code I have to write?

So is the case for option 2 with cube as final DT.

[Former Member]

Hi,

It seems I have given wrong approach. I am sorry for that.

Try this:

For Sales ODS :

Key fields are 0DOC_NUMBER, 0S_ORD_ITEM

For Billing ODS :

Key fields are 0DOC_NUMBER, 0S_ORD_ITEM, 0BILL_NUM, 0BILL_ITEM

Then Use Info set with link as:

0DOC_NUMBER of sales ODS = 0DOC_NUMBER of Billing ODS

0S_ORD_ITEM of sales ODS = 0S_ORD_ITEM of Billing ODS

With rgds,

Anil Kumar Sharma .P

[Former Member]

Anil, u are right. I have taken Sales doc & item as key field and bill doc & item as data field. The update type for billing item was already set to overwritten. Still the problem is coming. Any other solutions ???

[Former Member]

Hi,

You have tkaen the 'overwrite' , thats why in final ODS there will be only one record (for a combination of sales doc and sales item) with finally uploaded billing DOC and Billing ITem.

So see all the Billing Docs and Billing Items:

you have two options :

1) You have to put also Billing Doc and Bill Item in the key ( as Mr Paolo Siniscalco suggested)

2) Define the Cube as final data tareget and feed the data from Billing ODS and Sales ODS.

With rgds,

Anil Kumar Sharma .P

[Former Member]

Hi,

otherwise instead of poupalting data from Billing ODS and Sales ODS to final ODS , Define the Info set on the Billing ODS and Sales ODS.

May I know, the technical names of key fields in both ODS (Billing ODS and Sales ODS to final ODS) .And aslo the respective fileds(technical name) from respective Data source.

With rgds,

Anil Kumar Sharma .P

[Former Member]

Hi,

I hope in the last ODS you have taken "Sales Doc" and "Sales Item" as the key fields , And "Billing Doc" and "BillItem" as data fields .

Have you taken the 'overwrite' as 'update type' in the update rule of "BillItem"? (for the update rule of final DT)

Instead of taking ODS as the final data target take Info cube as the DT.

With rgds,

Anil Kumar Sharma .P

[Former Member]

You have to put also Billing Doc and Bill Item in the key; otherwise you'll get only the last value and KF are summarized.

Regards