Working in SAP BW Environment for modelling any scenarios, you may need to combine results from two or more quieries into one dataset, you can accomplish this job by storing the query results to DSO and then applying further transformation on DSO, but that’s process is not recommended and you may hard to model or maintain it later.
For that’s process, there is the best recommended way is you need to use APD ( Application Process Designer ) in SAP BW to combine the results of queries in seamless manner and also implement transformation logic on the result set.
How to do that?
You need to prepare two BEX queries and 1 DSO as target, create two BEX Queries using BEX Query Designer for example : TEST_QUERY1 and TEST_QUERY2 on Infocube 0SD_C03.
After you finished create two queries, you can continue to create 1 Direct Update DSO, don’t forget to include all of the key fields and data field from those queries into DSO .
Ok, you have all prerequisite for this scenario, now you need to open APD using RSANWB tcode, right-click GENERAL in the ANALYSIS PROCESS pane and choose CREATE ANALYSIS PROCESS.
Follow these steps to combine two queries using APD in SAP BW :
- Under the DATASOURCES option, click the query icon to open DATA SOURCES:QUERY screen, in this screen you need to select QUERY as data sources, and input name of the first query, you have to specify the INFOCUBE name first, in this case 0SD_C03.
- Using the same DATASOURCES option, select the second query as DATASOURCE and give the name of the second query.
- From the DATA TARGET option, click on the icon for DSO, Enter the name of DSO you;ve created earlier as data target.
- Merging two queries by selecting UNION OPERATOR from the TRANSFORMATION option.
Ok you finish merge two queries using basic UNION operation to combine them into Direct Update DSO target.