Intelligent modelling question for real BI people

[Former Member]

Notification Notif-itme Matl Plant Vendor Source Customer Notif-counter Defect

1000 1 4RA 100 Manuf 5000 1 Kink

1001 1 4RA ABC Purch 5001 1 Break

1002 1 5RA 300 Manuf 6000 1 Kink

Deliv Deliv-item Matl Customer Deliv qty

8000 1 4RA 5000 10

8001 1 4RA 5001 30

8002 1 5RA 6000 50

Manuf: Manufactured

Purch: Purchased goods

ECC LO Data sources: 2LIS_05_0QITEM (for notification) and 2LIS_12_VCITM (for deliveries)

DSOs: Notification DSO and Delivery DSO.

My reporting reqmt needs to combine both DSOs. Only Material, Customer and Batch nos are common in both DSOs.

KPI: Notification counter / Delivery quantity.

Drill downs can be any.

I know that multiprovider on DSOs will show data as shown in above table.

I want all data to come in same row based on material, batch and customer. Can I solve it by using Infoset or APD.

Is there alternate solution for modeling.

Please help.

Thanks

Shankar.

Edited by: Shankar Shankar on Sep 12, 2008 3:02 AM

[Former Member · Accepted solution]

To get one record as you said, you should build an Infoset on the top of the DSOs. It will get 1 record(as it is a join).

Thanks...

Shambhu

[Former Member · Accepted solution]

To make it more clear:

I have 2 DSOs

DSO-1: Has Notification no and item no as key fields. And data fields are are material, batch or serial no, plant, vendor, source (manufactured or purchased), material document posting date, customer, No of defects and Notification date.

DSO-2: Has Delivery no and item no as key fields. And data fields are are material, batch or serial no, customer delivery date, and delivery quantity

Report KPI : No of defects / Delivery quantity.

User should be able to navigate report with any drill downs like: customer, material, plant, batch, serial no.

Selection can be based on any range like: Notification date from - to, delivery date from - to, material document posting date from - to.

If I create an infoset with inner join between material, batch, customer and serial no..

Can I fulfill reporting requirement.

Thanks

Shankar

[Former Member]

join these two ODS based on material customer and select these two item nos from their respective ODS .

hope this fixes ur issue.

[Former Member]

Not to throw a spanner in the works but why can you not build this using a multi provider - from the original question it seems that you can identify the requirements from both sources

[Former Member]

Hi,

when you create an infoset for these two DSOs u will have to map the item no field in both the DSOs in the selection where u selcet what are the fields to be displayed.

for example when u r in selecting the fields if u don select item no in one DSO u will not get data from that DSO but u will get data from the DSO in which u have selected item no.

Select item no from both the ODS as its going to be the base key, beaed upon which the data is going to be mapped from both the ODSs and displayed.

if u want to drill down based on other charecteristics, pls make them navigational.

for example if u have a field in any of the ODSs which is not a key and u want to drill the data based on it, u will get the results only if its navigational in the DSO.

Hope this helps

Edited by: Ranjith Kumar Murugesan on Sep 12, 2008 9:28 AM

[Former Member]

DSO-1: Has Notification no and item no as key fields. And data fields are are material, batch or serial no, plant, vendor, source (manufactured or purchased), material document posting date, customer, No of defects and Notification date.

DSO-2: Has Delivery no and item no as key fields. And data fields are are material, batch or serial no, customer delivery date, and delivery quantity

Based on above info, you will have a join on item no. Since, its a key, it will not be blank. The records will get populated based on this item. You create first, most of the doubts will be clear once you will see the infoset data thru LISTCUBE.

Thnaks...

Shambhu

[Former Member]

Item no in DSO-1 is notification item no.

Item no in DSO-2 is delivery item no.

So these are not common keys.

I think joining material only or material and customer should work. Please let me know, if I am wrong.

Thanks

Shankar.

[Former Member]

u can join only similar fields.

if u have a similar key between these two ODS select it and then u can select all fields seperately from each ODS.

but u will have two item no fields. select the one based on ur requirement.

Edited by: Ranjith Kumar Murugesan on Sep 24, 2008 9:49 AM

[Former Member]

Yes. You will get what you are looking for. Create an infoset and create the query on the infoset.

Thanks...

Shambhu

[Former Member]

i want to know when i create infoset, how do i select (assign). Do I need to check (select) material, customer, batch and serial from both DSO or just any one of DSO in join.

What will happen when data comes in, each record will have either serial no or batch no, it will not have both together.

There will be situation, where input records will have only material and blank fields of batch, serial no, customer.

I want such records should be included in report.

Thanks

Shankar.