LAB 1 : Introduction to Data Warehousing
Scenario
Adventure Works Cycles, a fictional company that manufactures and sells bicycles and accessories to
customers worldwide. Adventure Works sells direct through an ecommerce website, as well as an international
reseller network.
Throughout this course, you will develop a data warehousing solution for Adventure Works Cycles, including
a data warehouse, an ETL process to extract data from source systems and populate the data warehouse, a
data quality solution, and a master data management solution.
Objectives
After completing this lab, you will be able to :
• Describe the data sources in the Adventure Works data warehousing scenario.
• Describe the ETL process in the Adventure Works data warehousing scenario.
• Describe the data warehouse in the Adventure Works data warehousing scenario.
Exercise 1 : Exploring Data Sources
Scenario
Adventure Works uses various software applications to manage different aspects of the business, and each
has its own data store. Specifically these are :
Internet sales processed through an e-commerce web application.
•
• Reseller sales processed by sales representatives, who use a specific application. Sales employee
details are stored in a separate human resources system.
• Reseller payments are processed by an accounting application.
• Products are managed in a product catalog and inventory system.
This distribution of data has made it difficult for users to answer key questions about the overall performance
of the business.
In this exercise, you will examine some of the Adventure Works data sources that will be used in the data
warehousing solution. The main tasks for this exercise are as follows:
1. Prepare the Lab Environment
2. View the Solution Architecture
3. View the Internet Sales Data Source
Publicité
4. View the Reseller Sales Data Source
5. View the Products Data Source
6. View the Human Resources Data Source
7. View the Accounts Data Source
8. View the Staging Database
Task 1 : Prepare the Lab Environment
1. Restore files all files in the « LABS-Atelier SID\ Lab01\BackupFiles » folder. Indication : Refer to
« Préparation Environnement Labxx » file in the « LABS-Atelier SID » folder for help.
Task 2 : View the Solution Architecture
1. Use Paint to view the Adventure Works DW Solution.jpg in the « LABS-Atelier SID\ Lab01» folder
2. Note the data sources in the solution architecture.
Note: In addition to the data sources that you will examine in this lab, the diagram includes a Microsoft® SQL
Server® Master Data Services model for product data and a SQL Server Data Quality Services task to cleanse
data as it is staged. These elements form part of the complete solution for the lab scenario, but they are not
present in this lab.
Task 3 : View the Internet Sales Data Source
2. Use Microsoft® SQL Server® Management Studio to open the View Internet Sales.sql query file in
the « LABS-Atelier SID\ Lab01» folder.
3. Execute the query and examine the results. Note that this data source contains data about customers
and the orders they have placed through the e-commerce web application
Task 4 : View the Reseller Sales Data Source
1. Use Microsoft® SQL Server® Management Studio to open the View Reseller Sales.sql query file in
the « LABS-Atelier SID\ Lab01 » folder.
2. Execute the query and examine the results. Note that this data source contains data about resellers
and the orders they have placed through Adventure Works reseller account managers.
Task 5 : View the Products Data Source
1. Use Microsoft® SQL Server® Management Studio to open the View Products.sql query file in the
« LABS-Atelier SID\ Lab01 » folder.
2. Execute the query and examine the results. Note that this database contains data about products
that Adventure Works sells, organized into categories and subcategories.
Publicité
Task 6 : View the Human Resources Data Source
1. Use Microsoft® SQL Server® Management Studio to open the View Employees.sql query file in the
« LABS-Atelier SID\ Lab01 » folder.
2. Execute the query and examine the results. Note that this database contains data about employees,
including sales representatives.
Task 7 : View the Accounts Data Source
1. Examine the comma-delimited text files in the « « LABS-Atelier SID\ Lab01\ BackupFiles\Accounts »
folder by opening them in Microsoft Excel®. Note that they contain details of payments made by
resellers.
2. Close all files when you have finished reviewing them, without saving any changes.
Task 8 : View the Staging Database
1.
In SQL Server Management Studio, in the Object Explorer pane, examine the tables in the Staging
database in the instance of SQL Server. Ensure you examine the Staging database, not an other
database.
2. Note that all tables other than dbo.ExtractLog in this database are empty.
Results: After this exercise, you should have viewed data in the InternetSales, ResellerSales, Human Resources,
and Products SQL Server databases, viewed payments data in comma-delimited files, and viewed an empty
staging database.
Exercise 2 : Exploring an ETL Process
Scenario
Now that you are familiar with the data sources in the Adventure Works data warehousing solution, you
will examine the ETL process used to stage the data, and then load it into the data warehouse. Adventure
Works use a solution based on SQL Server Integration Services to perform this ETL process.
The main tasks for this exercise are as follows:
1. View the Solution Architecture
2. Run the ETL Staging Process
3. View the Staged Data
4. Run the ETL Data Warehouse Load Process
Task 1 : View the Solution Architecture
Publicité
1. Use Paint to view Adventure Works DW Solution.jpg in the « LABS-Atelier SID\ Lab01 » folder.
2. Note that the ETL solution consists of two main phases : a process to extract data from the various
data sources and load it into a staging database, and another to load the data in the staging
database into the data warehouse. In this exercise, you will observe these ETL processes as they run.
Task 2 : Run the ETL Staging Process
1. Use Visual Studio to open the AdventureWorksETL.sln solution file in the « LABS-Atelier SID\
2.
Lab01» folder.
In Solution Explorer, view the SSIS packages that this solution contains, and then double-click Stage
Data.dtsx to open it in the designer.
3. View the control flow of the Stage Data.dtsx package, and then run the package by clicking Start
Debugging on the Debug menu. The package will run other packages to perform the tasks in the
control flow. This may take several minutes.
4. When the package has finished running, a message box will be displayed, although this may be
hidden by the Visual Studio window. Look for a new icon on the taskbar, and then click it to bring
the message box to the front. After viewing this message box, stop the package by clicking Stop
Debugging on the Debug menu.
Task 3 : View the Staged Data
1. Use SQL Server Management Studio to view the Staging database in the MIA-SQL instance of SQL
Server. Take care to view the Staging database, not the DQS_STAGING_DATA database.
2. Note the following tables now contain data :
• dbo.Customers
• dbo.EmployeeInserts
• dbo.InternetSales
• dbo.Payments
• dbo.Resellers
• dbo.ResellerSales
Task 4 : Run the ETL Data Warehouse Load Process
1.
In Visual Studio, in Solution Explorer, view the SSIS packages that the AdventureWorksETL.sln solution
Publicité
contains, and then double-click Load DW.dtsx to open it in the designer.
2. View the control flow of the Load DW.dtsx package, and then run the package by clicking Start
Debugging on the Debug menu. The package will run other packages to perform the tasks in the
control flow. This may take several minutes.
3. When the package has finished running, a message box will be displayed, although it may be hidden
by the Visual Studio window. Look for a new icon on the taskbar, and then click it to bring the
message box to the front. After viewing this message box, stop the package by clicking Stop
Debugging on the Debug menu.
4. Close Visual Studio when you have finished.
Results: After this exercise, you should have viewed and run the SQL Server Integration Services packages
that perform the ETL process for the Adventure Works data warehousing solution.
Exercise 3 : Exploring a Data Warehouse
Scenario
Now that you have explored the ETL process that is used to populate the Adventure Works data warehouse,
you can explore the data warehouse to see how it enables business users to view key information.
The main tasks for this exercise are as follows :
1. View the Solution Architecture
2. Query the Data Warehouse
Task 1 : View the Solution Architecture
1. Use Paint to view Adventure Works DW Solution.jpg in the « LABS-Atelier SID\ Lab01» folder.
2. Note that the data warehouse provides a central data source for business reporting and analysis.
Then close Paint.
Task 2 : Query the Data Warehouse
1. Use SQL Server Management Studio to open the Query DW.sql query file in the « LABS-Atelier SID\
Lab01» folder.
2. Execute the query and examine the results. Note that the data warehouse contains the data necessary
to view key metrics across multiple aspects of the business.
Results: After this exercise, you should have successfully retrieved business information from the data
warehouse.