Re: Access to heterogeneous data sources - XML

[Quoted] "Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:<1av3c.92957$PR3.1575500_at_attbi_s03>...
> "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

Jim,

[Quoted] Thanks for the input...I tend to agree with you that the only way to deal with this is to sketch out the business requirements at a high level and work from there. Unfortunately, my (somewhat) technically savvy clients don't agree!!! :-)

Thinking more in terms of the future, which way would you head in order to deal with this type of problem? Obviously, in order to be able to deal with foreign data in real-time, you will need a standardized way of transferring the following information: meta data about the data itself (data dictionary type of information, information about the meaning/interpretation of the data), the data itself, and standardized database queries (such as done through ODBC, perhaps).....are there any bleeding-edge initiatives out there to support this type of functionality?

Andrea Received on Wed Mar 10 2004 - 14:00:05 CET