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 • SQL*loader 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 => 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 => separate accounts for different production activities OLD Intervention code Desc. Date Labour Parts Total NEW Intervention code Desc. Date labour Parts Total Account

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