Innovations in Software Development
Introduction:
Research the concepts and technology of data ETL (Extraction, Transformation & Loading) and document the process from start to finish using Microsoft SSIS (SQL Server Integration Services) as the basis of your study.
Learning Outcome(s) Being Assessed:
1. Describe the operation of a data warehouse in support of business intelligence.
Details of Task:
ETL is an important process in the construction of Data WareHouse solutions. It consists of the following three primary phases, you must research each as well as the more specific steps contained within:
- Extracting Data
o From one or more external data sources.
- Transforming Data
o Ensure consistency and satisfy business requirements.
- Loading Data
o To the resultant Data WareHouse.
Microsoft SSIS provides a collection of tools to support ETL including:
- Data Flow Engine.
- Scripting Environment.
- Data Profiler.
"SSIS also acts to glue together the various servers and engines that make up SQL Server".
You must discuss the various tools that SSIS provides and relate them to the phases within ETL. You should also provide suitable examples from case studies (such as AdventureWorks & AdventureWorksDW) to reinforce your discussions.
What you should hand in:
A word processed report not exceeding 20 pages.
Guidelines:
- Correctly reference resources that you use.
- You may also wish to compare & contrast other ETL solutions.
Resources Required:
You may use the computing laboratories on the 6th & 7th floors and the university's database servers (ask the lecturer for details).
You should make use of these specific tools & resources:
- Microsoft SQL Server 2012.
- SQL Server Books Online.
- The Internet.