Header-data for item-level

[marco_simon2]

Hi experts,

quite often we've got the requirement, that an item-level report shall contain
the header-information as well. For example the order-date (which is maintained
on the header level) shall be available in item-level-reports as well.

Unfortunately the extractor does not support that scenario.

How do you handle such cases?

One option might be to extend the item-level export-structure and
filling the new field using the extraction-customer-exit.

But this has a hatch: If only the header-field is changed, no item-delta

will be triggered. Hence the item-records refering to this header won't
pass the exit (immedialtely) and won't be updated.

Another option might be a data-staging based solution:

After having loaded the header- and item-deltas, one could forward
the header-records to the item-infoprovider;  using a start-routine in
order to clone the header records for each item-level-record.

Futheron this could be used to update only those positions, where
the header changed recently.

Is this the best-practice? Or is there another option I didn't mind?

Best regards,

Marco

[yasemin_kilinc · Accepted solution]

Hi Marco,

I am not sure if it would cause any performance problem but an easier method would be getting the header data as the master data of the item level data. Then you could directly use the attributes in your query. Just an idea...

Regards

Yasemin...

[marco_simon2 · Accepted solution]

Yes, that would be a nice option as well.

That would mean to create master-data-records based on transactional records.

That might have the disadvantace that nav-attributes of the header-characteristics might not be available anymore, because the characteristics themselfe are already the attributes of the header-key (~doc-number). But it would be a nice an very general way to embed the all header-chars into the position.

I crossed that option several times lately, but I'm not yet sure if this is really the best-practice way to solve the simple and imho very general issue described above.

[yasemin_kilinc · Accepted solution]

Hi Marco,

I think the same as well. It may not be the best practice. But still if you require the nav. attributes of the header characteristics, you can still achieve that writing a field routine and reading the value from master data table of the header characteristic while loading.

Regards

Yasemin...

[former_member182470 · Accepted solution]

Hi Yasmein,

How can you bring Transaction data as Master data?

[marco_simon2 · Accepted solution]

Hi Suman,

our way to do that:

We load the transactional data (as usual) to an DSO (which is used for further data-staging to cubes, etc.) and use this DSO as a datasource (not literaly the BW-term) for loading them (partially) to the masterdata-tables of the primary-key InfoObject.

In the case of sales-orders we forward the "customer's order number" and the "order date" to the attributes of the InfoObject 0DOC_NUMBER. This forwarding is done regularly in a process-chain which is situated after the 'real' masterdata-chains and the general transactional-data-chains.

Does that give you a clue?

[former_member182470 · Accepted solution]

I got your most liked workaround. Please carry on with your development..

[former_member182470]

Hi,

It has to be achieved in Modeling. Load header data and item data in to two standard DSOs.

Create another Standard DSO(which can hold item level data) from the item DSO. Do a lookup of header DSO for every Order in Item DSO and update Order Date in new DSO.

Finally you can report on this third DSO.

Regards,

SUman

[former_member182470]

For complete understanding about Write optimized DSO, please check the below link:--------

http://scn.sap.com/docs/DOC-11852

[marco_simon2]

Hi,

thanks for your clear positioning for the staging-based solution.

I might be wrong, but could it be that the way (both of) you recommend contains a risk for inconsistent data?

Let's say I loaded item Data to ODS_I and header-data to ODS_H.

The ODS_HI contains the union of both data-provider.

At the moment of forwarding the data from ODS_I --> ODS_HI you'd read the
header-information from ODS_H for enhancing the item-record.

Now generaly delta-loading should be prefered, because of the reduced data-loading-times:

Header and items have been loaded till ODS_HI.

Now a user chages a header-record. The source-system will trigger a delta-record, which will be forwared till the ODS_H-InfoProvider. The item-level won't take any note of this change. Neither ODS_I nor ODS_HI will get additonal records. Hence the unioned record in ODS_HI will still show
the outage header-information, while ODS_H contains the correct information.

In the end, this is exactly the same scenario again, as described in my opening-post - only difference is, that the scenario has moved from the source-system to the bw-system.

[former_member182470]

If you have read my first reply claerly, i had mentioned to create a thired DSo which can store Item level info by looking up header DSO. Can you re-phrase your doubts with that to understand the procedure?

[marco_simon2]

Hi Suman,

yeah, i got that point.

This 3rd DSO is what I labled ODS_HI   (=Header + Item).

Let's try some ascii-painting

DS:Header  ----------------->  ODS:Header

                                             /   \

                                            /   enhance items

                                           /        \ 

DS:Item -->ODS:Items-/  - - - -  ------->  ODS:Header+Items

In this case  a header-record could find it's way to the ODS:Header,
without a corresponding item-record which makes it's way though the item-staging.

Hence the item (which already found it's way to ODS:Header+Item in the past) won't pass the enhancment-routine again. This will happen only, if a new delta for the item-record is triggered again.

[former_member182470]

I say to write an End routine by looking up header DSO and update the header info before the data is getting stored in 3rd DSO.

Do a fresh load from your sources and try to update all the data to 3rd DSO with that routine.

[marco_simon2]

Hi Suman,

Suman Chakravarthy K wrote:
I say to write an End routine by looking up header DSO and update the header info before the data is getting stored in 3rd DSO.

Fully understood that point. But the hatch is, that the end-routine is triggered only if new/updated item-records are forwarded from ODS:Items to ODS:Header+Items.

Do a fresh load from your sources and try to update all the data to 3rd DSO with that routine.

That would certainly work, but daily full-loading item-level data (which could be loaded using delta-method) seems not to be a really recommendable way, is it?

[former_member182470]

I appreciate you are understanding all points clearly. But one clarification here.

My idea is to do a full load once with that routine. So that all historical data will be with your desired logic.

Run your regular deltas as usual with that routine. So any changes to your old items will reflect with the routine logic. New records will anyway run through the rotuine. So all your data is with the logic in place.

[marco_simon2]

Suman Chakravarthy K wrote:
Run your regular deltas as usual with that routine. [..]. New records will anyway run through the rotuine. So all your data is with the logic in place.

That's exactly the point i doubt...

1) I do a full-update (=Init) for my headers (one of them is doc-nr: 111222)

2) I do a full-update (=Init) for my items (one of them is doc-nr: 111222-010)

3) I forward the items to the 3rd DSO. Items are enhanced (in the end-routine), everything's great.

4) I perform a change to a header-field of doc 111222

5) I do a delta-load for the headers --> doc 111222 is tranfered and updated

6) I do a delta-load for the items --> Nothing is transfered, since no item has been changed.

---> End-Routine of items-transformation is not triggered. Record in 3rd DSO is outaged.

Could you show me up my error in thinking?

[former_member182470]

To emphasize on your last point, I am thinking to suggest you to write a small ABAP program in SE38.

The logic would be like below :---

As soon as you do a delta load to Header DSO, you have to select the records in the active table of header DSO(as per the today's date) and update the details to the respective records which are already in the active table of  third DSO.

You can include this ABAP program in your process chain right after the delta load to header DSO steps.

[RamanKorrapati]

Hi Macro,

From header info how many fields information you want into item level dso(ods_hi)? only order date or need some more objects?

Thanks

[marco_simon2]

Hi Suman,

that would mean a manipulation to the active-data-table of the item-dso which bypasses the normal data-changing via the standard data-staging. Furtheron it does not take the changelog (which might be needed for data-forwarding to cube) into account.

Is that really the best-practice for the general problem I described initally?

In this case I will stick to the 2nd method I described in my first post. It makes sure that only updated/delta header-items are used as a basis for the item-updating, and it uses the normal datastaging via transformation and activation of records, which makes sure that the changelog is consistent as well.

Are there any disadvantages with this approach?

[marco_simon2]

Hi Ramanjaneyulu,

generally I'm searching for a best-practice way to get _all_ header information into the item-level DSO.

The quickliest (but as already described disadvantage-paved) way would certainly be the one Yasemin mentioned.

In my very special case I need that only for the order date.

Best regards,

  Marco

[former_member182470]

As you are so keen about 5th point, i thought about a workaround and gave you. That doesn't mean you literally do that. Whatever way you are confident about the data accuracy, you should go with that.

I can't suggest you any more. It is up to you. I tried to give all my ideas to make you understand. I think i am not able to..

Thanks,

Suman

[RamanKorrapati]

Hi Marco,

Yes, Yasemin suggestion will be quickest one.

if you want some field of header data at item level, that may be achievable. But if you want all header info at item level dso then you will get data redundancy and complex design.

Am not sure how much important to get all header info at item level dso.

If you know about LIS Extraction. this may be good for you. because there no is header or level wise info there.we can get all info thru respective data source. But LIS is old technique which was replaced by LO.

Thanks

[marco_simon2]

Good morning Ramanjaneyulu,

so far I expected the item-level to be the usual level for analysis-users, since you normaly never now in advance what characteristics exactly you'll need. And working with 2 sources - one for header-information, one for item-level - seems not to be very user-friedly. So I expected a item-level containing all header information as the normal case.

Ramanjaneyulu Korrapati wrote:
If you know about LIS Extraction. this may be good for you. because there no is header or level wise info there.we can get all info thru respective data source.  

That hits the nail right on the head...

When you write about "LIS Extraction", do you mean the extractstructures that can be maintained in TCode LBWE? If so, than this exactly the hatch, since in my case the OrderDate is not available for the item-datasource (2LIS_11_VAITM), but for the header (2LIS_11_VAHDR--MCVBAK--BSTDK).

Or did you mean something different?

Best regards,

  Marco

[RamanKorrapati]

Good Morning Marco,

Tx - LBWE purely relaed LO technique only.

As you need header info at item levels, i said as thru LIS we can get whole data thru one data source.

LIS Extraction, like LO there is no concept header data source/item data source. at LIS you may get sales data source(combine of header/item/schdule lines). For this LIS using t code are different.

http://scn.sap.com/thread/118061

http://scn.sap.com/message/1765546

Just i gave hint. dont divert.

Choose the possible way and go ahead. better to go with Yasemin suggestion.

Thanks

[former_member182470]

What is your comment on my last reply? Did i make you understand or not?

[marco_simon2]

Hi Suman,

I got your position.

You tried to show up possible ways, in this case a workaround.

And as it is very common with workarounds, it certainly isn't a perfect solution.

Maybe I had the wrong expectation when I opened the thread.

I expected the described issue (=enhancement of item-level data by corresponding header-data in a delta-load scenario) to be a very common case, which has a very common soultion.

Your answers lead to the idea that my case obviously is not that common, but quite special, and therefor needs a special handling/solution as well.

Anyway - I got your point, and understand why you replied in the way you did.
As a workaround the option you mentioned seems to be ok for me - but when searching for a more developed, round and charming solution, my initial described aproach seems to be better fitting. Hope you agree with that and got my position as well.

[former_member182470]

Ok, I got your position already. If you have decided to go with your own thought, then please close the thread by assigning helpful answers(if any). No force..it is up to you..

[marco_simon2]

Thanks for mentioning.
As you maybe noticed I alrady marked helpful answers.

I hoped to get some confirmation that I'm on the right track with my initial approach.
Instead I got an option that seems not to have advatages compared to the described way.

So in the end, my question is still open, hoping for a feedback in the kind of "yes, that's the usual way it is done" or "normally you'd go the following way in such cases". I am thankful for the time you spend in this thread, but I didn't get the impression that your input in the last posts really lead to an advantage compared with the initial already presented options. Anyway, thanks for the discussion.

[marco_simon2]

Thank you for that clearification and the explaining links and hints.

[RamanKorrapati]

Your welcome , have great day ahead.

[former_member182470]

Very nice assignment again..keep it up. I think I invested my time to get into soup...I will be careful next time...Thanks for the lesson..

[RamanKorrapati]

Hi Marco,

Rather than enhancing the extract structure, better to go with staging layer solution.

Load header data and item data to Write optimized DSO's. While moving data from item dso to further target level, you may call required information from Header DSO level based on that doc.

Try this at Dev system and check it.  do the all test cases and move further if its worked as you needs.

Thanks

[Former Member]

Thanks raman.. You give me a clue how to do a modelling at write optimized dso.. Never think about that before..

[RamanKorrapati]

Your welcome Edi.

When we need dso for staging then write optimized DSO will be best use. data will be avaible immedaite once we loaded.

Thanks