Filtering Data in Composite Provider

[#1]

Hi All,

I am trying to create a Composite Provider, where I want to join the data from two infoproviders. I am doing this in BW 7.4 SP11 system, which is on HANA database. I am trying to achieve something like below,

SELECT PROV1.A, PROV1.B, PROV1.C, PROV2.X, PROV2.Y, PROV2.Z
FROM INFOPROVIDER1 AS PROV1
LEFT OUTER JOIN INFOPROVIDER2 AS PROV2 ON PROV2.X = PROV1.A AND PROV2.Y = 'SAP';

I want to get all the records from provider 1 and from provider 2 I want only those records where Dimension Y = 'SAP'. For example, if Provider 1 has 100 records and Provider 2 has 20 records where X = A and Y= SAP, I am expecting output to have all 100 records from provider 1 and columns X, Y & Z are populated only for the 20 rows where the join condition is matching (as I am using LEFT OUTER JOIN).

So while creating the composite provider, provider 1 is added as a UNION and provider 2 is added as a JOIN using LEFT OUTER JOIN. Dimension A from provider 1 is mapped to dimension X from provider 2 for join condition and dimension Y is added as a separate field (not mapping to any field from provider 1) to the composite provider. When I pass filter value 'SAP' to the field Y, the output of the composite provider has only those records where Y = 'SAP', I meant to say it is fetching only those 20 records (as explained in the above example) and is behaving something like below,

SELECT * FROM ( SELECT PROV1.A, PROV1.B, PROV1.C, PROV2.X, PROV2.Y, PROV2.Z
FROM INFOPROVIDER1 AS PROV1
LEFT OUTER JOIN INFOPROVIDER2 AS PROV2 ON PROV2.X = PROV1.A )
WHERE Y = 'SAP';

Any pointer on the above issue, would be of great help!!

Thanks & Regards,

Vishwa

[Former Member]

Hello,

If you want to filter in a composite provider on a field or infoobject, you can create an new infoobject which contains the filter values and use that infoobject in an INNER JOIN. This type of filter is only restricted in equal to (=), if you need to use the not equal to (<>) in your filter you need to maintain the infoobject with the correct values.

In your example you need to create an infoobject YFILTERY which contains 'SAP' as masterdata. By using the INNER JOIN the result of INNER JOIN is only 'SAP' data.

If you need to adapt your filter values, you can do this by 'Maintain masterdata' on the infoobject

I use this method and it works.

Greetz

[Former Member]

Excellent Question on Composite Provider .

Even I am also facing same issue ,  when we have restricting on Provider2  Y = 'SAP' 

it behave like Inner Join .

However we are expecting 100 records  ( in LEFT OUTER JOIN).

I am also searching for this solution ......

One Solution possible  is to Create another Info provider PROV3 copy of Provider2 .

Move data to  PROV3    from PROV2    in DTP Filter Y = SAP .

Then Join PROV3 to  PROV1  LEFT OUTER JOIN .

then it will work fine .