Objective(s): Using the existing, normalized, AdventureWorks transactional database, you will use Visual Studio / MS-SSIS to build and populate TWO tables for a data warehouse. The data warehouse tables should be populated with a star-schema, de-normalized structure in mind. The data warehouse table designs should also adhere to the best practices discussed in your textbook.
Methodology: You will be given the AdventureWorks "Lite" (10 tables), normalized transaction database. Using the guidelines in Chapter 2 of your textbook for designing data warehouses, you will create TWO data warehouse tables in Microsoft's ETL Tool (SSIS - SQL Server Integration Services). Specifically, you will create and populate ONE Fact table and ONE Dimensional table. (Please note, in this project you are not creating a complete Star Schema, but rather, you are creating one example table of each of the two types of data warehouse tables).
Deliverable(s):
Build and populate your two data warehouse tables within Microsoft's ETL Tool (SSIS - SQL Server Integration Services), which is available via VMWare View. You should perform the "Execute Task" function within SSIS to successfully populate your two data warehouse tables.
Submit a 1 to 2 page summary report that answers the following questions:
If you were the DW Administrator for AdventureWorks, what other tables would you add to this data warehouse?
Hypothetically, if you were to add your recommended tables (from question #1), what example analyses could be performed using your data warehouse as a source?
For this first project, you will use the Microsoft ETL (Extract, Transform, and Load) software (called MS-SSIS or SQL Server Integration Services) within MS-Visual Studio to build and populate two tables in a star-schema data warehouse.
For the project you will build and populate one Dimension table and one Fact table in a hypothetical data warehouse. Your finished and populated tables and associated 1 to 2-page write-up.
- Note: You are not required to actually add your recommended tables in your MS-SSIS project.
Videos will walk you through the procedures to build both the Dimension table and the Fact table (containing Measure)