The data warehouse is the cornerstone of many business intelligence solutions and the key to analytics. In this module students will learn about the structure of a data warehouse and will learn how to apply SQL query skills to retrieving information from a data warehouse.
Prerequisites:
Before undertaking this module students should have basic SQL skills. They should understand how to filter data using the "where" clause and they should be able to join tables. They should also be familiar with using SQL Server and SQL Server Management Studio.
Technical Requirements:
Students need to have access to the Microsoft SQL Server 2012 relational database as well as SQL Server Management Studio 2012. The Express version of the database and Management Studio will work fine. Students will also need to have the following Microsoft databases installed: AdventureWorks2012, AdventureWorksDW2012. These databases are available free of charge from Microsoft's web-site.
Read the following:
Listen to the following lectures:
Complete the following assignments:
The data warehouse is the cornerstone of many business intelligence solutions and the key to analytics. The operational databases that you have worked with are highly normalized and designed to facilitate accurate data collection. Data warehouses, in contrast, are designed to facilitate data retrieval. They are designed to make it easy to query the database and retrieve meaningful information.
Data warehouses are typically relational databases so they are comprised of related tables with each table having rows and columns. However, the structure and relationship of the tables within a data warehouse differs from that in an operational database. As you will see, we often describe the table structure in a data warehouse as a star schema comprised of fact tables and dimension tables.
Because a data warehouse is a relational database, we still use SQL (Structured Query Language) to write queries to retrieve information. You can use your SQL skills, to query a data warehouse once you understand its structure. You will likely find querying a data warehouse easier than querying an operational database.
Step 1: View the video: Introduction to the Data Warehouse (9:38)
In this video, you will be introduced to the concept and structure of a data warehouse. Pay particular attention to the star schema and how it differs from other database structures you have seen. Try to understand the difference between fact tables and dimension tables.
Step 2: View the video demonstration: Querying a Data Warehouse (12:44)
In this video, you will be introduced to SQL queries useful with a data warehouse. Observe how the star schema structure exists in the demo data warehouse.
You may find the demo queries in the following .sql file:
Querying a Data Warehouse Demo (.sql)
Step 3: Review the SQL queries demonstrated in Step 2.
Using SQL Server Management Studio, open the .sql file containing the demonstration queries:
Querying a Data Warehouse Demo (.sql)
For each query, execute the query. Then make some modification of your choice. Execute the query again to see the results of your modification.
It is important to "practice, practice, practice" your SQL skills.
Note: The assignment has been provided as a text file for reviewers who are not using SQL Server. Students should be given the .sql file format so that they can write their queries using SQL Server.
This work is created by the National Information Security and Geospatial Technologies Consortium (NISGTC), and except where otherwise noted, is licensed under the Creative Commons Attribution 3.0 Unported License.
Authoring Organization: Bellevue College
Written by: Linda Rumans
Copyright © National Information Security, Geospatial Technologies Consortium (NISGTC)
Development was funded by the Department of Labor (DOL) Trade Adjustment Assistance Community College and Career Training (TAACCCT) Grant No. TC-22525-11-60-A-48; The National Information Security, Geospatial Technologies Consortium (NISGTC) is an entity of Collin College of Texas, Bellevue College of Washington, Bunker Hill Community College of Massachusetts, Del Mar College of Texas, Moraine Valley Community College of Illinois, Rio Salado College of Arizona, and Salt Lake Community College of Utah.
This workforce solution was funded by a grant awarded by the U.S. Department of Labor's Employment and Training Administration. The solution was created by the grantee and does not necessarily reflect the official position of the U.S. Department of Labor. The Department of Labor makes no guarantees, warranties or assurances of any kind, express or implied, with respect to such information, including any information on linked sites, and including, but not limited to accuracy of the information or its completeness, timeliness, usefulness, adequacy, continued availability or ownership.