Implementing a Data Warehouse with Microsoft SQL Server 2012

Learn how to design and implement an enterprise data warehouse, with Microsoft SQL Server.

Introduction

• Welcome
• What you should know
• Using the exercise files

1. Setting Up the Environment

• Finding and downloading SQL Server 2012 trial edition
• Installing SQL Server 2012 trial edition
• Finding and installing the sample data

2. Introduction to Data Warehousing

• Overview of data warehousing
• Understanding the differences between typical databases and data warehouses
• Implementing a data warehouse using a SQL database engine

3. Designing a Data Warehouse

• Logical design for a data warehouse
• Choosing between star and snowflake schema design techniques
• Physical design for a data warehouse

4. Data Warehouse Hardware

• Considerations for building a data warehouse
• Data warehouse reference architectures
• Appliances and big data

5. Creating an Extract Transform and Load (ETL) Solution with SQL Server Integration Services (SSIS)

• Introduction to ETL with SSIS
• Exploring source data
• Introduction to control flow
• Implementing data flow

6. Debugging and Troubleshooting SSIS Packages

• Debugging an SSIS package
• Logging SSIS package events
• Handling errors in an SSIS package

7. Implementing an Incremental ETL Process

• Introduction to incremental ETL
• Extracting modified data
• Loading modified data
• Working with slowly changing dimensions

8. Deploying and Configuring SSIS Packages

• Installing and maintaining SSIS components
• Deploying SSIS solutions
• Using SQL Server Agent to execute an ETL package
• Configuring advanced SSIS settings

9. Enforcing Data Quality

• Installing Data Quality Services
• Using Data Quality Services to cleanse data
• Using Data Quality Services to find duplicate data
• Utilizing Data Quality Services in an SSIS data flow

10. Consuming Data in a Data Warehouse

• Introduction to business intelligence
• Using Reporting Services with a data warehouse
• Introduction to data analysis with SQL Server Analysis Services (SSAS)

Conclusion

• Next steps