.net - Reports in SOA (Business Intelligence & Service Oriented Architecture)

Question

I have SOA with a Employee service and a Travel Service. The travel service will create a travelID entry for the employeeId in the [Travel] database. The employee will be using a “TravelUI” website (which calls the Travel Service to store details in DB) to request for a travel. There is a “ManagerUI” website which will be used by manager to approve the travel request. The “ManagerUI” website uses the Travel service as well as the Employee Service to get the details. When the manager approve the travel, the travel record (in [Travel] database become approved by using operations in Travel service.

Note: Employee details are stored in [Employee] database and the Employee service uses this data.

Now, we need to generate a report with TravelID, Travel Request Date, EmployeeID, EmployeeName, EmployeePhone. The first three information are from [Travel] database and the remaing are from [Employee] database. The report is to be generated using SSRS.

Here the problem is NOT abou whether it is possible to generate a report by combing two databases; but it become a complicated problem due to the introduction of SOA.

1. How can we resolve the problem

2. What are the mistakes in my design that made the problem complex?

3. Do you have any suggestions for any good articles on handling such a problem?

Note: SOA is planned using WCF here.

EDIT: Though the title mentions about Business Intelligence, I am looking for an answer that does not involves datamart/datawarehouse primarily. Datawarehouse answer is also welcome - but the primary aim is without datawarehouse.

READING:

1. Service-Oriented Business Intelligence http://msdn.microsoft.com/en-us/library/bb245659.aspx

2. A Service-oriented Architecture for Business Intelligence http://www.hpl.hp.com/personal/Claudio_Bartolini/download/soca07.pdf

3. Service-Oriented Architecture and Business Intelligence http://www.servicetechmag.com/I53/0811-2

4. Microsoft on the Enterprise Service Bus (ESB) http://msdn.microsoft.com/en-us/library/aa475433(v=bts.10).aspx

5. https://stackoverflow.com/questions/41353/net-esbs-out-there

Answer 1

The problem from what I can see is that SSRS violates the Contract Centralisation Pattern. Instead your report should be generated from either the services you have created, or by extending those services. Otherwise you create a tight technology based coupling between your report and your databases, which will make it more difficult to modify, migrate or re-implement your travel and employee systems when the need arrises. The more reports you add this way, the more difficult it will become to change your Travel and Employee implementation.

If you haven't already, I would create reporting operations on the travel service, e.g., if you are using SOAP based services, add a GetTravelRequests operation, which accepts some sort of querying and pagination parameters, that just returns the TravelID, Travel Request Date, EmployeeID of the matching records. Then create a GetEmployeeTravelRequests, which composes GetTravelRequests with a GetEmployeeDetails operation from the Employee service.

This will be slower that an SSRS based report, but you are free to then change the underlying implementation of the Travel and Employee services, so long as you don't change the service contract.

I've kind-of assumed you are using SOAP, which is what the answer above is based upon, however if RESTful services are an option, then I would recommend the following. Instead of exposing numeric TravelIDs and EmployeeIDs, instead use URIs. For instance, the travel service will create a travel resource for the employee URI in the Travel database. Then create an Atom based feed of travel requests. You can either stop there and where the report user needs employee details, they can follow the employee URI link. Otherwise, you can use the employee URIs to create a composed Atom feed that includes the employee details for each travel request.

The main advantage of this latter approach is the reduced DB load through the use of HTTP Caching; HTTP GET is the most optimised operation in the world. Your report is now a live report, rather than only as current as when it was last generated, which may be once a day or once a month and if you structure your feed correctly, then the non-head pages never change and can be cached for a year or longer. If you assume that employee details change infrequently, then you can set the max-age to 1 day, in which case a query for a particular employees details will only hit the database once a day.

Finally, another nice benefit, is that it become trivially easy to add a TravelRequests collection resource to the Employee resource, which contains an Atom based paginated list of all the travel requests for that employee.