Course Description
What is this course about?
A successful data warehouse project involves a set of concepts and methods designed to implement a data store as a basis for the business decision-making process. In this course, you gain the skills needed to develop an effective roadmap to implementing a robust data warehouse solution. Specifically, you learn to apply dimensional modeling to improve business decisions; extract, transform and load opeartional data based on organizational needs; and employ Business Intelligence (BI) tools to slice and dice data.
What is a data warehouse?
A data warehouse consolidates data into a common store and provides a single view of that data across disparate sources. For example, the data warehouse can bring together information from various departments in an organization, enabling a central view across the enterprise.
What are the benefits of a data warehouse?
A data warehouse can maintain history even if the source transactions do not. It can improve data quality by providing a consistent means of describing data and flagging or even fixing bad data. A data warehouse can restructure the data so that it can make sense to business users and other information workers. By doing so, it delivers excellent query performance, even for complex analytical queries, without impacting the operational systems.
What software is used in this course?
SQL Server, Analysis Services, Integration Services, Reporting Services, Report Builder, SharePoint 2010, Visio, and MS Office are used in this course to demonstrate the concepts you learn. This course is applicable to all users regardless of the software tools they are using.
Will I learn how to structure SQL queries to extract information from our data warehouse?
No, this course focuses on the business analysis process that is needed to deliver BI benefits.
This course does not address the mechanics of data warehouse-related tools, but it does demonstrate the utility of these tools to extract meaningful data to aid in the decision-making process.
My organization already has a data warehouse. How can this course help me?
Many data warehouse systems deliver little benefit to the enterprise. This course provides a clear methodology for re-engineering your existing system to give more timely and accurate information and actionable outcomes.
We currently don't have much of anything related to a data warehousing solution in our organization. We are essentially starting from scratch. How can this course help us?
This course helps you define the appropriate questions to ask regarding the implementation of a data warehousing solution. You can then take the proper actions to invest resources in the acquisition and implementation of an effective data warehousing solution for your organization.
I am not an IT professional, is this course right for me?
Yes, you do not need to have a technical background in order to benefit from this course. You learn best practices for managing a data warehousing project.
Is there a case study in the course?
Yes, a continuing case study provides you with the skills to design, plan and implement a data warehouse roadmap.
How much time is spent on each topic?
Content Hours
Introduction to Data Warehousing 1.5
Preparing an implementation plan 2.5
Analyzing organizational data requirements 3.0
Establishing dimensional modeling 4.0
Building the data warehouse 5.0
Leveraging Business Intelligence for data analysis 3.0
Deploying a complete data warehouse solution 4.0
Times, including the workshops, are estimates; exact times may vary according to the needs of each class.
What kinds of hands-on exercises and workshops are in the course?
There are hands-on exercises or workshops in every chapter. These grow in complexity as the course progresses. Exercises include:
- Analyzing the design to identify common flaws
- Collecting and analyzing business requirements
- Completing a database design using a modeling tool
- Designing facts and dimensions
- Extracting, transforming and loading data
- Employing Key Performance Indicators
- Assessing the impact of change requests
I'm attending this course from work using AnyWare - Learning Tree's web-based remote attendance platform. How will that impact what I learn?
You will participate fully in the course and acquire the same knowledge and skills as your classmates who participate in the classroom. You will have the same course materials, be able to easily communicate back and forth and ask questions of your instructors and peers, and you will control an in-classroom workstation dedicated entirely to you. Your instructor will be able to see exactly what you're doing and can interactively offer concrete help.
Agenda
Introduction to Data Warehousing
- Identifying data warehouse components
- Analyzing failed installations
- Determining stakeholders
- Developing a roadmap for project implementation
Preparing an Implementation Plan
Establishing a foundation
- Program vs. project requirements
- Scoping the project and evaluating business justification
- Comparing Agile vs. waterfall methodologies
Monitoring progress of the roadmap
- Tracking stages and communicating milestones
- Managing project expectations
- Assessing risk and mitigation plans
Analyzing Organizational Data Requirements
Determining business requirements
- Assessing the business problem
- Validating organizational goals
- Identifying expected outcomes
Soliciting user requirements
- Choosing diverse participants
- Ranking feedback based on value to the enterprise
Establishing Dimensional Modeling
Modeling the business process
- Star and Snowflake schema
- OLTP vs. OLAP
- Multidimensional cubes
- Hierarchies
Comparing modeling techniques
- Inmon and the relational approach
- Kimball and the dimensional approach
- Selecting the appropriate technique for your environment
Evaluating the model
- Creating fact tables and dimensions
- Pivoting data in Excel to answer business questions
Building the Data Warehouse
Architecting the Physical Database
- Mapping the logical data model to the physical data model
- Evaluating hardware considerations
Ensuring data quality
- Profiling source data
- Configuring the database for change data capture
- Refreshing the data warehouse
Extracting data from multiple sources
- Connecting to different data sources
- Databases
- Spreadsheets
- Text files
Transforming data during migration
- Cleansing and scrubbing data
- Mapping data elements and data types
- Conforming dimensions and facts
Loading data into a data warehouse
- Employing a staging database
- Automating incremental data loading
- Verifying success for data integrity
Leveraging Business Intelligence for Data Analysis
Identifying levels in the BI stack
- Demonstrating SharePoint as a deployment platform
- Utilizing Excel as a client tool for data analysis
- Querying data from the OLAP server
- Enumerating the components of the data tier
Exploring business intelligence tools
- Drill down through multidimensional hierarchies
- Surfacing Key Performance Indicators (KPIs) to evaluate the health of your organization
Deploying a Complete Data Warehouse Solution
Managing system deployment and support
- Assembling the testing group
- Organizing a pilot project
Taking advantage of industry standard tools and techniques
- Identifying available data warehousing tools
- Choosing the right tool based on organizational needs
- Adopting best practices
Planning for growth
- Controlling change management
- Choosing a method of data storage
- HOLAP
- MOLAP
- ROLAP
Audience
Anyone interested in implementing a data warehouse solution in their organization, and those who need to re-engineer an existing data warehouse. Prior experience with a data warehousing implementation and/or databases is helpful but not required.