Re: Access to heterogeneous data sources - XML

"Andrea M. Segovia" <andrea_at_mun.ca> wrote in message news:7589088f.0403080637.63c9f418_at_posting.google.com...
> Hello, everyone:
>
> We have a Oracle 3 tier application (using Oracle iAs to a back-end
> Oracle database on an Tru64 UNIX server) which also incorporates
> custom written analysis and mapping tools for fisheries-related data.
>
> There is interest in broadening the scope of this application to also
> gather data from other data sources. The problem is that the other
> data sources are not Oracle databases (some are MS-SQL Server, others
> are MS-Access databases). Moving data to an Oracle format is not a
> possibility, as some of these data sources are out of our
> organization's control. To complicate matters even further, some of
> the data sources are not local (ie. in remote locations in either
> internal or external networks from one end of the country to another).
>
> Is XML a possibility for this? What XML functionality is available
> through Oracle, MS-SQL server? How would I do this using XML?
>
> Regards,
> Andrea

XML is basically a file format and that in and by itself isn't going to help you. Currently, it is a big buzz word. Concentrate on the business problem and what a solution to your problem would look like. This will help you to come up with solutions that would fit the problem. (instead of a hammer and everything is a nail.)

You are basically talking about using an ETL tool. It is unlikely that the fishery data is stored in the same structures in all those different databases. That is not the same table and table name with columns that are matching in data type and name. (and meanings) So for each data source you will need to know what the "transformation" is so you can read the data. Major job. If the data sources are out of your control then it is unlikely you will be able to see them or connect to them.

Your best bet is probably to treat your central system as a data warehouse and copy the data to your system periodically. How to do that? You could load a java SQLServer driver into Oracle and connect to all those SQLServer databases and grab the data.(the other problem is that things won't always be up or available to connect to.) You would still have to transform the data into some common schema etc. Again , a highly detailed task. As for the MS Access databases, you could probably find some driver that can read those and use SQL to get at the data. Again you are going to have a lot of mapping to do.

Jim Received on Tue Mar 09 2004 - 20:35:41 CST