Reports in SOA (Business Intelligence & Service Oriented Architecture)
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.
I don't think your design is wrong. Your architecture just seems to be missing a data mart or warehouse or some sort of way to support business intelligence in addition to transaction processing.
BI in SOA is a complex topic and it's impossible to give advice without knowing some details about your architecture. But here are some articles to get you started:
- http://www.infoq.com/articles/BI-and-SOA
- http://blogs.informatica.com/perspectives/index.php/2008/09/30/how-soa-enhances-data-warehousing-and-business-intelligence/
Have you considered ESB for your SOA? It could make integrating a data mart easier into your SOA. See this article: http://www.b-eye-network.com/view/3018
One of the potential users of an ESB is a data integration service. In fact, several vendors have modified their data integration and ETL (extract, transform and load) tools to be event-driven so that they can consume event messages from an ESB. These events can carry information about source data changes, which can be used by the data integration service to incrementally update an operational data store (ODS) or data warehouse. This approach is particularly useful for operational BI applications that require intra-day data.
This is a good example of the kind of confusion that can result when you aren't clear about your design priorities. And in particular, for my taste, there is too much concern with the technical issues and not enough with the user requirements.
I would start out by working with the stakeholders to build a good set of user stories sufficient to identify the scope of the project, and to list the capabilities that will be used for a first phase acceptance test. Your chances for a successful project, and for reducing the likelihood for redesign later, is to agree on a description for a complete deliverable that is immediately useful and provides obvious value once the first phase is complete.
It should be composed entirely from the user's point of view, without even using the words "database", "SOA", "web service", "API", etc.
What you will be building will be a kind of contract agreement between you and the customer for a service of value to them; and once it's agreed, it shouldn't change except to increase its value to the customer in his/her terms. So your best bet is to try to defer consideration of the "how" issues until you have the "what" firmly nailed down.
Then you'll have the freedom to consider various technical options that might be used to deiiver the same result. Often there's a phase-one design that just provides enough to make it work; and you want to preserve the flexibility to learn and adjust the back end in any way you see fit as long as the conceptual contract/logical design is unchanged.
In my experience everyone will be happier if you put your first attention on being able to react to user expectations (which will fill out as everyone learns from the process) rather than pre-optimizing the technology. Microsoft gives you lots of ways to mix and match and evolve an enterprise design. If you're into Agile, remember to start with the least development that works and then iterate like mad.
There is no way I can see from the design that the Travel service will not need access to the Employee service in some way, shape or form. If they are operated in virtual isolation then you'll have a master data management issue just waiting to bite.
I've recently designed the architecture for a T&E system with the HR data within the corporate infrastructure and the T&E front end hosted as SaaS.
In that scenario the T&E system required a base level of HR data primarily to ensure that the employee was validated. It was also required to allow the system to correctly process travel bookings without requiring the employee to re-enter key data.
This was achieved by delivering the base employee data to the travel system, first as a bulk load and then through updates as employee data changed. This does require careful design as you are transporting some PII data but a secure transport protocol and good encryption between the source and destination mitigates for that.
Reporting on the travel bookings and activities are then entirely within the Travel system. They need to be held in a semi-warehouse state to ensure that changes in the base employee records don't pollute the historical travel records. These are 'transactions' and need to be stored as such.
With that in mind, while your design isn't exactly wrong, it's not exactly right. You are quickly going to hit issues that you need to revisit your design to resolve. My overarching recommendation would be to follow @le dorfier's advice and go back to the beginning. Design to meet all user requirements, make sure they are real requirements not just 'wants' (i.e. nice to have). The natural design will then include the requirements for not only the externally hosted front end but also the reporting required to satisfy the backend.
Virtually everything we design today has to be done so with interoperability in mind, we build modular software using components and patterns that deliver loose coupling. That flexibility saves us untold effort later and although it takes longer to design, it's so worth the effort.