1) Complex usage for the BUS
2) Longer time to code SPs
Direct access Pros
1) Ease of usage
Direct Access Cons
1) One view for each type of access whereas SPs can be coded with Parameters for various types of access
2) Performace unles you use materialized views which in turn causes lot more maintainenance headaches
I am trying to understand what is does the "best practice" or guidelines say. Any input would be appreciated.
As far as the ease and of implementation and integration of CDH is concerned, you should always prefer to go for SOA/any middleware that suits your integration environment. You can also add complex business rule transformations thorough BPEL manager.
On the other hand, coding PL/SQL APIs are time consuming and error-prone.
In my view we should always avoid the manual coding and leverage the feature of recent technologies as far as the budget is not a concern :-)