Is it possible to do an Outer join on datasets?
I have 2 databases that I have to pull & join data from.
I need to get SalesAgentNumber, StartDate, SalesAmount for the year into a report.
SalesAgentNumber & StartDate are both in database 'DB1'.
SalesAgentNumber & SalesAmount are both in database 'DB2'
So in BiP, I have created 2 datasets:
G_1
----
Select SalesAgentNumber, StartDate from DB1.Dim_SalesAgent
G_2
----
Select SalesAgentNumber, sum(SalesAmount) from DB2.Fact_Sales where SalesDate >= 1/1/2020
So for my datamodel, I then join SalesAgentNumber from G_1 to SalesAgentNumber in G_2.
Dataset G_1 will return 10 rows (for 10 different Sales Agents).
Dataset G_2 will only return 8 rows, since 2 Sales Agents did not have any sales in 2020.