Implementing a Data Warehouse with Microsoft SQL Server 2012

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

Learn how to design and implement an enterprise data warehouse. Microsoft Certified Trainer Martin Guidry shows how to design fact and dimension tables using both the star and snowflake techniques, use data quality services to cleanse data, and implement an ETL process with SQL Server integration services.

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

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