Unable to consolidate data from two DSOs into an InfoCube for reporting.

[Former Member]

Hello Experts

I am working on BW 7.0 to create BEx Report as below:

This report will have data coming from two different sources, some data from COPA DSO [such as Customer Number, Product Hierarchy1, Product Hierarchy2, Product Hierarchy3, Product Hierarchy4. Product Hierarchy5, Product Hierarchy6 and a few other Key Figures] and the rest [such as Product Hierarchy, Reference Document, Condition Type (both Active & Inactive), Condition Value and a few other Key Figures] from another DSO (ZSD_DS18) which is a copy of the BCT DSO (0SD_O06). I've chosen this DSO because this is the BCT DSO which is used to store data from a Standard Extractor 2LIS_13_VDKON.

Below are the screenshots of these 2 DSOs:

I have successfully extracted the data from 2LIS_13_VDKON (includes PROD_HIER but not Customer Number) and loaded into a DSO (ZSD_D17).

All the testing is done using only one Sales Document No (VBELN).

First test that I tried is.. to create an Infocube and loaded data from ZCOPA_01 and ZSD_DS18 and when the LISTCUBE was run on this InfoCube, the data is coming up in two different lines which is not very meaningful. Screenshot below:

Therefore, I have created another DSO (ZSD_DS17) to Consolidate the data from ZCOPA_01 & ZSD_DS18 establishing mappings between some of common Chars such as below:

ZCOPA_01                    ZSD_DS18

0REFER_DOC  <->        0BILL_NUM

0REFER_ITM    <->        0BILL_ITEM

0ME_ORDER    <->        0DOC_NUMBER

0ME_ITEM        <->        0S_ORD_ITEM

51 records are loaded from ZSD_DS18 into ZSD_DS17 and 4 records are loaded from ZCOPA_01 into ZSD_DS17 for a particular Sales Document Number.

When I am using a Write-Optimized DSO, data is coming in just 1 line but it is showing only 4 lines which is aggregated which is as expected since W/O DSO aggregates the data. However, when I use Standard DSO, the data is again splitting up into many lines.

Is there something that I am missing in here while designing the Data Model or does this call for some ABAP being done, if so, where or should I have to talk to the Functional Lead and then Enhance the Standard Extractor, and even if I do that, I would still have to bring in those Key Figures from ZCOPA_01 for my reporting?

Thank you very much in advance and your help is appreciated.

Thanks,

Chandu

[RafkeMagic]

in your (current) InfoCube setup, you could work with "constant selection" on the key figures

for the COPA key figures, you'll need to add product hierarchy to the key figures with a selection of # and constant selection flagged

for the SD key figures, you'll have to do the same for customer & the product hierarchy levels (instead of product hierarchy)