The project consisted of gathering Sales, PNL and WorkForce data from four different ERPs. The data is provided in three different types of data source: SQL Server, Oracle Database and file system. The file system data comes from cash register tickets, which means that all the data is inside one file and must be stripped down in a given order.
These systems do not interact with each other; the ETL part will have to merge them in a unified datamart to provide a complete view of the company. The requirement would be that all sources converge in the DBCS; then, the PNL, Workforce and part of the sales data would be loaded to PBCS to provide Actuals information for forecast planning. More detailed sales data should be loaded to a star schema that will be used in BICS. The client also wants the forecast data from PBCS to be loaded in BICS as well.
All this must be done outside the client’s infrastructure, which is very basic (only user PCs), with no servers or databases.
I think this is a best solution than the ODICS since its enabled me to work with the 11g version (that I prefer) and allow me to install the agent anyplace I want. In that case the agent were installed in the DBCS machine and this give me some improvement in the data load performance since the agent and the database were in the same machine. We had to buy more disk a more memory because of this but I think it was worthy.