Multidimensional Reporting

[Former Member]

Dear experts,

I want to join an existing data model containing the following 2 data flows:

1) Content Flow from DataSource 1_CO_PAxx to Cube 0COPA_C01 (via Content InfoSource)

2) Customed Flow with Billing-Data to Cube from 2LIS_13_VDITM (via InfoSource and DSO)

- 0COPA_C01 contains (amongst others):

- - CO-Document Number (0ME_CO_DOC)

- - Reference document for COPA (0ME_REFDOC) containing the Bill-Number

- - 0CALDAY

- - Lots of Key figures

- SD Cube contains (amongst others):

- - Bill-Number (0BILL_NUM)

- - 0CALDAY

- - Lots of Key figures

- - Key Fields in DSO are 0BILL_NUM AND 0BILL_ITEM

How can I join the two cubes in a MultiProvider so that I get selected data from both cubes in one single row per CO-Document?

Example:

0COPA_C01 contains:

- 0ME_CO_DOC = "123"

- 0ME_REFDOC = "456"

- 0CALDAY = 28.04.2011

- KYF 1 = 111

- KYF 2 = 222

SD Cube contains:

- 0BILL_NUM = "456"

- 0CALDAY = 28.04.2011

- KYF 3 = 333

- KYF 4 = 444

In a Query I'd like to select by 0CALDAY = 28.04.2011

Expected Output:

0ME_CO_DOC, 0BILL_NUM, KYF1, KYF2, KYF3, KYF4

123, 456, 111, 222, 333, 444

Thanks very much for any help

Patrick

[Former Member]

Hi Patrick,

Agree with above post. You can use constant selection.

Create RKFs for the SD Cube Key Fig. And give restrictions as InfoProvider = SD Cube (right click Constant Selection) OR COPA DOC = # (Constant Selection)

Output will be as expected.

[Former Member]

Hi Patrick,

The requirement which you gave is not possible through multiprovider .

At best what you can do through multiprovider is that you take the identification of 0calday from both the cubes , and by using 'Constant selection property' in query designer , you can display key figures of both the cube in a single row but a time you can view characteristics of only one infoprovider in a single row.

As soon as you will bring the characteristic of the other infoprovider using drilldown, it will come in second row.

Another way to achieve your requirement will be using infoset, inner join on 0calday . but infoset has its own performance issues .

Kindly check the pdf in the below link for the above explanation :

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/70dcd7b5-6a3d-2d10-c984-e835e3707...

Hope the reply was helpful.

Thanks & Regards,

Ashutosh Singh

[Former Member]

Hi Ashutosh,

this seems to be very helpful.

If I don't wanna solve this in Query Designer, is there a possibility in Staging process; e.g. with a DSO after COPA Cube which generates key fields and then processing in another Cube?

Cheers Patrick

[Former Member]

In that case, you can load COPA DSO first. (if you are using a DSO). Then while loading SD DSO you need lookup COPA DSO in the start routine and then read and populate COPA document number in the update routine.

this will be performance intensive data load as Copa has huge amount of data.

Constant Selection would be best and optimum solution.

[Former Member]

Hi Patrick,

Given the information you have provided, we can achieve your requirement at modelling level ,

When loading the data in the COPA dso , you can do a look up to the SD dso based on the condition below ;

0ME_REFDOC = 0BILL_NUM

0CALDAY = 0CALDAY

So in your COPA DSO you will have SD dso data also. Then you can do reporting over COPA Cube.

But in the above process you have to make sure at any cost that your SD dso is loaded properly , otherwise your look ups wount happen properly .So basically your data loading to the COPA dso will be slower and it will be dependent also on the SD DSO loading.

Hope the above reply was helpful.

Thanks & Regards,

Ashutosh Singh