One question I here a lot is how to handle the Join Operation.
For example: we have the data of Students, and Students Courses:
Then we want to Join it like this:
In the general case, I would say to do it on the database:Handling the Join Operation. After all, in other programming languages we don't do it in the application level either. The customer case, however, might be more complex, as I recently learned:
The two lists we want to join might be coming from different databases (or even different database types).
We might not have permission to create this join query on this database.
All this brings us to this point, where we want the application to handle this.
We need to extend the solution to include a piece of code, performing the join operation for us. This is done using a Web Service. Fortunately, there is a built in option to generate the Web Service, in the NetWeaver Developer Studio.
The steps in high level
Create a mockup Service Component in Visual Composer
For the detailed flow of creating the web service, follow the step in: Using Web Services in Visual Composer. I added details for the sake of this specific example, and some screenshots to make it a bit more clear.
Create Service Component skeleton for the Web Service
The relevant part in the documentation: Defining the Interface of the Service Component.
1. Open NetWeaver Developer Studio
2. Open the Visual Composer perspective
3. Create a new model
4. Add a Service Component to the model
5. Right Click => Drill Down
6. Add a Data In element
7. Open Define Data and press View => Hierarchy Tree
8. Add the students Node under the root.
9. Add the fields student_id, student_name in the right panel.
10. Add the students_courses Node under the root
11. Add the fields student_id, course_name
12. Add a Data Out element
13. Open Define Data, add the fields student_id, student_name, course_name
14. Save the model.
15. Go back to the main model, press Redefine Ports for the Service Component. Check all the ports and press OK.
We need to pass the service two lists, with a different set of fields: students and students_courses. To achieve this we defined a cluster, with two sub-nodes, one for each list.
The relevant part in the documentation: Implementing the Web Service.
I only added screenshots for some of the steps to make it a bit more clear.
We start the process with this the Generate WSDL option:
Here is the example Java Bin Skeleton created for us:
We only need to implement one method of one class in this case. See the following java coding I added:
public class JoinSEIImplBean {
public java.util.List<com.sap.vc.sad.Out1> join(com.sap.vc.sad.In1 in1) {
ArrayList<Out1> res = new ArrayList<Out1>();
List<InDirin1Students> students = in1.getStudents();
List<InDirin1StudentCourses> studentCourses = in1.getStudentCourses();
Map<String, InDirin1Students> studentsMap =
new HashMap<String, InDirin1Students>();
// convert students table to map
for (InDirin1Students s : students)
studentsMap.put(s.getStudentId(), s);
// go over students courses and find matches
for (ListIterator<InDirin1StudentCourses> it = studentCourses.listIterator();
it.hasNext(); ) {
InDirin1StudentCourses sc = it.next();
InDirin1Students s = studentsMap.get(sc.getStudentId());
if (s == null) continue;
Out1 out = new Out1();
out.setStudentId(sc.getStudentId());
out.setStudentName(s.getStudentName());
out.setCourseName(sc.getCourseName());
res.add(out);
}
return res;
}
}
Consume the new Web Service in our model
The relevant part in the documentation: Consuming the Web Service.
I only added screenshots for some of the steps to make it a bit more clear.
Adding destination for the Web Service looks like this in NWDS:
And in NWA:
Modeling steps:
1. Open the Search Panel and search for the new Web Service
2. Add the service to the model
3. Connect a Grid View to the output port of the service
4. Connect the existing data services to the input port of the Web Service
5. Define the mapping from the Get Students service to the Web Service:
6. Define the mapping from the Get Students Courses service to the Web Service:
That's it
We can now run our new application with the joined data.
Some notes:
What if we don't want to handle the join in a the application? And can't do it on the database where the data is originated? In that case we could have an external process copy the data of both sources to one database we can manage. Then we write the join operation on this database. Finally, consume the join result in Visual Composer (e.g. as a JDBC Stored Procedure).
When we need to pass several different lists to a Service in one input port: This port should have a clustered structure. each list could be represented as a different Sub-Node, thus allowing for its own structure.
Performance: Join operations might be costly. It is important we are aware of the Memory / CPU consumption of the specific Join we are implementing, lest we suffer a performance hit.
Related documents:
Paging records in the UI - Coding our own Web Service to get Row Numbers
- SAP Managed Tags:
