Hi all!!!!!

I think that I have a long way to go trought but, can anyone tel me which is the start point if I want to work with different Databases which have different structures?
I have to make an application that take information for different databases to fit a new database. The solutions that this new database will offer are to be compused with different field belonging to others systems.
I know my question is to general but that anyone have any Idea about how to face it ?

Thanks all in my first post and sorry for my english
Álvaro

"Structures" is a vague word. Are you attempting to move data between relational databases or between relational and other databases?

If the answer is between relational databases, are you moving data between different schemas?

If the answer is between different schemas, are you moving data between different datatypes?

If the answer is yes then you are performing Extract Transform and Load (ETL).

OK, I will try to complete the information: I have to fill in a Database using datas from differents databases, in concrete ORACLE, MySQL and SAP All of then have different structures and i only need a part of each. Thus, the answer for your first question can be YES. I don´t know what do you mean exatcly with different data types, i don´t have to convert any types, something that is a string in a databases are going to be a string in mein too. So perhaps the answer for the second question is NO. Anyway, while i wait for your new post i am reading something about ETL, since it´s the first time i have heard about it .
I thought to make it by XML, exporting and importing datas in a standard format which it´s easy to work with, what do you think about it?

Thanks,
Álvaro

I don't know about using XML as my exposure is limited.

I don't know if these data transfers are live or not. But a good idea is to come up with some intermediate staging area where you simply extract the information from the source databases, transform the information as needed and load it into a staging area. When you know the data is clean and merged you follow this by actually migrating onto the target system from the staging area.

I understand the Idea you mean but can you be a little bit concreter. How would you make this staging area?
How would you extract the datas from the different databases?
How can you know the datas are clean and fit your expectatives?
and how would you migrate to the target system?

I know that are difficult question but i nedd design information, I need to know which are the correct tools because i have no experience working with those topic.

Thanks,
Álvaro.

How would you make this staging area?

The staging area would be a database server with the same database package as the target database.

How would you extract the datas from the different databases?

The target database package will have utilities that enable you to connect to and extract from your source databases. If you have to perform transforms you can use SQL or an ETL tool such as Informatica.

How can you know the datas are clean and fit your expectatives?

You have to determine what the acceptable values are for each column in each table of your target database. The staging area will be set up to correspond to the subset of tables representing the data that is being extracted from the source, transformed and loaded into the target.

and how would you migrate to the target system?

The migration would be staging server to target server transfer of data by the same utilities used to ETL the source data. You may have to do some more contortions to incorporate the migrating data into the target database, this would be handled by SQL or the ETL tool.

[QUOTE][SIZE=1]Originally posted by leazfe
I have to make an application that take information for different databases to fit a new database.

This page on my Database Answers web site might help you :-
http://www.databaseanswers.com/data_migration/index.htm

I suggest that you follow these steps :-
1) print the page and then
2) think about, especially the things you do understand.
3) modifying it to suit your circumstances.
4) Produce a step-by-step plan and review it with your colleagues.

The key concept has been identified for you by Certes, which is to have a Staging Area (or maybe more than one).
You can then use SQL as your standard technique for moving data into and out of your Staging Area.

Maybe think about using Microsoft Access as a front-end and migrate some data sources into Access because you can then link to databases remote servers,(using ODBC), which makes life very simple.

Good luck.

Barry Williams
Principal Consultant
Database Answers

I have read up your page "Database Answers web site", and i think it can really help me but there is a lot of thinks that i don´t understand. I am getting it:
1. Choose a Data Modelling Tool with Reverse Engineering Capability,(such as ERWin).
2. Define and create the Data Dictionary.
----------------------------------
the final Database is already done, perhaps i would have to change its technology because now it´s make with access.
----------------------------------
3. Identify all the required Data Sources, and an 'owner' for each Source.
Data Feeds
Legacy Systems
Operational Data Stores
---------------------------------
Ok with the Data Sources but what do you mean with Data Feeds, Legacy Systems and Operational Data Stores?
--------------------------------
4. Define the Data Items required, in consultation with the Users.
--------------------------------
It´s already done.
--------------------------------
5. Create the Data Models for the Source Data.
--------------------------------
I don´t understand this step too. I am getting now the Databases soruce structures to find how can i fit my finally database (of course i need a staging area)
--------------------------------
6. Define Data Validation Checks (bottom-up) and Clean-Up Business Rules for Source Data.
7. Carry out an Audit of the Data Quality in major Databases, (bottom-up and top-down).
--------------------------------
I think i understand the sense but I don´t understand the Terms (Bottom-up or Clean-Up Bussiness Rules for Source Data). I am sure it´s due to my inexperience.
-------------------------------
8. Evaluate the benefits of a Data Cleansing Product, such as Seamless from the C and C Group.
9. Define the Staging Area, with MIRror Tables to store Extract Files.
10. Create the Business Data Model for the Consolidated Database
If the final target is an ERP, such as SAP, then create the Data Model for the Target ERP Database. For SAP, J.D.Edwards, Peoplesoft and Siebel, a Modelling Tool called Saphir, from Silwood Technology
can be very useful here.
11. Define the Data Mapping between Source and Target Data Items.
Optionally, create a CRUD Matrix to identify the interactions between Data and Functions.
-------------------------------
Till "CRUD Matrix" i am happy that i have read two steps whiout any question
------------------------------
12. Define Acceptance Tests for data in the Integrated Database.

I will let the Migrating process for another post
Thank in advance,
Álvaro

[QUOTE][SIZE=1]Originally posted by leazfe
I have created a Simplified Approach for you :-
http://www.databaseanswers.com/data_...n_approach.htm
I will make some comments on your experiences ...
>2. Define and create the Data Dictionary.
>----------------------------------
>the final Database is already done, perhaps i would have to change its >technology because now it´s make with access.
The Data Dictionary is not strictly necessary.
The basic minimum is a set of Mapping Specifications from Sources to Targets.
----------------------------------
3. Identify all the required Data Sources, and an 'owner' for each Source.
Data Feeds
Legacy Systems
Operational Data Stores
---------------------------------
Ok with the Data Sources but what do you mean with Data Feeds, Legacy Systems and Operational Data Stores?
Data Feeds = Regular Extracts from Operational Systems.
Legacy Systems = Systems about to be replaced.
ODS=Data from Systems which are currently Operational.

>--------------------------------
>5. Create the Data Models for the Source Data.
>--------------------------------
>I don´t understand this step too. I am getting now the Databases >soruce structures to find how can i fit my finally database (of course i >need a staging area)
I am a Data Analyst and always like to see Data Models but they are not strictly necessary.

>7. Carry out an Audit of the Data Quality in major Databases, (bottom->up and top-down).
>--------------------------------
>I think i understand the sense but I don´t understand the Terms >(Bottom-up or Clean-Up Bussiness Rules for Source Data).
Bottom-up means look at low-level data currently available, like Invoices, or Student Attendance Registers.

>11. Define the Data Mapping between Source and Target Data Items.
>Optionally, create a CRUD Matrix to identify the interactions between >Data and Functions.
>-------------------------------
>Till "CRUD Matrix" i am happy that i have read two steps whiout any >question
Again, CRUD Matrix is not essential. It helps to check that all Data Items are created at least once and used at least once. But alternatively, these checks fall out at the end according to the natural law that everything essential gets done before the job is finally finished !!!

Good luck

Barry

Thank you very much for your help. I have wrote the specifications for my data migration already, by using the steps you have collected. Thus, in my "staging area" there are querys from the different databases having the data i need. Those query are made based on the Specifications.
I would like to ask another thing. Since now the database is in Acess done, i have to check periodically all the others databases in order to realize that there is new solutions that can fit partialy my database. I don´t now to much about Oracle but i think that, i have read it´s possible to do it using "trigers" ( for example, i would have to fire it, as long as a total or partial solution is complete in the staging area) . If it´s true, does anyone know a way to do the same with Access? or do i have to change my target system? Or simply , perhaps there is a better way to achive the same results.

Thanks again,
Álvaro.