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