Data Warehouse with MS SQL Server Outline

Introduction to Data Warehousing

  • Overview of Data Warehousing
  • Considerations for a Data Warehouse Solution
  • Describe the key elements of a data warehousing solution
  • Describe the key considerations for a data warehousing project

Data Warehouse Hardware Considerations

  • Considerations for building a Data Warehouse
  • Data Warehouse Reference Architectures and Appliances
  • Describe key considerations for BI infrastructure.
  • Plan data warehouse infrastructure.

Designing and Implementing a Data Warehouse

  • Logical Design for a Data Warehouse
  • Physical design for a data warehouse
  • Describe a process for designing a dimensional model for a data warehouse
  • Design dimension tables for a data warehouse
  • Design fact tables for a data warehouse
  • Design and implement effective physical data structures for a data warehouse

Creating an ETL Solution with SSIS

  • Introduction to ETL with SSIS
  • Exploring Data Sources
  • Implementing Data Flow
  • Describe the key features of SSIS.
  • Explore source data for an ETL solution.
  • Implement a data flow by using SSIS

Implementing Control Flow in an SSIS Package

  • Introduction to Control Flow
  • Creating Dynamic Packages
  • Using Containers
  • Managing Consistency
  • Implement control flow with tasks and precedence constraints
  • Create dynamic packages that include variables and parameters
  • Use containers in a package control flow
  • Enforce consistency with transactions and checkpoints

Debugging and Troubleshooting SSIS Packages

  • Debugging an SSIS Package
  • Logging SSIS Package Events
  • Handling Errors in an SSIS Package
  • Debug an SSIS package
  • Implement logging for an SSIS package
  • Handle errors in an SSIS package

Implementing an Incremental ETL Process

  • Introduction to Incremental ETL
  • Extracting Modified Data
  • Loading Modified data
  • Plan data extraction
  • Extract modified data

Enforcing Data Quality

  • Introduction to Data Quality
  • Using Data Quality Services to Cleanse Data
  • Using Data Quality Services to Match data
  • Describe how Data Quality Services can help you manage data quality
  • Use Data Quality Services to cleanse your data
  • Use Data Quality Services to match data

Using Master Data Services

  • Master Data Services Concepts
  • Implementing a Master Data Services Model
  • Managing Master Data
  • Creating a Master Data Hub
  • Describe key Master Data Services concepts
  • Implement a Master Data Services model
  • Use Master Data Services tools to manage master data
  • Use Master Data Services tools to create a master data hub

Extending SQL Server Integration Services

  • Using Scripts in SSIS
  • Using Custom Components in SSIS
  • Include custom scripts in an SSIS package
  • Describe how custom components can be used to extend SSIS

Deploying and Configuring SSIS Packages

  • Overview of SSIS Deployment
  • Deploying SSIS Projects
  • Planning SSIS Package Execution
  • Describe considerations for SSIS deployment.
  • Deploy SSIS projects.
  • Plan SSIS package execution.

Consuming Data in a Data Warehouse

  • Introduction to Business Intelligence
  • Introduction to Reporting
  • An Introduction to Data Analysis
  • Describe BI and common BI scenarios
  • Describe how a data warehouse can be used in enterprise BI scenarios
  • Describe how a data warehouse can be used in self-service BI scenarios