Combine Header and Item DSOs

[Former Member]

How do we combine 2lis_11_vahdr, 2lis_11_vaitm and 2lis_11_vascl in One DSO?

If we have the key as Sales Order, Item and Schedule Line, the transformations from the former two set the missing keys to initial.

Should we not have transformations from the vadhr, vaitm and just do look up routines?

All thoughts appreciated.

[Former Member · Accepted solution]

If the requirement is to combine, 2lis_11_vascl, vaitm and vahdr in one DSO, what is the key? we would like vascl level details in the DSO, so I am assuming the key to be header, item and schedule line. How do I update the VDHDR and VDITM to this DSO?

[Former Member · Accepted solution]

hi,

Combining the three won't be possible as the three provide data at different aggregation level (the keys are differen), the key you are going to define should be the most detail so that no records are overwritten (schedule line details will be the key), when you map item and header to this DSO it should map the key fields of the DSO otherwise it would fail.

Also using write optimized DSO is not an option as the data retrieved by the 3 DS is huge, even the infoset would be a disaster.

The three DS data can only be combined at a cube level.

regards,

Arvind.

[Former Member · Accepted solution]

Hi,

It is very much possible.

DSO 1. Header has Sales Order as key, say A.

DSO 2.Item has Order and Item as keys, say A, B.

DSO 3.Schedule line has, Order, Item plus Scl line counter as keys, say A, B, C.

So we can look up DSO1 & 2 when loading DSO3.

But there is a catch... not all orders have Schedule lines. So not all Orders will be available in this DS.

[Former Member · Accepted solution]

Thank you Arvind and Parth for your answers.

We have a design where we are first bringing things into Write optimized dsos and then joining at a standard DSO level before going to the Cube. We do this to facilitate RRI jumps from the cube to a common DSO.

Any ways of putting VAHDR, VAITM and VASCL into one DSO without losing data?

Edited by: CC on May 27, 2011 9:50 PM

[Former Member · Accepted solution]

hi,

Architect point of view, there are couple of way to acheive it.

use infoset/multiprovider if reporting is not complex.

If reporting is complex then use below design to store whole data in single dso.

Rightnow u have 3 dso's at aquisition layer.

Whenever u load data from any one dso to target dso, do lookup 4m another 2 dsos.

Else use 1 intermediate dso with key sales order and data field as date and time. And after that load data into this dso frm ur 3 source dsos. End then load data 4m this dso to target dso by doing lookup on 3 source dsos. Complex design but very gud performance.

Regards,

Ashish

[Former Member · Accepted solution]

Thanks Ashish,

The issue with the first design is that not all header level documents have schedule lines so doing a lookup would drop documents that are not in 2lis_11_vascl.

The second one is a great idea. But is it really good design performance wise?

We have 60 fields in the header dso, 80 in item and a similar number in the schedule line.

Wouldn't looking up all these fields from these 3 sources offset the advantage of your design?

how would the deltas be handled??

Thanks again for the idea and I would really like to implement it if its good for performance.

Also, the reason we want to sneak in a composite DSO between these three acquisition DSOs and the Cube is that we have requirements for drill down jump queries.

All thoughts appreciated!

Edited by: CC on May 29, 2011 6:36 AM

[srikanthbwbw · Accepted solution]

Hi CC,

Not very sure with your requirements. But its not recommended to put all the data that is coming from the source directly into one DSO where the keys are entirely different. Usually all the data is stored in the staging layer and from there a consolidated DSO or cube is used.

For header, only the doc number will be the key but for other two docnumber and item will be key. When you are loading the header into the DSO where there is no item then it would be very difficult.

Other way.

Create a view and create a generic datasource in ECC on based tables OR

Enhance your datasource to get the required fields from hdr, item and schedule line items. This would be more simple than creating single dso and loading in BI.

Thanks

Srikanth

[Former Member · Accepted solution]

Hello CC,

I understand your requirement since we are facing the same issues.

1. Remember that it will not be enough to update only from your SCL DSO and enhance your missing Item and Header Data. You will miss all updates that happen only on HDR or ITM then. e.g. The Currency changes, this creates only a delta in the HDR, not in ITM or SCL.

2. Build an EDW Layer and an Harmonization Layer for your single Info DSO, afterwards join them into a consolidation DSO.

Our Solution is that we

1. load the SCL Delta into the Consolitation DSO.

2. Step 2 is to Create an Update/Delta from the normal Item Delta. Here the Items get duplicated to match the SCL Key.

3. Same as Step 2 but for HDR Information. Duplicate records so that it matches the SCL key.

This is not too easy, but it is the most valueble way to accomplish this target. Choose your SCL Key wisely!

Good luck

Joerg

[maxilopez]

Hi,

I think that it is worth analysing the alternative of using three separate DataStores to store information about headers (2LIS_11_VAHDR), items (2LIS_11_VAITM) and schedule line items (2LIS_11_VASCL).

Then, you can create an InfoSet to join the three DataStores. Headers can be joined with Items using Document number InfoObject. In addition, Items can be joined with Schedule line items using Document number and Item number InfoObjects.

Afterwards, reports can be created directly on the InfoSet or from a subsequent DataStore fed from the InfoSet.

I hope this helps you.

Regards,

-

Maximiliano