Data transfers into a database

1
Data transfers into a database

• First time system implementation
• From a manual system
• Data warehousing projects
• Database version upgrade
• ERP projects
• Migration
• From old to new system

2
Data transfers between systems

• Dynamic data (eg. sales orders)
• Interface required?
• Static data (eg. customers)
• Conversion required?

3
What can go wrong

• Data not available
• feature activated from implementation onwards
• Massive data entry
• Eg different account structure
• Data incomplete
• Data inconsistent (eg engineering vs accounts)
• Wrong level of granularity
• Data not clean
• New system requires changes new product codes

4
Data cleaning must address

• Different department record same info under
  different codes
• Multiple records of same company (under different
  names)
• Fields missing in input tables (eg c/o)
• Different depts. Record different addresses for
  same customer
• Use of different units for time periods

5
Labour intensive tasks

• Data entry
• Data checks
• Working on solving conflicts
• Allocating new codes
• Solution introduce as much automation as
  possible
• SQL / SQL loader (Oracle)
• Custom conversion programmes to extract, modify
  and upload data
• Filtering
• Parsing (eg excel)
• Staging areas for conversion in progress

6
Data utilities

• ORACLE is king of data handling
• Export to transfer data between DBs
• Extract both table structure and data content
  into dump file
• Import corresponding facility
• SQLloader automatic import from a variety of
  file formats into DB files
• Needs a control file

7
Control files using SQLloader

• Data tranfers in and out of DB can be automated
  using the loader
• Create a data file with the data(!)
• Create a control file to guide the operation
• Load creates two files
• Log file
• bad transactions file
• Also a discard file if control file has selection
  criteria in it

8
Example 1 the supplier file
New supplier code to include city where firm is
based Assignation of category based on amounts
purchased
OLD
Sup code Sup name Sup address City Phone 4 digits
9
Example 1 the supplier file
New supplier code to include city where firm is
based Assignation of category based on amounts
purchased
OLD
Sup code Sup name Sup address City Phone 4 digits
NEW
Sup code Sup name Sup address Phone Cat 3
letters 1,2,3 depending 4 digits on
total purchases last year
10
Example 2 New Cost Accounting Structure
Maintenance department expenditure 1 account gt
separate accounts for different production
activities
OLD
Intervention code Desc. Date Labour Parts Total
11
Example 2 New Cost Accounting Structure
Maintenance department expenditure 1 account gt
separate accounts for different production
activities
OLD
Intervention code Desc. Date Labour Parts Total
NEW
Intervention code Desc. Date labour Parts Total Ac
count
12
Example 3 merging files

• Complete customer file based on Accounts and
  Sales and Shipping

OLD (finance)
CustID name address city account number credit
limit balance
OLD (sales)
CustID name address city discount
rates sales_to_date rep_name
OLD (Shipping)
CustID name address city Preferred haulier
13
Example 4 change of business practices

• Payment by bank draft for international customers
• Automatic payment into account for national
  customers
• Payment direct into account for all customers

14
Problem for next week

• Write a script that solves problem 1