Combining Data into one Cube from two Data-sources..

[Former Member]

Dear Experts,

I am pulling data from two data sources and trying to combine in one Info-Cube. The data are like

Data-Source 01

1. GUID --Common

2.Document No ( User Entry)

3.Dist. Channel

4.Transaction Type

5.Date and Quantity

Data-Source 02

1.GUID -- Common

2.Billing Document ( If User drill down according to Document No , Billing Document should come in the report )

3.Billing date

4.Net Value

Out of the datas , The GUID is common between the 2 data-sources. I was thinking that, tha data will take according to its place and If i select the Document No in Report, it will atomatically fetch all the data like Tran type, dist ch, Billing Document No , Billing date.. .

The problem is , in the report Tha data is not coming as I was thinking.

And Another problem is , In future I need to create a Multiprovider between the above mentioned Info-cube and One ODS. And DOCUMENT NO is common in Cube and ODS.

Please Suggest,

How can I proceed for the following requirement.

Thanks,

Sanjana

[Former Member]

Hi,

I you scenario first Use a DSO tto consolidate the data.

This will give u all the records in a single line.

Then use an IC and report on it.

Regards,

Rahul

[Former Member]

I will suggest to use DSO instead of Infocube. DSO has overwrite property, therefore you will get all the characteristic value in one line. In your DSO you can have GUID as key field and remaining field will be Data field.

[Former Member]

hi,

when you are trying to combine the data from the two sources:

Data-Source 01

1. GUID --Common

2.Document No ( User Entry)

3.Dist. Channel

4.Transaction Type

5.Date and Quantity

Data-Source 02

1.GUID -- Common

2.Billing Document ( If User drill down according to Document No , Billing Document should come in the report )

3.Billing date

4.Net Value

it needs to have a link between document number and the billing document otherwise the data cannot be combined.

Find the common link between document number and billing document (in billing document you will have reference doc check if the values of ref doc match with document number).

create two separate cubes for the two data sources.

Create a MP on top of them and assign the fields from them. This way the data will be visible but it would create two rows with billing flow values coming in one line and the document flow values coming in other.

if you want all to come in a single row then you have to lookup from either flow to the other flow and populate the data in a single cube.

regards,

Arvind.

[Former Member]

Hi Sanjana,

In your case cube will create a problem because it will have multiple records . For example :

Data-Source 01 :

1. GUID -- 101

2.Document No - 999

3.Dist. Channel - DL

4.Transaction Type - GPRO

5.Date and Quantity - 20.02.2011 & 20

Data-Source 02

1.GUID -- 101

2.Billing Document - 6000

3.Billing date - 03.03.2011

4.Net Value - 500

Your cube will have 2 records . And your requirement is to show above two records in 1 record in the report .

Why dont you make an ODS in between , where you can put GUID as the Key field and rest all the fields as data fields. Create 2 transformations to this DSO from the 2 datasources . And let it get updated one by one . Your DSO will have 1 record only . Now either you do reporting on this DSO or take the data to the cube .

Hope the above reply was helpful.

Kind Regards,

Ashutosh Singh

Edited by: Ashutosh Singh on May 19, 2011 1:34 PM