Infocube/query: more than a record by item

[Former Member]

Hi everybody,

Please review this issue and kindly suggest a solution. I have an InfoCube feeded by 3 DSOs, here I go with the structures:

Zo01

ZDOCNR (Key)

ZPOSNR (Key)

ZDOCTP (Key)

ZREQNR

ZPOSRQ

ZIND

ZKYF01

ZKYF02

Zo02

ZDOCNR

ZPOSNR

ZREQNR (Key)

ZPOSRQ (Key)

ZKYF03

ZKYF04

Zo03

ZDOCNR (Key)

ZPOSNR (Key)

ZREQNR

ZPOSRQ

ZKYF01

ZKYF02

(Key) are the keys for each one of the DSOs, as you can see, each one of them has different key combinations. Now, my InfoCube let's supose has this structure:

Zic01

ZDOCNR

ZPOSNR

ZDOCTP

ZREQNR

ZPOSRQ

ZIND

ZKYF01

ZKYF02

ZKYF03

ZKYF04

We want to generate a report over this InfoCube having the info which comes from the 3 mentioned before DSOs but when reporting more than a record appears for each item. Let's supose this sample:

Zo01

ZDOCNR ZPOSNR ZDOCTP ZREQNR ZPOSRQ ZIND ZKYF01 ZKYF02

0001 1 X 1 1 LIB 123 456

Zo02

ZDOCNR ZPOSNR ZREQNR ZPOSRQ ZKYF03 ZKYF04

0001 1 1 1 789 012

Zo03

ZDOCNR ZPOSNR ZREQNR ZPOSRQ ZKYF01 ZKYF02

-no data-

the results in the InfoCube and therefore in the query is as follows:

Zic01

ZDOCNR ZPOSNR ZDOCTP ZREQNR ZPOSRQ ZIND ZKYF01 ZKYF02 ZKYF03 ZKYF04

0001 1 X 1 1 LIB 123 456

0001 1 1 1 780 012

I would like you to suggest how can I design the model so I can get these results (in the InfoCube and therefore at the report output):

ZDOCNR ZPOSNR ZDOCTP ZREQNR ZPOSRQ ZIND ZKYF01 ZKYF02 ZKYF03 ZKYF04

0001 1 X 1 1 LIB 123 456 780 012

Thanks a lot in advance. With best regards,

Bernardo

[sanjiv_mohta3 · Accepted solution]

Bernardo,

What is the purpose of the three DSOs? Do you have them because you have three sources feeding into them? Is it possible for you to consolidate the three DSOs into a single DSO and have it fed from the different sources? If you are able to consolidate into one DSO with the doc no & line no as the key then feed the cube from this single DSO you should be able to resolve your issue.

Regards,

Sanjiv

[Former Member · Accepted solution]

Sanjiv:

The three DSOs are feeded from different sources. Any other idea?

Thanks a lot,

Bernardo

[sanjiv_mohta3 · Accepted solution]

Bernardo,

Not sure how far down the line you are in your design, but, if it is flexible you should still be able to populate a single DSO if you have the DOC no and Line NO as keys from all 3 sources. Assuming you get this information from all three sources.

You could create the new DSO with just DOC no and Line No as the key and have all other fields (from all 3 DSOs) as data fields.

Create a transformations from each source into the DSO and set the fields not supplied by the source to no update. The DSO will do the work for you to consolidate into a single record.

Regards,

Sanjiv

[Former Member · Accepted solution]

Just created a new DSO feeded by the three DSOs, having as keys:

ZDOCNR (Key)

ZPOSNR (Key)

ZREQNR (Key)

ZPOSRQ (Key)

the data were updated correctly and matched in only one record after activations. Further the data were load from new DSO into InfoCube and report now looks as I wanted.

Thanks a lot everybody and best regards!

Bernardo

[purvang_zinzuwadia]

I think there is no problem with your design, since you are loading cube from 3 DSOs, it will create 3 diff records in cube even if record has the same key. Still to avoid double record you can do as below.

- create end routine on the tranformation and fill the ZDOCTP and ZIND field while loading from other DSOs. This will make sure that these fields doesn't remain blanks while loading to cube.

- Purvang

[Former Member]

Purvang,

You meant: fill the ZDOCTP and ZIND by reading the data using the same keys but looking up for them in the other DSOs? or just assign a value so it will not be empty?

Thanks a lot for your response,

Bernardo

[purvang_zinzuwadia]

sequence of loading cube will be this.

first load DSO 1 thru which has ZDOCTP and ZIND keys are getting populated.

then while loading DSO 2 and 3, in end routine read data from cube based on other keys that will give you ZDOCTP and ZIND. so populate this in end routine.

- Purvang