- Course Objectives: After completing this course, students will be able to:
- Plan and implement cost-effective SQL Server 2012 BI projects
- Design and construct data warehouses
- Extract, transform, and load data with Integration Services
- Develop OLAP cubes and dimensions using Analysis Services
- Create Reporting Services reports using both SQL and MDX
- Apply performance-tuning techniques to get the most from your solutions
Lesson 1:
What Is a Business Intelligence Solution?
Rapid Application Development for BI Solutions
Lesson 2:
A Big Picture Overview
The 10,000-Foot View
Interviewing and Isolating Data
Plan the Solution
Creating the Data Warehouse
Create the ETL Process
Creating a Cube
Creating Reports
Testing the Solution
Approve, Release, and Prepare
Lesson 3:
Planning Solutions
Outline the Steps in the Process
Documenting the Requirements
Locating Data
Defining the Roles
The IT, Security and Licensing Requirements
Documenting the Solution Plan
Implementation
Lesson 4:
Designing Data Warehouses
What Is a Data Warehouse?
What Is a Data Mart?
Competing Definitions
Starting with an OLTP Design
A Typical OLTP Database Design
A Typical Data Warehouse Database Design
Stars and Snowflakes
Dimensional Patterns
Conformed Dimensions
Adding Surrogate Keys
Slowly Changing Dimensions
Lesson 5:
Implementing Data Warehouses with SQL
SQL Server Management Studio
Configuration Manager
Management Studio Windows
Creating Data Warehouse Database
Creating Tables
Creating a Date Dimension Table
Getting Organized
Lesson 6:
ETL Processing with SQL
Performing the ETL Programming
Deciding on Full or Incremental Loading
Isolating the Data to Be Extracted
Identifying the Transformation Logic
Programming Your Transformation Logic
The SQL Query Designer
Updating Your BI Documentation
Building an ETL Script
Working in the Abstract
Lesson 7:
Beginning the ETL Process with SSIS
Starting Your SSIS Project
Adding a Project to an Existing Solution
Renaming Your SSIS Package
The Anatomy of an SSIS Package
Data Connections
Execute SQL Tasks
The Progress/Execution Results Tabs
Resetting Your Destination Database
Lesson 8:
Concluding the ETL Process with SSIS
Data Flows
Outlining a Data Flow Task
Configuring the Data Source
The OLE DB Source Editor
Data Flow Paths
Error Outputs Paths
Configuring the Data Destination
Executing the Entire Package
Lesson 9:
Beginning an SSAS Project
SQL Server vs. Analysis Server Databases
OLAP Cubes vs. Reporting Tables
SQL Server vs. Analysis Server Applications
SSAS Projects
Data Sources
Data Source Views
Dimensions
Lesson 10:
Configuring Dimensions with SSAS
The Dimension Designer
Build, Deploy, and Process
Browsing the Dimension
Lesson 11:
Creating and Configuring SSAS Cubes
Creating Cubes
Processing the Cube
Configuring Cubes
The Browser Tab
The Cube Structure Tab
The Dimension Usage Tab
The Calculations Tab
Making a Test Copy of a Cube
KPIs
Lesson 12:
Additional Cube and Dimension Configurations
Additional Cube Configurations
Additional Dimension Configurations
Managing Your Cubes and Dimension
Lesson 13:
Reporting with SQL Queries
Identifying the Data
Joining Table Data
Ordering Results
Formatting Results Using SQL Functions
Filtering Results
Adding Dynamic Filters with Parameters
Adding Aggregations
Using Subqueries
Creating KPI Queries
Adding Abstraction Layers
Using Your Code in Reporting Applications
Lesson 14:
Reporting with MDX
Key Concepts and Terms
Programming with MDX
The Non Empty Clause
Using Your Code in Reporting Applications
Queries Reporting with Microsoft Excel
Microsoft’s BI Reporting
Excel Reports from the Data Warehouse
Changing Connection Properties
Using Stored Procedures
Working with Excel Reports from a Cube
Testing Your Reports
Creating Charts
Saving to PDF
Lesson 15:
Beginning an SSRS Project
SSRS Architecture
Creating SSRS Objects
Managing the Report
Configuring Reports with SSRS
Creating a Report Template
Adding a Header and Footer
Setting Report Properties
Designing the Header
Renaming Report Items
Using Expressions
Completing the Header
Configuring the Footer
Saving the Report Template
Lesson 16:
Testing and Tuning BI Solutions
Testing the BI Solution
Tuning the BI Solution
Performance Measurements
Creating a Metadata Database