BIData 201A: : Final Description: BI Concepts Planning Implementation Project

Tim Pauley

BIData 201A, Assignment 07

Due: November 28, 2016 *Class 8

UW Extension

Certificate in Business Intelligence: Building the Data Warehouse

---

Building the Data Warehouse Project

In this project, you will create a simple BI solution using some of the data found in Microsoft’s AdventureWorks2012 database. You instructor has created a simplified version of this database for you, and your job will be to use this new project database to create the solution.

Project Scope

This BI solution is being created for a small department that is interested in customer sales. The tables are filled with a moderate amount of data as shown in Figure 1. The number of users of the solution will be less than ten employees. So, performance tuning is not considered an issue at this time. We will be creating the prototype of the final solution. This solution must include a SQL Server Data warehouse and an SSIS ETL package. The solution must also contain the developer documentation in the form of an Excel spreadsheet and a formal documentation in the form of a Word document.

Required:

·       (1) SQL Server Data warehouse

·       (1) SSIS ETL package

·       (1) Developer documentation (Excel spreadsheet)

·       (1) Formal documentation (Word document)

Figure 1: The number of rows in AW_Project01_OLTP_DB’s tables

OLTP Source Database

The new project database (called AW_Project01_OLTP_DB) contains the tables shown in Figure 2. Your instructor has provided a backup file to restore to this database on your computer. You will find this backup file along with a script file to perform the restoration in the project folder.

NOTE: I recommend that you place the Project folder in your C:\_BISolutions folder before you run the SQL restoration script or the script will not work. Optionally, you may also change the paths in the SQL script.

Figure2: The tables of the AW_Project01_OLTP_DB database.

Note: The data in this database has known inconsistences, so examine the data carefully and "if in doubt, leave it out!"

Figure2: The tables of the AW_Project01_OLTP_DB database.

Dimensions and Measures

From these tables, you must extract (2) measures and (5) dimensions. Keep in mind that the dimensions include a fact dimension, a many-to-many dimension, and three standard dimensions.

Timeline

The project contains 3 milestones. At each of these milestones are a portion of the project. The milestones are:

Design and Begin Documentation (Due by class 8)

In milestone 01 you must create developer design documentation using an Excel spreadsheet and the beginning of the formal design documentation in a Word docx file. The steps to do this covered in first 3 modules of this class. 

·       Module 1:  An Overview of BI Projects and Solutions (Ch. 1 and 2)

·       Module 2: Planning and Documentation (Ch. 3)

·       Module 3: Designing the Data Warehouse (Ch. 4)

You will need to turn in a Word document, Excel spreadsheet, and a VS solution; similar to the ones shown here:

Create the Data Warehouse (Due by class 9)

In this milestone, you need to create the Data Warehouse script and add it to your BI solution. The steps to do this covered in the 4th module of this class. 

·       Module 4: Building the Data Warehouse (Ch4 and 5)

You will need to turn in a SQL database creation script, and backup and restore script, and a database backup file, in addition to your previous files from Milestone 01; similar to the ones shown here:

NOTE: I recommend you start your ETL script directly after creating the database so that you will have a head start on Milestone 03. You have been warned!

Create an ETL Process and Final Documentation (Due by class 10)

In this milestone, you need to create an ETL script and an SSIS project in your BI solution. The steps to do this covered in the 5th and 6th modules of this class. 

·       Module 5: ETL processing with SQL (Ch. 6)

·       Module 6: ETL processing with SSIS (Ch. 7 and 8)

Note: You need to turn in a SQL database ETL script and SSIS project, in addition to your previous files from Milestone 01 and 02; like the ones shown here:

Project partner

Each student will have a project partner. Project partner provide assistance and peer reviews during the process.

You can choose a project partner if you want, but if you have not done so by milestone 2, one will be assigned to you.

Turning in Your Work

As each milestone is completed students are to submit their work to Canvas and on the discussion topic for each Milestone. The Student's project partner will download and provide a peer review using the zipped files posted to the discussion topic. Peer reviews should be returned within 3 to 5 days (please!)

Note: Students need only to turn in a project to qualify for a passing grade. The only purpose of the evaluation is for everyone to see the different ways that the project can be done.