Transcript Database merge

C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n
Database merge
Table of contents
•
Please, do not remove this slide if you want to use the Create table of
contents functionality.
•
How to use:
•
•
•
•
•
C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n
•
2
•
First finalize your presentation
When your done choose Create table of contents from the TE Tools tab
Title slide headings will get the first level bullets
Content slide headings the second level bullets
Front page and Table of contents page will not be included
If you have content slides without headings, those will not be included
Note! Table of contents will be always created on the second slide of your
presentation. If you have removed the slide or used it as content slide, insert
a new empty slide on that place.
C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n
I have 2 DB that I have to merge.
Which are the different alternatives
and how to solve the merging
riskless, quickly and with an
appropriate cost ?
Types of merging
Objectives
Data transfer to a “not empty” target Database
Examples
Data integration in existing Databases
Data &
structures
Objectives
Databases consolidation
Examples
Datawarehouse
Data &
structures &
programs
Objectives
Application merging
Examples
Organizations merging, applications
integration
Data
Database
C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n
Applications
What does this mean for IT
merge scenario’s
C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n
merge B to A
(or A to B)
merge A & B
into C
merge A & B
to obtain (A+B)
prog A
prog B
prog A
progB
prog A
prog B
A
B
A
B
A
B
prog A
prog C
A
C
prog A
prog B
A+B
SCENARIO 1MERGE SCENARIO 1
MERGE A INTO B (or B to A)
prog A
prog B
A
B
prog A
A
•
from a technical point of view it is a simple problem of “data migration”
•
the data of applications A have to be moved to database B
A for their needs
business have to decide the “best” application (A or B)
•
for data migration two main problems must be solved
C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n
•
•
•
the compatibility between the source (A) and source (B)
the “deduplication” question
SCENARIO 2MERGE SCENARIO 2
MERGE A & B INTO C
prog A
progB
A
B
prog C
C
•
business selected application C which is
•
•
a new or an existing application
a package
A
the database structure C is different from database A structure
and database B structure
•
from a technical point of view it is a problem of two “data migration”
C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n
•
•
•
•
the data of applications A have to be moved to database C
the data of applications B have to be moved to database C
this scenario is the same as the previous one: the problems are same but must be solved two times
SCENARIO 3MERGE SCENARIO 3
MERGE A & B TO OBTAIN (A+B)
prog A
prog B
A
B
prog A
prog B
A+B
•
the database structure (A+B) is a “meta structure” integrating database A structure and database B
structure the target (A+B) are obviously “compatible” with source A & B
•
A
from technical point of view it is a problem of two “application
migration”
•
C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n
•
•
•
the database (A+B) have to be defined
for A application
•
the data have to be “moved” to (A+B)
•
programs must be adapted to use (A+B)
for B application
•
the data have to be “moved” to (A+B)
•
programs must be adapted to use (A+B)
for “data” only the problem of “deduplication” must be solved
merge
scenario’s
IMPACTS OF THE
3 SCENARIO
1
2
3
prog A
progB
prog A
prog B
prog A
prog B
A
B
A
B
A
B
prog C
prog A
C
A
prog A
prog B
A+B
impacts
C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n
scenario
technical
business & organization
1) merge B to A
(or A to B)
training for users B (or users A)
2) merge A & B
to obtain C
new application
training for users A & B
3) merge A & B
to obtain (A+B)
no training for users
A
programs
no impact
on programs
new devpt ?
package ?
rewriting ?
adapt prgs. A
&
adapt prgs. B
database
structures
content (data)
no impact
A+B
on target
structure
C
A+B
new structure
covering
(A & B)
A+B
WHAT IS THE BEST SCENARIO ?
THE BEST SCENARIO IS THE ONE PRODUCING A SOLUTION COMPLIANT TO
BUSINESS NEEDS
Two ways to know the business needs :
•
a classical top-down approach starting from “business needs”
a bottom-up approach starting from the existing applications (A &/or B)
this two approaches are complementary
•
•
•
•
REVER’s technologies help the “project owner” (business)
C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n
•
•
•
to understand, based on the semantic model, which system of the two existing application is the most
appropriate to answer their needs
to evaluate differences between the 2 sources .
to check how far they are compatible
WHAT IS THE PROBLEM OF THE
IS“COMPATIBILITY”
two Information Systems are “compatible” if they share the same concepts at the
semantic level (IS definitions)
C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n

This means that for a merge of two databases it is necessary

to compare concepts and definition of the two semantics schemas (semantic level)

to compare the database structure implementation of the logical schemas (logical level)

to compare the data value of the two databases (physical level)
EXAMPLES OF SEMANTIC DIFFERENCES
Source 1
Source 2
example 1 : definition differences
contracts :
a contract
can be signed
by one person
Or by a group
contracts :
a contract can
only be signed
by one person
example 2 : relational differences
C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n
contracts
additional
clauses
disasters
contracts
impoverishment
?
enrichment
additional
clauses
disasters
EXAMPLES OF LOGICAL DIFFERENCES
Source 1
Source 2
example 1 : differences in transformation
a multivalued field :
PHONENBR (5)
is designed as 5 columns
PHONE1, …PHONE5
is designed as 1 table with a
foreign key
PHONENBR, FK
C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n
example 2 : differences in implementation
a relation between two entities
is implemented as a « set » in a
CODASYL DBMS  records
must be accessed via the
« record owner »
is implemented as a
« FOREIGN KEY » in a
relational DBMS  records
can be accessed directly
EXAMPLES OF PHYSICAL DIFFERENCES
Source 1
Source 2
example 1 : differences in format
a field « BIRTHDATE »
is define as 6 digit
is define as « date »
C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n
example 2 : differences in value
a field « SEX »
is filled by « M » or « F »
is filled by « 1 » or « 2 »
WHAT DOES REVER’s TECHNOLOGIES
BRING TO A DATABASE MERGE
for scenario 1 & 2 : REVER’s technologies
•
•
•
•
•
•
•
•
C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n
•
rebuild the three level (physical, logical, semantic) models for the source and the target database
allow the business to compare the two semantic models and to define the “mapping” between source and
target model at semantic level
based on this mapping compare automatically source and target models at logical and physical level
check the data quality in the source and target databases
identify where are the ‘incompatibility” between source and target databases
allow to define mapping “rules” to “map” the data from source to target database
generate automatically programs to move, transform and load data
identify which programs present higher risks and must be tested
WHAT DOES REVER’s TECHNOLOGIES BRING
TO A DATABASE MERGE
for scenario 3 : REVER’s technologies
•
•
•
•
•
•
rebuild automatically the three level (physical, logical, semantic) models for the two databases
build a “meta” model for the “target” database” which included all structures and relations of both
databases
generate automatically programs to move data from the two sources databases to the target
adapt automatically programs of the two source applications to use the target database
identify which programs present higher risks and must be tested
C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n
this solution is very useful when the source databases are implemented in different DBMS (e.g. IMS and
IDMS) and the target is a third DBMS (e.g. : relational)
It is the solution of choice to have a “quick and riskless merge” giving time to think more in detail which
other scenario would best fit the business needs and evolution ( rewrite the application, buy a package…)
SCENARIO 3 DETAILS
C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n
The different steps for scenario 3
In a nutshell, where can REVER help ?
•
•
•
•
Before deploying the right approach
The first decision is a business decision
Rever can highlight the risk linked to the chosen approach
REVER can highlight the order of migration of each procedure (group)
Identification of all links between procedures
Technical phasing integrating programs & data
•
•
•
•
•
•
C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n
•
•
•
•
During deployment of the selected approach
Target DB conception
Data quality control and compatibility with the new structure
Automatic generation of the unload/load programs
Mapping help Source/target
Automatic programs transformation if needed
After migration
Logical dataset extraction
Knowledge base available for future evolutions
XML comparator
REVER ADDED VALUE
On top of the added value induced by the MDDE:
•
•
•
•
REVER solutions provide several added values:
•
•
•
•
•
•
C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n
Automatic generation of the programs
Documentation always available
Models available after migration
•
•
Choice between “soft” evolution or“Big Bang”
Optimized native target base, ready for new developments
Integration into the target model of new functions and/or constraints if needed
Data validation before merging
Consistency of the merged data
Merging checks
Automatic modification of the existing programs to access the new DB
C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n
MERGING ACTIVITIES
C o p y ri g ht 2 0 0 8 Ti e t o C o rp o ra t i o n
Questions & Answers