A small-scale version of a common ETL process, that shows data integration and data warehouse concepts.

A small-scale version of a common ETL process, that shows data integration and data warehouse concepts.T he resulting data should be loaded into MySQL database using an ETL Process. For this process, try to ensure that it as repeatable as possible. Following are the steps that you will need to execute: Extract: The two files (ETLManualData.xlsx and ETLLegacyDBData.txt) are provided to represent data from two different systems. Transform: After reviewing the data, determine what the final combined data will be like in your MySQL database and what changes will need to be implemented. some considerations: 1. What table(s) will you need to create in MySQL to store the data; and what will be the columns or attributes of the table(s), and their associated metadata (e.g. datatypes, sizes, etc.)? 2. What changes need to be made to ensure Data Quality and Integrity? For example, what errors need to be corrected in the data, and how will they be corrected? (e.g., Will it be a manual error correction process? Can database constraints be configured to automatically flag errors and fix them as they are being inserted?) Should there be standards defined for the data? (e.g. all caps) 3. How will conflicts be resolved? (e.g. what to do when values in one system conflict with those of the other) 4. What will you do with data that is provided in one system but not the other? HINTS: 1. For DATE data: MySQL has a STR_TO_DATE() function that verifies dates when converting from a string in a specified format 2. Can use ENUM() for MySQL table data types to check for valid values NOTE: It is important that you do not lose any data and that all the data is somehow accessible in the consolidated database table(s) Write a program using any programming language of your choice (e.g. C++) to do the following: 1. Read the extracted data 2. Transform data into the final combined data format 3. Generate file(s) that will be used in the LOAD phase 4. Log all errors that were encountered into an error log file NOTE: There may be manual fixes needed based on the errors flagged LOAD: Determine how you will load the data into the database (where possible, try to utilize method(s) that would be able to account for larger amounts of data) Then load the data Verify that your data is loaded correctly with all the considerations from the TRANSFORM step handled. At the completion of this ETL Exercise, the combined, transformed, and cleansed data from the two systems (legacy and manual XLS) should be successfully loaded into your MySQL database. I would need the following: 1.Document that includes: TRANSFORM considerations (e.g. answers to the questions above in the TRANSFORM section) Detailed steps of a plan outlining the full ETL process that you followed, in order to LOAD the combined data into your MySQL database 2.Program code for the TRANSFORM 3.SQL output displaying data in the final table(s) in your MySQL database after the whole ETL process is completed 4.Error log file(s)

Get Help With a similar task to - A small-scale version of a common ETL process, that shows data integration and data warehouse concepts.

Login to view and/or buy answers.. or post an answer
Additional Instructions:

Orders OrderID Customer Sales Rep Region Product Unit Price Units Sold Total SalesDate 1001 Big Company J. Smith Central Office Chair 75 100 7500 3-Nov-16 1002 Small Company J. Smith Central Conference Table 500 1 500 4-Nov-16 1003 Mid Industrial E. White Central Office Table 350 10 3500 4-Nov-16 1004 Gen Corp E. White Central Office Chair 50 1000 50000 11-Nov-16 1005 Gen Corp E. White Central Office Table 300 1000 300000 12-Nov-16

OrderNumber, Unit, Unit Price, Number Sold, Customer, Region, Date 1001, End Table, 175.00, 3, ABC Corporation, EAST, 10/5/15 1005, Coffee Table, 200, 2, John NewHouse, Central, 10/7/15 1100, Computer Desk, 375.00, 10, Office Industries, WEST, 11/10/15 2002, Entertainment Center, 650, 1, Jane Branch, West, 2/30/16 2003, Writers Desk, 325, 1, Joe White, Central, 2/3/16 2010, Dinint Table, 800, 1, Faye Gathers, East, 2/15/16 3003, Computer Desk, 250, 20, NewOffices Inc, 11/12/16

Related Questions

Similar orders to A small-scale version of a common ETL process, that shows data integration and data warehouse concepts.
111
Views
0
Answers
Simple beginner SQL relational Database project
My idea of the project is to create an application that is called MyBookshelf, that stores books and has the feature of tracking the reading process. So the database is for that app, and you can design it in anyway you like don't go any deep in the design ...
104
Views
0
Answers
Microsoft SQL Server
I have included the instructions in the file below....
291
Views
0
Answers
SQL ER Diagram
Build a data model relationship diagram/ER diagram (with visio and/or lucid charts) . The data model should be extremely clear for cardinality, as many attributes as possible with primary key and foreign key relationships. Summary: I am a startup com...
359
Views
0
Answers
MySQL Homework: Employee Tracker

Developers are often tasked with creating interfaces that make it easy for non-developers to view and interact with information stored in databases. Often these interfaces are known as **C**ontent **M**anagement **S**ystems. In this homework assignment,...