Transcript Data transfers into a database

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
Data transfers between systems
• Dynamic data (eg. sales orders)
– Interface required?
• Static data (eg. customers)
– Conversion required?
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
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
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
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
• SQL*loader automatic import from a
variety of file formats into DB files
– Needs a control file
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
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
4 digits
Sup name
Sup address
City
Phone
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
4 digits
Sup name
Sup address
City
Phone
NEW
Sup code
3 letters +
4 digits
Sup name
Sup address… Phone Cat
1,2,3 depending
on total purchases
last year
Example 2 – New Cost
Accounting Structure
Maintenance department expenditure:
1 account => separate accounts for different production activities
OLD
Intervention code
Desc. Date
Labour Parts Total
Example 2 – New Cost
Accounting Structure
Maintenance department expenditure:
1 account => separate accounts for different production activities
OLD
Intervention code
Desc. Date
Labour Parts Total
NEW
Intervention code
Desc. Date
labour Parts Total Account
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
discount rates
rep_name
OLD (sales)
CustID* name
address city
sales_to_date
OLD (Shipping)
CustID**
name
address city
Preferred haulier
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
Problem for next week
• Write a script that solves problem 1